Using mybatis to realize batch insertion in spring

Time:2021-1-19

There are three implementations: foreach, spring transaction, and ExecutorType.BATCH .

1. Foreach mode

This way is actually to splice SQL statements, generate a long SQL, and bind many variables. If the amount of data is small (less than 1000), this method can be used. If the amount of data is too large, the database may report an error.

  • Define interface
public interface StudentMapper05 {
    public void insertStudent(List<Student> studentList);
}
  • Define mapper

For Oracle Database

<insert id="insertStudent">
    BEGIN
    <foreach collection="list" item="student" index="index" separator="">
        INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) 
        VALUES
        (SEQ_ID.nextval, #{student.name}, #{student.branch}, #{student.percentage}, #{student.phone}, #{student.email});
    </foreach>
    END;
</insert>

The meaning of this mapper is to splice the submitted studentlist into a long SQL, which is similar to:

BEGIN
INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) VALUES (SEQ_ID.nextval, ?, ?, ?, ?, ?);
INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) VALUES (SEQ_ID.nextval, ?, ?, ?, ?, ?);
INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) VALUES (SEQ_ID.nextval, ?, ?, ?, ?, ?);
...
END;

If there are several studentlists, how many insert statements will be generated, and each? Will be bound with variables. Therefore, when there is a large amount of data in studentlist, the generated SQL will be very long, resulting in errors in database execution.

  • dao
public class StudentDao05 {
    Private studentmapper05 studentmapper; // omit getters and setters
    
    public void insertStudentList(List<Student> studentList) {
        studentMapper.insertStudent(studentList);
    }
}
  • beans

mybatis-spring-05.xml:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="oracleDataSource" />
    <property name="configLocation" value="classpath:mybatis/config/mybatis-config-05.xml"/>
</bean>
<bean id="studentMapper05" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <property name="mapperInterface" value="com.ws.experiment.spring.mybatis.mapper.StudentMapper05" />
    <property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
<bean id="studentDao05" class="com.ws.experiment.spring.mybatis.dao.StudentDao05">
    <property name="studentMapper" ref="studentMapper05" />
</bean>
  • Main function
public static void main(String[] args) {
    String[] configFiles = new String[]{"spring-beans- config.xml "," mybatis / mybatis-spring-05. XML "}; // configure datasource and mybatis related beans respectively
    ApplicationContext context = new ClassPathXmlApplicationContext(configFiles);
    
    StudentDao05 studentDao = (StudentDao05)context.getBean("studentDao05");
    
    int counts[] = new int[]{10, 50, 100, 200, 500, 1000, 2000, 3000, 5000, 8000};
    for (int count : counts) {
        List<Student> studentList = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            Student st = new Student();
            st.setName("name");
            st.setBranch("");
            st.setEmail("");
            st.setPercentage(0);
            st.setPhone(0);
            studentList.add(st);
        }
        long startTime = System.currentTimeMillis();
        studentDao.insertStudentList(studentList);
        long endTime = System.currentTimeMillis();
        System.out.println (insert + count + pen data time: + (Endtime - starttime) + "Ms");
    }
}
  • test result
Insert 100 data time: 197 MS
Time for inserting 200 data: 232 MS
Insert 500 data time: 421 MS
Insert 1000 data: 650 MS
Time for inserting 2000 data: 1140 MS
Time for inserting 3000 data: 27113 MS
Insert 5000 data time: 98213 MS
Insert 8000 data time: 301101 MS

2. With the help of spring transaction

With the help of spring transaction, insert a set of data

  • Start spring transaction
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="oracleDataSource" />
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />
  • Define interface
public interface StudentMapper06 {
    public void insertStudent(@Param("student") Student student);
}
  • mapper
<insert id="insertStudent">
    INSERT INTO test_student(ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL) 
    VALUES
    (SEQ_ID.nextval, #{student.name}, #{student.branch}, #{student.percentage}, #{student.phone}, #{student.email})
</insert>
  • dao
public class StudentDao06 {
    Private studentmapper06 studentmapper; // omit getters and setters
    
    @Transactional // spring transaction control
    public void insertStudentList(List<Student> students) {
        for (Student student : students) {
            studentMapper.insertStudent(student);
        }
    }
}
  • beans
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="oracleDataSource" />
    <property name="configLocation" value="classpath:mybatis/config/mybatis-config-06.xml"/>
</bean>
<bean id="studentMapper06" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <property name="mapperInterface" value="com.ws.experiment.spring.mybatis.mapper.StudentMapper06" />
    <property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
<bean id="studentDao06" class="com.ws.experiment.spring.mybatis.dao.StudentDao06">
    <property name="studentMapper" ref="studentMapper06" />
</bean>
  • main

slightly

  • test result
Batchinsert001 insert 10 data time: 602 MS
Batchinsert001 insert 50 data time: 196 MS
Batchinsert001 insert 100 data time: 284 MS
Batchinsert001 insert 200 data time: 438 MS
Batchinsert001 insert 500 data time: 944 MS
Batchinsert001 insert 1000 data time: 1689 MS
Batchinsert001 insert 2000 data time: 3138 MS
Batchinsert001 insert 3000 data time: 4427 MS
Batchinsert001 insert 5000 data time: 7368 MS
Batchinsert001 insert 8000 data time: 11832 MS

3. Use ExecutorType.BATCH

The basic principle isSqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);, set sqlsession in batch mode

There are three settings:

3.1 setting in mybatis config file

Configuration files can be configured in sqlsessionfactorybean

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="oracleDataSource" />
    <property name="configLocation" value="classpath:mybatis/config/mybatis-config-06.xml"/>
</bean>

In this mybatis configuration file, set the batch mode:

<configuration>
    <settings>
        <! -- switch on the executor of batch by default -- >
        <setting name="defaultExecutorType" value="BATCH" />
    </settings>
    <mappers>
        <mapper class="com.ws.experiment.spring.mybatis.mapper.StudentMapper06" />
    </mappers>
</configuration>

In this way, all sqlsessions opened by default are in batch mode. Combined with spring transaction (see spring transaction settings in the previous section), batch insertion can be realized.

Test results:

Batchinsert001 insert 10 data time: 565 MS
Batchinsert001 insert 50 data time: 117 MS
Batchinsert001 insert 100 data time: 98 MS
Batchinsert001 insert 200 data time: 106 MS
Batchinsert001 insert 500 data time: 145 MS
Batchinsert001 insert 1000 data time: 132 MS
Batchinsert001 insert 2000 data time: 154 MS
Batchinsert001 insert 3000 data time: 163 MS
Batchinsert001 insert 5000 data time: 200 ms
Batchinsert001 insert 8000 data time: 250 ms

3.2 create your own sqlsession and manually commit

SqlSessionFactory sqlSessionFactory = (SqlSessionFactory)context.getBean("sqlSessionFactory");
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
StudentMapper06 studentMapper = sqlSession.getMapper(StudentMapper06.class);
for (int i = 0; i < count; i++) {
    Student st = new Student();
    st.setName("name");
    ...
    studentMapper.insertStudent(st);
}
sqlSession.commit();
sqlSession.clearCache();
sqlSession.close();

Test results:

Batchinsert002 insert 10 data time: 568 MS
Batchinsert002 insert 50 data time: 157 MS
Batchinsert002 insert 100 data time: 132 MS
Batchinsert002 insert 200 data time: 135 ms
Batchinsert002 insert 500 data time: 148 MS
Batchinsert002 insert 1000 data time: 139 MS
Batchinsert002 insert 2000 data time: 151 MS
Batchinsert002 insert 3000 data time: 139 MS
Batchinsert002 insert 5000 data time: 207 MS
Batchinsert002 insert 8000 data time: 299 MS

3.3 using sqlsessiontemplate to create bean in XML file

Create a sqlsessiontemplate and inject it into mapperfactorybean to generate the corresponding mapper:

<! -- with ExecutorType.BATCH How to insert database -- >
<bean id="batchSqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" />
    <constructor-arg name="executorType" value="BATCH" />
</bean>
<bean id="studentMapper06_batch" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <property name="mapperInterface" value="com.ws.experiment.spring.mybatis.mapper.StudentMapper06" />
    <property name="sqlSessionTemplate" ref="batchSqlSessionTemplate" />
</bean>
<bean id="studentDao06_batch" class="com.ws.experiment.spring.mybatis.dao.StudentDao06">
    <property name="studentMapper" ref="studentMapper06_batch" />
</bean>

After combining with spring transaction (see spring transaction settings in the previous section), batch insertion can be realized

test result

Batchinsert003 insert 10 data time: 651 MS
Batchinsert003 insert 50 data time: 133 MS
Batchinsert003 insert 100 data time: 124 MS
Batchinsert003 insert 200 data time: 129 MS
Batchinsert003 insert 500 data time: 144 MS
Batchinsert003 insert 1000 data time: 179 MS
Batchinsert003 insert 2000 data time: 229 MS
Batchinsert003 insert 3000 data time: 241 MS
Batchinsert003 insert 5000 data time: 216 MS
Batchinsert003 insert 8000 data time: 259 MS