【编程技巧】JPA(JPQL)批量操作的示例及真实执行逻辑

使用如下方式时，实际等同于先查询记录，然后根据记录的id进行精准删除，并且删除一个不存在的记录，会抛异常。

@Transactional
void deleteAllByIdIn(List<String> ids);

@Transactional
void deleteByName(String name);

show-sql 显式为：

Hibernate: select tkktable0_.id as id1_526_, tkktable0_.app as app2_526_, tkktable0_.code as code3_526_, tkktable0_.name as name5_526_ from tkk0302 tkktable0_ where tkktable0_.name=?

Hibernate: delete from tkk0302 where id=?

Hibernate: delete from tkk0302 where id=?

 

使用JPQL批量操作时，会直接翻译为native SQL，此种方式对PostCommitDeleteEventListener等实体变更的监听来说是捕捉不到的。

@Transactional
@Modifying
@Query(value = "update from TkkTable t set t.app = 'xyy' where t.name = :name and (t.code = '11')")
void updateAllByName(String name);

@Transactional
@Modifying
@Query(value = "delete from TkkTable t where t.name = ?1 and (t.code = '11')")
void deleteAllByName(String name);

对应SQL如下

Hibernate: update tkk0302 set app='xyy' where name=? and code='11'

Hibernate: delete from tkk0302 where name=? and code='11'

 

 

附注1：

sql中的参数有两种写法：

• 使用占位符：?1，依据参数顺序从1到n
• 使用命名参数：:name，相应的方法中的参数需要加上注解：@Param("name") String name

 

参考：

https://blog.csdn.net/weixin_39963096/article/details/111493213

https://blog.csdn.net/xiapi3/article/details/114318716