Hibernate: order multiple one-to-many relations

Question

I have a search screen, using JSF, JBoss Seam and Hibernate underneath. There are columns for A, B and C, where the relations are as follows:

A (1< -- >) B (1< -- >) C

A has a List< B > and B has a List< C > (both relations are one-to-many).

The UI table supports ordering by any column (ASC or DESC), so I want the results of the query to be ordered. This is the reason I used Lists in the model.

However, I got an exception that Hibernate cannot eagerly fetch multiple bags (it considers both lists to be bags). There is an interesting blog post here, and they identify the following solutions:

1. Use @IndexColumn annotation (there is none in my DB, and what's more, I want the position of results to be determined by the ordering, not by an index column)
2. Fetch lazily (for performance reasons, I need eager fetching)
3. Change List to Set

So, I changed the List to Set, which by the way is more correct, model-wise.

• First, if don't use @OrderBy, the PersistentSet returned by Hibernate wraps a HashSet, which has no ordering. So, when I iterate over it in the UI, the order is random, whatever ordering the database did.
• Second, If I do use @OrderBy, the PersistentSet wraps a LinkedHashSet, which has ordering and is what I would like. However, the OrderBy property is hardcoded, and takes precedence over whatever ordering I set both using Collections(link) or HQL (link). As such, all other ordering I request through the UI comes after it.

I tried again with Sets, and used SortedSet (and its implementation, TreeSet), but I have some issues:

1. I want ordering to take place in the DB, and not in-memory, which is what TreeSet does (either through a Comparator, or through the Comparable interface of the elements).
2. Second, I found that there is the Hibernate annotation @Sort, which has a SortOrder.UNSORTED and you can also set a Comparator. I still haven't managed to make it compile, but I am still not convinced it is what I need.

Any advice?

UPDATE:

One of the requirements is for the sorting to take place in the DB.

UPDATE2:

Created a simple Maven project and committed it as a Google Code project. This is my personal playground for the problem.

Answer 1

What's the point of ordering in the DB when the same result set can be reordered by any column? If you need to hit the DB every time when a different column is clicked on the UI, you just create a performance issue for yourself. This is exactly the case when it makes sense to order the set in memory.

About bags and lists, this is what the Hibernate bok has to say:

Bags may not be sorted (there is no TreeBag, unfortunately), nor may lists; the order of list elements is defined by the list index.

Answer 2

Based on what Hibernate in Action said and the workaround provided by your own answer, you could sort your collection at runtime to avoit your exception

@Entity
public class Aa {

     private List<Bb> bbList - new ArrayList<Bb>();

     @OneToMany
     public List<Bb> getBbList() {
         return bbList;
     }

     @Transient
     public List<Bb> getBbListSortedBySomeProperty() {
         Collections.sort(bbList, new Comparator<Bb>() {
             public int compare(Bb o1, Bb o2) {
                 return o1.getSomeProperty().compareTo(o2.getSomeProperty());
             }
         });

         return bbList;

     }

}

Be aware someProperty must implement Comparable

...

@Entity
public class Bb {

     private List<Cc> ccList - new ArrayList<Cc>();

     @OneToMany
     public List<Cc> getCcList() {
         return ccList;
     }

}