今回やりたいこと
Doma, Doma2はSIerで人気[要出典]で、SI案件ではよく見るDBアクセスフレームワークですね。
しかし、Doma2には制約があり、マルチスレッドからアクセスできなかったり（実際にやると例外吐かれる）、
values句に複数レコードあるinsertの機能を標準で用意していなかったり…(@BatchInsertはinsert文を複数回投げる仕様)。
そこで今回はvalues句に複数レコードある場合のinsertの機能をDomaから使えるように、ゴリゴリ書いてみました。
※MySQLの場合は、values句に複数レコード書いて1つのinsertにまとめたほうが圧倒的に速いんです。
↓参考資料
MySQL Connector/Jでプロパティをあれこれ変えてベンチマークその1：バッチ編 - Qiita
追記
ゴリゴリ書いたところ、MySQLのJDBCにrewriteBatchedStatements=trueという設定を行うと、複数のinsertを1つのinsertにまとめてくれるらしいことをコメントにて知ったので、設定例を載せておく。
設定例
今回試した環境では２つ設定する必要がありました。
-
- JDBCに
rewriteBatchedStatements=trueを設定
- JDBCに
-
-
BatchSizeをデフォルトから変更
-
2の数字については、こちらの記事によると100以下がいいとのことなので、今回は100にしました。
JDBC経由で100万件取得・追加してみた - kagamihogeの日記
設定方法
Daoのメソッド毎に設定する方法
batchSizeが設定可能です。
後述するConfigで設定している、又はクエリ毎に設定したいという場合でなければ不要です。
@BatchInsert(batchSize = 100)
int[] batchInsert(List<Main.Entity2> entityList);
Configで設定する方法
※↓のConfigのコードはHikariCPを使う場合の設定例です。
//【ここ】と書いた部分が今回該当する設定となります。
今回はMariaDBを使いましたが、MySQLでもたぶん同じだと思います。
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.seasar.doma.SingletonConfig;
import org.seasar.doma.jdbc.Config;
import org.seasar.doma.jdbc.Naming;
import org.seasar.doma.jdbc.dialect.Dialect;
import org.seasar.doma.jdbc.dialect.MysqlDialect;
import org.seasar.doma.jdbc.tx.LocalTransactionDataSource;
import org.seasar.doma.jdbc.tx.LocalTransactionManager;
import org.seasar.doma.jdbc.tx.TransactionManager;
import javax.sql.DataSource;
@SingletonConfig
public class DBConfig implements Config {
private static final DBConfig CONFIG = new DBConfig();
private final Dialect dialect;
private final LocalTransactionDataSource dataSource;
private final TransactionManager transactionManager;
private DBConfig() {
dialect = new MysqlDialect();
var config = new HikariConfig();
config.setDriverClassName("org.mariadb.jdbc.Driver");
config.setJdbcUrl("jdbc:mariadb://localhost:3306");
config.addDataSourceProperty("user", "root");
config.addDataSourceProperty("password", "password");
config.addDataSourceProperty("cachePrepStmts", true);
config.addDataSourceProperty("prepStmtCacheSize", 250);
config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
config.addDataSourceProperty("useServerPrepStmts", true);
config.addDataSourceProperty("useLocalSessionState", true);
config.addDataSourceProperty("rewriteBatchedStatements", true);//【ここ】
config.addDataSourceProperty("cacheResultSetMetadata", true);
config.addDataSourceProperty("cacheServerConfiguration", true);
config.addDataSourceProperty("elideSetAutoCommits", true);
config.addDataSourceProperty("maintainTimeStats", false);
config.addDataSourceProperty("maintainTimeStats", false);
var hikariDataSource = new HikariDataSource(config);
dataSource = new LocalTransactionDataSource(hikariDataSource);
transactionManager = new LocalTransactionManager(
dataSource.getLocalTransaction(getJdbcLogger()));
}
@Override
public Dialect getDialect() {
return dialect;
}
@Override
public Naming getNaming() {
return Naming.LENIENT_SNAKE_LOWER_CASE;
}
@Override
public int getBatchSize() {
return 100;//【ここ】
}
@Override
public DataSource getDataSource() {
return dataSource;
}
@Override
public TransactionManager getTransactionManager() {
return transactionManager;
}
public static DBConfig singleton() {
return CONFIG;
}
}
ちなみに↓はHikariCP作者によるMySQLのJDBCの推奨設定らしいです。
MySQL Configuration · brettwooldridge/HikariCP Wiki
JDBCにて1つのinsertをまとめているため、Domaのログではinsertが複数になってますけど、DB側のgeneral_logを見るとちゃんと1つのinsertにまとまってます。
自前でゴリゴリ書いたコード（一応残しておく）
import org.seasar.doma.Dao;
import org.seasar.doma.jdbc.Config;
import java.sql.SQLException;
import java.util.List;
@Dao
public interface BulkDao {
DBUtils dbUtils = new DBUtils();
/**
* valuesのデータが複数あるinsertを実行. 入力が0件以外の場合は組み立てたinsert文をキャッシュします.
* 入力が0件の場合は、insert文の組み立て、キャッシュ処理を実行しません.
*
* @param entityList valuesに設定するエンティティのリスト.
* @param <E> insertデータの型
* @return executeUpdateの結果値を返す.入力が0件の場合はexecuteUpdateを実行せず、0を返す.
*/
default <E> int bulkInsertMultiValues(List<E> entityList) {
return dbUtils.bulkInsertMultiValues(
() -> {
try {
return Config.get(this).getDataSource().getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
},
entityList);
}
}
import lombok.RequiredArgsConstructor;
import org.seasar.doma.Table;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Supplier;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
public class DBUtils {
/**
* valuesのデータが複数あるinsertを実行. 入力が0件以外の場合は組み立てたinsert文をキャッシュします.
* 入力が0件の場合は、insert文の組み立て、キャッシュ処理を実行しません.
*
* @param connectionSupplier コネクション取得関数
* @param entityList valuesに設定するエンティティのリスト.
* @param <E> insertデータの型
* @return executeUpdateの結果値を返す.入力が0件の場合はexecuteUpdateを実行せず、0を返す.
*/
<E> int bulkInsertMultiValues(
Supplier<Connection> connectionSupplier, List<E> entityList) {
if (!entityList.isEmpty()) {
var clazz = entityList.get(0).getClass();
var cachedData = _BulkDaoMethod.getSqlWithMethod(clazz);
var sql = new StringBuilder(cachedData.sql);
var params = _BulkDaoMethod.getParams(entityList, cachedData, sql);
return _BulkDaoMethod.executeUpdate(connectionSupplier, sql, params);
}
return 0;
}
}
/** キャッシュする内容 */
@RequiredArgsConstructor
class _SQLWithMethod {
final CharSequence sql;
final CharSequence values;
final List<Method> methodsList;
}
/** BulkDaoの中で使用するメソッド */
class _BulkDaoMethod {
static ConcurrentHashMap<Class, _SQLWithMethod> cache = new ConcurrentHashMap<>();
static _SQLWithMethod getSqlWithMethod(Class<?> clazz) {
return cache.computeIfAbsent(
clazz,
_c -> {
Table tableAnnotation = clazz.getAnnotation(Table.class);
StringBuilder sql = new StringBuilder();
sql.append("insert into ").append(tableAnnotation.name());
var methodsList =
Arrays.stream(clazz.getDeclaredMethods())
.filter(e -> e.getName().startsWith("get"))
.collect(Collectors.toList());
var columuns =
methodsList.stream()
.map( // メソッド名からカラム名へ変換
e -> camelToSnake(e.getName().substring(3)))
.collect(Collectors.toList());
sql.append(" (").append(String.join(",", columuns)).append(")").append("values ");
var values = new StringBuilder("(");
IntStream.range(0, columuns.size() - 1).forEach((i) -> values.append("?,"));
values.append("?),");
return new _SQLWithMethod(sql, values, methodsList);
});
}
static <E> List<Object> getParams(
List<E> entityList, _SQLWithMethod cachedData, StringBuilder sql) {
var params = new ArrayList<>();
entityList.forEach(
values -> {
cachedData.methodsList.forEach(
method -> {
try {
var p = method.invoke(values);
params.add(p);
} catch (IllegalAccessException | InvocationTargetException e) {
throw new RuntimeException(e);
}
});
sql.append(cachedData.values);
});
sql.deleteCharAt(sql.length() - 1);
return params;
}
static int executeUpdate(
Supplier<Connection> connectionSupplier, StringBuilder sql, List<Object> params) {
Connection conn = connectionSupplier.get();
try (var preparedStatement = conn.prepareStatement(sql.toString());){
var i = 0;
for (var p : params) {
++i;
preparedStatement.setObject(i, p);
}
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private static String camelToSnake(String snake) {
var sb = new StringBuilder(snake.length()<<1);
var input = snake.toCharArray();
sb.append(Character.toLowerCase(input[0]));
for (int i = 1; i < input.length; i++) {
var c = input[i];
if (Character.isUpperCase(c)) {
sb.append('_').append(Character.toLowerCase(c));
} else {
sb.append(c);
}
}
return sb.toString();
}
}
あ、java11環境で書いたので、varを書きまくってるので、Java8とかだとそのままだと動かないです…。
それと、lombokを使用していることを前提としています。
dbは小文字スネークケース前提です。
使用例
@org.seasar.doma.Table(name="kintai.work_time")
@org.seasar.doma.Entity
@Data
public static class Entity2{
int workTimeId;
String remarks;
}
var list = new ArrayList<ExampleEntity>()
list.add(exampleEntity0);
list.add(exampleEntity1);
BulkDao.bulkInsertMultiValues(list);
このコードで工夫したところ
ClassオブジェクトとInsert文をキャッシュさせた。
キャッシュのキーがジェネリクスになってるので、実際のオブジェクトから取るしかなく（他にも方法はあるかもしれないが、多分あっても面倒な気がする）…。
感想
preparedStatement#setObjectなど、indexの指定をするものとか、
状態を持つような設計のStringBuiderと関数型って相性悪いなあ。
チェック例外もだけど。