【Java】Java で 複数 INSERT を実行する

■ はじめに

 * Java で 複数 INSERT を実行することを考える

  => 色々な方法があるが、パフォーマンスがよく、実装しやすいものがいい

■ 実行環境

 * OS : Windows10
 * DB : MySQL5.7

■ テストデータ

CREATE TABLE `person` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NULL DEFAULT NULL,
  `sex` CHAR(1) NULL DEFAULT 'f',
  `birth_date` DATE NOT NULL,
  `createdate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(`name`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

■ サンプル

 * PreparedStatement.addBatch() / executeBatch() を使う
  => PreparedStatement.addBatch() で貯めて、PreparedStatement.executeBatch()で実行する

https://docs.oracle.com/javase/jp/8/docs/api/java/sql/PreparedStatement.html

InsertDemo.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertDemo {

  private final static String URL = "jdbc:mysql://localhost:3306/sampledb01?useSSL=false&characterEncoding=UTF-8";
  private final static String USERNAME = "root";
  private final static String PASSWORD = "password";
  private final static String SQL = "INSERT INTO person(name, sex, birth_date) VALUES (?, ?, ?);";

  public static void main(String[] args) {
    String[][] samplePeople = { { "Mike", "m", "2001-01-09" }, { "Tom", "m", "1982-11-29" },
        { "Smith", "m", "1976-03-14" }, { "Ken", "m", "1982-11-29" }, { "Naomi", "f", "1962-06-11" },
        { "Sam", "m", "1982-01-30" }, { "Amy", "f", "1933-09-23" }, { "Cathy", "f", "1921-07-12" },
        { "Bob", "m", "1956-12-29" }, { "Tommy", "m", "1999-02-21" }, };

    System.out.println("Start");
    long start = System.currentTimeMillis();
    try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);) {
      try (PreparedStatement preparedStatement = connection.prepareStatement(SQL);) {
        connection.setAutoCommit(false);

        for (int i = 0; i < samplePeople.length; i++) {
          preparedStatement.setString(1, samplePeople[i][0]);
          preparedStatement.setString(2, samplePeople[i][1]);
          preparedStatement.setString(3, samplePeople[i][2]);
          preparedStatement.addBatch();
          System.out.println(preparedStatement.toString());
        }
        int[] result = preparedStatement.executeBatch();
        System.out.println("Count : " + result.length);

        connection.commit();
      } catch (SQLException ex) {
        ex.printStackTrace();
        connection.rollback();
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      long end = System.currentTimeMillis();
      System.out.println("[Done]" + (end - start));
    }
  }
}

出力結果

Start
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Mike', 'm', '2001-01-09');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Tom', 'm', '1982-11-29');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Smith', 'm', '1976-03-14');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Ken', 'm', '1982-11-29');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Naomi', 'f', '1962-06-11');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Sam', 'm', '1982-01-30');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Amy', 'f', '1933-09-23');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Cathy', 'f', '1921-07-12');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Bob', 'm', '1956-12-29');
com.mysql.jdbc.JDBC42PreparedStatement@45ff54e6: INSERT INTO person(name, sex, birth_date) VALUES ('Tommy', 'm', '1999-02-21');
Count : 10
[Done]431

参考文献

https://dev.classmethod.jp/server-side/java/java_mysql_transaction/
http://kagamihoge.hatenablog.com/entry/20131210/1386672188
http://d.hatena.ne.jp/sh2/20090528