jdbc 批处理

package com.itheima.batch;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import org.junit.Test;

import com.itheima.utils.JdbcUtil;
/**
 * 批处理  st.addBatch(sql);    st.executeBatch()   st.clearBatch();
 *       Statement     
 *             1.sql语句固定
 *             2.可以一次性批量执行不同的sql
 * 
 *       PreparedStatement
 *             1.sql语句的参数是不确定的
 *             2.一次性批量执行相同的sql
 
 create table testbatch(
     id int primary key,
     name varchar(20)
 );
 *
 */
public class BatchTest {

    @Test   
    public void testBatchStatement(){
        Connection con = null;
        Statement st =null;
        
        try {
            con = JdbcUtil.getConnection();
            st = con.createStatement();
            String sql="insert into testbatch values(1,'cgx')";
            String sql2 = "update testbatch set name='aj' where id=1";
            
            st.addBatch(sql);//将sql语句 加入到指处理队列中
            st.addBatch(sql2);
            
            //3.执行
            int []result = st.executeBatch();//int []用于保存每条语句受影响的行数
            for(int i:result){
                System.out.println(i);
            }
            
            //4.清空批处理队列
            st.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.release(null, st, con);
        }
    }
    @Test   
    public void testBatchPreparedStatement(){
        Connection con = null;
        PreparedStatement st =null;
        
        try {
            con = JdbcUtil.getConnection();
            String sql="insert into testbatch values(?,?)";
            st = con.prepareStatement(sql);
            
            //赋值
            for (int i = 0; i <100000000; i++) {//这是一个坑
                st.setInt(1, 10+i);
                st.setString(2, "cgx"+i);
                //添加到队列中  (内存中  溢出)
                st.addBatch();
            }
            
            //执行批处理
            st.executeBatch();
            //清空批处理队列
            st.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.release(null, st, con);
        }
    }
    
    //优化PreparedStatement实现批处理
    @Test   
    public void testBatchPreparedStatement2(){
        Connection con = null;
        PreparedStatement st =null;
        
        try {
            con = JdbcUtil.getConnection();
            String sql="insert into testbatch values(?,?)";
            st = con.prepareStatement(sql);
            
            //赋值
            for (int i = 0; i <1000; i++) {//这是一个坑
                st.setInt(1, 100+i);
                st.setString(2, "cgx"+(100+i));
                
                
                //添加到队列中  (内存中  溢出)
                st.addBatch();
                if(i%100==0){
                   st.executeBatch();//分批执行批处理
                    //清空批处理队列
                   st.clearBatch();
                }
            }
            
            //执行批处理----当不是1000整数倍时，它就会执行
            st.executeBatch();
            //清空批处理队列
            st.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.release(null, st, con);
        }
    }
}