Solve the problem that sharding JDBC does not support batch import

Directory sharding JDBC does not support batch import sharding-jdbc does not support batch update and modification of multiple sql statements

sharding JDBC does not support batch import

package com.ydmes.service.impl.log;
import com.ydmes.domain.entity.log.BarTraceBackLog;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import java.util.List;
@Component
public class BarTraceBackLogBatchServiceImpl implements ApplicationContextAware {
private static ApplicationContext applicationContext;
public void batchInsertBarTraceBackLogs(List<BarTraceBackLog> barTraceBackLogs) {
DefaultTransactionDefinition definition = new DefaultTransactionDefinition();
definition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
definition.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
DataSourceTransactionManager transactionManager = (DataSourceTransactionManager)
applicationContext.getBean("shardingTransactitonManager");
TransactionStatus transactionStatus = transactionManager.getTransaction(definition);
for (BarTraceBackLog barTraceBackLog : barTraceBackLogs) {
//UserDao Have create Method, single insert 
applicationContext.getBean(BarTraceBackLogServiceImpl.class).insertSelective(barTraceBackLog);
}
transactionManager.commit(transactionStatus);
}
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
this.applicationContext = applicationContext;
}
}

sharding-jdbc does not support batch updating of multiple sql statements

Today, the project has a need to brush data, and the modification of a single piece of data is too slow. I want to write a batch of update, and I wrote sql twice. I found that the sub-table is not supported. sharding-jdbc will only fill my first table name into the correct table name, and the following table names have not been modified.

mybastis is as follows:

<update id="batchUpdate" >
<foreach collection="userList" item="item" index="index" separator=";">
update t_user_data set `province_id`=#{item.provinceId} where member_id = #{item.memberId}
</foreach>
</update>

sql is as follows:

update t_user_data_1 set `province_id`=?,`region_id`=? where member_id = ? ;
update t_user_data set `province_id`=?,`region_id`=? where member_id = ? ;

Thus, these multiple sql statement updates are not supported.

Revise ideas

Since the table name does not support automatic multiple, I will replace the table name myself.

First, the table name is obtained in the code according to the table sub-table rules, and then the value is assigned on each sql statement

The code is as follows:

userData1.setTableName("t_user_data_" + userData.getMemberId() % 8);

For example, if my table is divided into 8 tables, take the remainder according to 8 so that each piece of data corresponds to the table name.

mybastis is as follows:

<update id="batchUpdate" >
<foreach collection="userList" item="item" index="index" separator=";">
update ${item.tableName} set `province_id`=#{item.provinceId} where member_id = #{item.memberId}
</foreach>
</update>

Note 1 here: Table names and column names need to be passed in with ${}, and parameters use # {}.

The final sql is as follows:

update t_user_data_1 set `province_id`=?,`region_id`=? where member_id = ? ;
update t_user_data_2 set `province_id`=?,`region_id`=? where member_id = ? ;

This solves the problem of batch update.