Hibernate 2.1 / Oracle10g

I have a simple mapping that contains a list:



which works well enough in terms of inserting data into the database, I can make a new PathEventSet object and call setPathEvents with an ArrayList<String> (which represents the type and order) and save it with hibernate and it populates the path_event_set_order table with the correct id, order and type values and puts an entry in the path_event_set table. All Lovely.

My issue arises when later on, say after an app restart, I have a ArrayList<String> of types and I want to find out what the id for it is in the database. Since I'm trying to find the path_event_set_id I can't query by id, I have to instead query by value, which in this case is the list. I have tried creating a new PathEventSet object and setting it's list and trying to use that as an example object for a criteria by example load, but it seems to ignore the list values and just loads all PathEventSets that I have in the database:



I could write some code that shuffles through the ArrayList<String> myself and build up the appropriate table self joins and where clause segments but that seems like a waste of effort since presumably hibernate can be made to do all that for me if I can just configure it properly.

I am missing something obvious, is this not the way that people model ordered sets of data? Any thoughts would be appreciated. Thanks.

Well to answer my own question, I don't think there is anyway to get hibernate to magic up the query by example or use HQL to bind a list into a query as I would like. Maybe I'm wrong but I couldn't work out how to make hibernate do this. That said, there are two solutions to the problem. One is to just do the query myself:



Which works fine for me, but I know the number of items in my list is going to be small and the total table I am self joining is going to to be small so the query performance should be super reasonable.

The other solution is to make the set table contain a hash_key field in addition to the the id of the list we care about. So when one goes to save a PathEventSet on calls setPathEvents(List) and in the code for setPathEvents you traverse the list and build a hashkey for that particular ordered list and call myPathEvent.setHashKey(theKey) and save it.

So basically change the setter from:


to



Then in the future when you want to find the id for a particular ordered list you can just do something like "from PathEventSet where hashKey = :hashKey" and set the hashKey using the buildKey function from the object. The only problem of course is if you get a hash collision. This should be very improbable if you are using something like md5sum and not hashing public data (i.e., so a user can't engineer collisions) but depending on your application that might not be good enough. You could keep using the hash solution but check the size of your results list (which you should be doing anyway) and if it is greater than one then you had a collision and you should now use the slower self joined HQL query method to resolve the collision.

Anyway, maybe that will be useful for someone else.