Java批量执行sql

直接上代码

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;


public class sqlExecute {

    public void executeBatch() throws Exception {

        DbUtil dbUtil = new DbUtil();
        Connection con = dbUtil.getCon();
        String sql = "insert into pousheng_member_channel_rels (member_id,type,register_at) select id,\"YY\" as TYPE,register_at from mc_profiles limit 100000 offset ";


        Statement st = null;
        int j=0;
        try {
            long start = System.currentTimeMillis();
            st = con.createStatement();
            con.setAutoCommit(false);
            j=4000000;
            for (int i=0;i<20;i++,j+=100000){
                String s=sql+j;
                System.out.println(s);
                st.addBatch(s);
            }
            st.executeBatch();
            st.clearBatch();
            con.commit();
            long end = System.currentTimeMillis();
            System.out.println("200万数据插入耗时："+(end-start)/1000+"s");
        } catch (Exception e) {
            System.out.println("起点"+j+"错误请重新执行脚本");
            e.printStackTrace();
        } finally {
            dbUtil.closeCon(con);
        }
    }
}

单条sql执行每执行一条会有io的开销，导致耗时长，这里使用的是批处理提交事务的方式，一次提交20条sql，每次执行插入10万条数据，这样减少了io的开销，速度也会快很多