Java 使用 JDBC 往 MySQL 批量插入百万条数据

示例代码：

import com.mysql.jdbc.Connection;

import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class MysqlBatchUtil {

private String sql = "INSERT INTO intest (username, password, create_time) VALUES (?, ?, now())";
private String connectStr = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useServerPrepStmts=false&rewriteBatchedStatements=true";
private String username = "root";
private String password = "123";

private void doStore() throws ClassNotFoundException, SQLException, IOException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = (Connection) DriverManager.getConnection(connectStr, username, password);
        conn.setAutoCommit(false);
int count = 0;
        PreparedStatement psts = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 1; i <= 5000000; i++) {
            psts.setString(1, "user" + i);
            psts.setString(2, "pass" + i);
            psts.addBatch();   // 加入批量处理
            count++;
        }
        psts.executeBatch(); // 执行批量处理
        conn.commit(); // 提交
long end = System.currentTimeMillis();
        System.out.println("数量=" + count);
        System.out.println("运行时间=" + (end - start));
        conn.close();
    }

public static void main(String[] args) {
try {
new MysqlBatchUtil().doStore();
        } catch (ClassNotFoundException | SQLException | IOException e) {
            e.printStackTrace();
        }
    }
}

 

运行结果：

数量=5000000
运行时间=63977