Search code examples
hibernatespring-data-jpaspring-data

Spring JPA with pageable and subquery causing query syntax error

I have a Spring Data Repository defined as given below:

public interface InvoiceRepository extends JpaRepository<Invoice, Long> {
   @Query(value = "select i.id as id,  " +
            "i.invoiceStatus as invoiceStatus, " +
            "(select sum(r.id) from Invoice r ) as paid " +
            "from Invoice i ")
    Page<InvoiceQuery> getInvoicesByStatusAndName(Pageable pageable);

}

The subquery given above is not the real one, but this simple query helps reproduce the the problem.

When my springboot(2.4.5) application comes up, I get the following exception

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting EOF, found ')' near line 1, column 62 [select count(r) from com.anish.webacc.orm.entities.Invoice r ) as paid from com.anish.webacc.orm.entities.Invoice i ] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.4.30.Final.jar:5.4.30.Final]

Now if i change

Page<InvoiceQuery> getInvoicesByStatusAndName(Pageable pageable);

to

List<InvoiceQuery> getInvoicesByStatusAndName(Pageable pageable);

then it works perfectly (without the paging part).

InvoiceQuery is a projection interface -

public interface InvoiceQuery {
    Long getId();
    InvoiceStatus getInvoiceStatus();
    Double getPaid();
}

It appears that the query generated is incorrect - for some reason i suspect that the paratheses in the subquery is causing the issue. If i remove the subquery i dont have an issue

This is the first time i am trying to do pagination so i maybe doing doing something really silly here - any help will be appreciated.

thx -anish

Solution

  • The problem is when you are using inner queries Spring data JPA is not able to derive the count query. See when you want return type as Page , Page - has total pages and total elements as the two things it returns along with the Paginated result.

       int getTotalPages();

    long getTotalElements();

    So to get these two things that is total elements with your conditions and total pages by given pagesize it has to derive a count() query, in your case it is unable to derive that count() query. You can explicitly specify the count query in your "@Query" annotation. Like below

      @Query(value = "select new com.example.package.InvoiceQuery(i.id as id,i.invoiceStatus as invoiceStatus,(select sum(r.id) from Invoice r ) as paid ) from Invoice i",
 countQuery = "select count(*) from Invoice")
    Page<InvoiceQuery> getInvoicesByStatusAndName(Pageable pageable);