前言:
这里我列举了MyBatis和MyBatis-Plus常用的五种批量插入的方式,进行了详细的总结归纳,写的非常详细,整体思路清晰明了,只分享干货。
目录
六、MyBatis-Plus提供的InsertBatchSomeColumn方法(推荐)
一、准备工作
1、导入pom.xml依赖
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <scope>runtime</scope>
- </dependency>
- <!--Mybatis依赖-->
- <dependency>
- <groupId>org.mybatis.spring.boot</groupId>
- <artifactId>mybatis-spring-boot-starter</artifactId>
- <version>2.2.2</version>
- </dependency>
- <!--Mybatis-Plus依赖-->
- <dependency>
- <groupId>com.baomidou</groupId>
- <artifactId>mybatis-plus-boot-starter</artifactId>
- <version>3.5.2</version>
- </dependency>
- <dependency>
- <groupId>org.projectlombok</groupId>
- <artifactId>lombok</artifactId>
- <optional>true</optional>
- </dependency>
2、配置yml文件
- server:
- port: 8080
-
- spring:
- datasource:
- username: mysql用户名
- password: mysql密码
- url: jdbc:mysql://localhost:3306/数据库名字?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
- driver-class-name: com.mysql.cj.jdbc.Driver
-
- mybatis:
- mapper-locations: classpath:mapping/*.xml
3、公用的User类
- @Data
- public class User {
-
- private int id;
- private String username;
- private String password;
- }
二、MyBatis利用For循环批量插入
1、编写UserService服务类,测试一万条数据耗时情况
- @Service
- public class UserService {
- @Resource
- private UserMapper userMapper;
- public void InsertUsers(){
- long start = System.currentTimeMillis();
- for(int i = 0 ;i < 10000; i++) {
- User user = new User();
- user.setUsername("name" + i);
- user.setPassword("password" + i);
- userMapper.insertUsers(user);
- }
- long end = System.currentTimeMillis();
- System.out.println("一万条数据总耗时:" + (end-start) + "ms" );
- }
- }
2、编写UserMapper接口
- @Mapper
- public interface UserMapper {
-
- Integer insertUsers(User user);
- }
3、编写UserMapper.xml文件
- <?xml version="1.0" encoding="UTF-8" ?>
- <!DOCTYPE mapper
- PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
- <mapper namespace="com.ithuang.demo.mapper.UserMapper">
- <insert id="insertUsers">
- INSERT INTO user (username, password)
- VALUES(#{username}, #{password})
- </insert>
- </mapper>
4、进行单元测试
- @SpringBootTest
- class DemoApplicationTests {
-
- @Resource
- private UserService userService;
-
- @Test
- public void insert(){
- userService.InsertUsers();
- }
-
- }
5、结果输出
一万条数据总耗时:26348ms
三、MyBatis的手动批量提交
1、其他保持不变,Service层作稍微的变化
- @Service
- public class UserService {
- @Resource
- private UserMapper userMapper;
- @Resource
- private SqlSessionTemplate sqlSessionTemplate;
- public void InsertUsers(){
- //关闭自动提交
- SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
- UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
- long start = System.currentTimeMillis();
- for(int i = 0 ;i < 10000; i++) {
- User user = new User();
- user.setUsername("name" + i);
- user.setPassword("password" + i);
- userMapper.insertUsers(user);
- }
- sqlSession.commit();
- long end = System.currentTimeMillis();
- System.out.println("一万条数据总耗时:" + (end-start) + "ms" );
- }
- }
2、结果输出
一万条数据总耗时:24516ms
四、MyBatis以集合方式批量新增(推荐)
1、编写UserService服务类
- @Service
- public class UserService {
- @Resource
- private UserMapper userMapper;
- public void InsertUsers(){
- long start = System.currentTimeMillis();
- List<User> userList = new ArrayList<>();
- User user;
- for(int i = 0 ;i < 10000; i++) {
- user = new User();
- user.setUsername("name" + i);
- user.setPassword("password" + i);
- userList.add(user);
- }
- userMapper.insertUsers(userList);
- long end = System.currentTimeMillis();
- System.out.println("一万条数据总耗时:" + (end-start) + "ms" );
- }
- }
3、编写UserMapper接口
- @Mapper
- public interface UserMapper {
-
- Integer insertUsers(List<User> userList);
- }
4、编写UserMapper.xml文件
- <?xml version="1.0" encoding="UTF-8" ?>
- <!DOCTYPE mapper
- PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
- <mapper namespace="com.ithuang.demo.mapper.UserMapper">
- <insert id="insertUsers">
- INSERT INTO user (username, password)
- VALUES
- <foreach collection ="userList" item="user" separator =",">
- (#{user.username}, #{user.password})
- </foreach>
- </insert>
- </mapper>
5、输出结果
一万条数据总耗时:521ms
五、MyBatis-Plus提供的SaveBatch方法
1、编写UserService服务
- @Service
- public class UserService extends ServiceImpl<UserMapper, User> implements IService<User> {
- public void InsertUsers(){
- long start = System.currentTimeMillis();
- List<User> userList = new ArrayList<>();
- User user;
- for(int i = 0 ;i < 10000; i++) {
- user = new User();
- user.setUsername("name" + i);
- user.setPassword("password" + i);
- userList.add(user);
- }
- saveBatch(userList);
- long end = System.currentTimeMillis();
- System.out.println("一万条数据总耗时:" + (end-start) + "ms" );
- }
- }
2、编写UserMapper接口
- @Mapper
- public interface UserMapper extends BaseMapper<User> {
-
- }
3、单元测试结果
一万条数据总耗时:24674ms
六、MyBatis-Plus提供的InsertBatchSomeColumn方法(推荐)
1、编写EasySqlInjector 自定义类
- public class EasySqlInjector extends DefaultSqlInjector {
-
-
- @Override
- public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
- // 注意:此SQL注入器继承了DefaultSqlInjector(默认注入器),调用了DefaultSqlInjector的getMethodList方法,保留了mybatis-plus的自带方法
- List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
- methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
- return methodList;
- }
-
- }
2、定义核心配置类注入此Bean
- @Configuration
- public class MybatisPlusConfig {
-
- @Bean
- public EasySqlInjector sqlInjector() {
- return new EasySqlInjector();
- }
- }
3、编写UserService服务类
- public class UserService{
- @Resource
- private UserMapper userMapper;
- public void InsertUsers(){
- long start = System.currentTimeMillis();
- List<User> userList = new ArrayList<>();
- User user;
- for(int i = 0 ;i < 10000; i++) {
- user = new User();
- user.setUsername("name" + i);
- user.setPassword("password" + i);
- userList.add(user);
- }
- userMapper.insertBatchSomeColumn(userList);
- long end = System.currentTimeMillis();
- System.out.println("一万条数据总耗时:" + (end-start) + "ms" );
- }
- }
4、编写EasyBaseMapper接口
- public interface EasyBaseMapper<T> extends BaseMapper<T> {
- /**
- * 批量插入 仅适用于mysql
- *
- * @param entityList 实体列表
- * @return 影响行数
- */
- Integer insertBatchSomeColumn(Collection<T> entityList);
- }
5、编写UserMapper接口
- @Mapper
- public interface UserMapper<T> extends EasyBaseMapper<User> {
-
- }
6、单元测试结果
一万条数据总耗时:575ms
七、总结
以上就是我对目前MyBatis常用的批量插入方法的总结
来源:https://blog.csdn.net/HJW_233/article/details/126909439 标签:批量,UserMapper,System,五种,user,一万条,MyBatis,User From: https://www.cnblogs.com/konglxblog/p/17112631.html