【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。
好学若饥,谦卑若愚