Spring Data JPA:添加分页后投影会损坏 [英] Spring Data JPA: Projection gets broken after adding pagination

问题描述

我有一个使用投影的查询，只要返回类型为List，它就可以很好地工作，但是添加分页后它将停止工作.

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.

这是工作代码:

@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);

一旦执行此操作，上下文就会开始失败，因为在解析时会在SELECT和FROM语句之间插入select count(me)，以使查询无效:

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:

由以下原因引起:org.hibernate.hql.internal.ast.QuerySyntaxException: 意外令牌:在第3行第1列附近选择[SELECT DISTINCT new com.mycompany.dto.MyDto(me.property1，me.property2，...)选择 count(me) FROM com.mycompany.MyEntiry me ...] 在org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) 在org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) 在org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:291) 在org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:186) 在org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141) 在org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:115) 在org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:77) 在org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) 在org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553) 在org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662) ...还有88个

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

问题:如何使其正常工作?是Spring Data错误吗?

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

注意:

• 我添加的查询过于简单，实际上我的实际查询从不同的表中收集了不同的值，没有投影就无法实现
• 我正在使用Spring Boot 1.5.8.RELEASE

推荐答案

尝试使用本机" Spring Data JPA

Try to use 'native' Spring Data JPA projections.

这应该很好:

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)

已更新

尝试添加参数

这篇关于Spring Data JPA:添加分页后投影会损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！