欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

java 下执行mysql 批量插入的几种方法及用时

程序员文章站 2023-12-01 13:48:53
方法1: java code复制代码 代码如下:conn = drivermanager.getconnection(jdbc_url, jdbc_user, jdbc_...

方法1:

java code

复制代码 代码如下:

conn = drivermanager.getconnection(jdbc_url, jdbc_user, jdbc_pass);
        pstmt = conn
                .preparestatement("insert into loadtest (id, data) values (?, ?)");
        for (int i = 1; i <= count; i++) {
            pstmt.clearparameters();
            pstmt.setint(1, i);
            pstmt.setstring(2, data);
            pstmt.execute();
        }

myisam:246.6秒、innodb:360.2秒

方法2: 使用事务,不自动commit

java code

复制代码 代码如下:

conn = drivermanager.getconnection(jdbc_url, jdbc_user, jdbc_pass);
        conn.setautocommit(false);
        pstmt = conn
                .preparestatement("insert into loadtest (id, data) values (?, ?)");
        for (int i = 1; i <= count; i++) {
            pstmt.clearparameters();
            pstmt.setint(1, i);
            pstmt.setstring(2, data);
            pstmt.execute();
            if (i % commit_size == 0) {
                conn.commit();
            }
        }
        conn.commit();

innodb:31.5秒

方法3: executebatch

java code

复制代码 代码如下:

conn = drivermanager.getconnection(jdbc_url
                + "?rewritebatchedstatements=true", jdbc_user, jdbc_pass);
        conn.setautocommit(false);
        pstmt = conn
                .preparestatement("insert into loadtest (id, data) values (?, ?)");
        for (int i = 1; i <= count; i += batch_size) {
            pstmt.clearbatch();
            for (int j = 0; j < batch_size; j++) {
                pstmt.setint(1, i + j);
                pstmt.setstring(2, data);
                pstmt.addbatch();
            }
            pstmt.executebatch();
            if ((i + batch_size - 1) % commit_size == 0) {
                conn.commit();
            }
        }
        conn.commit();

innodb:5.2秒

上面的使用时必须
1)rewritebatchedstatements=true
2)useserverprepstmts=true

方法4:先load再commit

java code

复制代码 代码如下:

conn = drivermanager.getconnection(jdbc_url, jdbc_user, jdbc_pass);
        conn.setautocommit(false);
        pstmt = conn.preparestatement("load data local infile '' "
                + "into table loadtest fields terminated by ','");
        stringbuilder sb = new stringbuilder();
        for (int i = 1; i <= count; i++) {
            sb.append(i + "," + data + "\n");
            if (i % commit_size == 0) {
                inputstream is = new bytearrayinputstream(sb.tostring()
                        .getbytes());
                ((com.mysql.jdbc.statement) pstmt)
                        .setlocalinfileinputstream(is);
                pstmt.execute();
                conn.commit();
                sb.setlength(0);
            }
        }
        inputstream is = new bytearrayinputstream(sb.tostring().getbytes());
        ((com.mysql.jdbc.statement) pstmt).setlocalinfileinputstream(is);
        pstmt.execute();
        conn.commit();