Doma and Doma2 are popular [citation needed] in SIer, and they are DB access frameworks that are often seen in SI projects.
However, Doma2 has restrictions, and it cannot be accessed from multiple threads (exception is thrown when actually doing it).
The insert function with multiple records in the values clause is not provided as standard ... (@BatchInsert is an insert statement Specifications to throw multiple times).
So, this time, I wrote it so that the insert function when there are multiple records in the values clause can be used from Doma.
As I wrote it, I learned in the comments that if you set rewriteBatchedStatements = true in JDBC of MySQL, it seems that multiple inserts will be combined into one insert, so I will post a setting example.
In the environment I tried this time, I had to set two.
--1. Set rewriteBatchedStatements = true in JDBC
--2 Change BatchSize from the default
As for the number 2, according to this article, 100 or less is good, so I set it to 100 this time. I tried to get and add 1 million items via JDBC -Diary of kagamihoge
batchSize can be set. It is not necessary unless it is set in Config described later or you want to set it for each query.
@BatchInsert(batchSize = 100)
int[] batchInsert(List<Main.Entity2> entityList);
// [here] is the setting that corresponds to this time.
I used MariaDB this time, but I think it's probably the same with 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);//【here】
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;//【here】
}
@Override
public DataSource getDataSource() {
return dataSource;
}
@Override
public TransactionManager getTransactionManager() {
return transactionManager;
}
public static DBConfig singleton() {
return CONFIG;
}
}
By the way, ↓ seems to be the recommended setting of JDBC of MySQL by HikariCP author. MySQL Configuration · brettwooldridge/HikariCP Wiki
Since one insert is put together in JDBC, there are multiple inserts in the Doma log, but if you look at the general_log on the DB side, it is properly organized into one 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();
/**
*Execute insert with multiple values data.If the number of inputs is other than 0, the assembled insert statement will be cached..
*If there are 0 inputs, insert statement assembly and cache processing will not be executed..
*
* @list of entities to set in param entityList values.
* @param <E>insert data type
* @return Returns the result value of executeUpdate.If there are 0 inputs, executeUpdate is not executed and 0 is returned..
*/
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 {
/**
*Execute insert with multiple values data.If the number of inputs is other than 0, the assembled insert statement will be cached..
*If there are 0 inputs, insert statement assembly and cache processing will not be executed..
*
* @param connectionSupplier Connection acquisition function
* @list of entities to set in param entityList values.
* @param <E>insert data type
* @return Returns the result value of executeUpdate.If there are 0 inputs, executeUpdate is not executed and 0 is returned..
*/
<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;
}
}
/**Content to cache*/
@RequiredArgsConstructor
class _SQLWithMethod {
final CharSequence sql;
final CharSequence values;
final List<Method> methodsList;
}
/**Methods used in 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( //Convert method name to column name
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();
}
}
Ah, since I wrote it in the java11 environment, I'm writing var all the time, so it doesn't work with Java 8 as it is ...
Also, I'm assuming you're using lombok.
db is a lowercase snake case assumption.
@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);
Cached Class object and Insert statement. Since the cache key is generic, I have to take it from the actual object (there may be other ways, but I think it's annoying) ...
Something that specifies an index, such as preparedStatement # setObject,
Functional type is incompatible with StringBuider designed to have a state.
There are also check exceptions.
Recommended Posts