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 setNULLS 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 !!