0

I have a list of sub-values for a class in Hibernate. My initial implementation was a Set, but that returns the values in what is essentially 'database order' (likely based on a clustered index, which is almost insertion order).

I looked at switching to List, so I added a , but I quickly ran into a problem with there being nulls in the java Lists that were getting returned. Some of my data is written by Hibernate, but some of it is also written by SQL scripts.

What I'd like is a sortOrder attribute that isn't tied to the position in the list, so that if there are duplicated i.e. { 1, 3, 3, 10 } it is mapped appropriately to { 0, 1, 2, 3} where the order of the 2nd and 3rd item fall back to database order. Nulls are fine also, and I don't care if they get put at the front or back of the list (so long as the behavior is consistent).

I looked at Bag, but I wasn't sure how to get the sortOrder attribute into the definition, and I was a bit worried about it allowing duplicates. I'd still like to have the same constraints as I did when I had Set (I believe the parent/child tuple was always unique).

Suggestions? Can I tweak Bag to make it act as a List with a sortOrder as opposed to a List with an index? What else do I need to do to protect the code from getting hung up by data entered directly by SQL?

Paul W Homer
  • 2,728
  • 1
  • 19
  • 25

2 Answers2

1

I had the same issue as well in a project I was working on. The final soultion I can up with was using a database trigger to keep this positional data in the correct order. The trigger takes the records in the list and ranks them in the correct and updates the position.

I don't think you'll have any other options as you are altering data using sql as well as hibernate.

Phil Whittaker
  • 434
  • 4
  • 20
  • Is there a race condition between the time the rows are visible and the time the trigger updates? After you create the schema, I guess you are running SQL to add the trigger? – Paul W Homer Jun 22 '12 at 16:31
  • I was using sql server and a trigger was part of the trasaction process, so there will be no issue with timing. The trigger was wrtten in sql and was just added as a update trigger on the table that was the isssue. This way is very clean and stops all nulls from getting into your position colum. – Phil Whittaker Jun 22 '12 at 17:09
  • I'll definitely consider it, but so far I am database agnostic so I'd prefer to find a 100% Hibernate solution. Did you play around with Bags at all? – Paul W Homer Jun 22 '12 at 17:17
  • I did a little bit but I found it very hard going. I agree with keeping a solution database agnostic but every database supports triggers so it wouldn't be much of a trade off. – Phil Whittaker Jun 22 '12 at 17:25
0

If you're using hibernate annotations, you can use javax.persistence.OrderBy to order the elements "in memory" by a specified property without introducing an index column, see:

http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html_single/#entity-hibspec-collection

Ramon
  • 8,202
  • 4
  • 33
  • 41
  • I haven't been using the annotations, I just have an hbm file with the schema laid out. I saw the orderBy mechanics, but my preference would be to keep most of the sorting in the database, since for many of my implementations that's actually a big separate box with more horsepower than the app box. – Paul W Homer Jun 22 '12 at 20:37
  • Also, I'd really like a sortOrder column explicitly because I'm not ordering on the data, but rather on the user's order of the data (which no doubt is somewhat irrational :-) – Paul W Homer Jun 22 '12 at 20:38