MyBatis的五種批量查詢實(shí)例總結(jié)

更新時(shí)間：2023年02月28日 10:00:46 作者：誰是黃黃

為了提高代碼的執(zhí)行效率,我們有時(shí)需要采用批量查詢的查詢方法,下面這篇文章主要給大家介紹了關(guān)于MyBatis的五種批量查詢的相關(guān)資料,需要的朋友可以參考下

一.直接循環(huán)插入
二.關(guān)閉MySql自動(dòng)提交，手動(dòng)進(jìn)行循環(huán)插入提交
第三種：用List集合的方式插入數(shù)據(jù)庫（推薦）
第四種: MyBatis-Plus提供的SaveBatch方法
第五種 MyBatis-Plus提供的InsertBatchSomeColumn方法（推薦）
總結(jié)

一.直接循環(huán)插入

@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("耗時(shí)："+( end - start ) + "ms");
        return (end-start);
    }
    }

 Integer insertUsers(User user);

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

最終耗時(shí)：14s多

二.關(guān)閉MySql自動(dòng)提交，手動(dòng)進(jìn)行循環(huán)插入提交

@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;

//    手動(dòng)開啟sql的批量提交
    private final   SqlSessionTemplate sqlSessionTemplate;

    @Override
    public Long add2(){
        //關(guān)閉自動(dòng)提交
        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);
        }
        //自動(dòng)提交SQL
        sqlSession.commit();
        long end = System.currentTimeMillis();
        System.out.println("耗時(shí)："+( end - start ) + "ms");
        return (end-start);
    }
    }

平均：0.12s

第三種：用List集合的方式插入數(shù)據(jù)庫（推薦）

@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("耗時(shí)："+( 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("耗時(shí)："+( end - start ) + "ms");
        return (end-start);
    }
    }

直接報(bào)錯(cuò)：

看報(bào)錯(cuò)信息：

長(zhǎng)串：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

翻譯一下：

可以發(fā)現(xiàn)就是我們的id超出范圍：

int類型改為bigint即可

故此我們可以得出一個(gè)結(jié)論：設(shè)置數(shù)據(jù)庫id的時(shí)候設(shè)置為bigint還是蠻好的哈

平均時(shí)間：0.2s

第五種 MyBatis-Plus提供的InsertBatchSomeColumn方法（推薦）

InsertBatchSomeColumn方法了解

這個(gè)類的注解就寫的很明白

擴(kuò)展這個(gè)InsertBatchSomeColumn方法

@Slf4j
public class EasySqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        // 注意：此SQL注入器繼承了DefaultSqlInjector(默認(rèn)注入器)，調(diào)用了DefaultSqlInjector的getMethodList方法，保留了mybatis-plus的自帶方法
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        log.info("擴(kuò)展的getMethodList方法被框架調(diào)用了");
        return methodList;
    }
}

擴(kuò)展的方法注入bean容器

/**
 * @author Stone
 * @date 2023/1/3
 * @apiNote
 */
@Configuration
public class MybatisPlusConfig {
    @Bean
    public  EasySqlInjector sqlInjector(){
        return new EasySqlInjector();
    }
}

創(chuàng)建一個(gè)Mapper去實(shí)現(xiàn)我們的擴(kuò)展的飛方法

public interface EasySqlInjectMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入 僅適用于mysql
     *
     * @param entityList 實(shí)體列表
     * @return 影響行數(shù)
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);
}

業(yè)務(wù)層

@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("耗時(shí)："+( end - start ) + "ms");
        return (end-start);
    }

耗時(shí)： 0.2 s