Hello Everyone:
I have been experiencing some problems with update. I have a simple table made up there columns and when load a row in that table and change a column, the update fails. I was wondering if anyone can be kind enough to give me some hints. Here are more info:

Hibernate version:

2.1.6

Mapping documents:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>

<class name="iam.ccol.model.RouteSequence" table="ccol.ROUTE_SEQ" >

<id name="id" column="ROUTE_SEQ_ID" type="long" unsaved-value="0">
<generator class="native"/>
</id>

<property name="key" type="string" column="KEY" not-null="true" unique="true" update="false"/>
<property name="value" type="string" column="VALUES" not-null="true" update="true"/>
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

HibernateUtil util = HibernateUtil.getInstance();
Session session = util.getSession();
RouteSequenceDAO routeDAO = new RouteSequenceDAO();
util.beginTransaction();
RouteSequence r = routeDAO.getRouteSequenceById(1);
r.setValue("1;2;3;4;5");
session.flush();
//session.update(r); (this also gives me the same exception)
util.commitTransaction();
assertEquals(r.getValue(),"1;2;3;4;5");

Full stack trace of any exception that occurs:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "2" constrains table "CCOL.ROUTE_SEQ" from having duplicate rows for those columns. SQLSTATE=23505

Name and version of the database you are using:

DB2 8.1

Debug level Hibernate log excerpt:

show generated sql

Thanks for you reply Anthony. As I was trying to fix the problem. I realized that If I run the code alone (while commenting out all other test cases) it works. However, I run it in conjunction with the following test case it fails(Even it does not get to the point of generating update sql):

public void testGetRouteSequenceById() {

try {
HibernateUtil util =HibernateUtil.getInstance();
Session session = util.getSession();
util.beginTransaction();
RouteSequenceDAO routeDAO = new RouteSequenceDAO();
RouteSequence r = routeDAO.getRouteSequenceById(1);
assertEquals(Constants.CONNECTIVITY, r.getKey().trim());
assertEquals("3,6,12,89,12,50,21", r.getValue().trim());
session.clear();

r = routeDAO.getRouteSequenceById(2);
assertEquals(Constants.APPLICATION, r.getKey().trim());
assertEquals("13,26,5,1,62,25", r.getValue().trim());
session.clear();

r = routeDAO.getRouteSequenceById(3);
assertEquals(Constants.SYSTEM, r.getKey().trim());
assertEquals("29,9,15,11,31,25", r.getValue().trim());
session.clear();
util.commitTransaction();
session.close();

} catch (Exception e) {
fail("An unexpected exception has occured --> " + e.getMessage());
HibernateUtil.getInstance().closeSession();
}
}

Only the following sql is generated which is related to the above testcase:

8/20/04 16:38:38:674 EDT] 92dcf65 SystemOut O Hibernate: select routeseque0_.ROUTE_SEQ_ID as ROUTE_SE1_0_, routeseque0_.KEY as KEY0_, routeseque0_.VALUES as VALUES0_ from ccol.ROUTE_SEQ routeseque0_ where routeseque0_.ROUTE_SEQ_ID=?
[8/20/04 16:38:38:721 EDT] 92dcf65 SystemOut O Hibernate: select routeseque0_.ROUTE_SEQ_ID as ROUTE_SE1_0_, routeseque0_.KEY as KEY0_, routeseque0_.VALUES as VALUES0_ from ccol.ROUTE_SEQ routeseque0_ where routeseque0_.ROUTE_SEQ_ID=?
[8/20/04 16:38:38:752 EDT] 92dcf65 SystemOut O Hibernate: select routeseque0_.ROUTE_SEQ_ID as ROUTE_SE1_0_, routeseque0_.KEY as KEY0_, routeseque0_.VALUES as VALUES0_ from ccol.ROUTE_SEQ routeseque0_ where routeseque0_.ROUTE_SEQ_ID=?


I really do not understand what is going on. I would appreciate if you can give me some hints on why it works when I run it alone. Just for the sake of completeness , here is also the whole testcase for the update :

public void testUpdateRouteSequence(){
try{
HibernateUtil util = HibernateUtil.getInstance();
Session session = util.getSession();
RouteSequenceDAO routeDAO = new RouteSequenceDAO();
util.beginTransaction();
RouteSequence r = routeDAO.getRouteSequenceById(super.getTableId("ROUTE_SEQ_ID","KEY",Constants.CONNECTIVITY,"CCOL.ROUTE_SEQ"));
r.setValue("1;2;3;4;5");
session.update(r);
util.commitTransaction();
assertEquals(r.getValue(),"1;2;3;4;5");

}catch(Exception e){
fail("An unexpected exception has occured --> " + e.getMessage());
HibernateUtil.getInstance().closeSession();
}
}

just look at your utl class and DAO, the other seems good

My DAO and Util class are pretty straight forward, May be I am missing something. Here are the two classes:

public class RouteSequenceDAO {


public RouteSequenceDAO() {}

public RouteSequence getRouteSequenceById(long routeId) {
Session session = HibernateUtil.getInstance().getSession();
RouteSequence route = null;
try {
route =
(RouteSequence) session.load(
iam.ccol.model.RouteSequence.class,
new Long(routeId));
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}
return route;
}

public Collection getRouteSequenceAll() {
Session session = HibernateUtil.getInstance().getSession();
try {
return session.createCriteria(RouteSequence.class).list();
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}
}

}


And the Util class:

public class HibernateUtil {

private static Configuration configuration=null;
private static SessionFactory sessionFactory = null;
private static final ThreadLocal threadSession = new ThreadLocal();
private static final ThreadLocal threadTransaction = new ThreadLocal();
private static final HibernateUtil hUtil = new HibernateUtil();

private HibernateUtil() {
try {
configuration = new Configuration();
sessionFactory = configuration.configure().buildSessionFactory();

} catch (Throwable ex) {
ex.printStackTrace(System.out);
throw new ExceptionInInitializerError(ex);
}
}

public static HibernateUtil getInstance() {

return hUtil;
}

/**
* Returns the original Hibernate configuration.
*
* @return Configuration
*/
public static Configuration getConfiguration() {
return configuration;
}

/**
* Returns the SessionFactory used for this static class.
*
* @return SessionFactory
*/
public static SessionFactory getSessionFactory() {
return sessionFactory;
}

/**
* Rebuild the SessionFactory with the static Configuration.
*
*/
public void rebuildSessionFactory()
throws InfrastructureException {
synchronized(sessionFactory) {
try {
sessionFactory = getConfiguration().buildSessionFactory();
} catch (Exception ex) {
throw new InfrastructureException(ex);
}
}
}

/**
* Rebuild the SessionFactory with the given Hibernate Configuration.
*
* @param cfg
*/
public void rebuildSessionFactory(Configuration cfg)
throws InfrastructureException {
synchronized(sessionFactory) {
try {
sessionFactory = cfg.buildSessionFactory();
configuration = cfg;
} catch (Exception ex) {
throw new InfrastructureException(ex);
}
}
}

/**
* Retrieves the current Session local to the thread.
* If no Session is open, opens a new Session for the running thread.
*
* @return Session
*/
public Session getSession() {
Session s = (Session) threadSession.get();
// Open a new Session, if this thread has none yet

try {
if (s == null) {
s = sessionFactory.openSession();
threadSession.set(s);
}else if(s!= null && ! s.isOpen()){
s = sessionFactory.openSession();
threadSession.set(s);
}
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}

return s;
}

/**
* Closes the Session local to the thread.
*/
public void closeSession() {
try {
Session s = (Session) threadSession.get();
threadSession.set(null);
if (s != null && s.isOpen())
s.close();
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}
}

/**
* Start a new database transaction.
*/
public void beginTransaction() {
Transaction tx = (Transaction) threadTransaction.get();
try {
if (tx == null) {
tx = getSession().beginTransaction();
threadTransaction.set(tx);
}
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}
}

/**
* Commit the database transaction.
*/
public void commitTransaction() {
Transaction tx = (Transaction) threadTransaction.get();
try {
threadTransaction.set(null);
if (tx != null && !tx.wasCommitted() && !tx.wasRolledBack())
tx.commit();
threadTransaction.set(null);
} catch (HibernateException ex) {
rollbackTransaction();
throw new InfrastructureException(ex);
}
}

public void rollbackTransaction() {
Transaction tx = (Transaction) threadTransaction.get();
try {
threadTransaction.set(null);
if (tx != null && !tx.wasCommitted() && !tx.wasRolledBack()) {
tx.rollback();
}
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
} finally {
closeSession();
}
}
}

now, tell me what's happening with this:

HibernateUtil util = HibernateUtil.getInstance();
Session session = util.getSession();
RouteSequenceDAO routeDAO = new RouteSequenceDAO();
Transaction tx = session.beginTransaction();
RouteSequence r = routeDAO.getRouteSequenceById(super.getTableId("ROUTE_SEQ_ID","KEY",Constants.CONNECTIVITY,"CCOL.ROUTE_SEQ"));
r.setValue("1;2;3;4;5");
session.flush();
tx.commit();
// don't forget to finish by closing the session
//session.update(r);
//util.commitTransaction();

Hi Anthony:

It worked !! After some deliberation, I realized that it was a problem with HibernateUtil. The problem occured when I added modified getSession().

The original method was like follows:

public Session getSession() {
Session s = (Session) threadSession.get();
// Open a new Session, if this thread has none yet

try {
if (s == null) {
s = sessionFactory.openSession();
threadSession.set(s);
} } catch (HibernateException ex) {
throw new InfrastructureException(ex);
}

return s;
}

However, I had an issue that the session used get closed without an explicit call to session.close(), that is to say, I used to open the session in the "setup" method and then close it in "teardown" , but somewhere down the line session used to get closed so I changed getSession to follows:

public Session getSession() {
Session s = (Session) threadSession.get();
// Open a new Session, if this thread has none yet

try {
if (s == null) {
s = sessionFactory.openSession();
threadSession.set(s);
} else if (s != null && !s.isOpen()) {
s = sessionFactory.openSession();
threadSession.set(s);
}
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}

return s;
}



but this means when session get closed (for some unknown reason that I do not know), the transaction related that particular session remains in threadlocal variable without being committed. So next request to getTransaction returns the tx for the previous session. So after looking at your piece of code I modified getSession as follows, and now it works:

public Session getSession() {
Session s = (Session) threadSession.get();
// Open a new Session, if this thread has none yet

try {
if (s == null) {
s = sessionFactory.openSession();
threadSession.set(s);
} else if (s != null && !s.isOpen()) {
s = sessionFactory.openSession();
threadSession.set(s);
Transaction tx = (Transaction)threadTransaction.get();
if(tx != null)
commitTransaction();
}
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}

return s;
}

Thanks a lot for your help Anthony, In the beginning The Threadlocal pattern seemed very appealing but It turns out to be not as appealling as it looks. Once again thanks

Threadlocal pattern is really a good thing.
I'll advise you to use a servlet filter to manage the open, close and thread attachment.
You can also write a static method getSession in this filter.
For the transaction, there is no "good" automatic or threadlocal solution, you should always explicitly manage the transaction at business level.