使用JDBC在MySQL数据库中快速批量插入数据

 

 

 

-- 批量插入
INSERT INTO TABLE (col1, col2, col3)
VALUES
    (val1, val2, val3),
    (val1, val2, val3),
    (val1, val2, val3)
;

-- 单条插入
INSERT INTO TABLE (col1, col2, col3) VALUES (val1, val2, val3);
INSERT INTO TABLE (col1, col2, col3) VALUES (val1, val2, val3);
INSERT INTO TABLE (col1, col2, col3) VALUES (val1, val2, val3);

-- 批量插入的效率比单条插入高N倍。

 

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DbStoreHelper {

    private String insert_sql;
    private String charset;
    private boolean debug;

    private String connectStr;
    private String username;
    private String password;

    public DbStoreHelper() {
        connectStr = "jdbc:mysql://localhost:3306/db_ip";
        // connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";
        insert_sql = "INSERT INTO tb_ipinfos (iplong1,iplong2,ipstr1,ipstr2,ipdesc) VALUES (?,?,?,?,?)";
        charset = "gbk";
        debug = true;
        username = "root";
        password = "***";
    }

    public void storeToDb(String srcFile) throws IOException {
        BufferedReader bfr = new BufferedReader(new InputStreamReader(new FileInputStream(srcFile), charset));
        try {
            doStore(bfr);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            bfr.close();
        }
    }

    private void doStore(BufferedReader bfr) throws ClassNotFoundException, SQLException, IOException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(connectStr, username,password);
        conn.setAutoCommit(false); // 设置手动提交
        int count = 0;
        PreparedStatement psts = conn.prepareStatement(insert_sql);
        String line = null;
        while (null != (line = bfr.readLine())) {
            String[] infos = line.split(";");
            if (infos.length < 5)   continue;
            if (debug) {
                System.out.println(line);
            }
            psts.setLong(1, Long.valueOf(infos[0]));
            psts.setLong(2, Long.valueOf(infos[1]));
            psts.setString(3, infos[2]);
            psts.setString(4, infos[3]);
            psts.setString(5, infos[4]);
            psts.addBatch();          // 加入批量处理
            count++;
        }
        psts.executeBatch(); // 执行批量处理
        conn.commit();  // 提交
        System.out.println("All down : " + count);
        conn.close();
    }

}

 

在MySQL JDBC连接字符串中还可以加入参数，

rewriteBatchedStatements=true，mysql默认关闭了batch处理，通过此参数进行打开，这个参数可以重写向数据库提交的SQL语句，具体参见：http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html

useServerPrepStmts=false，如果不开启(useServerPrepStmts=false)，使用com.mysql.jdbc.PreparedStatement进行本地SQL拼装，最后送到db上就是已经替换了?后的最终SQL.

 

在此稍加改进，连接字符串中加入下面语句（代码构造方法中去掉注释）：

connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";

 

ref: https://www.cnblogs.com/hhthtt/p/11008307.html