使用JDBC高效批量新增数据

package com.sitech.ddoe.test;

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;

public class BatchInsertTest {

    private String insertSql;
    private String connectUrl;
    private String username;
    private String password;
    // 每commitNum提交一次
    private int commitNum = 6000;

    public BatchInsertTest() {
        connectUrl = "jdbc:mysql://ip:port/dbname";
        connectUrl += "?useServerPrepStmts=false&rewriteBatchedStatements=true";
        insertSql = "INSERT INTO log_performance (msg_id,consume_time) " + " VALUE (?,?)";
        username = "****";
        password = "****";
    }

    public static void main(String[] args) throws IOException {
        String srcFile = "D:/Temp/log/performance-analysis.log";
        Long beginTime = System.currentTimeMillis();
        new BatchInsertTest().storeToDb(srcFile);
        long time = System.currentTimeMillis() - beginTime;
        System.out.println("Consume time :" + String.valueOf(time / 1000) + "s");
    }

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

    private void doStore(BufferedReader reader) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(connectUrl, username, password);
        conn.setAutoCommit(false); // 设置手动提交
        int count = 0;
        PreparedStatement ps = conn.prepareStatement(insertSql);
        String str = null;
        while (null != (str = reader.readLine())) {
            String[] s = str.substring(str.indexOf("]") + 2).split("-");
            ps.setLong(1, Long.valueOf(s[0]));
            ps.setLong(2, Long.valueOf(s[1]));
            ps.addBatch();
            count++;
            // 每commitNum条记录插入一次
            if (count % commitNum == 0) {
                ps.executeBatch();
                conn.commit();
            }
        }
        ps.executeBatch();
        conn.commit();
        System.out.println("All down : " + count);
        conn.close();
    }

}

文章出处:

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