Spring boot+JPA(hibernate)配置复杂查询的坑

最新推荐文章于 2025-06-17 09:11:16 发布

原创最新推荐文章于 2025-06-17 09:11:16 发布 · 7.3k 阅读

2 ·

CC 4.0 BY-SA版权

javaweb 专栏收录该内容

12 篇文章

订阅专栏

本文作者从MyBatis转向Hibernate时遇到各种问题，分享了HQL的限制，如不支持union操作，需要转换实现；MySQL与其它数据库在SQL语法上的细微差别，如MySQL删除操作中不能嵌套查询；以及Spring Boot+JPA配置复杂查询的解决办法，例如使用@Modifying、@Transactional和nativeQuery来处理复杂删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

小弟刚从mybatis转到hibernate，遇到了不少坑，在这里不定期更新。以告慰相同处境的亲。

1、首先hibernate的HQL语法有很多坑，有些在SQL中能执行的语句，在HQL中未必能执行，比如HQL就不支持union操作，可以考虑把你要union的两个查询分开写，然后将结果合起来，传到你要使用union的地方。

2、其次，注意mysql数据库与SqlServer、oracle数据库存在差别的，虽然SQL语法大体差不多，但是在细小的地方还是不同。

比如：MySQL不支持，在增删操作语句中嵌套本表的查询子语句。

delete from table where id in(select id from table where id >10);//会报错啊，兄弟们，提示mysql不支持自身子嵌套。

修改成：

delete from table where id in(select id from (select id from table where id >10) as A);//成功。

3、对于Spring boot+JPA的复杂查询，需要进行如下配置才可以。不然会报如下错误：

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'mainApi':
Unsatisfied dependency expressed through field 'checkProductStatusService': Error creating bean with name 'checkProductStatusService':
Unsatisfied dependency expressed through field 'checkProductRepository': Error creating bean with name 'checkProductStatusRepository':
Invocation of init method failed; nested exception is java.lang.IllegalArgumentException:
Validation failed for query for method public abstract java.util.List com.mode.repository.CheckProductStatusRepository.findID2()!;
nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'checkProductStatusRepository':
Invocation of init method failed; nested exception is java.lang.IllegalArgumentException:
Validation failed for query for method public abstract java.util.List com.mode.repository.CheckProductStatusRepository.findID2()!;
nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'checkProductStatusService':
Unsatisfied dependency expressed through field 'checkProductRepository': Error creating bean with name 'checkProductStatusRepository':
Invocation of init method failed; nested exception is java.lang.IllegalArgumentException:
Validation failed for query for method public abstract java.util.List com.mode.repository.CheckProductStatusRepository.findID2()!;
nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'checkProductStatusRepository':
Invocation of init method failed; nested exception is java.lang.IllegalArgumentException:
Validation failed for query for method public abstract java.util.List com.mode.repository.CheckProductStatusRepository.findID2()!

以delete为例：

@Transactional
public interface CheckProductStatusRepository extends JpaRepository<CheckProductStatus, Long> {

@Modifying
@Transactional
@Query(value = "delete from check_product_status where id not in (:listParam)", nativeQuery = true)
void deleteCheckProductStatus(@Param("listParam") List<Integer> listParam);

@Query(value = "select id from check_product_status where product_url is not null and product_url <>'' group by product_url", nativeQuery = true)
List<Integer> findID1();

@Query(value = "select id from check_product_status where product_url is null or product_url ='' group by spu", nativeQuery = true)
List<Integer> findID2();
}