Mybatis在使用过程需要使用批量新增、更新操作时候，需要自己定义组装sql，并且还有设置数据库连接的参数支持多语句查询，具体操作关键点大致有如下所述。

依赖

pom.xml文件

<!-- MyBatis 启动器 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

数据库连接配置

注意对于@Insert的批量组装，无需设置连接的属性，对于@Update的批量多条语句的执行，需要在mysql的链接上开启allowMultiQueries=true，具体如下

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://55.13.2.139,55.13.3.124:3306/prfcrddb?characterEncoding=UTF-8&useSSL=true&allowMultiQueries=true
    username: xxx
    password: xxx
    driverClassName: com.mysql.jdbc.Driver
    max-active: 2
    max-idle: 2
    min-idle: 2
    initial-size: 2

Mapper代码

Mapper定义中使用foreach进行sql组装

package xxx.infrastructure.mapper;

import xxx.domain.entity.CrdNbrInf;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @program: xxxx
 * @description: xxx
 * @author: xxxx
 * @create: 2019-12-20 11:51
 **/
@Mapper
public interface CrdNbrInfMapper {
    @Select("select * from t_crd_nbr_inf where ser_app_nbr = #{serAppNbr} and crd_nbr_sts= #{crdNbrSts}")
    List<CrdNbrInf> query(@Param("serAppNbr") String serAppNbr, @Param("crdNbrSts") String crdNbrSts);

    @Insert("insert t_crd_nbr_inf(ser_app_nbr, rule_id, crd_nbr, crd_nbr_sts, crd_pol_lvl, start_pos) values(#{serAppNbr}, #{ruleId}, #{crdNbr}, #{crdNbrSts}, #{crdPolLvl}, #{startPos})")
    int insertCrdNbrInf(CrdNbrInf crdNbrInf);

    @Update("update t_crd_nbr_inf set crd_nbr_sts = #{crdNbrSts} where ser_app_nbr = #{serAppNbr} and crd_nbr = #{crdNbr} and crd_nbr_sts = '2'")
    int updateCrdNbrInf(CrdNbrInf crdNbrInf);

    /**
     * 批量新增
     * @param crdNbrInfList
     * @return
     */
    @Insert("<script> insert t_crd_nbr_inf(ser_app_nbr, rule_id, crd_nbr, crd_nbr_sts, crd_pol_lvl, start_pos) values" +
            " <foreach collection='crdNbrInfList' index='index' item='item' separator=','  >" +
                 "(#{item.serAppNbr}, #{item.ruleId}, #{item.crdNbr}, #{item.crdNbrSts}, #{item.crdPolLvl}, #{item.startPos}) "  +
            "</foreach> " +
            "</script>")
    int batchInsertCrdNbrInf(@Param("crdNbrInfList")List<CrdNbrInf>  crdNbrInfList);


    /**
     * 批量更新
     * @param crdNbrInfList
     * @return
     */
    @Update({"<script>" ,
            " <foreach collection='crdNbrInfList' index='index' item='item'   separator=';' >" ,
            " update t_crd_nbr_inf set crd_nbr_sts = #{item.crdNbrSts}  where ser_app_nbr=#{item.serAppNbr}  and crd_nbr=#{item.crdNbr} and crd_nbr_sts = '2' "  ,
            "</foreach> " ,
            "</script>"})
    int batchUpdateCrdNbrInf(@Param("crdNbrInfList")List<CrdNbrInf>  crdNbrInfList);
}

单元测试

package xxx.infrastructure.mapper;

import xxx.domain.entity.CrdNbrInf;
import lombok.extern.slf4j.Slf4j;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.ArrayList;
import java.util.List;
@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class CrdNbrInfMapperTest {

    @Autowired
    CrdNbrInfMapper crdNbrInfMapper;


    @Test
    public void give新增列表_when调用批量新增_then会新增多个() {

        List<CrdNbrInf> list = new ArrayList<CrdNbrInf>();
        CrdNbrInf crdnbrinf =  CrdNbrInf.builder().serAppNbr("dfasfasdf243243234240").ruleId(9).crdNbr("622512112121").crdNbrSts("9").build();
        CrdNbrInf crdnbrinf2 = CrdNbrInf.builder().serAppNbr("dfasfasdf243243234241").ruleId(9).crdNbr("6225121121211").crdNbrSts("9").build();
        CrdNbrInf crdnbrinf3 = CrdNbrInf.builder().serAppNbr("dfasfasdf243243234242").ruleId(9).crdNbr("622512121212").crdNbrSts("9").build();
        list.add(crdnbrinf);
        list.add(crdnbrinf2);
        list.add(crdnbrinf3);
        int cnt = crdNbrInfMapper.batchInsertCrdNbrInf(list);
        Assert.assertEquals(3, cnt);

    }

    @Test
    public void give更新列表_when调用批量更新_then会更新多个() {

        List<CrdNbrInf> list = new ArrayList<CrdNbrInf>();
        CrdNbrInf crdnbrinf = CrdNbrInf.builder().serAppNbr("dfasfasdf243243234230").crdNbr("622512112121").crdNbrSts("88").build();
        CrdNbrInf crdnbrinf2 = CrdNbrInf.builder().serAppNbr("dfasfasdf243243234231").crdNbr("6225121121211").crdNbrSts("88").build();
        CrdNbrInf crdnbrinf3 = CrdNbrInf.builder().serAppNbr("dfasfasdf243243234232").crdNbr("622512121212").crdNbrSts("88").build();
        list.add(crdnbrinf);
        list.add(crdnbrinf2);
        list.add(crdnbrinf3);
        int cnt = crdNbrInfMapper.batchUpdateCrdNbrInf(list);
        Assert.assertEquals(1, cnt);
    }


    @Test
    public void give新增列表_when调用批量新增和非批量_then比对执行耗时() {

        //batch
        List<CrdNbrInf> list = new ArrayList<CrdNbrInf>();
        List<CrdNbrInf> listLoop = new ArrayList<CrdNbrInf>();
        for (int i = 0; i < 50; i++) {
            CrdNbrInf crdnbrinf =  CrdNbrInf.builder().serAppNbr("test2432432370000"+i).ruleId(9).crdNbr("622512112100"+i).crdNbrSts("9").build();
            CrdNbrInf crdnbrinf2 =  CrdNbrInf.builder().serAppNbr("txxt2432432380000"+i).ruleId(9).crdNbr("600512112100"+i).crdNbrSts("9").build();
            list.add(crdnbrinf);
            listLoop.add(crdnbrinf2);
        }
        long beginTime = System.currentTimeMillis();
        log.info("start. batch" );
        int cnt = crdNbrInfMapper.batchInsertCrdNbrInf(list);
        log.info("end.查询选号结果结束，消耗时间为：" + (System.currentTimeMillis() - beginTime) + "ms");

        //loop
        long beginTime2 = System.currentTimeMillis();
        log.info("start. loop ");
        for (CrdNbrInf crdNbrInf : listLoop) {
            crdNbrInfMapper.insertCrdNbrInf(crdNbrInf);
        }
        log.info("end. loop 结束，消耗时间为：" + (System.currentTimeMillis() - beginTime2) + "ms");
    }
}

未决问题

1、@update更新多条只返回一条

衍生问题

mybaits如何解析及执行批量sql？

Mybatis批量新增更新操作