java mysql 用addBatch和executeBatch优化批量插入

注意

1 最新的jdbc驱动

2 连接url 中 rewriteBatchedStatements=true

3 每批数据大于3

http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html

实验记录：打开了rewriteBatchedStatements后

打开rewriteBatchedStatements后，根据wireshark嗅探出的mysql报文可以看出

batchDelete(10条记录)  =>  发送一次请求，内容为”delete from t where id = 1; delete from t where id = 2; delete from t where id = 3; ….”

batchUpdate(10条记录)  =>  发送一次请求，内容为”update t set … where id = 1; update t set … where id = 2; update t set … where id = 3 …”

batchInsert(10条记录)  =>   发送一次请求，内容为”insert into t (…) values (…) , (…), (…)”

对delete和update，驱动所做的事就是把多条sql语句累积起来再一次性发出去；而对于insert，驱动则会把多条sql语句重写成一条风格很酷的sql语句，然后再发出去。 官方文档说，这种insert写法可以提高性能（”This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements”）

下载最新的JDBC的驱动程序。
MYSQL URL的配置参数如下：
jdbc:mysql://54.200.190.80:3306/ccb_ucstar?rewriteBatchedStatements=true

private void batchParseGroup(){
        Connection con= null;
        PreparedStatement ps =null;
        try {
            con= DbConnectionManager.getConnection();
            con.setAutoCommit(false);
            String sql = " insert into jivegroup(uri,groupname,pgroupid,description,creationdate,modificationdate,priority,selfpriority) values(?,?,?,?,?,?,?,?)";
            ps= con.prepareStatement(sql);
            for(int i=0;i<groupList.size();i++){
                Group group = groupList.get(i);
                ps.setString(1, group.getUri());
                ps.setString(2, group.getName());
                ps.setString(3, group.getPgroupId());
                ps.setString(4, group.getName());
                ps.setString(5, ""+System.currentTimeMillis());
                ps.setString(6, ""+System.currentTimeMillis());
                ps.setInt(7, group.getPriority());
                ps.setInt(8, group.getPriority());
                ps.addBatch();
                if(i%100==0){
                    ps.executeBatch();
                }
            }
            con.commit();
            ps.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DbConnectionManager.closeConnection(ps, con);
        }
    }

© 2015 - 2016, 新之助meow. 原创文章转载请注明： 转载自http://www.xinmeow.com