jpa在批量添加的时候，存储慢如何解决问题

46 阅读 0 评论 31 点赞

我是靠谱客的博主听话蜗牛，最近开发中收集的这篇文章主要介绍jpa在批量添加的时候，存储慢如何解决问题，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

spring.datasource.url = jdbc:mysql://xxxxxxxx:xxxx/xxxxx?useSSL=false&useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&autoReconnect=true

入口下加@EnableTransactionManagement
@SpringBootApplication
@EnableTransactionManagement
service实现类下加
@Service
@Transactional
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTempla

1、controller层

//获取用户答题
@CrossOrigin
@RequestMapping("/saveAnswer1")
public void saveAnswer1() throws IOException, ParseException {
   List<ScoreVO> listscoreVO = new ArrayList<ScoreVO>();
   for(int i=0;i<=20;i++)   {
       ScoreVO score=new ScoreVO();
       score.setTasId(UUID.randomUUID().toString().replaceAll("-", ""));
        score.setQuId("1234567");
        score.setSurveyId("134567890ghjk");
        score.setTssId("234567890-");
        score.setUserId("SFSFSFSDF");
        listscoreVO.add(score);
   }
   long start = System.currentTimeMillis();
   //改造前后代码、自行补充
   int result=answerService.test(listscoreVO);
   long end = System.currentTimeMillis();
   System.out.println("花费时间:"+(end-start));
   System.out.println("插入数据:"+result);
}

2、service层

public void test(List<ScoreVO> listscoreVO) {
   //批量转数组
SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(listscoreVO.toArray());
String sql = "INSERT INTO t_answer_score(tas_id,user_id,survey_id,qu_id,tss_id) VALUES (:tasId,:userId,:surveyId,:quId,:tssId)";
namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
   return 0;
}

3、实体类

/**
*  用户答题分数表
*/
@Entity
@Getter
@Setter
@ToString
@NoArgsConstructor
@Table(name = "t_answer_score")
@GenericGenerator(name = "jpa-uuid", strategy = "uuid")
public class ScoreVO implements Serializable {
private static final long serialVersionUID = -3199317015260021676L;
@Id
@GeneratedValue(generator = "jpa-uuid")
@Column(length = 32)
private String tasId;
private String userId;
private String surveyId;
private String quId;
private Double score;
private String tssId;

}

开启数据空批量配置：rewriteBatchedStatements=true
开启事务：@EnableTransactionManagement ，@Transactional
本人测试了几种批量保存的方式 1、saveAll jpa自带方法 2、改造saveAll方法，直接不进行比对保存的与表中数据是否相同直接使用 JPA EntityManager persist 方法进行循环保存 3、jdbcTemplate .batchUpdate 方法进行保存都不如上述方法效率高，前提是主键值是由自己主动生成，大家可以试试