java - 如何使用 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);
}

当我尝试执行 findAllUserName 时，它工作正常。但是当使用 findAllUserNameAndEmpCode.. 它会抛出以下异常 while starting tomcat:

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  

我不知道为什么，以及如何将此查询转换为 SELECT count(..) ？ expecting CLOSE, found ',' 是什么意思？？
请帮助..谢谢

Answer 1

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

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