Shulou(Shulou.com)06/03 Report--

This article mainly explains "there are several ways to insert MyBatis in bulk". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "there are several ways to insert MyBatis in bulk"!

The database uses sqlserver,JDK version 1.8, which runs in SpringBoot environment, and compares three available ways:

Execute a single insert statement repeatedly

Xml splicing sql

Batch execution

First of all, let's come to a conclusion: a small amount of insertion, please use to insert a single piece of data repeatedly, convenient. For larger quantities, please use batch processing. (you can consider the need to insert about 20 pieces of data as the limit, in my test and database environment is a hundred milliseconds, convenience is the most important.

Don't use the way xml splices sql at any time.

Code

Splicing the xml of SQL

NewId () is the function that sqlserver generates UUID, which has nothing to do with the content of this article.

INSERT INTO tb_item VALUES

(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}

Mapper interface

Mapper is the interface of mybatis plug-in tk.Mapper, which has little to do with the content of this article.

Public interface ItemMapper extends Mapper {

Int insertByBatch (List itemList)

}

Service class

@ Service

Public class ItemService {

@ Autowired

Private ItemMapper itemMapper

@ Autowired

Private SqlSessionFactory sqlSessionFactory

/ / batch processing

@ Transactional

Public void add (List itemList) {

SqlSession session = sqlSessionFactory.openSession (ExecutorType.BATCH,false)

ItemMapper mapper = session.getMapper (ItemMapper.class)

For (int I =; I < itemList.size (); iTunes +) {

Mapper.insertSelective (itemList.get (I))

If (i00 submission 999) {/ / commit every 1000 entries to prevent memory overflow

Session.commit ()

Session.clearCache ()

}

}

Session.commit ()

Session.clearCache ()

}

/ / stitching sql

@ Transactional

Public void add1 (List itemList) {

ItemList.insertByBatch (itemMapper::insertSelective)

}

/ / Loop insertion

@ Transactional

Public void add2 (List itemList) {

ItemList.forEach (itemMapper::insertSelective)

}

}

Test class

@ RunWith (SpringRunner.class)

@ SpringBootTest (webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = ApplicationBoot.class)

Public class ItemServiceTest {

@ Autowired

ItemService itemService

Private List itemList = new ArrayList ()

/ / generate test 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\":\ "094b5eb5e0384bb1aaa822880a428b6d\",\ n "+

"\" projectName\ ":\" Project _ TEST1\ ",\ n" +

"\" serialNumber\ ":\" 1Accord 2\ ",\ n" +

"\" setupPosition\ ":\" 1B column\ ",\ n" +

"\ spec\":\ "200X200X200\",\ 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 =; I < 1000; iTunes +) {/ / the test modifies this quantity

ItemList.add (test1)

}

}

/ / batch processing

@ Test

@ Transactional

Public void tesInsert () {

ItemService.add (itemList)

}

/ / concatenate strings

@ Test

@ Transactional

Public void testInsert1 () {

ItemService.add1 (itemList)

}

/ / Loop insertion

@ Test

@ Transactional

Public void testInsert2 () {

ItemService.add2 (itemList)

}

}

Test results:

After many tests, 10 pieces of 25 pieces of data are highly volatile, but basically all of them are at the level of 100 milliseconds.

Among them, the splicing sql method inserts 500items and 1000 Times errors (it seems that the sql statement is too long, this article is related to the database type and has not been tested in other databases): com.microsoft.sqlserver.jdbc.SQLServerException: the incoming tabular data flow (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. Should be up to 2100

Can be found

The time complexity of loop insertion is O (n), and the constant C is very large.

The time complexity of splicing SQL insertion (should) is O (logn), but the number of successful completion is small and uncertain.

The time complexity of batch efficiency is O (logn), and the constant C is relatively small.

Conclusion

Although it is very inefficient to insert a single piece of data in a loop, the amount of code is very small. In the case of plug-ins using tk.Mapper, only the code is needed:

@ Transactional

Public void add1 (List itemList) {

ItemList.forEach (itemMapper::insertSelective)

}

Therefore, it must be used when the amount of data that needs to be inserted is small.

Xml splicing sql is the least recommended way, when using a large number of xml and sql statements to write, it is easy to make mistakes, and the work efficiency is very low. More importantly, although efficiency is OK, but when you really need efficiency, you hang up, what is the use of you?

Batch execution is recommended when a large amount of data is inserted, and it is also convenient to use.

At this point, I believe you have a deeper understanding of "several ways to insert MyBatis in bulk". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.