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

测试环境

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

		
			org.springframework.boot
			spring-boot-starter-web
		
		
			org.mybatis.spring.boot
			mybatis-spring-boot-starter
			2.2.2
		
		
			com.baomidou
			mybatis-plus-boot-starter
			3.5.0
		
		
			mysql
			mysql-connector-java
			runtime
		
		
			com.zaxxer
			HikariCP
		
		
			org.projectlombok
			lombok
			provided
		
		
			org.springframework.boot
			spring-boot-starter-test
			test
		
	

Mapper.xml配置

	
		insert into user
		(name,password,createtime)
		values
		
			(#{item.name},#{item.password},#{item.createTime})
		
	

UserMapper.java

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

UserService.java

@Service
public class UserService extends ServiceImpl {
	@Autowired
	private UserMapper userMapper;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Transactional
	public boolean saveBatchCustom(List list) {
		return userMapper.saveBatchCustom(list);
	}

	@Transactional
	public void jdbcBatchInsert(List list, final int batchSize) {
		String sql = "insert into user (name,password,createtime) values(?,?,?)";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				UserPO userPO = list.get(i);
				ps.setString(1, userPO.getName());
				ps.setString(2, userPO.getPassword());
				ps.setDate(3, new Date(userPO.getCreateTime().getTime()));
			}
			@Override
			public int getBatchSize() {
				return batchSize < 1 ? 1000 : batchSize;
			}
		});
	}
}

测试类

@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 query = new QueryWrapper();
		userMapper.delete(query);
		logger.info("清空数据表");
	}
	@Test
	public void mybatisForeach() {
		StopWatch sw = new StopWatch("testBatchInsert2");
		sw.start();
		List 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 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 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());
	}
}

测试结果

在个人电脑上测试了一个大体范围。具体可能因环境而异，不会有太大差距

1. 数据量大于1000
   jdbcBatchSave > mybatisPlusBatchSave >mybatisForeach并随着数据量越大差距越大。
2. 小于1000大于300
   jdbcBatchSave > mybatisPlusBatchSave >mybatisForeach 之间性能差距在缩小，mybatisPlusBatchSave与mybatisForeach每次测试数据不固定，
3. 小于 300
   推荐使用mybatisPlusBatchSave,