【Java】MyBatis 批量插入数据，以及性能测试

测试环境

Java 1.8 ，spring boot 2.6.4具体依赖如下

<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.2.2</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.5.0</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

Mapper.xml配置

	<insert id="saveBatchCustom">
		insert into user
		(name,password,createtime)
		values
		<foreach collection="list" item="item" separator=",">
			(#{item.name},#{item.password},#{item.createTime})
		</foreach>
	</insert>

UserMapper.java

@Mapper
public interface UserMapper extends BaseMapper<UserPO> {
	boolean saveBatchCustom(List<UserPO> list);
}

UserService.java

@Service
public class UserService extends ServiceImpl<UserMapper, UserPO> {
	@Autowired
	private UserMapper userMapper;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	 @Resource
    private  MybatisBatchUtils batchUtils;
    
    @Transactional
    public int mybatisBatch(List<UserPO> list) {
        return batchUtils.batchUpdateOrInsert(list, UserMapper.class, (item,userMapper)->userMapper.insert(item));    	
    }
	@Transactional
	public boolean saveBatchCustom(List<UserPO> list) {
		return userMapper.saveBatchCustom(list);
	}
	@Transactional
	public void jdbcBatchInsert(List<UserPO> list, final int batchSize) {
		String sql = "insert into user (name,password,createtime) values(?,?,?)";
		jdbcTemplate.batchUpdate(sql, list, batchSize, new ParameterizedPreparedStatementSetter<UserPO>() {
			@Override
			public void setValues(PreparedStatement ps, UserPO userPO) throws SQLException {
				ps.setString(1, userPO.getName());
				ps.setString(2, userPO.getPassword());
				ps.setDate(3, new Date(userPO.getCreateTime().getTime()));
			}			
		});	
	}	
}

@Component
public class MybatisBatchUtils {
    
    /**
    * 每次处理1000条
    */
    private static final int BATCH_SIZE = 1000;    
    @Resource
    private SqlSessionFactory sqlSessionFactory;
    
    /**
    * 批量处理修改或者插入
    *
    * @param data     需要被处理的数据
    * @param mapperClass  Mybatis的Mapper类
    * @param function 自定义处理逻辑
    * @return int 影响的总行数
    */
    public  <T,U,R> int batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<T,U,R> function) {
        int i = 1;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            U mapper = batchSqlSession.getMapper(mapperClass);
            int size = data.size();
            for (T element : data) {
                function.apply(element,mapper);
                if ((i % BATCH_SIZE == 0) || i == size) {
                    batchSqlSession.flushStatements();
                }
                i++;
            }
            // 非事务环境下强制commit，事务情况下该commit相当于无效
            batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
        } catch (Exception e) {
            batchSqlSession.rollback();
        } finally {
            batchSqlSession.close();
        }
        return i - 1;
    }
}

测试类

@SpringBootTest
public class BatchInsertDataTest {
	private Logger logger = LoggerFactory.getLogger(getClass());	
	private static final int MAXCOUNT = 10000;
	@Autowired
	private UserMapper userMapper;
	@Autowired
	private UserService userService;
	@BeforeEach
	public void atest1() {
		Wrapper<UserPO> query = new QueryWrapper<UserPO>();
		userMapper.delete(query);
		logger.info("清空数据表");
	}
	@Test
	public void mybatisForeach() {
		StopWatch sw = new StopWatch("testBatchInsert2");
		sw.start();
		List<UserPO> list = new ArrayList<>();
		UserPO user = null;
		for (int i = 0; i < MAXCOUNT; i++) {
			user = new UserPO();
			user.setName("test" + i);
			user.setPassword("p" + i);
			list.add(user);
		}
		userService.saveBatchCustom(list);
		sw.stop();
		logger.info("saveBatchCustom:{}", sw.getTotalTimeMillis());
	}
	@Test
	public void mybatisPlusBatchSave() {
		StopWatch sw = new StopWatch("mybatisPlusBatchSave");
		sw.start();
		List<UserPO> list = new ArrayList<>();
		UserPO user = null;
		for (int i = 0; i < MAXCOUNT; i++) {
			user = new UserPO();
			user.setName("test" + i);
			user.setPassword("p" + i);
			list.add(user);
		}
		userService.saveBatch(list, 500);
		sw.stop();
		logger.info("mybatisPlusBatchSave:{}", sw.getTotalTimeMillis());
	}
	@Test
	public void jdbcBatchSave() {
		StopWatch sw = new StopWatch("jdbcBatchSave");
		sw.start();
		List<UserPO> list = new ArrayList<>();
		UserPO user = null;
		for (int i = 0; i < MAXCOUNT; i++) {
			user = new UserPO();
			user.setName("test" + i);
			user.setPassword("p" + i);
			user.setCreateTime(new Date());
			list.add(user);
		}
		userService.jdbcBatchInsert(list, 500);
		sw.stop();
		logger.info("jdbcBatchSave:{}", sw.getTotalTimeMillis());
	}
}

测试结果

Mybatis Batch 提交和 Jdbc批处理大体相近。性能略低于jdbc。