Count in Spring Data JPQL join query throwing IllegalArgumentException

user_4567083 0
Viewed 457

I have a query using JPQL in the annotation @Query in Spring Data which looks like below

@Query(value = "select distinct a from EntityA as a left join fetch a.listEntityB as b where ...")
  public List<EntityA > find....(@Param("") String value, Pageable pageable);

Now I need to know the total number of records of the query. As Pageable is giving me partial list, I am writing another count query without the Pageable. My count query looks like below:

@Query(value = "select count(distinct a) from EntityA as a left join fetch a.listEntityB as b where ...) 
  public long count...(@Param("") String value);

However, I am getting "IllegalArgumentException: Validation failed for query" error from this query to count the records. When I change the count query to "select count(distinct a) from EntityA" , it works fine. I am not sure what is the problem when I use the join. I did not get any helpful documents so far. Is there any better solution to get the total number of records for the Pageable query.

spring-data-jpa jpql
1 Answers

Put the CountQuery in the same annotation as the query itself. So

@Query(value = "select ...", countQuery = "select count..")
public List<EntityA > find....(@Param("") String value, Pageable pageable);

Also remove the "fetch" from the countQuery.

To get the total records, you can just reference the page.getTotalElements() to return the total number of records.

user_2139840
Related