Spring Data JPA: Projection gets broken after adding pagination

Question

I have a query that uses projections, it works well as long as the return type is List, but it stops working after adding pagination.

Here is the the working code:

@Query("SELECT DISTINCT \n" +
        "  new com.mycompany.dto.MyDto(me.property1, me.property2, ...) \n" +
        "FROM MyEntiry me...")
List<MyDto> findEntities();

I need to extend it adding pagination, so I change it to:

@Query("SELECT DISTINCT \n" +
        "  new com.mycompany.dto.MyDto(me.property1, me.property2, ...) \n" +
        "FROM MyEntiry me...")
Page<MyDto> findEntities(Pageable pageable);

Once I do that the context starts failing because while parsing it inserts select count(me) between SELECT and FROM statements so that the query become invalid:

SELECT DISTINCT 
  new com.mycompany.dto.MyDto(me.property1, me.property2, ...)  
select count(me) FROM com.mycompany.MyEntiry me ...

The context fails with the following exception:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: select near line 3, column 1 [SELECT DISTINCT new com.mycompany.dto.MyDto(me.property1, me.property2, ...) select count(me) FROM com.mycompany.MyEntiry me ...] at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:291) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:186) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:115) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:77) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662) ... 88 more

Question: How to make it work? Is it Spring Data bug?

Note:

The query I added is oversimplified, my real query in fact gathers different values from different tables and I can't implement it without projections
I'm using Spring Boot 1.5.8.RELEASE

Cepr0 Cepr0 · Accepted Answer · 2018-05-17T12:32:08

Try to use 'native' Spring Data JPA projections.

This should work well:

public interface MyProjection {
    String getProperty1();
    //...
}

Page<MyProjection> getDistinctAllBy(Pageable pageable);

But, if your query joins many tables you cannot use the pagination without some pain (for example: 1, 2)

UPDATED

Try to add parameter countQuery to @Query annotation:

@Query(value = "select ...", countQuery = "select count(me) from MyEntiry me")
Page<MyDto> findEntities(Pageable pageable);

Spring Data JPA: Projection gets broken after adding pagination

1 Answers