Hibernate Criteria API - Ordering Results (ORDER BY Clause)

The ORDER BY clause is used to sort the results of a database query based on one or more columns. In Hibernate, you achieve this using the Criteria API and its orderBy method. This allows you to retrieve data in a specific order, such as alphabetically, numerically, or chronologically.

Understanding the ORDER BY Clause

The basic syntax of the ORDER BY clause in SQL is:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...; 

• ORDER BY: Specifies the column(s) to sort by.
• ASC: Specifies ascending order (default).
• DESC: Specifies descending order.

Ordering Results with Hibernate Criteria API

The Hibernate Criteria API provides the orderBy() method to implement the equivalent of the SQL ORDER BY clause. You typically use it in conjunction with the CriteriaBuilder and Root objects.

Ascending Order

To sort results in ascending order (from smallest to largest, or A to Z), you use criteriaBuilder.asc(root.get("propertyName")).

Example (Ascending Order):

 import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Root;
import jakarta.persistence.criteria.Order;
import java.util.List;

public class CriteriaOrderByAscExample {

    public static void main(String[] args) {
        StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
                .configure() // configures settings from hibernate.cfg.xml
                .build();
        try {
            SessionFactory sessionFactory = new MetadataSources( registry ).buildMetadata().buildSessionFactory();

            try (Session session = sessionFactory.openSession()) {
                Transaction transaction = session.beginTransaction();

                // Example: Assuming you have a 'Product' entity with a 'name' field
                CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
                CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
                Root<Product> root = criteriaQuery.from(Product.class);

                // Order by product name in ascending order
                Order order = criteriaBuilder.asc(root.get("name"));
                criteriaQuery.orderBy(order);

                List<Product> products = session.createQuery(criteriaQuery).getResultList();

                for (Product product : products) {
                    System.out.println("Product Name: " + product.getName());
                }

                transaction.commit();
            }
            sessionFactory.close();
        }
        catch (Exception e) {
            // handle the exception
            e.printStackTrace();
            StandardServiceRegistryBuilder.destroy( registry );
        }
    }
}

// Sample Product entity (replace with your actual entity)
import jakarta.persistence.*;

@Entity
@Table(name = "products")
class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private double price;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public double getPrice() {
		return price;
	}

	public void setPrice(double price) {
		this.price = price;
	}

	@Override
	public String toString() {
		return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";
	}


} 

Explanation:

• A CriteriaBuilder is created from the session.
• A CriteriaQuery of type Product is created.
• A Root<Product> is created from the query, representing the entity being queried.
• The `criteriaBuilder.asc(root.get("name"))` creates an `Order` object specifying ascending order on the "name" property of the Product entity.
• The `criteriaQuery.orderBy(order)` sets the order for the query.
• Finally, the query is executed and the results are printed.

Descending Order

To sort results in descending order (from largest to smallest, or Z to A), you use criteriaBuilder.desc(root.get("propertyName")).

Example (Descending Order):

 import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Root;
import jakarta.persistence.criteria.Order;

import java.util.List;

public class CriteriaOrderByDescExample {

    public static void main(String[] args) {
        StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
                .configure() // configures settings from hibernate.cfg.xml
                .build();
        try {
            SessionFactory sessionFactory = new MetadataSources( registry ).buildMetadata().buildSessionFactory();

            try (Session session = sessionFactory.openSession()) {
                Transaction transaction = session.beginTransaction();

                // Example: Assuming you have a 'Product' entity with a 'price' field
                CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
                CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
                Root<Product> root = criteriaQuery.from(Product.class);

                // Order by product price in descending order
                Order order = criteriaBuilder.desc(root.get("price"));
                criteriaQuery.orderBy(order);

                List<Product> products = session.createQuery(criteriaQuery).getResultList();

                for (Product product : products) {
                    System.out.println("Product Name: " + product.getName() + ", Price: " + product.getPrice());
                }

                transaction.commit();
            }
            sessionFactory.close();
        }
        catch (Exception e) {
            // handle the exception
            e.printStackTrace();
            StandardServiceRegistryBuilder.destroy( registry );
        }
    }
} 

Explanation:

• This example is similar to the ascending order example, except it uses criteriaBuilder.desc(root.get("price")) to specify descending order on the "price" property.

Ordering by Multiple Columns

You can order by multiple columns by passing multiple Order objects to the orderBy() method. The order of the Order objects determines the precedence of sorting.

Example (Ordering by multiple columns):

 import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Root;
import jakarta.persistence.criteria.Order;

import java.util.List;

public class CriteriaOrderByMultipleExample {

    public static void main(String[] args) {
        StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
                .configure() // configures settings from hibernate.cfg.xml
                .build();
        try {
            SessionFactory sessionFactory = new MetadataSources( registry ).buildMetadata().buildSessionFactory();

            try (Session session = sessionFactory.openSession()) {
                Transaction transaction = session.beginTransaction();

                // Example: Assuming you have a 'Product' entity with a 'name' and 'price' field
                CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
                CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
                Root<Product> root = criteriaQuery.from(Product.class);

                // Order by name (ascending) then by price (descending)
                Order orderName = criteriaBuilder.asc(root.get("name"));
                Order orderPrice = criteriaBuilder.desc(root.get("price"));
                criteriaQuery.orderBy(orderName, orderPrice);

                List<Product> products = session.createQuery(criteriaQuery).getResultList();

                for (Product product : products) {
                    System.out.println("Product Name: " + product.getName() + ", Price: " + product.getPrice());
                }

                transaction.commit();
            }
            sessionFactory.close();
        }
        catch (Exception e) {
            // handle the exception
            e.printStackTrace();
            StandardServiceRegistryBuilder.destroy( registry );
        }
    }
} 

Explanation:

• This example first orders by "name" in ascending order and then by "price" in descending order. This means if multiple products have the same name, they will then be sorted by price in descending order.

Important Considerations

• Type Safety: Using the Criteria API provides type safety, as you're working with Java objects and properties rather than raw SQL strings.
• Readability: Criteria queries can be more readable and maintainable than complex HQL queries, especially when dealing with dynamic sorting requirements.
• Performance: Ordering can impact performance, especially on large datasets. Ensure your database indexes are optimized for the columns you're ordering by.
• Null Handling: The behavior of how NULL values are ordered can vary between databases. Consult your database documentation for details. Hibernate uses the database's default behavior.