服务器之家:专注于服务器技术及软件下载分享
分类导航

PHP教程|ASP.NET教程|Java教程|ASP教程|编程技术|正则表达式|C/C++|IOS|C#|Swift|Android|VB|R语言|JavaScript|易语言|vb.net|

服务器之家 - 编程语言 - Java教程 - java多线程批量拆分List导入数据库的实现过程

java多线程批量拆分List导入数据库的实现过程

2022-03-07 00:50AresCarry Java教程

这篇文章主要给大家介绍了关于java多线程批量拆分List导入数据库的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者工作具有一定的参考学习价值,需要的朋友可以参考下

一、前言

前两天做了一个导入的功能,导入开始的时候非常慢,导入2w条数据要1分多钟,后来一点一点的优化,从直接把list怼进Mysql中,到分配把list导入Mysql中,到多线程把list导入Mysql中。时间是一点一点的变少了。非常的爽,最后变成了10s以内。下面就展示一下过程。

二、直接把list怼进Mysql

使用mybatis的批量导入操作:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
@Transactional(rollbackFor = Exception.class)
  public int addFreshStudentsNew2(List<FreshStudentAndStudentModel> list, String schoolNo) {
      if (list == null || list.isEmpty()) {
          return 0;
      }
      List<StudentEntity> studentEntityList = new LinkedList<>();
      List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
      List<AllusersEntity> 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());  //密码设置为考生号
          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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
<insert id="insertAll" parameterType="com.dmsdbj.itoo.basicInfo.entity.EnrollStudentEntity">
      insert into tb_enroll_student
      <trim prefix="(" suffix=")" suffixOverrides=",">
              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        </trim>       
      values
      <foreach collection="list" item="item" index="index" separator=",">
      (
              #{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()       
      )  
      </foreach>               
</insert>

代码说明:

底层的mapper是通过逆向工程来生成的,批量插入如下,是拼接成类似: insert into tb_enroll_student()values (),()…….() ;

这样的缺点是,数据库一般有一个默认的设置,就是每次sql操作的数据不能超过4M。这样插入,数据多的时候,数据库会报错Packet for query is too large (6071393 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.,虽然我们可以通过

类似 修改 my.ini 加上 max_allowed_packet =67108864

67108864=64M

默认大小4194304 也就是4M

修改完成之后要重启mysql服务,如果通过命令行修改就不用重启mysql服务。

完成本次操作,但是我们不能保证项目单次最大的大小是多少,这样是有弊端的。所以可以考虑进行分组导入。

三、分组把list导入Mysql中

同样适用mybatis批量插入,区别是对每次的导入进行分组计算,然后分多次进行导入:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
@Transactional(rollbackFor = Exception.class)
   public int addFreshStudentsNew2(List<FreshStudentAndStudentModel> list, String schoolNo) {
       if (list == null || list.isEmpty()) {
           return 0;
       }
       List<StudentEntity> studentEntityList = new LinkedList<>();
       List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
       List<AllusersEntity> 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());  //密码设置为考生号
           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 <= c * b; e = e + c) {
           enResult = enrollStudentDao.insertAll(enrollStudentEntityList.subList(e - c, e));
           stuResult = studentDao.insertAll(studentEntityList.subList(e - c, e));
           allResult = allusersFacade.insertUserList(allusersEntityList.subList(e - c, e));
       }
       if (d != 0) {
           enResult = enrollStudentDao.insertAll(enrollStudentEntityList.subList(c * b, enrollStudentEntityList.size()));
           stuResult = studentDao.insertAll(studentEntityList.subList(c * b, studentEntityList.size()));
           allResult = allusersFacade.insertUserList(allusersEntityList.subList(c * b, allusersEntityList.size()));
       }
 
       if (enResult > 0 && stuResult > 0 && allResult) {
           return 10;
       }
       return -10;
   }

代码说明:

这样操作,可以避免上面的错误,但是分多次插入,无形中就增加了操作实践,很容易超时。所以这种方法还是不值得提倡的。

再次改进,使用多线程分批导入。

四、多线程分批导入Mysql

依然使用mybatis的批量导入,不同的是,根据线程数目进行分组,然后再建立多线程池,进行导入。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
@Transactional(rollbackFor = Exception.class)
  public int addFreshStudentsNew(List<FreshStudentAndStudentModel> list, String schoolNo) {
      if (list == null || list.isEmpty()) {
          return 0;
      }
      List<StudentEntity> studentEntityList = new LinkedList<>();
      List<EnrollStudentEntity> enrollStudentEntityList = new LinkedList<>();
      List<AllusersEntity> 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());  //密码设置为考生号
          allusersEntityList.add(allusersEntity);
      });
 
 
      int nThreads = 50;
 
      int size = enrollStudentEntityList.size();
      ExecutorService executorService = Executors.newFixedThreadPool(nThreads);
      List<Future<Integer>> futures = new ArrayList<Future<Integer>>(nThreads);
 
      for (int i = 0; i < nThreads; i++) {
          final List<EnrollStudentEntity> EnrollStudentEntityImputList = enrollStudentEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
          final List<StudentEntity> studentEntityImportList = studentEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
          final List<AllusersEntity> allusersEntityImportList = allusersEntityList.subList(size / nThreads * i, size / nThreads * (i + 1));
 
         Callable<Integer> task1 = () -> {
        studentSave.saveStudent(EnrollStudentEntityImputList,studentEntityImportList,allusersEntityImportList);
             return 1;
          };
        futures.add(executorService.submit(task1));
      }
      executorService.shutdown();
      if (!futures.isEmpty() && futures != null) {
          return 10;
      }
      return -10;
  }

代码说明:

上面是通过应用ExecutorService 建立了固定的线程数,然后根据线程数目进行分组,批量依次导入。一方面可以缓解数据库的压力,另一个面线程数目多了,一定程度会提高程序运行的时间。缺点就是要看服务器的配置,如果配置好的话就可以开多点线程,配置差的话就开小点。

五、小结

通过使用这个操作真是不断的提高了,项目使用技巧也是不错。加油~~ 多线程哦~~

到此这篇关于java多线程批量拆分List导入数据库的文章就介绍到这了,更多相关java多线程批量拆分List内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!

原文链接:https://blog.csdn.net/kisscatforever/article/details/79817039

延伸 · 阅读

精彩推荐
  • Java教程JVM 实战 OutOfMemoryError 异常

    JVM 实战 OutOfMemoryError 异常

    Java堆用于储存对象实例,我们只要不断地创建对象,并且保证GC Roots到对象之间有可达路径来避免垃圾回收机制清除这些对象,那么随着对象数量的增加,...

    运维开发故事8192021-08-31
  • Java教程Java 判断字符串中是否包含中文的实例详解

    Java 判断字符串中是否包含中文的实例详解

    这篇文章主要介绍了Java 判断字符串中是否包含中文的实例详解的相关资料,这里提供实例来说明该如何实现这样的功能,需要的朋友可以参考下...

    carl-zhao5722020-12-24
  • Java教程教你如何使用Java输出各种形状

    教你如何使用Java输出各种形状

    本文小编将向大家介绍的是如何利用Java输出各种不同的形状,本文一共介绍了七种有趣的形状,感兴趣的小伙伴赶快收藏起来吧...

    CodingDGSun4562022-01-06
  • Java教程java面向对象:API(接口)与集合(ArrayList)

    java面向对象:API(接口)与集合(ArrayList)

    这篇文章主要介绍了Java语言面向对象的API与集合,还是十分不错的,这里给大家分享下,需要的朋友可以参考,希望能够给你带来帮助...

    IT_ZI-O5722021-12-10
  • Java教程Java jvm垃圾回收详解

    Java jvm垃圾回收详解

    这篇文章主要介绍了JVM的垃圾回收总结,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编...

    月亮的-影子7812022-03-01
  • Java教程JAVA异常体系结构详解

    JAVA异常体系结构详解

    Java把异常当作对象来处理,并定义一个基类java.lang.Throwable作为所有异常的超类,下面通过本文给大家分享JAVA异常体系结构,感兴趣的朋友一起看看吧...

    dongguo5632021-02-07
  • Java教程基于Java 谈回调函数

    基于Java 谈回调函数

    回调函数就是一个通过函数指针调用的函数。如果你把函数的指针(地址)作为参数传递给另一个函数,当这个指针被用来调用其所 指向的函数时,我们就...

    hackeris7672020-10-23
  • Java教程详细聊聊Spring MVC重定向与转发

    详细聊聊Spring MVC重定向与转发

    大家应该都知道请求重定向和请求转发都是web开发中资源跳转的方式,这篇文章主要给大家介绍了关于Spring MVC重定向与转发的相关资料,文中通过示例代码介...

    Dynasty7762021-12-22