• Mybatis三种批量插入数据的方式

    https://www.jb51.net/article/210089.htm 

     
    这篇文章主要介绍了Mybatis的三种批量插入方式，帮助大家更好的理解和学习使用Java，感兴趣的朋友可以了解下
    目录

     

    1. 循环插入

    mapper.xml：

    1
    2
    3
    4
    5
    6
    7
    <?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.buhe.demo.mapper.StudentMapper">
      <insert id="insert" parameterType="Student">
        INSERT INTO tb_student (name, age, phone, address, class_id) VALUES (#{name},#{age},#{phone},#{address},#{classId})
      </insert>
    </mapper>

    mapper接口：

    1
    2
    3
    public interface StudentMapper {
        int insert(Student student);
    }

    测试代码：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    @SpringBootTest
    class DemoApplicationTests {
        @Resource
        private StudentMapper studentMapper;
     
        @Test
        public void testInsert(){
            //数据生成
            List<Student> studentList = createData(100);
     
            //循环插入
            long start = System.currentTimeMillis();
            studentList.stream().forEach(student -> studentMapper.insert(student));
            System.out.println(System.currentTimeMillis() - start);
        }
     
        private List<Student> createData(int size){
            List<Student> studentList = new ArrayList<>();
            Student student;
            for(int i = 0; i < size; i++){
                student = new Student();
                student.setName("小王" + i);
                student.setAge(18);
                student.setClassId(1);
                student.setPhone("1585xxxx669");
                student.setAddress("未知");
                studentList.add(student);
            }
     
            return studentList;
        }
    }

    2. foreach标签

    mapper.xml：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    <?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.buhe.demo.mapper.StudentMapper">
      <insert id="insert" parameterType="Student">
        INSERT INTO tb_student (name, age, phone, address, class_id) VALUES (#{name},#{age},#{phone},#{address},#{classId})
      </insert>
     
      <insert id="insertBatch">
        INSERT INTO tb_student (name, age, phone, address, class_id) VALUES
        <foreach collection="list" separator="," item="item">
            (#{item.name},#{item.age},#{item.phone},#{item.address},#{item.classId})
        </foreach>
      </insert>
    </mapper>

    mapper接口：

    1
    2
    3
    4
    5
    public interface StudentMapper {
        int insert(Student student);
     
        int insertBatch(List<Student> studentList);
    }

    测试代码：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    @SpringBootTest
    class DemoApplicationTests {
        @Resource
        private StudentMapper studentMapper;
     
        @Test
        public void testInsertByForeachTag(){
            //数据生成
            List<Student> studentList = createData(100);
     
            //使用foreach标签，拼接SQL插入
            long start = System.currentTimeMillis();
            studentMapper.insertBatch(studentList);
            System.out.println(System.currentTimeMillis() - start);
        }
     
     
        private List<Student> createData(int size){
            List<Student> studentList = new ArrayList<>();
            Student student;
            for(int i = 0; i < size; i++){
                student = new Student();
                student.setName("小王" + i);
                student.setAge(18);
                student.setClassId(1);
                student.setPhone("1585xxxx669");
                student.setAddress("未知");
                studentList.add(student);
            }
     
            return studentList;
        }
    }

     

    3. 批处理

    测试代码：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    @SpringBootTest
    class DemoApplicationTests {
        @Autowired
        private SqlSessionFactory sqlSessionFactory;
     
        @Test
        public void testInsertBatch(){
            //数据生成
            List<Student> studentList = createData(100);
     
                    //使用批处理
            long start = System.currentTimeMillis();
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
            StudentMapper studentMapperNew = sqlSession.getMapper(StudentMapper.class);
            studentList.stream().forEach(student -> studentMapperNew.insert(student));
            sqlSession.commit();
            sqlSession.clearCache();
            System.out.println(System.currentTimeMillis() - start);
        }
     
        private List<Student> createData(int size){
            List<Student> studentList = new ArrayList<>();
            Student student;
            for(int i = 0; i < size; i++){
                student = new Student();
                student.setName("小王" + i);
                student.setAge(18);
                student.setClassId(1);
                student.setPhone("1585xxxx669");
                student.setAddress("未知");
                studentList.add(student);
            }
     
            return studentList;
        }
    }

     

    三种方式的对比

    MySQL服务器版本：5.6.4

    其他依赖版本如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.4.4</version>
            <relativePath/> <!-- lookup parent from repository -->
        </parent>
        <groupId>com.buhe</groupId>
        <artifactId>demo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>demo</name>
        <description>Demo project for Spring Boot</description>
        <properties>
            <java.version>1.8</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
     
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
     
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.41</version>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.1</version>
            </dependency>
        </dependencies>
     
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
     
            <resources>
                <resource>
                    <directory>src/main/java</directory>
                    <includes>
                        <include>**/*.xml</include>
                    </includes>
                </resource>
            </resources>
        </build>
     
    </project>

    三种插入方式在不同数据量下的表现，测试结果：

    插入方式10条100条500条1000条
    循环插入 496ms 3330ms 15584ms 33755ms
    foreach标签 268ms 366ms 392ms 684ms
    批处理 222ms 244ms 364ms 426ms

    三种方式中，批处理的方式效率是最高的，尤其是在数据量大的情况下尤为明显。

    其次是foreach标签，foreach标签是通过拼接SQL语句的方式完成批量操作的。但是当拼接的SQL过多，导致SQL大小超过了MySQL服务器中max_allowed_packet变量的值时，会导致操作失败，抛出PacketTooBigException异常。

    最后是循环插入的方式，这种方式在数据量小的时候可以使用，在数据量大的情况下效率要低很多。

    ___________________________________________________________

    1. isnull(expr)

    如果expr为null，则返回1，否则返回0

    SELECT ISNULL(null) from dual; --结果为1

    SELECT isnull(1/0) from dual; --结果为1

    SELECT isnull(1/null) from dual; --结果为1

    SELECT ISNULL(5) from dual; --结果为0

    SELECT ISNULL('a') from dual; --结果为0

    SELECT ISNULL(5+5) from dual; --结果为0

    2. ifnull(expr1, expr2)

    根isnull类似，expr1不为null的情况下，返回expr1，返回expr2

    SELECT IFNULL(1,2) from dual; --1

    SELECT IFNULL(null,2) from dual; --2

    SELECT IFNULL(1/0,'can not be null') from dual; --'can not be null'

    3. nullif(expr1, expr2)

    nullif和ifnull完全不同，它表示如果两个表达式相同，则返回null，否则返回expr1的值

    个人感觉应该叫null if equals

    SELECT NULLIF(1,3) from dual; --1

    SELECT NULLIF(3,3) from dual; --null

    SELECT NULLIF(1+2,3) from dual; --null

    4. coalesce(expr1, expr2,… exprN)

    发音是core or less，英文的意思是联合/合并，在mysql中的用途是返回第一个不是null的值

    SELECT COALESCE(null,1/0,2) from dual; --2

    SELECT COALESCE(null,1/0,2,3) from dual; --2

    只有两个参数的情况下，相当于ifnull

    SELECT IFNULL(null,2) from dual; --2

    SELECT COALESCE(null,2) from dual; --2
    ————————————————
     
  • 相关阅读:
    网络编程前戏
    上传文件数据到数据库
    SpringMVC点滴（1）
    python全栈学习--day2
    python全栈学习--day1
    python练习册 每天一个小程序 第0013题
    python练习册 每天一个小程序 第0012题
    python练习册 每天一个小程序 第0011题
    [XMAN筛选赛](web)ctf用户登录
    python练习册 每天一个小程序 第0010题
  • 原文地址：https://www.cnblogs.com/kelelipeng/p/16803239.html
Copyright © 2020-2023  润新知