如何使用Spring Data JPA以分页的方式检索特定列？

Question

@Entity
@Table(name = "user")
public class User implements Serializable{

    @Id
    @Column(name = "id", unique = true)
    private String userId;

    @Column(name = "user_name")
    private String userName;

    @Column(name = "emp_code")
    private String empCode;
    // ... other properties
}  

@Repository
public interface UserRepository extends PagingAndSortingRepository<User, String>
{
    // .... working 
    @Query("select u.userName from User u")
    Page<User> findAllUserName(Pageable pageable);


    //... not working
    @Query("select u.userName, u.empCode from User u")
    Page<User> findAllUserNameAndEmpCode(Pageable pageable);
}

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found ',' near line 1, column 29 [select count(u.userName,u.empCode) from com.entity.User u]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:278)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:138)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:105)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:221)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:199)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1778)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291)
... 63 more  

Answer 1

您应该指定计数查询。您的select函数的页面返回值需要知道将有多少结果。因此它会发送一个COUNT查询,该查询可能是从您的select查询中生成的,如下所示：

select count(u.userName,u.empCode) from com.entity.User u

这是错误的，因为COUNT函数只需要一个参数。因此您应该创建自定义计数查询（可能如下所示）：

select count(u.userName) from com.entity.User u

并将其放入@query注释中：

@Query(
    value = "select u.userName, u.empCode from User u",
    countQuery = "select count(u.userName) from com.entity.User u"
)
Page<User> findAllUserNameAndEmpCode(Pageable pageable);