CODEWITHSUNDEEP
hibernatejpasql-order-byjpa-2.1
Dave A
Dave A

Reputation: 2830

How do I tell JPA CriteriaBuilder to order by the only column of data returned?

I’m using JPA 2.1 with Hibernate 4.3.6.Final (MySQL 5.5). In my JPA CriteriaBuilder query, my only column returned is a expression that ultimately evaluates to an Integer. However, how do I tell JPA to order the results based on the first column alone? I have …

    final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
    final CriteriaQuery<Integer> criteria = builder.createQuery(Integer.class);
    final Root<User> user = criteria.from(User.class);
    criteria.where(builder.and(builder.like(user.get(User_.userName), userNamePrefix + "%"),
                               builder.equal(user.get(User_.url), url)));

    criteria.select( builder.substring(user.get(User_.userName), userNamePrefix.length()).as(Integer.class) );
    criteria.orderBy( ??? );

What do I put in “???” to tell JPA to order on the only column of data returned? I realize I could get all columns and use Java to sort, but I would like to avoid that at the moment.

Thanks, - Dave

Upvotes: 2

Views: 5861

Answers (2)

Fabian
Fabian

Reputation: 3440

I ended up using the criteriaBuilder to build an Order object for me. ie

criteria.orderBy(builder.asc(user.get(User_.userNmae)));

Similar question and more detailed answer : JPA criteria query, order on class

Upvotes: 0

Neil Stockton
Neil Stockton

Reputation: 11531

Something like

Expression selectedExpr = builder.substring(user.get(User_.userName);
criteria.select(selectedExpr, userNamePrefix.length()).as(Integer.class));
Order orderExpr = builder.asc(selectedExpr);
criteria.orderBy(orderExpr);

Upvotes: 1

Related Questions