5

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

Let's say 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

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. 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.

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

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

double-beep
  • 5,031
  • 17
  • 33
  • 41
Markos Fragkakis
  • 7,499
  • 18
  • 65
  • 103

2 Answers2

3

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.

Péter Török
  • 114,404
  • 31
  • 268
  • 329
  • Hi Peter, thanks for the answer. I have the impression that sorting (and filtering) take place much faster in the DB, where you can also create your own indexes. I may be mistaken, of course. I would use server sorting (with Java), if I wanted very specialized sorting that would be easy only with a Comparator. – Markos Fragkakis Apr 12 '10 at 15:27
  • @Péter Török (+1) For what Hibernate in Action said – Arthur Ronald Apr 13 '10 at 03:06
  • @Markos Sorting by itself may be done faster in the DB (especially for huge result sets), but hitting the DB (almost always) incurs a substantial latency (except maybe when the DB runs on the same machine as your client). Typically you lose more by the latency than what you gain by faster ordering. Of course don't just believe me - the best is to measure it yourself, in your own setup. – Péter Török Apr 13 '10 at 07:56
  • @Markos This also depends on the size of your result set. If you have 100 rows in the table, you can load all at once, and I am certain it is faster to reorder them in memory than hitting the DB. If you have a million rows, you have to resort to paging, so you can't even have all the rows in memory at the same time - this may be a case for ordering in the DB. – Péter Török Apr 13 '10 at 08:01
  • Thanks for the insight (+1). However, until I find time to investigate further, I will keep up with sorting in the DB. – Markos Fragkakis Apr 13 '10 at 12:18
0

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;
     }

}
Arthur Ronald
  • 33,349
  • 20
  • 110
  • 136