SpringDataJPA 高效批量操作

一、背景:

  • 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();
        }
    }
}

三、测试

@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. 循环写入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;
    }
}

六、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);
    

七、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"

  • 5
    点赞
  • 26
    收藏
    觉得还不错? 一键收藏
  • 5
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值