Mybatis批量新增三种方式

导入依赖

 <!-- 数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <!--        web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--test-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

Dao

@Repository // 代表持久层
public interface DeptMapper  {

    int addDept(Dept dept);

    int foreachAdd(List<Dept> list);
}

记得在启动类上加@MapperScan(“com.example.demo.dao”)

DeptMapper.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.example.demo.dao.DeptMapper">


<!--普通新增-->
    <insert id="addDept" parameterType="com.example.demo.pojo.Dept">
        INSERT INTO dept (dname, db_source) VALUES (#{dname},#{dbSource})
    </insert>


    <!--foreachMySql写法-->
    <insert id="foreachAdd"  parameterType="java.util.List">
        insert into dept (
        dname,
        db_source

        )
        values
        <foreach collection="list" item="dept" index="index" separator="," >
            (
            #{dept.dname,jdbcType=VARCHAR},
            #{dept.dbSource,jdbcType=VARCHAR}

            )
        </foreach>
    </insert>


</mapper>

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
  private long deptno;
  private String dname;
  private String dbSource;
}

配置文件

# mysql 5 驱动不同 com.mysql.jdbc.Driver
# mysql 8 驱动不同com.mysql.cj.jdbc.Driver、需要增加时区的配置
serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/db01?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

mybatis-plus.mapper-locations=classpath:mapping/*Mapper.xml

核心测试类

@SpringBootTest
class DemoApplicationTests {


    @Autowired
    private DeptMapper deptMapper;


    //测试数据
    public List<Dept> textData(){
        ArrayList<Dept> deptList = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            Dept dept = new Dept();
            dept.setDname("foreach");
            dept.setDbSource("db02");
            deptList.add(dept);
        }
        return deptList;
    }

//    循环插入
    @Test
    public void add(){
        List<Dept> textData = textData();//测试数据
        long start = System.currentTimeMillis();//开始时间
        for(Dept dept : textData){
            deptMapper.addDept(dept);
        }
        System.out.println(System.currentTimeMillis() - start);//统计时间
    }

//    foreach标签
    @Test
    public void foreach(){
        List<Dept> textData = textData();//测试数据
        long start = System.currentTimeMillis();//开始时间
        deptMapper.foreachAdd(textData);
        System.out.println(System.currentTimeMillis() - start);//统计时间
    }

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Test
    public void testInsertBatch(){

        List<Dept> textData = textData();//测试数据
        long start = System.currentTimeMillis();//开始时间
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
        DeptMapper studentMapperNew = sqlSession.getMapper(DeptMapper.class);
        textData.stream().forEach(student -> studentMapperNew.addDept(student));
        sqlSession.commit();
        sqlSession.clearCache();
        System.out.println(System.currentTimeMillis() - start);//统计时间
    }
}

总结

其实实际意义上来说，包括在程序里面for循环还是在sql里面for循环都不算是批量操作。只有将ExecutorType设置为BATCH模式才是真正意义上的批量操作。并且事实证明在sql循环时设置batch与否其实执行时间差别不是很大，几乎可以忽略不计。所以其实如果不是特别要求性能。可以直接在sql中使用for循环即可。谨慎使用batch，如果需要使用batch，请在需要的函数上面设置batch，不要全局使用。因为batch也是有副作用的。比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，；此外，对于update、delete无法返回更新、插入条数。这在某型情形下是不符合业务要求的