MyBatis的五种批量插入

一.直接循环插入

@RestController
@RequestMapping("/mybatis3/user")
@RequiredArgsConstructor
public class UserController {

    private final IUserService iUserService;

    @GetMapping("/one")
    public Long one(){
       return iUserService.add();
    }
}


  Long add();


@Service
@RequiredArgsConstructor
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {

    private final UserMapper userMapper;

    @Override
    public Long add() {

        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");
        return (end-start);
    }
    }



 Integer insertUsers(User user);

 <insert id="insertUsers" >
        insert into user(username,password)
        values (#{username}, #{password})
    </insert>



最终耗时:14s多
在这里插入图片描述

二.关闭MySql自动提交,手动进行循环插入提交

@RestController
@RequestMapping("/mybatis3/user")
@RequiredArgsConstructor
public class UserController {

    private final IUserService iUserService;

        @GetMapping("/one")
    public Long one(){
       return iUserService.add();
    }
}


 Long add2();


@Service
@RequiredArgsConstructor
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {

    private final UserMapper userMapper;

//    手动开启sql的批量提交
    private final   SqlSessionTemplate sqlSessionTemplate;

    @Override
    public Long add2(){
        //关闭自动提交
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        UserMapper mapper = 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);
            mapper.insertUsers(user);
        }
        //自动提交SQL
        sqlSession.commit();
        long end = System.currentTimeMillis();
        System.out.println("耗时:"+( end - start ) + "ms");
        return (end-start);
    }
    }

平均:0.12s
在这里插入图片描述

第三种:用List集合的方式插入数据库(推荐)

@RestController
@RequestMapping("/mybatis3/user")
@RequiredArgsConstructor
public class UserController {

    private final IUserService iUserService;

       @GetMapping("/one3")
    public Long one3(){
        return iUserService.add3();
    }
}


  Long add3();


@Service
@RequiredArgsConstructor
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {

    private final UserMapper userMapper;

  @Override
    public Long add3(){
        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.insertUsersThree(userList);
        long end = System.currentTimeMillis();
        System.out.println("耗时:"+( end - start ) + "ms");
        return (end-start);
    }
    }


 Integer insertUsersThree(List<User> userList);
 

<insert id="insertUsersThree">
        insert into user(username,password)
        values
        <foreach collection="userList" item="user" separator=",">
            (#{user.username},#{user.password})
        </foreach>
    </insert>

第四种: MyBatis-Plus提供的SaveBatch方法

@RestController
@RequestMapping("/mybatis3/user")
@RequiredArgsConstructor
public class UserController {

    private final IUserService iUserService;

@GetMapping("/one4")
    public Long one4(){
        return iUserService.add4();
    }
}


  Long add4();


@Service
@RequiredArgsConstructor
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {

    private final UserMapper userMapper;

@Override
    public Long add4() {
        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");
        return (end-start);
    }
    }

直接报错:
在这里插入图片描述
看报错信息:
长串:Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: com.huang.mybatis3.mapper.UserMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Data truncation: Out of range value for column ‘id’ at row 1
; Data truncation: Out of range value for column ‘id’ at row 1; nested exception is java.sql.BatchUpdateException: Data truncation: Out of range value for column ‘id’ at row 1] with root cause

短串:Data truncation: Out of range value for column ‘id’ at row 1

翻译一下:
在这里插入图片描述
可以发现就是我们的id超出范围:
在这里插入图片描述
int类型改为bigint即可

故此我们可以得出一个结论:设置数据库id的时候设置为bigint还是蛮好的哈

平均时间:0.2s
在这里插入图片描述

第五种 MyBatis-Plus提供的InsertBatchSomeColumn方法(推荐)

InsertBatchSomeColumn方法了解

在这里插入图片描述
这个类的注解就写的很明白

扩展这个InsertBatchSomeColumn方法
@Slf4j
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));
        log.info("扩展的getMethodList方法被框架调用了");
        return methodList;
    }
}
扩展的方法注入bean容器
/**
 * @author Stone
 * @date 2023/1/3
 * @apiNote
 */
@Configuration
public class MybatisPlusConfig {
    @Bean
    public  EasySqlInjector sqlInjector(){
        return new EasySqlInjector();
    }
}
创建一个Mapper去实现我们的扩展的飞方法
public interface EasySqlInjectMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入 仅适用于mysql
     *
     * @param entityList 实体列表
     * @return 影响行数
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);
}

业务层


 @Override
    public Long add5() {
        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");
        return (end-start);
    }

耗时: 0.2 s
在这里插入图片描述

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值