Guide to Sorting using Hibernate

Learn to sort the entities fetched from the database using hibernate HQL, native SQL, Criteria queries and Comparator interface. Note that imposing an order on a set is called sorting.

1. Using HQL

1.1. Simple Sorting

Sorting the result using HQL is very easy. We just need to put the ‘ORDER BY‘ clause in the query according to our requirements.

String hql = "FROM EmployeeEntity e ORDER BY e.firstName;

SelectionQuery query = session.createQuery(hql, EmployeeEntity.class);
List<EmployeeEntity> employeeList = query.getResultList();

Check out the SQL statement in the logs.

Hibernate: select e1_0.ID,e1_0.EMAIL,e1_0.FIRST_NAME,e1_0.LAST_NAME 
    from Employee e1_0 
    order by e1_0.FIRST_NAME

Note that the default sorting order is ascending.

1.2. Sorting on Multiple Fields and Orders

To sort on more than two fields, we can edit the ‘ORDER BY‘ clause with those fields and their sorting order, ASC or DESC.

String hql = "FROM ForeignKeyAssEmployeeEntity e ORDER BY e.firstName ASC, e.email DESC";

SelectionQuery query = session.createQuery(hql, EmployeeEntity.class);
List<EmployeeEntity> list = query.getResultList();

Hibernate: select e1_0.ID,e1_0.EMAIL,e1_0.FIRST_NAME,e1_0.LAST_NAME 
	from Employee e1_0 
	order by e1_0.FIRST_NAME asc,e1_0.EMAIL desc

1.3. Handling NULL Values

To customize how NULL values should be handled while ordering the rows, we can append NULLS FIRST or NULLS LAST clause at end of HQL query.

• NULLS FIRST puts null values at the beginning of the result set
• NULLS LAST puts null values at the last

String hql = "FROM ForeignKeyAssEmployeeEntity e ORDER BY e.firstName ASC, e.email DESC NULLS FIRST";

SelectionQuery query = session.createQuery(hql, EmployeeEntity.class);
List<EmployeeEntity> list = query.getResultList();

Hibernate: select e1_0.ID,e1_0.EMAIL,e1_0.FIRST_NAME,e1_0.LAST_NAME 
	from Employee e1_0 
	order by e1_0.FIRST_NAME asc,e1_0.EMAIL desc nulls first

2. Using Jakarta’s Critera API

2.1. Simple Ordering

The odering is set using the CriteriaBuilder.asc() and CriteriaBuilder.desc() methods that return an instance of jakarta.persistence.criteria.Order.

We can add as many Order instance in the CriteriaQuery.orderBy(listOfOrders) method.

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<EmployeeEntity> criteriaQuery =
    builder.createQuery(EmployeeEntity.class);

Root<EmployeeEntity> root = criteriaQuery.from(EmployeeEntity.class);

List<Order> orderList = new ArrayList();
orderList.add(builder.asc(root.get("firstName")));

TypedQuery<EmployeeEntity> query = session
    .createQuery(criteriaQuery.select(root).orderBy(orderList));

List<EmployeeEntity> list = query.getResultList();

Hibernate: select e1_0.ID,e1_0.EMAIL,e1_0.FIRST_NAME,e1_0.LAST_NAME 
	from Employee e1_0 
	order by e1_0.FIRST_NAME asc,e1_0.EMAIL desc

2.2. Handling NULL Values

Unfortunately, the NULLS FIRST and NULLS LAST clauses. These are a Hibernate-specific extension of the standard. They are not supported by JPA’s CriteriaQuery.

Through it is possible to simulate the behavior using coalesce() method but I will heighly recommend to use HQL or native SQL queries in these kind of requirements.

3. Using Native SQL Queries

Execution of native SQL queries is controlled via the NativeQuery interface, which is obtained by calling session.createNativeQuery() or entityManager.createNativeQuery() methods.

We can directly use the ORDER BY clause to the SQL query.

List<EmployeeEntity> list = session
  .createNativeQuery("SELECT * FROM Employee e ORDER BY e.FIRST_NAME", 
  		EmployeeEntity.class)
  .list();

Hibernate: SELECT * FROM Employee e ORDER BY e.FIRST_NAME

Similarily, we can apply sorting on multiple fields as well as handle NULL values.

List<EmployeeEntity> list = session
  .createNativeQuery("SELECT * FROM Employee e ORDER BY e.FIRST_NAME ASC, e.EMAIL DESC NULLS FIRST", 
  		EmployeeEntity.class)
  .list();

Hibernate: SELECT * FROM Employee e ORDER BY e.FIRST_NAME ASC, e.EMAIL DESC NULLS FIRST

4. Sorting Associated Collections

4.1. @OrderBy

The primary entity might have relationships with another entity and thus can have an associated List or Set of entities.

If we want to internally sort this associated entity collection, we can use @OrderBy annotation that specifies the ordering of the elements of a collection valued association when the association or collection is retrieved.

• The @OrderBy annotation can take multiple entity properties, and each property can take an ordering direction too
• If ASC or DESC is not specified, ASC (ascending order) is assumed.
• Also, if the ordering element is not specified for an entity association, ordering by the primary key of the associated entity is assumed.

@Entity
@Table(name = "Employee")
public class EmployeeEntity implements Serializable {

	//...

  @OneToMany
  @OrderBy("accountType ASC")
  private Set<AccountEntity> accounts;
}

When specifying an ordering over an element collection of embeddable type, the dot notation must be used to specify the attribute or attributes that determine the ordering.

For example, in given example, Zipcode is an @Embedded type.

@Entity
@Table(name = "Employee")
public class EmployeeEntity implements Serializable {

	//...

  @OneToMany
  @OrderBy("zipcode.zip ASC")
  private Set<AddressEntity> addresses;
}

@Embeddable 
public class AddressEntity {
	 //...
   @Embedded protected Zipcode zipcode;
}

@Embeddable 
public class Zipcode {
   protected String zip;
   protected String plusFour;
}

4.2. @OrderColumn

Contrary to natural expectations, the ordering of a list is by default not maintained in the databse. To maintain the order, it is necessary to explicitly use the jakarta.persistence.OrderColumn annotation.

The persistence provider is responsible for updating the ordering upon flushing to the database to reflect any insertion, deletion, or reordering affecting the list. This way, we do not need to explicitly provide the ordering when fetching the entities.

• The @OrderColumn annotation is specified on a OneToMany or ManyToMany relationship or on an element collection.
• The @OrderBy annotation is not used when @OrderColumn is specified.

@Entity
@Table(name = "Employee")
public class EmployeeEntity implements Serializable {

	//...

  @ElementCollection
  @OrderColumn(name = "card_name")
  private List<CreditCard> creditCards;
}

5. Using Sorted Sets

Hibernate has the ability to map sorted sets. A sorted set orders its elements in memory via an associated Comparator.

There are 2 options for sorting a set – naturally or using an explicit comparator.

5.1. @SortNatural

A set is naturally sorted using the natural sort comparator for its elements. Generally this implies that the element type is Comparable.

In given example, because Account is defined as Comparable, its compare() method will be used to sort the accounts in this set.

@Entity
public class Employee {

	// ...

	@ElementCollection
	@SortNatural
	private SortedSet<Account> accounts;
}

5.2. @SortComparator

The @SortComparator allows sorting based on a specific Comparator implementation. In given example, the explicit NameComparator will be used for sorting the accounts.

@Entity
public class EntityWithSortedSet {

	// ...

	@ElementCollection
	@SortComparator( AccountTypeComparator.class )
	private SortedSet<Account> accounts;
}

6. Conclusion

In this sort tutorial, we learned to sort the entities collection fecthed using the databse using various techniques such as Jakarta persistence’s Criteria API, Native SQL queries, HQL queries and annotations such as @OrderBy and @OrderColumn.

Happy Learning !!