2

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?

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
Rajeev Ranjan
  • 3,588
  • 6
  • 28
  • 52
  • Is it not possible to convert to `CriteriaQuery` that would be easy to construct dynamically? I am a bit sceptic about other possibilities. What exactly you meant _But that also suits for a case where you have a single table to query_? Maybe problems with this https://stackoverflow.com/a/30436524/6413377? – pirho Nov 13 '17 at 14:48

1 Answers1

1

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.

Nathanael
  • 361
  • 1
  • 3
  • 12