使用Spring Data JPA自定义update执行慢的问题

https://blog.csdn.net/wherwh/article/details/89380494

public interface ParaRepository extends JpaRepository<Para,String>{
    @Modifying
    @Transactional 
    @Query(nativeQuery = true, value = "update clr_para set item_value = ?2 where item_name = ?1")
    int update(String itemName, String itemValue);
}

调用执行：

@Transactional
public void update() {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
    this.lastClearingDate = this.clearDate;
    paraRepository.update(ParaItemType.LAST_CLEARING_DATE.name().toLowerCase(),sdf.format(this.lastClearingDate));
}

该函数只是修改一个配置项记录. 但是,耗时30多秒.

 

解决方法一：采用JdbcTemplate执行同样的命令,耗时42毫秒.测试代码如下：

@Transactional
public void update() {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
    this.lastClearingDate = this.clearDate;
    
    JdbcTemplate jdbcTemplate = (JdbcTemplate)Application.getBean(JdbcTemplate.class);
    String sql = String.format("update clr_para set item_value='%s' where item_name='last_clearing_date'",sdf.format(this.lastClearingDate));
    jdbcTemplate.execute(sql);
}

解决方法二：不使用Native Query【差别：nativeQuery = false. value采用JPQL方法.】

public interface ParaRepository extends JpaRepository<Para,String>{
    @Modifying
    @Transactional 
    @Query(nativeQuery = false, value = "update Para set itemValue = ?2 where itemName = ?1")
    int update(String itemName, String itemValue);
}

 

 

执行Native Query之前执行了flush.

见下文

How does AUTO flush strategy work in JPA and Hibernate
https://vladmihalcea.com/how-does-the-auto-flush-work-in-jpa-and-hibernate/