spring-data-jpa Spring Data JPA：添加分页后投影中断

zphenhs4 于 2022-11-10 发布在 Spring

关注(0)|答案(1)|浏览(195)

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 thatthe 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

spring-data-jpa

来源：https://stackoverflow.com/questions/50391119/spring-data-jpa-projection-gets-broken-after-adding-pagination

1条答案

按热度按时间

bmp9r5qi1#

请尝试使用“本机”Spring Data JPA投影。
这应该可以很好地工作：

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

Page<MyProjection> getDistinctAllBy(Pageable pageable);

但是，如果您的查询联接了许多表，则使用分页时会遇到一些麻烦（例如：第二个）

已更新

尝试将参数countQuery添加到@Query注解中：

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

赞(0）回复(0）举报 2022-11-10

我来回答

spring-data-jpa Spring Data JPA：添加分页后投影中断

1条答案

相关问题

热门标签

最新问答