SpringDataJPA 高效批量操作_jpa 后端 批量调用

一、背景：

• SpringDataJPA的API是没有高效批量操作的，它的saveAll底层是循环操作并且先读取数据库检查数据是否存在再去插入数据，性能效率很低。
• 写过了好多增删改查的代码，功能也实现了很多，也是因为没有遇到大批量的MySQL数据库操作业务，就没有去找高效的数据库操作方法，之前的代码都是循环操作数据库。
• 系统版本升级在今天差不多已经完结，迁移完数据库后，就顺便抽了时间找找怎么样能在SpringBoot整合的SpringDataJPA项目中高效的批量操作数据库，以便后期优化之前的代码，接下来言归正。
• SpringDataJPA实战项目很好用，在第六标题有个人观点。

二、BatchService.java

1. service实现层通过@PersistenceContext注解注入EntityManager接口。
2. 批量写入调用persist方法（参数为实体对象），再调用flush刷新到数据库，后clear。
3. 更新数据调用merge（参数为实体对象），同样调用flush刷新到数据库，后clear。

@Transactional
@Service
public class BatchService {

    @PersistenceContext
    private EntityManager entityManager;

    /**
     * 批量插入
     *
     * @param list 实体类集合
     * @param <T>  表对应的实体类
     */
    public <T> void batchInsert(List<T> list) {
        if (!ObjectUtils.isEmpty(list)){
            for (int i = 0; i < list.size(); i++) {
                entityManager.persist(list.get(i));
                if (i % 50 == 0) {
                    entityManager.flush();
                    entityManager.clear();
                }
            }
            entityManager.flush();
            entityManager.clear();
        }
    }

    /**
     * 批量更新
     *
     * @param list 实体类集合
     * @param <T>  表对应的实体类
     */
    public <T> void batchUpdate(List<T> list) {
        if (!ObjectUtils.isEmpty(list)){
            for (int i = 0; i < list.size(); i++) {
                entityManager.merge(list.get(i));
                if (i % 50 == 0) {
                    entityManager.flush();
                    entityManager.clear();
                }
            }
            entityManager.flush();
            entityManager.clear();
        }
    }
}
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

三、测试

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class DatabaseTest {

    @Value("${indexCode}")
    private String indexCode;
    @Resource
    private StockRepository stockRepository;
    @Resource
    private BatchService batchService;

    /**
     * 循环写入
     */
    @Test
    public void forTest() {
        List<String> indexCodeArr = Arrays.asList(indexCode.split(","));
        Set<String> indexCodeSet = new HashSet<>(indexCodeArr);
        log.info("indexCodeSet:{}", JSONObject.toJSONString(indexCodeSet));
        log.info("indexCodeSe.size():{}", indexCodeSet.size());

        long timeIdStart = System.currentTimeMillis();
        String time = TimeUtil.FORMAT.get().format(timeIdStart);
        int record = 0;
        for (String indexCode : indexCodeSet) {
            record += stockRepository.updateIndexCalculated(indexCode, time);
        }
        log.info("record:{}", record);
        log.info("运行时间,time:{}秒", (System.currentTimeMillis() - timeIdStart) / 1000.0);
    }

    /**
     * 批量写入
     */
    @Test
    public void batchTest() {
        List<String> indexCodeArr = Arrays.asList(indexCode.split(","));
        Set<String> indexCodeSet = new HashSet<>(indexCodeArr);
        log.info("indexCodeSet:{}", JSONObject.toJSONString(indexCodeSet));
        log.info("indexCodeSe.size():{}", indexCodeSet.size());

        long timeIdStart = System.currentTimeMillis();
        String time = TimeUtil.FORMAT.get().format(timeIdStart);
        List<IndexCalculated> list = new ArrayList<>();
        for (String indexCode : indexCodeSet) {
            IndexCalculated indexCalculated = new IndexCalculated();
            indexCalculated.setIndexCode(indexCode);
            indexCalculated.setUpdateTime(time);
            list.add(indexCalculated);
        }
        batchService.batchInsert(list);
//        batchService.batchUpdate(list);
        log.info("运行时间,time:{}秒", (System.currentTimeMillis() - timeIdStart) / 1000.0);
    }
}
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

四、测试结果

1. 循环写入74条记录用时12.342秒。
2. 批量写入74条记录用时2.139秒。
3. 体验感受：循环写入方式操作几十条数据需要十多秒，而通过EntityManager批量操作则可以将时间减少到两秒，简直不要太爽！！

五、 实体类（附加）

@Table(name = "index_calculated")
@Entity
public class IndexCalculated implements Serializable {

    @Id
    @Column(name = "index_code")
    private String indexCode;

    @Column(name = "update_time")
    private String updateTime;

    public String getIndexCode() {
        return indexCode;
    }

    public void setIndexCode(String indexCode) {
        this.indexCode = indexCode;
    }

    public String getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(String updateTime) {
        this.updateTime = updateTime;
    }
}
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

六、Repository.java（附加，个人觉得JPA很方便实用）

1. nativeQuery属性设置为true，可以在value里写原生sql即数据库能直接运行的sql，避开JPA的API这样就很灵活，便于sql优化。
2. 就第一点来说再加上SpringDataJPA与SpringBoot的方便整合，SpringDataJPA确实很方便好用，省去了Mybatis的xml配置。

@Repository
public interface StockRepository extends JpaRepository<IndexCalculated, String> {

    @Query(nativeQuery = true, value = "SELECT `stock_code` AS `stockCode`,`stock_name` AS `stockName`,`stock_display_name` AS `stockDisplayName` FROM `stock_security`")
    List<Map<String, Object>> stockIndexInfoOfStock();

    @Query(nativeQuery = true, value = "SELECT `index_code` AS `stockCode`,`index_name` AS `stockName`,`index_display_name` AS `stockDisplayName` FROM `index_info` WHERE `index_code` IN (SELECT `index_code` FROM `index_calculated`)")
    List<Map<String, Object>> stockIndexInfoOfIndex();

    @Query(nativeQuery = true, value = "SELECT `stock_code` FROM `sector_stock` WHERE `sector_code` IN ?1")
    List<Object> stockOfSector(String[] sectorCodeArr);

    @Query(nativeQuery = true, value = "SELECT `name` AS `sectorName`,`stock_code` AS `stockCode` FROM `industry_sector`,`sector_stock` WHERE industry_sector.`code`=sector_stock.`sector_code`")
    List<Map<String, Object>> industryOfStock();

    @Query(nativeQuery = true, value = "SELECT `index_code` AS `code`,`index_name` AS `name`,`index_display_name` AS `displayName` FROM `index_info` WHERE `index_code` IN (SELECT `index_code` FROM `index_calculated`)")
    List<Map<String, Object>> allIndexInfoOfCalculated();

    @Transactional
    @Modifying
    @Query(nativeQuery = true, value = "INSERT INTO `index_calculated`(`index_code`,`update_time`) VALUES (?1,?2)")
    int updateIndexCalculated(String indexCode, String updateTime);
    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

七、application.yml（附加）

server:
  port: 9116
spring:
  #  main:
  #    web-application-type: none
  datasource:
    url: jdbc:mysql://###:3306/v1_stock_market_system?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8&autoReconnect=true&failOverReadOnly=false
    username: ###
    password: ###
    driver-class-name: com.mysql.jdbc.Driver
    hikari:
      read-only: false
      connection-timeout: 60000
      idle-timeout: 60000
      validation-timeout: 3000
      max-lifetime: 60000
      login-timeout: 5
      maximum-pool-size: 60
      minimum-idle: 10
  jpa:
    generate-ddl: false
    show-sql: false
    hibernate:
      ddl-auto: none
    database: mysql
    open-in-view: true
  redis:
    host: ###
    port: ###
    password: ###
  elasticsearch:
    jest:
      uris: http://###:9132,http://###:9132,http://###:9132,http://###:9132,http://###:9132

indexCode: "399695.XSHE,399005.XSHE,399001.XSHE,000003.XSHG,000002.XSHG,000001.XSHG,399006.XSHE,000016.XSHG,000033.XSHG,000034.XSHG,000036.XSHG,000039.XSHG,000040.XSHG,000041.XSHG,000042.XSHG,000055.XSHG,000056.XSHG,000062.XSHG,000097.XSHG,000122.XSHG,000134.XSHG,000158.XSHG,000159.XSHG,000160.XSHG,000161.XSHG,000162.XSHG,000300.XSHG,000812.XSHG,000813.XSHG,000819.XSHG,000941.XSHG,000943.XSHG,000944.XSHG,000945.XSHG,000949.XSHG,399001.XSHE,399005.XSHE,399006.XSHE,399300.XSHE,399355.XSHE,399356.XSHE,399368.XSHE,399380.XSHE,399393.XSHE,399394.XSHE,399395.XSHE,399396.XSHE,399417.XSHE,399418.XSHE,399419.XSHE,399420.XSHE,399429.XSHE,399431.XSHE,399432.XSHE,399433.XSHE,399434.XSHE,399436.XSHE,399438.XSHE,399439.XSHE,399440.XSHE,399441.XSHE,399678.XSHE,399687.XSHE,399688.XSHE,399693.XSHE,399695.XSHE,399803.XSHE,399804.XSHE,399805.XSHE,399806.XSHE,399808.XSHE,399928.XSHE,399929.XSHE,399960.XSHE,399991.XSHE,399994.XSHE,399996.XSHE,399106.XSHE"

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