将动态条件添加到JPA自定义查询 [英] Add dynamic criteria to a JPA custom Query

问题描述

我有一个使用HQL编写的复杂查询（使用多个连接和子查询），这是我在Repository类中使用的。类似于下面的内容 -

  @Repository 
 public interface DataRepository扩展PagingAndSortingRepository< Data，String> {
 
公共列表< Data> findByService（@Param（服务）服务服务，Pageable页面）; 
 
 @Query（SELECT DISTINCT d from Data d
 +WHERE（d.working in（SELECT d1 from Data d1
 +JOIN d1.working d1w 
 +JOIN d1.service s WITH（s in（：serviceList）））
 +或d.cleared IS NOT NULL）AND [..几个其他的CRITERIA]）
公共页面< Data> findForServices（@Param（serviceList）Set< Service> serviceList，Pageable页面）; 
 .... 
  

现在我需要动态添加条件。这些标准在数量上是很灵活的，这使我不能将它包括进 HQL 直通车中。无论如何可能吗？

通过互联网筛选我遇到了动态查询。但是，我猜他们只适用于在存储库中的查询中没有自定义查询的情况，即 @Query / code>。

还有另外一个有趣的问题我找到了。但是，这也适用于需要查询单个表的情况。

我不想切换到原始SQL查询。如何解决这个问题？

解决方案

提及的规范和谓词的Criteria API有点难以适应，但它是处理动态条件的好方法。
我认为不可能将基于注释的查询与编程式查询创建混合在一起。

I have a complex query(using multiple joins and subqueries) written in HQL which I have used in a Repository class. Similar to one below -

@Repository
public interface DataRepository extends PagingAndSortingRepository<Data,String> {

    public List<Data> findByService(@Param("service")Service service, Pageable page);

    @Query("SELECT DISTINCT d from Data d "
            +" WHERE (d.working in (SELECT d1 from Data d1 "
                +" JOIN d1.working d1w "
                +" JOIN d1.service s WITH (s in (:serviceList)))"
            +" OR d.cleared IS NOT NULL) AND [..several other CRITERIA]")
    public Page<Data> findForServices(@Param("serviceList")Set<Service> serviceList, Pageable page);
....

Now I need to add criteria to it dynamically. These criteria are flexible in number which is holding me from including it into the HQL straightaway. Is it anyhow possible?

Sifting through the internet I have come across solutions for dynamic query. But, I guess they would be working only for cases where I do not have a custom query i.e.- no @Query at the query in the repository.

There was another interesting question I found. But that also suits for a case where you have a single table to query.

I do not want to be switching over to raw SQL queries. How do I solve this?

解决方案

The mentioned Criteria API with specifications and predicates is a little bit difficult to get used to but it is a good way to handle dynamic conditions. I don't think it is possible to mix the annotation based query with programmatic query creation.

这篇关于将动态条件添加到JPA自定义查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！