前言
后台开发中,批量往数据库写数据是一个很常见的功能,下面就简单实现一下使用 dynamic-sql 来 batch 写入。
实现介绍
添加依赖
在项目的 pom.xml 中配置 dynamic-sql 及 mysql 相关的依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.mybatis.dynamic-sql</groupId>
<artifactId>mybatis-dynamic-sql</artifactId>
<version>1.4.0</version>
</dependency>
Mapper 接口
新建一个 Mapper 接口,并继承 org.mybatis.dynamic.sql.util.mybatis3.CommonInsertMapper
import com.jack.entity.User;
import org.mybatis.dynamic.sql.util.mybatis3.CommonInsertMapper;
/**
* <p>
* Mapper 接口
* </p>
*
* @author jack
*/
public interface UserDynamicMapper extends CommonInsertMapper<User> {
}
逻辑实现类
批量新增逻辑的实现类,需加入 spring 管理
import com.jack.entity.User;
import com.jack.mapper.UserDynamicMapper;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.dynamic.sql.SqlColumn;
import org.mybatis.dynamic.sql.SqlTable;
import org.mybatis.dynamic.sql.insert.BatchInsertModel;
import org.mybatis.dynamic.sql.insert.render.BatchInsert;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.util.AbstractColumnMapping;
import org.mybatis.dynamic.sql.util.PropertyMapping;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* Description:通过 dynamic-sql 来 batch insert
*
* @author jack
* @version 1.0
*/
@Configuration
public class DynamicBatchInsertMapper {
@Autowired
private SqlSessionFactory sqlSessionFactory;
public void batchInsert(List<User> list) {
try (
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)
) {
UserDynamicMapper mapper = session.getMapper(UserDynamicMapper.class);
SqlTable sqlTable = SqlTable.of("tb_user");
List<AbstractColumnMapping> columnMappingList = new ArrayList<>();
Field[] fields = User.class.getDeclaredFields();
for (Field field : fields) {
String fieldName = field.getName();
if ("serialVersionUID".equals(fieldName)) {
continue;
}
columnMappingList.add(PropertyMapping.of(SqlColumn.of(
transUpper2Under(fieldName), sqlTable), fieldName));
}
BatchInsert<User> batchInsert = BatchInsertModel.withRecords(list)
.withTable(sqlTable)
.withColumnMappings(columnMappingList)
.build()
.render(RenderingStrategies.MYBATIS3);
batchInsert.insertStatements().forEach(mapper::insert);
session.commit();
}
}
/***
* <p>
* 驼峰转下划线
* </p>
* @author jack
*
* @param src 原字符串
* @return java.lang.String
*/
private String transUpper2Under(String src) {
if (src == null || "".equals(src.trim())) {
return src;
}
char[] arr = src.toCharArray();
StringBuilder sb = new StringBuilder(src.length());
for (char c : arr) {
if (Character.isUpperCase(c)) {
sb.append('_').append(Character.toLowerCase(c));
} else {
sb.append(c);
}
}
return sb.toString();
}
}
使用
使用 spring 注入,然后调用即可
@Autowired
private DynamicBatchInsertMapper dynamicBatchInsertMapper;
public void testDynamicBatchInsertUser(int count) {
long t1 = System.currentTimeMillis();
dynamicBatchInsertMapper.batchInsert(getUserList(count));
System.out.println("【DynamicBatchInsertMapper】插入条数:【" + count + "】耗时:【"
+ (System.currentTimeMillis() - t1) + "】");
}
结语
到此,使用 dynamic-sql 来 batch 写入数据的实现就介绍完了,后续继续其他方式的批量写入 …
如果您看到了这里,欢迎和我沟通交流!
一个95后码农
个人博客:fy-blog