MyBatis three kinds of batch insertion method comparison, I recommend the third!

Official account: Java Xiaokaxiu, website: Javaxks.com

The author: the building the building Lord, link: www.jianshu.com/p/cce617be9…

The database uses sqlserver, JDK version 1.8, running in the SpringBoot environment to compare the three available methods

1. Execute a single insert statement repeatedly
2. XML stitching SQL
3. Batch execution

First say the conclusion: a small number of insert please use repeated insert single data, convenient. If the number is large, use batch processing. (Consider a limit of about 20 inserts as required, which in both my test and database environments takes about 100 milliseconds, convenience being the most important). Never use XML to concatenate SQL.

code

XML newId() for concatenated SQL is a function for SQL Server to generate UUID and is irrelevant to this article

<insert parameterType="java.util.List">
    INSERT INTO tb_item VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (newId(),#{item.uniqueCode},#{item.projectId},#{item.name},#{item.type},#{item.packageUnique},
        #{item.isPackage},#{item.factoryId},#{item.projectName},#{item.spec},#{item.length},#{item.weight},
        #{item.material},#{item.setupPosition},#{item.areaPosition},#{item.bottomHeight},#{item.topHeight},
        #{item.serialNumber},#{item.createTime}</foreach>
</insert>
Copy the code

Mapper is an interface of mybatis plugin tk.Mapper, which is not relevant to this article

public interface ItemMapper extends Mapper<Item> {
    int insertByBatch(List<Item> itemList);
}
Copy the code

The Service class

@Service public class ItemService { @Autowired private ItemMapper itemMapper; @Autowired private SqlSessionFactory sqlSessionFactory; @transactional public void add(List<Item> itemList) {SqlSession Session = sqlSessionFactory.openSession(ExecutorType.BATCH,false); ItemMapper mapper = session.getMapper(ItemMapper.class); for (int i = 0; i < itemList.size(); i++) { mapper.insertSelective(itemList.get(i)); Session.mit (); if(I %1000==999){// Commit once every 1000 to prevent memory overflow session.mit (); session.clearCache(); } } session.commit(); session.clearCache(); } / / stitching SQL @ Transactional public void add1 (List < Item > itemList) {itemList. InsertByBatch (itemMapper: : insertSelective); } / / cycle insert @ Transactional public void add2 (List < Item > itemList) {itemList. ForEach (itemMapper: : insertSelective); }}Copy the code

The test class

@RunWith(SpringRunner.class) @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = ApplicationBoot.class) public class ItemServiceTest { @Autowired ItemService itemService; private List<Item> itemList = new ArrayList<>(); List@before public void createList(){String json ="{\n" + "\"areaPosition\": TEST \ "\", \ n "+" \ "bottomHeight \" : 5, \ n "+" \ "factoryId \" : \ \ "0", "+" \ \ n "length \" : 233.233, \ n "+" \ "material \" : \"Q345B\",\n" + " \"name\": \"TEST\",\n" + " \"package\": false,\n" + " \"packageUnique\": \"45f8a0ba0bf048839df85f32ebe5bb81\",\n" + " \"projectId\": 094 b5eb5e0384bb1aaa822880a428b6d \ "\", \ n "+" \ "projectName \" : \ "project _TEST1 \", \ n "+" \ "serialNumber \" : 1/2 \ "\", \ n "+" \ "setupPosition \" : \ "column 1 b \", \ n "+" \ "spec \" : \ "200 x200x200 \", \ n "+" \ "topHeight \" : 10,\n" + " \"type\": \"Steel\",\n" + " \"uniqueCode\": \"12344312\",\n" + " \"weight\": 100\n" + " }"; Item test1 = JSON.parseObject(json,Item.class); test1.setCreateTime(new Date()); for (int i = 0; i < 1000; I++) {itemlist.add (test1); }} @test@transactional public void tesInsert() {itemService.add(itemList); } @test@transactional public void testInsert1(){itemService.add1(itemList); } @test@transactional public void testInsert2(){itemService.add2(itemList); }}Copy the code

Test results:

After many tests, the volatility of 10 and 25 data inserts is large, but they are basically in the level of 100 milliseconds

The SQL statement is too long. This clause is related to the database type and has not been tested by other databases. Com. Microsoft. Essentially. JDBC. SQLServerException: incoming table format data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters are provided in this RPC request. Maximum should be 2100

You can find

• The time complexity of cyclic insertion is O(n), and the constant C is large
• The time complexity of stitching SQL inserts (should) be O(logn), but the number of successful inserts is infrequent and uncertain
• The efficiency of batch processing has a time complexity of O(logn), and the constant C is small

conclusion

Cyclic insertion of a single piece of data is extremely inefficient, but requires very little code. In the case of the tk.Mapper plug-in, only code is required:

@Transactional
public void add1(List<Item> itemList) {
    itemList.forEach(itemMapper::insertSelective);
}
Copy the code

Therefore, it is definitely used when there is a small amount of data that needs to be inserted.

XML concatenation SQL is the least recommended method because it is error-prone and inefficient to write large chunks of XML and SQL statements. More key point is, although efficiency is ok, but really need efficiency when you die, want you what use?

Batch execution is recommended for large data inserts and is easier to use.