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

This article introduces the knowledge of "how to achieve java multithreading to split List into the database in batches". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Directly import list into Mysql

Bulk import operations using mybatis:

@ Transactional (rollbackFor = Exception.class) public int addFreshStudentsNew2 (List list, String schoolNo) {if (list = = null | | list.isEmpty ()) {return 0;} List studentEntityList = new LinkedList (); List enrollStudentEntityList = new LinkedList (); List allusersEntityList = new LinkedList (); for (FreshStudentAndStudentModel freshStudentAndStudentModel: list) {EnrollStudentEntity enrollStudentEntity = new EnrollStudentEntity (); StudentEntity studentEntity = new StudentEntity () BeanUtils.copyProperties (freshStudentAndStudentModel, studentEntity); BeanUtils.copyProperties (freshStudentAndStudentModel, enrollStudentEntity); String operator = TenancyContext.UserID.get (); String studentId = BaseUuidUtils.base58Uuid (); enrollStudentEntity.setId (BaseUuidUtils.base58Uuid ()); enrollStudentEntity.setStudentId (studentId); enrollStudentEntity.setIdentityCardId (freshStudentAndStudentModel.getIdCard ()); enrollStudentEntity.setOperator (operator) StudentEntity.setId (studentId); studentEntity.setIdentityCardId (freshStudentAndStudentModel.getIdCard ()); studentEntity.setOperator (operator); studentEntityList.add (studentEntity); enrollStudentEntityList.add (enrollStudentEntity); AllusersEntity allusersEntity = new AllusersEntity (); allusersEntity.setId (enrollStudentEntity.getId ()); allusersEntity.setUserCode (enrollStudentEntity.getNemtCode ()) AllusersEntity.setUserName (enrollStudentEntity.getName ()); allusersEntity.setSchoolNo (schoolNo); allusersEntity.setTelNum (enrollStudentEntity.getTelNum ()); allusersEntity.setPassword (enrollStudentEntity.getNemtCode ()); / / password is set to candidate number allusersEntityList.add (allusersEntity);} enResult = enrollStudentDao.insertAll (enrollStudentEntityList); stuResult = studentDao.insertAll (studentEntityList) AllResult = allusersFacade.insertUserList (allusersEntityList); if (enResult > 0 & stuResult > 0 & & allResult) {return 10;} return-10;}

Mapper.xml

Insert into tb_enroll_student id, remark, nEMT_aspiration, nEMT_code, nEMT_score, student_id, identity_card_id, level, major, name Nation, secondary_college, operator, sex, is_delete, account_address, native_place, original_place, used_name, pictrue Join_party_date, political_status, tel_num, is_registry, graduate_school, create_time, update_time values (# {item.id,jdbcType=VARCHAR} # {item.remark,jdbcType=VARCHAR}, # {item.nemtAspiration,jdbcType=VARCHAR}, # {item.nemtCode,jdbcType=VARCHAR}, # {item.nemtScore,jdbcType=VARCHAR}, # {item.studentId,jdbcType=VARCHAR}, # {item.identityCardId,jdbcType=VARCHAR}, # {item.level,jdbcType=VARCHAR} # {item.major,jdbcType=VARCHAR}, # {item.name,jdbcType=VARCHAR}, # {item.nation,jdbcType=VARCHAR}, # {item.secondaryCollege,jdbcType=VARCHAR}, # {item.operator,jdbcType=VARCHAR}, # {item.sex,jdbcType=VARCHAR}, 0, # {item.accountAddress,jdbcType=VARCHAR} # {item.nativePlace,jdbcType=VARCHAR}, # {item.originalPlace,jdbcType=VARCHAR}, # {item.usedName,jdbcType=VARCHAR}, # {item.pictrue,jdbcType=VARCHAR}, # {item.joinPartyDate,jdbcType=VARCHAR}, # {item.politicalStatus,jdbcType=VARCHAR}, # {item.telNum,jdbcType=VARCHAR} # {item.isRegistry,jdbcType=TINYINT}, # {item.graduateSchool,jdbcType=VARCHAR}, now (), now ())

Code description:

The underlying mapper is generated by reverse engineering. The batch inserts are as follows, which are spliced into something similar: insert into tb_enroll_student () values (), ()... . ()

The disadvantage is that the database generally has a default setting, which is that the data per sql operation cannot exceed 4m. In this way, when there is a lot of data, the database will report an error Packet for query is too large (6071393 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable., although we can pass

Similar modification my.ini plus max_allowed_packet = 67108864

671088640064m

The default size is 4194304, or 4m.

Restart the mysql service after the modification is completed. If you modify it through the command line, you do not need to restart the mysql service.

Complete this operation, but we can not guarantee the maximum size of the project at a time, which has drawbacks. So you can consider group import.

Import list into Mysql in groups

Mybatis batch insertion is also applicable. The difference is that each import is calculated in groups, and then imported multiple times:

@ Transactional (rollbackFor = Exception.class) public int addFreshStudentsNew2 (List list, String schoolNo) {if (list = = null | | list.isEmpty ()) {return 0;} List studentEntityList = new LinkedList (); List enrollStudentEntityList = new LinkedList (); List allusersEntityList = new LinkedList (); for (FreshStudentAndStudentModel freshStudentAndStudentModel: list) {EnrollStudentEntity enrollStudentEntity = new EnrollStudentEntity (); StudentEntity studentEntity = new StudentEntity () BeanUtils.copyProperties (freshStudentAndStudentModel, studentEntity); BeanUtils.copyProperties (freshStudentAndStudentModel, enrollStudentEntity); String operator = TenancyContext.UserID.get (); String studentId = BaseUuidUtils.base58Uuid (); enrollStudentEntity.setId (BaseUuidUtils.base58Uuid ()); enrollStudentEntity.setStudentId (studentId); enrollStudentEntity.setIdentityCardId (freshStudentAndStudentModel.getIdCard ()); enrollStudentEntity.setOperator (operator) StudentEntity.setId (studentId); studentEntity.setIdentityCardId (freshStudentAndStudentModel.getIdCard ()); studentEntity.setOperator (operator); studentEntityList.add (studentEntity); enrollStudentEntityList.add (enrollStudentEntity); AllusersEntity allusersEntity = new AllusersEntity (); allusersEntity.setId (enrollStudentEntity.getId ()); allusersEntity.setUserCode (enrollStudentEntity.getNemtCode ()) AllusersEntity.setUserName (enrollStudentEntity.getName ()); allusersEntity.setSchoolNo (schoolNo); allusersEntity.setTelNum (enrollStudentEntity.getTelNum ()); allusersEntity.setPassword (enrollStudentEntity.getNemtCode ()); / / password is set to candidate number allusersEntityList.add (allusersEntity);} int c = 100; int b = enrollStudentEntityList.size () / c Int d = enrollStudentEntityList.size ()% c; int enResult = 0; int stuResult = 0; boolean allResult = false; for (int e = c; e 0 & & stuResult > 0 & & allResult) {return 10;} return-10;}

Code description:

This operation can avoid the above errors, but multiple inserts virtually increase the operation practice, and it is easy to time out. Therefore, this method is not worth advocating.

Improve again, using multi-thread batch import.

4. Import Mysql in batches by multi-thread

The bulk import of mybatis is still used, except that it is grouped according to the number of threads, and then a multi-thread pool is established for import.

@ Transactional (rollbackFor = Exception.class) public int addFreshStudentsNew (List list, String schoolNo) {if (list = = null | | list.isEmpty ()) {return 0;} List studentEntityList = new LinkedList (); List enrollStudentEntityList = new LinkedList (); List allusersEntityList = new LinkedList (); list.forEach (freshStudentAndStudentModel-> {EnrollStudentEntity enrollStudentEntity = new EnrollStudentEntity (); StudentEntity studentEntity = new StudentEntity ()) BeanUtils.copyProperties (freshStudentAndStudentModel, studentEntity); BeanUtils.copyProperties (freshStudentAndStudentModel, enrollStudentEntity); String operator = TenancyContext.UserID.get (); String studentId = BaseUuidUtils.base58Uuid (); enrollStudentEntity.setId (BaseUuidUtils.base58Uuid ()); enrollStudentEntity.setStudentId (studentId); enrollStudentEntity.setIdentityCardId (freshStudentAndStudentModel.getIdCard ()); enrollStudentEntity.setOperator (operator) StudentEntity.setId (studentId); studentEntity.setIdentityCardId (freshStudentAndStudentModel.getIdCard ()); studentEntity.setOperator (operator); studentEntityList.add (studentEntity); enrollStudentEntityList.add (enrollStudentEntity); AllusersEntity allusersEntity = new AllusersEntity (); allusersEntity.setId (enrollStudentEntity.getId ()); allusersEntity.setUserCode (enrollStudentEntity.getNemtCode ()) AllusersEntity.setUserName (enrollStudentEntity.getName ()); allusersEntity.setSchoolNo (schoolNo); allusersEntity.setTelNum (enrollStudentEntity.getTelNum ()); allusersEntity.setPassword (enrollStudentEntity.getNemtCode ()); / / password is set to candidate number allusersEntityList.add (allusersEntity);}); int nThreads = 50; int size = enrollStudentEntityList.size (); ExecutorService executorService = Executors.newFixedThreadPool (nThreads) List futures = new ArrayList (nThreads); for (int I = 0; I

< nThreads; i++) { final List EnrollStudentEntityImputList = enrollStudentEntityList.subList(size / nThreads * i, size / nThreads * (i + 1)); final List studentEntityImportList = studentEntityList.subList(size / nThreads * i, size / nThreads * (i + 1)); final List allusersEntityImportList = allusersEntityList.subList(size / nThreads * i, size / nThreads * (i + 1)); Callable task1 = () ->

{studentSave.saveStudent (EnrollStudentEntityImputList,studentEntityImportList,allusersEntityImportList); return 1;}; futures.add (executorService.submit (task1));} executorService.shutdown (); if (! futures.isEmpty () & & futures! = null) {return 10;} return-10;}

Code description:

Above is through the application of ExecutorService to establish a fixed number of threads, and then grouped according to the number of threads, batch import. On the one hand, it can relieve the pressure on the database, and the other side has more threads, which will increase the running time of the program to a certain extent. The disadvantage is that it depends on the configuration of the server. If the configuration is good, you can open multiple threads, and if the configuration is poor, turn it down.

"how to achieve java multi-thread batch split List import database" content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.