Mybatis批量插入测试

Mybatis批量插入测试

1、Mybatis测试前的配置

1. pom配置

   <dependencies>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-web</artifactId>
               <version>2.7.0</version>
           </dependency>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-data-jpa</artifactId>
               <version>2.7.0</version>
           </dependency>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-jdbc</artifactId>
               <version>2.7.0</version>
           </dependency>
           <dependency>
               <groupId>org.mybatis.spring.boot</groupId>
               <artifactId>mybatis-spring-boot-starter</artifactId>
               <version>2.2.0</version>
           </dependency>
           <dependency>
               <groupId>com.alibaba</groupId>
               <artifactId>druid</artifactId>
               <version>1.1.16</version>
           </dependency>
   
           <dependency>
               <groupId>mysql</groupId>
               <artifactId>mysql-connector-java</artifactId>
               <version>5.1.48</version>
           </dependency>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-test</artifactId>
               <version>2.7.0</version>
               <scope>test</scope>
           </dependency>
           <dependency>
               <groupId>junit</groupId>
               <artifactId>junit</artifactId>
               <version>4.12</version>
               <scope>test</scope>
           </dependency>
       </dependencies>
   

2. springboot的配置

   spring:
     datasource:
       url: jdbc:mysql://localhost:3306/test?allowMultiQueries=true #?serverTimezone=GMT%2B8&characterEncoding=UTF-8&allowMultiQueries=true
       username: ****
       password: ****
       driver-class-name: com.mysql.jdbc.Driver
       type: com.alibaba.druid.pool.DruidDataSource
   
       initializeSize: 5
       minIdle: 5
       maxActive: 20
   mybatis:
     config-location: classpath:mybatis/mybatis-config.xml
     mapper-locations: classpath:mybatis/mapper/*.xml
   

3. mybatis配置

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE configuration
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-config.dtd">
   <configuration>
   
   </configuration>
   

4. 测试前的配置

   建表语句

   CREATE TABLE `user` (
     `id` int(11) NOT NULL,
     `name` varchar(40) DEFAULT NULL,
     `age` int(11) DEFAULT NULL,
     `gender` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

   实体类

   public class User {
       private Integer id;
   
       private String name;
   
       private Integer age;
   
       private Integer gender;
   
       public User() {
       }
   }
   

   mapper接口

   @Mapper
   public interface UserMapper {
   
       User getUserById(Integer id);
   
       void save(User user);
   
       void batchSave(List<User> list);
   
       void batchSaveImpl(List<User> list);
   }
   

   mapper-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.frankie.mapper.UserMapper">
       <!--Employee getEmpById(Integer id);
   
       void insert(Employee employee);-->
   
       <select id="getUserById" resultType="com.frankie.entity.User">
           SELECT * FROM user WHERE id=#{id}
       </select>
   	<!--单条插入-->
       <insert id="save" parameterType="com.frankie.entity.User">
           INSERT INTO user
               (id,name,age,gender)
           VALUES
               (#{id},#{name},#{age},#{gender})
       </insert>
   	<!--批量插入，有发送数据的限制，allowMultiQueries-->
       <insert id="batchSave" parameterType="java.util.List">
           <foreach collection="list" item="item" separator=";">
               INSERT INTO user (id,name,age,gender) VALUES (#{item.id},#{item.name},#{item.age},#{item.gender})
           </foreach>
       </insert>
   	<!--批量插入，有发送数据大小的限制-->
       <insert id="batchSaveImpl" parameterType="java.util.List">
           INSERT INTO user
           (id,name,age,gender)
           VALUES
           <foreach collection="list" index="index" item="item" separator=",">
               (#{item.id},#{item.name},#{item.age},#{item.gender})
           </foreach>
       </insert>
   </mapper>
   

   service类

   @Service
   public class UserService {
       private UserMapper mapper;
   
       @Autowired
       public void setMapper(UserMapper mapper) {
           this.mapper = mapper;
       }
   
       @Transactional
       public User getUserById(Integer id) {
           return mapper.getUserById(id);
       }
   
       @Transactional
       public int save(User user) {
           mapper.save(user);
           return 1;
       }
   
       @Transactional
       public void batchSave(List<User> userList) {
           mapper.batchSave(userList);
       }
   
   
       @Transactional
       public void batchSaveImpl(List<User> userList) {
           mapper.batchSaveImpl(userList);
       }
   }
   

   测试类

   @RunWith(SpringRunner.class)
   @SpringBootTest(classes = {MyApp.class})
   public class UserServiceTest {
       @Autowired
       UserService userService;
   
       static int NUM = 10000;
       @Test
       public void getUserById() {
   
           User user = userService.getUserById(1);
           System.out.println(user);
       }
   
       @Test
       public void save() {
           long start = System.currentTimeMillis();
           for (int i = 1; i <= NUM; i++) {
               User user = new User();
               user.setId(i);
               user.setName("Tom"+i);
               user.setAge(18);
               user.setGender(1);
               userService.save(user);
           }
           long end = System.currentTimeMillis();
           System.out.println("have time = " + (end - start));
       }
   
       @Test
       public void batchSave() {
           List<User> users = new ArrayList<>();
           long start = System.currentTimeMillis();
           for (int i = 1; i <= NUM; i++) {
               User user = new User();
               user.setId(i);
               user.setName("Jack"+i);
               user.setAge(18);
               user.setGender(1);
               users.add(user);
           }
           userService.batchSave(users);
           long end = System.currentTimeMillis();
           System.out.println("have time = " + (end - start));
       }
   
       @Test
       public void batchSaveImpl() {
           List<User> userList = new ArrayList<>();
           long start = System.currentTimeMillis();
           for (int i = 1; i <= NUM; i++) {
               User user = new User();
               user.setId(i);
               user.setName("Andy"+i);
               user.setAge(18);
               user.setGender(1);
               userList.add(user);
           }
           userService.batchSaveImpl(userList);
           long end = System.currentTimeMillis();
           System.out.println("have time = " + (end - start));
       }
   }
   

5. 测试结果(ms)

   	单条插入	批量插入	批量插入Impl
   1	29111	1798	691
   2	28935	1584	687
   3	28470	1493	646

   结论

   在进行多条数据插入时，批量插入比单条插入有更好的速度，不同的批量插入方法的优化效率不一致，其中使用foreach拼接数据的方式效率最高。