One mapping that doesn’t work well in Hibernate or Java
Hibernate does a good job of managing relationships for you. If your data forms a nice object graph, not only do you not have to write SQL, you don’t even need to write Hibernate’s query language! Just traverse your graph and Hibernate will load objects as needed.
I’ve stumbled across a relationship that Hibernate doesn’t handle particularly well. Hibernate does a good job of staying true to the semantics of the Java collections framework. The exception to this is their “bag”, which is an unordered collection of items that can contain the same item multiple times. They use a List to represent this, but make it clear in the docs that the order is not maintained.
What I need is a little different from anything in java.util… It needs to be ordered like a List and provide for random access and update of elements like a List. But, any given item should only appear in there once. I tried mapping this with a List (ignoring the only appearing once in the list requirement for the moment) and it almost worked. Updating the list proved to be problematic though (details in this Hibernate Forum thread). My situation is complicated a little bit by the fact that this is a bidirectional many-to-many relationship that has this interesting collection on one side and a Set on the other.
Remarkably, I’m not the only one with this problem. I think that certain piece of data lend themselves to this structure. It would be nice to get a generic solution together for this (after meeting my specific application need, of course 
This may not completely solve your problem but java.util.LinkedHashSet provides Set behavior (uniqueness) and maintains insertion order like a list.
True, and Hibernate can produce LinkedHashSets with the right parameters. Unfortunately, LinkedHashSets don’t let you add data to arbitrary places in the set (as you can with a List).
How about a sorted set with the objects placed in the set implementing a custom Comparable implementation that produces the order you require?
I’ve considered that. We decided to punt for now, because this doesn’t touch many pieces of code. “Punting” at this point means just replacing the list with a new list, forcing Hibernate to delete all of the rows for that list and recreate them. Not super efficient or elegant, but works fine for this particular problem.
I’m pretty sure we’ll do a better solution before the end of the project.
Did you finally solve this problem? We are facing exactly the same situation and cant find the way out. We are also “punting”
but I really would like it solved by the persistence framework.
Please see my post in http://forum.hibernate.org/viewtopic.php?t=942105
Actually, A LinkedHashSet is not sorted, but ordered, so the way Hibernate handles it (with a “sort-by” clause) is inadequate.
Kevin,
Do you have any nice solution for this problem yet?
Sometimes you do need a List (to get transparent updating of sort order column) but that List can’t contain duplicates. I’ve seen numerous examples of this in the real world. I’ve tried mapping this as a List. It works fine for the initial save of the List. However, when you update the List (either removing items or shuffling their positions), the SQL generated by Hibernate is bizarre, as illustrated by your post to the Hibernate forums.
To refresh your memory, here is the SQL for removing the first item from your List:
1) delete from JoinTable where Table1Key_ID = 44 and Table2Key_ID = 77
2) update JoinTable set Table2Key_ID=66 where Table1Key_ID=44 and Table1Key_Index=0
3) update JoinTable set Table2Key_ID=77 where Table1Key_ID=44 and Table1Key_Index=1
For the life of me, I can’t figure out why they are done that way. Of course, I realize that the net effect of the two statements is correct but it violates the PK (Table1Key_ID + Table2Key_ID). Why the heck don’t they do this instead?:
1) delete from JoinTable where Table1Key_ID = 44 and Table2Key_ID = 55
2) update JoinTable set Table1Key_Index=0 where Table1Key_ID=44 and Table2Key_ID=66
3) update JoinTable set Table1Key_Index=1 where Table1Key_ID=44 and Table2Key_ID=77
That would have the same net effect without violating the PK.
Hi Joe,
Fortunately (for me), I’ve been doing Python for the last year and a half. Your post makes me curious to ask on the SQLAlchemy list if this problem is dealt with there.
Use sortedset