Hi I'm trying to insert a number of records into a database

The team persistent class comes first and it has a many-to-one relationship with the Country persistent class.


The team class has a cascade-all attribute set, and when i use SaveOrUpdate on the session it was my hope that it would find out if the many-to-one relationship existed with the country then save or take the reference key as necessary.

However as i insert team after team, hibernate seems to continually try to insert new countries until it generates a constraint violation exception.

I've checked the Wiki and the reference manual. I've tried changing batching attributes, changing the dynamic-insert attribute,clearing and flushing session inside the loop, checked my equals and hashcode methods.

What am i doing wrong?

Hibernate version:
3.1.3

Mapping documents:



<many-to-one name="country"
class="arb.persistance.object.nameType.Country" column="country_id"
cascade="all" />


.......
<hibernate-mapping>
<class name="arb.persistance.object.nameType.Country" table="country" dynamic-insert="true">
<id name="id" type="int" column="id">
<generator class="seqhilo">
<param name="sequence">ids</param>
<param name="max_lo">100</param>
</generator>
</id>
<property name="name" column="name" type="string" unique="true"/>



</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
s = HibernateUtil.currentSession();
for (Iterator ii = utils
.getCollection(ds, TeamBean .class)
.iterator(); ii.hasNext();) {

s.beginTransaction();
TeamBean bmBean = (TeamBean ) ii.next();

bmBean.setCountry(country[i++]);
s.saveOrUpdate(bmBean);



}
s.getTransaction().commit();

Full stack trace of any exception that occurs:
Batch entry 0 insert into public.country (name, id) values (Malta, 206) was aborted. Call getNextException to see the cause.
- SQL Error: 0, SQLState: 23505
- ERROR: duplicate key violates unique constraint "country_name_key"
- Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:91)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:86)
at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:171)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2048)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2427)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)

Name and version of the database you are using:
postgres 1.4.2

The generated SQL (show_sql=true):
into public.country (name, id) values (Malta, 206)

Debug level Hibernate log excerpt:

How are you populating that array of countries? You can't just create new countries every time you create a new team. You must use the countries as saved to the database: the Country objects in memory must have the database ID in them.

Normally, cascading goes one-to-many. Many-to-one cascading is significantly more complex, especially if there's any chance that you're dealing with detached one-end objects.

the countries are coming from an xml datafeed, I thought that a saveOrUpdate call will check the database to see if the data exists, but perhaps without an id this is not possible.

Are there any design patterns for dealing with child saves on a many-to-one relationship?

If you want to do this automatically, you'll need an interceptor or event to deal with this, or else prepopulate every country into your DB and make them an enum or similar, so that the ID is always known, even before you save.

The only other solution is the correct one: load the country object using the country details in your XML file first. If the country object loads then use it, otherwise create a new country object.

Ok thanks thats very useful, I guess a cascading saveOrupdate isnt quite what i thought it would be.
i.e. the decision to saveOrUpdate is made once at the root.

I'll now go through and try to load first then save, i thought this might have been automated, thanks for your help.

Ok, so im still looking for a shortcut, but if i attempt to save all of the countries in one transaction , would the fact that some of them have the same hash-value mean that hibernate would automatically strip out duplicates before the transaction is committed?

G

I guess if you make a collection of countries (a new class CountrySet, with Set<Country> getCountries()) then you can do that easily. Otherwise, I think you'd have to load then saveOrUpdate each one. The hashCode only (mostly) applies when using hashed collections.

ok so i went through, kept the cascade all in the objects but now i added an interceptor to the session on save or update.

The interceptor uses an example criteria to search for based on the object to search for it. I'm noticed a few bugs with natural keys that are yet to be fixec however i find the Criteria for the example object creates a natural-key good enough for me to use by default.

So all there is left to do is run the query load the entity and set the entity's id to the id from the queried entity right?

Wrong!

Hibernate seems to have already assigned an id to the entity, attempting to change this results in an exception.

then i thought perhaps i just merge the entity back into the session?

uh-uh, i doubt hibernate will recognise them as the same objects since they have different ids. (although i havent tried this point , and I could be wrong)

So now im stuck is it really that hard to an insert if the natural key is not existent in the database and an update otherwise, (nbn this is from the child end of a many to one relationship)

I don't think that you can fiddle with IDs in interceptors. In "normal" code, you could evict the object, change its ID, then save it.

Are you sure you can't just give up on dynamic creation of countries, and prepopulate all countries in your table? Make country an enum? All sorts of performance advantages there. And there's only around 200 countries...