SQL Zone is brought to you in partnership with:

Scott is a Senior Software Architect at Altamira Corporation. He has been developing enterprise and web applications for over 15 years professionally, and has developed applications using Java, Ruby/Rails, Groovy/Grails and Python. His main areas of interest include object-oriented design, system architecture, testing, and frameworks of all types including Spring, Hibernate, Ruby on Rails, Grails, and Django. In addition, Scott enjoys learning new languages to make himself a better and more well-rounded developer a la The Pragmatic Programmers' advice to "learn one language per year." Scott is a DZone MVB and is not an employee of DZone and has posted 43 posts at DZone. You can read more from them at their website. View Full User Profile

Sorting Collections in Hibernate Using SQL in @OrderBy

09.16.2009
| 61072 views |
  • submit to reddit
When you have collections of associated objects in domain objects, you generally want to specify some kind of default sort order. For example, suppose I have domain objects Timeline and Event:
@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.

References
Published at DZone with permission of Scott Leberknight, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Andrew Arrigoni replied on Wed, 2009/09/16 - 10:18am

Pretty slick. Particularly the comparator bit, as I have several places where I do Java side processing to determine values that are often sorted by.

Robin Bygrave replied on Wed, 2009/09/16 - 6:13pm

Nice post.

From a database design angle you have chosen to use null for endYear instead of say 2001 or 3999.

2001 -> 2001 ... event occurs only in 2001

2001 -> 3999 ... event occurs every year from 2001 to 3999 (so year 3999 is special effectively meaning an unterminated event etc).

Given you chose null I'm guessing you probably end up using NVL quite a bit in other parts of your application ... query to find all events that occured in 2002 etc. The upside is that your UI doesn't have to treat 3999 specially (aka if 3999 display unlimited/unterminated etc ... but you still probably do... aka if null display unlimited/unterminated etc).

I'm wondering the benefits you get from using null (instead of making endYear required and having a MAX_END_YEAR = 3999 etc). This sort of thing occurs with start and end dates a fair bit and I'm curious as to the benefits of using null.

Apologies as this is really a side issue to your post... but I'm curious... 

Thanks, Rob.

Scott Leberknight replied on Thu, 2009/09/17 - 12:15pm in response to: Robin Bygrave

Rob,

Good point about nulls being a royal PITA in general. In the specific case I had, the data was actually not used like that, i.e. it was not searched by date. I changed the actual class names from my real project but they were similar in concept, and the "Events" containing startYear and endYear were really just for reference purposes and were basically metadata for the parent "Timeline" domain object, sort of like a resume where you use dates to delineate previous jobs but you probably don't go around searching resumes by years (or at least I don't).

So to answer your question, since we don't need to search by those dates, using nulls wasn't an issue in that regard. And thus we don't end up having to use nvl or anything else throughout the app b/c this is localized to one domain object. The only logic is that when reports are generated the users wanted the year ranges to look like "2001" when there's no end date and "2001-02" when there is an end date. The display logic was actually perhaps a bit easier with nulls in this case since we don't need to check if the endYear is the same as startYear.

- Scott

Robin Bygrave replied on Thu, 2009/09/17 - 4:20pm in response to: Scott Leberknight

Scott, 

Yes I follow and buy that. Thanks!

- Rob.

common1 common replied on Wed, 2009/11/25 - 10:25am

There is one caveat: OSU’s campus is connected to Internet2 with a multi-gbps connection, but that does not mean your computer has a multi-gigabit connection to the campus network.  Professional Logo Design - stationery design

Sathees Kumar replied on Fri, 2010/09/17 - 7:08am

Hi, I am having a problem in using @OrderBy in Hibernate. I have 3 tables associated with one another and I have mapped these table with my persitance class. then i try to generate select query with join condition using entityManager.createQuery. It generate query but in Orderby clause it takes primary key as default. I dont want primary key column in order by clause. I need to order by seqno which is another field in the associated table. Pls advice me how to remove that primary key column in order by list. Thanks Sathees

Justin Guo replied on Sat, 2010/11/13 - 8:35pm

Thanks so much! This post has helped me so much with using @orderby in hibernate.

 controlling ejaculation

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.