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

Question

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

Answer 1

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

Answer 2

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