package java5.blob;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.junit.Test;

import java3.util.JDBCUtils;

/*
 * 	使用PreparedStatement实现批量数据的操作
 * update、delete本身就具有批量操作的效果
 * 所以主要研究如何用PreparedStatement实现更高效的批量插入
 */
public class InsertTest {
	//批量插入方式二：使用PreparedStatement
	@Test
	public void testInsert1() throws Exception{
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			long start = System.currentTimeMillis();
			conn = JDBCUtils.getConnedtion();
			String sql="insert into goods(name)values(?)";
			ps = conn.prepareStatement(sql);
			for(int i=1;i<=200;i++){
				ps.setObject(1, "name_"+i);
				ps.execute();
			}
			long end = System.currentTimeMillis();
			
			System.out.println("花费的时间为："+(end-start));
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource(conn, ps);
		}
		
	}
	
	/*
	 * 	批量插入的方式三：
	 * 1、addBatch()、executeBatch()、clearBatch()
	 * 2、mysql服务器默认关闭批处理，需要配置参数，让mysql开启批处理的支持
	 * 		?rewriteBatchedStatements=true  写在配置文件的url后面
	 * 
	 */
	@Test
	public void testInsert2() throws Exception{
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			long start = System.currentTimeMillis();
			
			conn = JDBCUtils.getConnedtion();
			String sql="insert into goods(name)values(?)";
			ps = conn.prepareStatement(sql);
			for(int i=1;i<=20000;i++){
				ps.setObject(1, "name_"+i);
				//1.赞sql
				ps.addBatch();
				if(i%500==0){
					//2.执行batch
					ps.executeBatch();
					//3.清空batch
					ps.clearBatch();
				}
			}
			
			long end = System.currentTimeMillis();
			System.out.println("花费的时间为："+(end-start));
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource(conn, ps);
			
		}
		
	}
	
	//批量插入的方式四：设置连接不允许自动提交数据
	@Test
	public void testInsert3() throws Exception{
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			long start = System.currentTimeMillis();
			
			conn = JDBCUtils.getConnedtion();
			conn.setAutoCommit(false);
			String sql="insert into goods(name)values(?)";
			ps = conn.prepareStatement(sql);
			for(int i=1;i<=20000;i++){
				ps.setObject(1, "name_"+i);
				
				//1.赞sql
				ps.addBatch();
				
				if(i%500==0){
					//2.执行batch
					ps.executeBatch();
					
					//3.清空batch
					ps.clearBatch();
				}
			}
			
			//提交数据
			conn.commit();
			
			long end = System.currentTimeMillis();
			System.out.println("花费的时间为："+(end-start));
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			
			JDBCUtils.closeResource(conn, ps);
		}
		
		
		
	}
}