Hibernate Tips: How to use an ORDER BY clause in a CriteriaQuery



Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.

Join the Persistence Hub!


Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please leave a comment below.

Question:

How do I create a CriteriaQuery which returns the selected entities in the ascending order of an attribute?

Solution:

You can define an ORDER BY clause with the orderBy method of the CriteriaQuery interface and the asc or desc method of the CriteriaBuilder interface.

The following CriteriaQuery returns Book entities in the ascending order of their title attribute.

// Define the CriteriaQuery
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Book> cq = cb.createQuery(Book.class);
Root<Book> root = cq.from(Book.class);
cq.orderBy(cb.asc(root.get(Book_.title)));

// Execute query with pagination
List<Book> books = em.createQuery(cq).getResultList();

I get a CriteriaBuilder from the EntityManager and create a CriteriaQuery that returns Book entities.

Then I define the FROM clause by setting the Book entity as the Root of the query.

In the next step, I call the asc method on the CriteriaBuilder to create an ascending Order of the title attribute. The Book_ class, which I use to reference the title attribute, is part of the JPA metamodel. I then use the Order object to create the ORDER BY clause.

That’s all you need to do to define a CriteriaQuery with an ORDER BY clause. You can then use the CriteriaQuery to create and execute a TypedQuery.

Based on the CriteriaQuery, Hibernate generates the following SQL statement.

07:12:02,889 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as id1_1_,
        book0_.publisherid as publishe5_1_,
        book0_.publishingDate as publishi2_1_,
        book0_.title as title3_1_,
        book0_.version as version4_1_ 
    from
        Book book0_ 
    order by
        book0_.title asc

Learn more:

You can learn more about CriteriaQuerys with the following Hibernate Tips:

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!


Related Articles

Responses

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  1. thanks for this useful information

  2. thanks for this useful information