|

Ordering vs Sorting with Hibernate – What should you use?


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.


OK, first of all, I’m sorry about the distinction between ordering and sorting. My English teacher would probably be mad at me if she would read this.

But I didn’t come up with the names. That’s how the features are called in Hibernate.

And yes, that are 2 different features.

Both allow you to define the order of the elements in a to-many association. But they use different approaches, and you should be familiar with the difference if performance is important for your application.

The difference between Sorting and Ordering

Sorting and ordering seem to do the same. They bring the associated entities into a defined order. But they differ in the approach.

When use sorting, Hibernate will load the associated Book entities from the database and use a Java Comparator to sort them in memory. That is not a good approach for huge Sets of entities.

Ordering uses an ORDER BY clause in the SQL statement to retrieve the entities in the defined order. Database handles these kinds of operations very efficiently. So this approach is a lot better for huge associations.

2 quick notes before we look at the 2 options in more detail:

  1. Hibernate applies sorting and ordering every time it retrieves the associated entities. You should be sure that you always need the associated entities in the defined order. Otherwise ordering and sorting are just an unnecessary overhead that slows down your application.
  2. Sorting is a proprietary Hibernate feature. If you’re using a different JPA implementation, you should skip the sorting part and go directly to the ordering chapter.

OK, let’s start with sorting.

Sorting

In all examples in this post, I will use an Author entity with a many-to-many association to a Book entity. The association mapping differs for both approaches. So please, pay close attention to the type and the mapping annotations of the books attribute.

@Entity
public class Author implements Comparable<Author> {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "id", updatable = false, nullable = false)
	private Long id;

	@Version
	private int version;

	private String name;

	@ManyToMany(mappedBy = "authors")
	// requires sorting or ordering
	private SortedSet<Book> books = new TreeSet<Book>();

	...

	@Override
	public int compareTo(Author o) {
		log.info("compare");
		return name.compareTo(o.getName());
	}
}
@Entity
public class Book implements Comparable<Book> {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "id", updatable = false, nullable = false)
	private Long id;

	@Version
	private int version;

	private String title;

	@ManyToMany
	@JoinTable(name = "book_author", 
		   joinColumns = { @JoinColumn(name = "fk_book") }, 
		   inverseJoinColumns = { @JoinColumn(name = "fk_author") })
	// requires sorting or ordering
	private SortedSet<Author> authors = new TreeSet<Author>();

	...

	@Override
	public int compareTo(Book o) {
		log.info("compare");
		return title.compareTo(o.getTitle());
	}
}

As you can see in the code snippet, I use a SortedSet as the type of the attributes that map the relationship. Sorting requires you to either use a SortedSet or a SortedMap as an attribute type. For both of them, all elements have to implement Comparable and stores them in a defined order.

In this example, the implementation of the compare method of the Book entity is pretty simple. It uses the compareTo method of the String class to sort them the alphabetically by its title.

You can choose between 2 approaches to sort the elements of your association. You can either use natural sorting, or you can provide a custom Comparator.

Natural Sorting

You can tell Hibernate to use natural sorting by annotating the association attribute with @SortNatural. This approach uses the Comparable implementation of the related entities.

@Entity
public class Author implements Comparable<Author> {
	...

	@ManyToMany(mappedBy = "authors")
	@SortNatural
	private SortedSet<Book> books = new TreeSet<Book>();

	...
}

Let’s load an Author entity with its 3 associated Book entities from the database and see what happens.

Note: The query in the following code snippet uses a JOIN FETCH clause to retrieve the Author entity together with all associated Book entities. This is a common best practice to efficiently initialize associations.

TypedQuery<Author> q = em.createQuery("SELECT a FROM Author a JOIN FETCH a.books b WHERE a.id = :id", Author.class);
q.setParameter("id", 1L);
Author a = q.getSingleResult();

log.info(a);
for (Book b : a.getBooks()) {
	log.info(b);
}

As you can see in the log output, Hibernate executes the query without an ORDER BY clause. It then uses the compare method of the Book entity to sort the 3 Book entities alphabetically and stores them in the SortedSet.

18:10:22,018 DEBUG SQL:92 - select author0_.id as id1_0_0_, book2_.id as id1_1_1_, author0_.name as name2_0_0_, author0_.version as version3_0_0_, book2_.title as title2_1_1_, book2_.version as version3_1_1_, books1_.fk_author as fk_autho2_2_0__, books1_.fk_book as fk_book1_2_0__ from Author author0_ inner join book_author books1_ on author0_.id=books1_.fk_author inner join Book book2_ on books1_.fk_book=book2_.id where author0_.id=?
18:10:22,085  INFO Book:107 - compare
18:10:22,085  INFO Book:107 - compare
18:10:22,085  INFO Book:107 - compare
18:10:22,087  INFO TestSortVsOrder:43 - Author [id=1, name=B Author]
18:10:22,088  INFO TestSortVsOrder:45 - Book [id=3, title=A Book]
18:10:22,089  INFO TestSortVsOrder:45 - Book [id=1, title=B Book]
18:10:22,089  INFO TestSortVsOrder:45 - Book [id=2, title=C Book]

Custom Comparator

When you don’t want to use the Comparable implementation of your entities for your sorting, you can use the @SortComparator annotation to provide a custom Comparator.

@Entity
public class Author implements Comparable<Author> {
	...

	@ManyToMany(mappedBy = "authors")
	@SortComparator(SortById.class)
	private SortedSet<Book> books = new TreeSet<Book>();

	...
}

As you might guess from its name, the Comparator in this example uses the id attribute to compare the Book entities.

public class SortById implements Comparator<Book> {

	Logger log = Logger.getLogger(SortById.class.getSimpleName());
	
	@Override
	public int compare(Book o1, Book o2) {
		log.info("SortById.compare");
		return o1.getId().compareTo(o2.getId());
	}

}

When you now execute the same test case as in the previous example, Hibernate uses the SortById Comparator instead of the compare method of the Book entity. And you can see in the log output, that the books attribute now contains the associated Book entities in the order of their id attributes.

TypedQuery<Author> q = em.createQuery("SELECT a FROM Author a JOIN FETCH a.books b WHERE a.id = :id", Author.class);
q.setParameter("id", 1L);
Author a = q.getSingleResult();

log.info(a);
for (Book b : a.getBooks()) {
	log.info(b);
}
18:21:30,966 DEBUG SQL:92 - select author0_.id as id1_0_0_, book2_.id as id1_1_1_, author0_.name as name2_0_0_, author0_.version as version3_0_0_, book2_.title as title2_1_1_, book2_.version as version3_1_1_, books1_.fk_author as fk_autho2_2_0__, books1_.fk_book as fk_book1_2_0__ from Author author0_ inner join book_author books1_ on author0_.id=books1_.fk_author inner join Book book2_ on books1_.fk_book=book2_.id where author0_.id=?
18:21:31,097  INFO SortById:13 - SortById.compare
18:21:31,098  INFO SortById:13 - SortById.compare
18:21:31,098  INFO SortById:13 - SortById.compare
18:21:31,098  INFO SortById:13 - SortById.compare
18:21:31,105  INFO TestSortVsOrder:43 - Author [id=1, name=B Author]
18:21:31,118  INFO TestSortVsOrder:45 - Book [id=1, title=B Book]
18:21:31,118  INFO TestSortVsOrder:45 - Book [id=2, title=C Book]
18:21:31,119  INFO TestSortVsOrder:45 - Book [id=3, title=A Book]

That’s all about sorting for now. As you’ve seen, sorting doesn’t have any effect on the database query. It happens after Hibernate retrieved the associated entities. You can either use the compareTo method implementation of the associated entity class or a custom Comparator to sort the entities.

Now, let’s take a look at ordering.

Ordering

The ordering feature is defined by the JPA specification and does what most developers expect. It uses an ORDER BY clause in the SQL query to retrieve the associated entities in a specific order.

In contrast to the sorting feature, you can use ordering on Lists and your entities don’t have to implement the Comparable interface. You shouldn’t use it with SortedSets to avoid an additional sorting of the already ordered entities.

You can define the ORDER BY clause with JPA’s @OrderBy annotation. If you don’t provide any additional information, Hibernate will order the associated entities by their primary key. If you want to retrieve them in a different order, you can define an orderby_list with one or more orderby_items as follows:

orderby_list::= orderby_item [,orderby_item]*
orderby_item::= [property_or_field_name] [ASC | DESC]

So, when you want to retrieve the associated Book entities in alphabetical order of their title, you just need to add the annotation @OrderBy(“title ASC”) to the books attribute.

@Entity
public class Author {
	...

	@ManyToMany(mappedBy = "authors")
	@OrderBy("title ASC")
	private List<Book> books = new ArrayList<Book>();

	...
}LoadOrderByTitle.log

When you now use the same test case as in the previous examples to load an Author entity with all related Book entities, you can see that Hibernate added an ORDER BY clause to the SQL statement. So this time, the database returns the records in the defined order and Hibernate just needs to map them to Book entities.

TypedQuery<Author> q = em.createQuery("SELECT a FROM Author a JOIN FETCH a.books b WHERE a.id = :id", Author.class);
q.setParameter("id", 1L);
Author a = q.getSingleResult();

log.info(a);
for (Book b : a.getBooks()) {
	log.info(b);
}
08:58:26,871 DEBUG SQL:92 - select author0_.id as id1_0_0_, book2_.id as id1_1_1_, author0_.name as name2_0_0_, author0_.version as version3_0_0_, book2_.title as title2_1_1_, book2_.version as version3_1_1_, books1_.fk_author as fk_autho2_2_0__, books1_.fk_book as fk_book1_2_0__ from Author author0_ inner join book_author books1_ on author0_.id=books1_.fk_author inner join Book book2_ on books1_.fk_book=book2_.id where author0_.id=? order by book2_.title asc
08:58:27,022  INFO TestSortVsOrder:46 - Author [id=1, name=B Author]
08:58:27,024  INFO TestSortVsOrder:48 - Book [id=3, title=A Book]
08:58:27,024  INFO TestSortVsOrder:48 - Book [id=1, title=B Book]
08:58:27,024  INFO TestSortVsOrder:48 - Book [id=2, title=C Book]

Sorting or Ordering? What should you use?

After you’ve read this post, I hope it became obvious that you should order your entities and not sort them. The 2 main reasons are performance and portability:

  • The ordering of result set records is a typical task for relational databases. They are highly optimized for it, and you can easily benefit from that by annotating your entity association with an @OrderBy annotation.
  • The ordering feature is defined by the JPA specification so you can use it with every compliant JPA implementation, like Hibernate, EclipseLink or OpenJPA.

Summary

When you want to retrieve associated entities in a specific order, Hibernate can sort or order them.

Sorting happens within your Java application using a Comparable or Comparator implementation.

Ordering adds an ORDER BY clause to the database query so that the database performs the tasks. That is the preferable approach because databases are highly optimized for these kinds of operations and handle them more efficiently than your Java code.

But before you introduce ordering to your application, please keep in mind that Hibernate will order the associated entities every time it retrieves them from the database. So, better make sure, that you always need the entities in the given order. Otherwise, you add unnecessary overhead to your application which slows down the performance.

3 Comments

  1. Hi Thorben, thank you for this.

    I was wondering if it’s possible to order by an alias. Eg. say I have a calculated field with an alias named “relevance”; is it possible to order the result list by this? I’m trying something like the following but I can only pick fields from the entity for ordering. Say I wanted to sort by a relevance score and then by the name (which is a field):

    List orderList = new ArrayList();
    orderList.add(cb.asc(“relevance”));
    orderList.add(cb.asc(MyEntity_.name”));
    query.orderBy(orderList);

    Thanks for the article!

    1. Avatar photo Thorben Janssen says:

      Hi Julius,

      it seems like you can’t order by an alias. But you can do something like this:
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery cq = cb.createTupleQuery();
      Root root = cq.from(Book.class);
      Path at = root.get(Book_.title);
      cq.multiselect(at.alias(“myTitle”));
      cq.orderBy(cb.asc(at));

      You can store the Path reference to the attribute you want to select in a variable, use that one in your select clause and assign it an alias. In the next step, you can define the ORDER BY clause using the Path variable.

      Regards,
      Thorben

  2. Avatar photo Binh Thanh Nguyen says:

    Thanks, nice to know that!

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.