batch-insert-dynamic-sql

前言

后台开发中，批量往数据库写数据是一个很常见的功能，下面就简单实现一下使用 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 写入数据的实现就介绍完了，后续继续其他方式的批量写入 …