Mybatis batch insert which strong?

preface

In this paper, Mybatis is used for batch insertion to compare the differences between two different insertion methods.

test

Precautions for batch insertion:

1, add parameter allowMultiQueries=true when connecting database, support multi-statement execution, batch processing

2. Whether the database supports massive data writing, set max_allowed_packet parameter to ensure the amount of data submitted in batches

Stitching SQL

public void batchDemo(a) {
        long start = System.currentTimeMillis();
        List<User> list = new ArrayList<>();
        for (int i = 0; i < 5000; i++) {
            User user = new User();
            user.setId(UUID.randomUUID().toString());
            user.setName("feiyangyang");
            user.setPwd("feiyangyang");
            list.add(user);
        }
        userService.batchForeach(list);
        long end = System.currentTimeMillis();
        System.out.println("-- -- -- -- -- -- -- -- -- -- -- -- -- -- --" + (start - end) + "-- -- -- -- -- -- -- -- -- -- -- -- -- -- --");
    }
Copy the code

<insert id="batchForeach" parameterType="com.fyy.druid.entity.User">
    insert into
    user(id,`name`,pwd)
    values
    <foreach collection ="userList" item="user" separator =",">
        (#{user.id}, #{user.name}, #{user.pwd})
    </foreach>
</insert>
Copy the code

Batch insert

public void batchInsert(a) {
    long start = System.currentTimeMillis();
    SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH);
    UserService mapper = sqlSession.getMapper(UserService.class);

    for (int i = 0; i < 5000; i++) {
        User user = new User();
        user.setId(UUID.randomUUID().toString());
        user.setName("feiyangyang");
        user.setPwd("feiyangyang");
        mapper.batchInsert(user);
    }
    sqlSession.commit();
    long end = System.currentTimeMillis();
    System.out.println("-- -- -- -- -- -- -- -- -- -- -- -- -- -- --" + (start - end) + "-- -- -- -- -- -- -- -- -- -- -- -- -- -- --");
}
Copy the code

Data contrast