Hi,
I am trying to figure out how to update a chain of objects without knowing their PK.
Lets say I have two tables (and objects) County and State. State contains counties (parent-child assoc). Both tables use a surrogate key for PK but they both have unique constraints. In the application I want to update the tables using data from a different datasource (same Java objects but without id). If the tables are empty I can use session.save(state) and the state and it's containing counties are saved (with cascade attribute). But how should I update the table records if they already exists? I have tried to find the stored instance first by using a bussiness key and then set the new state's id to this id and update. This works fine but the counties are not updated new rows are inserted (which violates the county tables unique constraint). I understand why the new rows are inserted (since all county objects ids are null) but I don't know how to solve it nice? I would like to avoid using composite-ids and I would also like to avoid iterating through the county collection because in reality the county objects referes to other transient objects and these to others etc...


Thanks,
William

First, needs to perform search by value (county name in your case). And if result set is empty then save your object by call save(). Thus, there are no any composite id and needless object loading.

It seems to me that composite-ids would be just the thing. Why do you wish to avoid them? I think it is better to solve this in your mapping (getting proper unique ids for objects) than adding complexity to wherever you need this in your code.
It is quite possible, though, that the previous respondent's solution is faster.

Hi,
Thank you both for your replies,
Composite ids actually works pretty good for State where I can use name or abbreviation. For county I don't know if the names are unique if not I have to use both name and state. Anyways I simplified the model a bit in the post. In the real application the counties have a collection of cities and the cities have a collection of zipcodes. So the zipcodes need to have foreign keys to cities and cities to counties etc. If I use only composite ids I will end up with lots of extra columns in these tables (right?) that I would like to avoid. So thats why I wanted to use surrogate keys.
But I could agree to use composite-ids in Hibernate if there is a way to have another column generated by a generator (which the reffering tables could use as key)?
Is there maybe a way to use select-before-update and specify a select sql? ;)

A_gura: if I understand you right I have to iterate through each level of collection and select the entries from db first? Since there are so many objects I would like to avoid that. But if I do that what is the pragmatic way of updating each object? move id or propeties use evic etc?

Thanks,
William

No. Iteration is too expensive. Simple method is query execution with EXIST clause.

I'm not sure I understand. Could you explain or provide some pseudo code?
Lets say I want to save or update a state object which contains a set of county objects.

Problem:
You have few transient instances of County. You want to save them as dependent objects of State. You might be add all County objects to your State object and then call save() for State, but there is problem with unique constraint.

Decision:
Save your Counties separately from State entity.

1. Check, that County with same name doesn't exist in DB. For example, you may use count for it.


Count work fast and doesn't load any objects. If result value equals zero, then no County object with given name in database (it's new county).

2. Set reference to State into County instance and store them.

Ok thanks,
That's what I though then I have to loop through each county object when I save a state.
First I thought there was a way to get cascade to work. I thought maybe I could specify two ids something like this: