Hi,
In my domain model one of the classes has a surrogate key and a unique key constraint. On attempting a saveOrUpdate for an instance of this object I get a unique key constraint violation error. This is because an insert was issued instead of an update.

How can i handle the case when the new instance that needs to be persisted has the same unique key but an update should be issued since other columns have changed?

You will need to load or merge the existing object into your session. Then you can issue a saveOrUpdate.. also make sure to check your unsaved-value="null" in your <id> tag.

Thanks JT.
I am actually doing a bulk insert/update. In such a case I guess you mean loading all the entries from the table into the session. Some of these tables may have millions of entries and I guess it wont be efficient loading them all into the session context. Are there any efficient ways of performing a bulk insert/update?
There is every possibility that out of the 100 in the batch being uploaded 99 are already present in the table and need to be updated.

Jeevak

My experience is that all I really have to do is to make sure the ID's are set properly, and set the flag I was telling you about unsaved-value="true" and if I set the ID on the object and change some of the attribute values it will save properly (with our with out loading/merging).

Example:

session scope >>
while(read Entities from file)
{
Entity et=new Entity()
et.setID(100); //and 100 is ID from file & exists in database already.
et.setAttr("something new");
session.saveOrUpdate(et); //it will "update" properly.
//here you may consider to clear up memory.
session.evict(et);
}
<< session scope

Also notice that if you get an DB Exception thrown (and even caught) in your session you will have to restart a new Session.

it might be worth while trying to load via
<sql-insert ..callable="true">...
Then create a function that receives all the properties as params and checks to see if item is in db already - actually in oracle if you insert and a unique key violation happens you can catch it in PLSQL so it doesn't ever return to hibernate, and proceed with the rest of the list (ie hib calling function with next row) - this is probaby faster than selecting first, then insert if not there.

The docs recommend returning the number of rows inserted in your function.

check out page 165 of reference.pdf for this approach.
http://www.hibernate.org/hib_docs/v3/reference/en/pdf/hibernate_reference.pdf

JT,
With unsaved-value=true I am marking every new instance as transient and hence an insert is issued every time I call saveOrUpdate. The functionality i need is that it should update if the entity is already in the db.

The second solution you suggested is PL/SQL based which is DB dependent and the solution we are building needs to be DB independent.

JT,
The example which you provided for unsaved-value=true, the primary ID is an id you assign and hence the database identity remains the same because the primary key is the same. In my case the primary identifier is sequence generated and thats the reason why hibernate issues an insert instead of an update.

JT,
The example which you provided for unsaved-value=true, the primary ID is an id you assign and hence the database identity remains the same because the primary key is the same. In my case the primary identifier is sequence generated and thats the reason why hibernate issues an insert instead of an update.

sorry it was a typo my first post showed: unsaved-value="null"
second one was a mistake.

JT,
The default value for unsaved-value is null.

I am using sequences as well, and I am not having this problem that you are... so by way of suggesting a next step: I'd would boil it down to a very simple test case and make sure it worked.
Something along the lines of how I have tried to outline in my pseudo-code example.

Is this based on the assumption that I have loaded the existing entity from the database into the current session context?
In my simple test I have the id values for all the entities I ant to persist set to unsaved-value=null. However on issuing the saveOrUpdate I still run into the constraintviolation exception. The session context does not load any entities from the db.

Jeevak

JT,
I am trying out the brute force method because the unsaved-value doesnt seem to work for me,please see my last post.

I also attempted the brute force method of catching the ConstraintViolationexception and then doing an update. From the debug logs it seems like the update was flushed to the db but finally when i look at the db there is no change. Everything seems to have gone through fine except for this one log which i found fishy.

success of batch update unknown: 0

post your hib.xml file and the java code that you are doing... I'll see if I can identify any issues...

Below are: hib.cfg.xml, couple of important mapping files and the code



Hibenate config file
------------------------

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.cglib.use_reflection_optimizer">true</property>
<property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="hibernate.connection.password">strongmail</property>
<property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:orcl</property>
<property name="hibernate.connection.username">sm</property>
<property name="hibernate.default_schema">SM</property>
<property name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>
<!--property name="hibernate.jdbc.batch_size">100</property-->
<!--property name="hibernate.hbm2ddl.auto">create-drop</property-->
<mapping resource="Customer.hbm.xml" />
<mapping resource="Failure.hbm.xml" />
<mapping resource="Mail.hbm.xml" />
<mapping resource="Mailing.hbm.xml" />
</session-factory>
</hibernate-configuration>
==============================================

DAO saveOrUpdate method
-----------------------------------

protected void saveOrUpdate(Object obj) {
Transaction t = null;
Session s = null;
try {
s = getSession();
t = beginTransaction(s);
saveOrUpdate(obj, s);
commitTransaction(t);
}
catch (HibernateException e) {
if (null != t) t.rollback();
throw e;
}
finally {
closeSession(s);
}
}

==============================================

logic for persistence
-----------------------
Transaction tx=null;
try{
tx=s.beginTransaction();
MailingDAO dao=new MailingDAO();
try{
dao.saveOrUpdate(mailing);
}
catch(ConstraintViolationException e){
dao.update(mailing);
}
CustomerDAO customerdao=new CustomerDAO();
Set mailset=mailing.getMails();
Iterator iterate=mailset.iterator();

while(iterate.hasNext())
{
Mail mail=(Mail)iterate.next();
MailDAO maildao=new MailDAO();

Customer customer=mail.getCustomer();
try{
customerdao.saveOrUpdate(customer);
}
catch(ConstraintViolationException e)
{
customerdao.update(customer);
}

Failure failure=mail.getFailure();
FailureDAO failuredao = new FailureDAO();
try{
failuredao.saveOrUpdate(failure);
}
catch(ConstraintViolationException e)
{
failuredao.update(failure);
}

try{
maildao.saveOrUpdate(mail);
}
catch(ConstraintViolationException e)
{
maildao.update(mail);
}

}
tx.commit();
}
catch(HibernateException e){
e.printStackTrace();
tx.rollback();
}
finally{
s.close();
}

==========================================

Mail entity mapping
---------------------

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 7, 2006 3:23:56 PM by Hibernate Tools 3.1.0.beta4 -->
<hibernate-mapping>
<class name="com.strongmail.domainobjects.Mail" table="MAIL">
<id name="seqMailId" type="string" unsaved-value="null">
<column name="SEQ_MAIL_ID" length="50" />
<generator class="native" />
</id>
<many-to-one name="mailing" class="com.strongmail.domainobjects.Mailing" fetch="select" unique-key="unique-mail">
<column name="SEQ_MAILING_ID" length="50" />
</many-to-one>
<many-to-one name="customer" class="com.strongmail.domainobjects.Customer" fetch="select">
<column name="CUSTOMER_ID" length="100" />
</many-to-one>
<many-to-one name="failure" class="com.strongmail.domainobjects.Failure" fetch="select">
<column name="SEQ_FAILURE_ID" length="100" />
</many-to-one>
<property name="rowId" type="string" unique-key="unique-mail">
<column name="ROW_ID" length="50" />
</property>
<property name="success" type="java.lang.Boolean">
<column name="SUCCESS" precision="1" scale="0" />
</property>
</class>
</hibernate-mapping>


=============================================

Mailing entity mapping
------------------------

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 7, 2006 3:23:56 PM by Hibernate Tools 3.1.0.beta4 -->
<hibernate-mapping>
<class name="com.strongmail.domainobjects.Mailing" table="MAILING">
<id name="seqMailingId" type="string" unsaved-value="null">
<column name="SEQ_MAILING_ID" length="50" />
<generator class="native" />
</id>
<property name="mailingId" type="string" unique-key="unique-mailing">
<column name="MAILING_ID" length="50" />
</property>
<property name="databaseId" type="string" unique-key="unique-mailing">
<column name="DATABASE_ID" length="100" />
</property>
<property name="mailDate" type="string">
<column name="MAIL_DATE" length="20" />
</property>
<property name="serialNo" type="string">
<column name="SERIAL_NO" length="100" />
</property>
<set name="mails" inverse="true">
<key>
<column name="SEQ_MAILING_ID" length="50" />
</key>
<one-to-many class="com.strongmail.domainobjects.Mail" />
</set>
</class>
</hibernate-mapping>