How to retrieve specific columns in Paginated way using Spring Data JPA?

Question

I have One entity class, its service and repository as follows:

@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

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

When I am trying to execute findAllUserName it works properly. but when using findAllUserNameAndEmpCode.. it throws following exceptions 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

I dont know why, and how its converting this query to SELECT count(..) ? What is meaning of expecting CLOSE, found ',' ??
Please help.. Thanks

Have you tried Page findAllUserName(Pageable pageable); ? BTW the SELECT count(..) is fired because of Page return value so that you know total number of elements matching your criteria. — daerin, Aug 25 '14 at 11:56
@daerin question updated btw. `findAllUserName` works properly but when trying to return two column its giving error. in case of `findAllUserNameAndEmpCode`.. application gives error while starting. — Aman Gupta, Aug 25 '14 at 11:59
Maybe this will help: http://stackoverflow.com/questions/22007341/spring-jpa-selecting-specific-columns — daerin, Aug 25 '14 at 12:04
@daerin Thanks.. I looked at it initially.. it works.. but result is not paginated.. somehow I need `Page` entity to be returned — Aman Gupta, Aug 25 '14 at 12:07
why do you need to limit the columns. Can't you just find all. I doubt your User class contains too much anyway for any optimization to be needed — Niels Bech Nielsen, Aug 25 '14 at 12:29
@NielsBechNielsen Yes User class contains other properties too.. and one simply may not want to return all those properties if he only require few columns at client side. — Aman Gupta, Aug 25 '14 at 13:06

score 8 · Accepted Answer · answered Aug 25 '14 at 12:29

You should specify the count query. The Page return value of your select function needs to know how many results there will be. So it sends a COUNT query that is probably made from your select query and looks like this:

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

which is wrong because COUNT function takes only one parameter. So you should create your custom count query (probably like this):

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

and place it into @Query annotation:

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

in spring data version 4.x, it is no need set @Query's countQuery prop. — sendreams, Jan 24 '16 at 06:45

How to retrieve specific columns in Paginated way using Spring Data JPA?

1 Answers1