Error "org.hibernate.query.sqm.ParsingException: line 1:21 mismatched input 'rtn' expecting {<EOF>, ',', FROM, GROUP, ORDER, WHERE}"

I'm stuck with this error when i try to limit result to n rows.

The query is defined in the query atribute. Here the method

public interface PaymentRepositoryJpa extends JpaRepository<Payment, Long> {

@Query("SELECT top(:top) new rtn.domain.common.structs.Payment(p.id, entryDate, paymentDate, ...)"
            + " from SomeTable" 
            + " where p.state = 'P'")
List<Payment> findTopN( @Param("top") int  top);

when findTopN is invoked i get the exception:

Error "org.hibernate.query.sqm.ParsingException: line 1:21 mismatched input 'rtn' expecting {, ',', FROM, GROUP, ORDER, WHERE}"

This looks more a error from the JPA framework rather then an SQL error.

The query works fine if i remove the top parameter.

There are many posts explaining how to limit the results but can't find one that combines the constructor, the query attribute and the top parameter. Any idea in what i am doing wrong?

I'm using java spring JPA, version 3.0.3.

Solution

As far as I can tell top is not a JPQA keyword. It's only available is native in DBs that support - see this answer

It seems your options are this, that abuses Pageable

@Query("SELECT new rtn.domain.common.structs.Payment(p.id, entryDate, paymentDate, ...)"
            + " from SomeTable" 
            + " where p.state = 'P'")
    List<Payment> findByTopN(Pageable pageable);
}

And in the Service, use a PageRequest, returning a Page object:

Page<Payment> paymentsPage = paymentRepositoryJpa.findByTop(PageRequest.of(0, top));
List<Payment> payments= paymentsPage.getContent();

or this, which leverages setMaxResults():

List<Payment> findTopN(@Param("top") int  top) {
    return entityManager.createQuery("SELECT new rtn.domain.common.structs.Payment(p.id, entryDate, paymentDate, ...)"
            + " from SomeTable" 
            + " where p.state = 'P'",
      Payment.class).setMaxResults(top).getResultList();
}