Sorting Collections in Hibernate Using SQL in @OrderBy

@Entity
class Timeline {

    @Required 
    String description

    @OneToMany(mappedBy = "timeline")
    @javax.persistence.OrderBy("startYear, endYear")
    Set<Event> events
}

@Entity
class Event {

    @Required
    Integer startYear

    Integer endYear

    @Required
    String description

    @ManyToOne
    Timeline timeline
}

In the above example I've used the standard JPA (Java Persistence API) @OrderBy annotation which allows you to specify the order of a collection of objects via object properties, in this example a @OneToMany association . I'm ordering first by startYear in ascending order and then by endYear, also in ascending order. This is all well and good, but note that I've specified that only the start year is required. (The @Required annotation is a custom Hibernate Validator annotation which does exactly what you would expect.) How are the events ordered when you have several events that start in the same year but some of them have no end year? The answer is that it depends on how your database sorts null values by default. Under Oracle 10g nulls will come last. For example if two events both start in 2001 and one of them has no end year, here is how they are ordered:

2001 2002  Some event
2001 2003  Other event
2001       Event with no end year

What if you want to control how null values are ordered so they come first rather than last? In Hibernate there are several ways you could do this. First, you could use the Hibernate-specific @Sort annotation to perform in-memory (i.e. not in the database) sorting, using natural sorting or sorting using a Comparator you supply. For example, assume I have an EventComparator helper class that implements Comparator. I could change Timeline's collection of events to look like this:

@OneToMany(mappedBy = "timeline")
@org.hibernate.annotations.Sort(type = SortType.COMPARATOR, comparator = EventCompator)
 Set<Event> events

Using @Sort will perform sorting in-memory once the collection has been retrieved from the database. While you can certainly do this and implement arbitrarily complex sorting logic, it's probably better to sort in the database when you can. So we now need to turn to Hibernate's @OrderBy annotation, which lets you specify a SQL fragment describing how to perform the sort. For example, you can change the events mapping to :

@OneToMany(mappedBy = "timeline")
@org.hibernate.annotations.OrderBy("start_year, end_year")
 Set<Event> events

This sort order is the same as using the JPA @OrderBy with "startYear, endYear" sort order. But since you write actual SQL in Hibernate's @OrderBy you can take advantage of whatever features your database has, at the possible expense of portability across databases. As an example, Oracle 10g supports using a syntax like "order by start_year, end_year nulls first" to order null end years before non-null end years. You could also say "order by start_year, end year nulls last" which sorts null end years last as you would expect. This syntax is probably not portable, so another trick you can use is the NVL function, which is supported in a bunch of databases. You can rewrite Timeline's collection of events like so:

@OneToMany(mappedBy = "timeline")
@org.hibernate.annotations.OrderBy("start_year, nvl(end_year , start_year)")
 Set<Event> events

The expression "nvl(end_year , start_year)" simply says to use end_year as the sort value if it is not null, and start_year if it is null. So for sorting purposes you end up treating end_year as the same as the start_year if end_year is null. In the contrived example earlier, applying the nvl-based sort using Hibernate's @OrderBy to specify SQL sorting criteria, you now end with the events sorted like this:

2001       Event with no end year
2001 2002  Some event
2001 2003  Other event

Which is what you wanted in the first place. So if you need more complex sorting logic than what you can get out of the standard JPA @javax.persistence.OrderBy, try one of the Hibernate sorting options, either @org.hibernate.annotations.Sort or @org.hibernate.annotations.OrderBy. Adding a SQL fragment into your domain class isn't necessarily the most elegant thing in the world, but it might be the most pragmatic thing.