|

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


Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.


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!

4 Comments

  1. thanks for this useful information

    1. Avatar photo Thorben Janssen says:

      You’re welcome

  2. Avatar photo Navdeep Singh says:

    thanks for this useful information

    1. Avatar photo Thorben Janssen says:

      Thanks for reading my blog 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

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