JDBC实现批量操作

JDBC实现批量操作

• 建表sql

CREATE TABLE `temp` (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR (30) NOT NULL COMMENT '姓名'
)

• 批量insert

@Test
public void batchInsert() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        //mysql需要设置rewriteBatchedStatements=true，否则即使使用executeBatch也不会批量处理
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.1.200:3306/test?rewriteBatchedStatements=true", "admin", "test123");
        PreparedStatement preparedStatement = connection.prepareStatement("insert into temp(name) values(?)");

        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.addBatch();

            if (i % 500 == 0) {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        preparedStatement.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println(end - start);
    } catch (Exception e) {
        e.printStackTrace();
    }
}