Hello,

I am working on a web application bases on Spring MVC and uses Hibernate 3.2.6.ga to persist the data in Postgres database.
I am trying to copy the data from one table to another with same schema where I have shared the same set of java beans between two sets of tables. Now the entity I want to store is not a simple entity but composite object which is why saving this entity to database needs 3 tables. To copy this entity I have created same set of another 3 tables with different names. When I try to copy, the problem is that hibernate links first level of objects correctly but not from second onwards. Please suggest what is the best way to implement this scenario. I have detailed the problem below. Please be patient to go through such a long description. Responses will be appreciated greatly:

I have an entity called 'Query' and in certain conditions, I need to move some of the records from Query table to another table with same schema. Query is not a simple entity; it has List of objects of type DataSource as one of its attributes and DataSource has List of DataFields as one of its attributes. In short, Query corresponds to multipe Datasources and DataSource correspponds to multiple DataFields. There are 3 database tables and 3 different hibernate beans I have created to store the Query object in database. Please look at following beans and their mapping:

public class Query implements Serializable {
...
private List dataSources;
}
public class DataSourceMainHBean implements Serializable {
...
private List dataFields;
}
public class DataFieldMainHBean implements Serializable {
...
}

Query.hbm.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.akazaresearch.phenonet.server.main.persist.hiber">
<class name="Query" table="runtime_build_query" entity-name="Query">
<id name="queryId" column="id" >
<generator class="increment"/>
</id>
...
<list name="dataSources" inverse="false" cascade="all">
<key column="queryid" />
<index column="dsidx"/>
<one-to-many entity-name="runtimeQueryAttachedDSEntry"/>
</list>
</class>
</hibernate-mapping>

DataSourceMainHBean.hbm.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.akazaresearch.phenonet.server.main.persist.hiber">
<class name="DataSourceMainHBean" table="runtime_build_datasources" entity-name="runtimeQueryAttachedDSEntry">
<id name="id" column="id" >
<generator class="increment"/>
</id>
...
<list name="dataFields" inverse="false" cascade="all">
<key column="dsid" />
<index column="dfidx"/>
<one-to-many class="DataFieldMainHBean"/>
</list>
</class>
</hibernate-mapping>

DataFieldMainHBean.hbm.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.akazaresearch.phenonet.server.main.persist.hiber">
<class name="DataFieldMainHBean" table="runtime_build_datafields" >
<id name="id" column="id" >
<generator class="increment"/>
</id>
...
</class>
</hibernate-mapping>

I have created another set of 3 tables to store the moved records. I want to share the same Java hibernate beans between these set of tables; so I have following mapping for another set of tables:

ChinMainDeletedQuery.hbm.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.akazaresearch.phenonet.server.main.persist.hiber">
<class name="Query" table="chinmain_deleted_query" entity-name="deletedQuery">
<id name="queryId" column="id" >
<generator class="assigned"/>
</id>
...
<list name="dataSources" inverse="false" cascade="all">
<key column="queryid" />
<index column="dsidx"/>
<one-to-many entity-name="deletedQueryDatasources"/>
</list>
</class>
</hibernate-mapping>

ChinMainDeletedQueryDataSourcesHBean.hbm.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.akazaresearch.phenonet.server.main.persist.hiber">
<class name="DataSourceMainHBean" table="chinmain_deleted_query_datasources" entity-name="deletedQueryDatasources">
<id name="id" column="id" >
<generator class="assigned"/>
</id>
...
<list name="dataFields" inverse="false" cascade="all">
<key column="dsid" />
<index column="dfidx"/>
<one-to-many entity-name="deletedQueryDatafields"/>
</list>
</class>
</hibernate-mapping>

ChinMainDeletedQueryDataFieldsHBean.hbm.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.akazaresearch.phenonet.server.main.persist.hiber">
<class name="DataFieldMainHBean" table="chinmain_deleted_query_datafields" entity-name="deletedQueryDatafields">
<id name="id" column="id" >
<generator class="assigned"/>
</id>
...
</class>
</hibernate-mapping>

Method to copy records from original Query table to deleted Query table is as follows:
private static void saveDeletedQuery(Query query) {
ChinLogger.logmsg(ChinLogger.CHINLOG_DEBUG, "QueryMgr -> saveDeletedQuery");
if (null != query){
ChinLogger.logmsg(ChinLogger.CHINLOG_DEBUG, "Query id: " + query.getQueryId());
ChinLogger.logmsg(ChinLogger.CHINLOG_DEBUG, "Saving Deleted query instance to another table");
Transaction tx = null;
Session session = InitSessionFactory.getInstance().getCurrentSession();
try {
tx = session.beginTransaction();
query.setLastUpdateTimestamp(chinutil.getCurrentTimestampInstance());
session.save("deletedQuery", query);
tx.commit();
} catch (HibernateException e) {
e.printStackTrace();
if (tx != null && tx.isActive())
tx.rollback();
}
ChinLogger.logmsg(ChinLogger.CHINLOG_DEBUG, "Saving Deleted query instance to another table complete.");
}
}

Exception I get is as follows:
DEBUG: Saving Deleted query instance to another table
Hibernate: select datasource0_.queryid as queryid1_, datasource0_.id as id1_, da
tasource0_.dsidx as dsidx1_, datasource0_.id as id6_0_, datasource0_.dsid as dsi
d6_0_ from runtime_build_datasources datasource0_ where datasource0_.queryid=?
Hibernate: insert into chinmain_deleted_query (userid, name, description, summar
y, approved, complete, activated, finalized, result_download_approved, last_upda
te_timestamp, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: update runtime_build_datasources set queryid=null, dsidx=null where q
ueryid=?
Hibernate: update chinmain_deleted_query_datasources set queryid=?, dsidx=? wher
e id=?
org.hibernate.StaleStateException: Batch update returned unexpected row count fr
om update [0]; actual row count: 0; expected: 1

It seems that hibernate is not getting the object mapping correctly which is why it trying to store list of DataSources of deleted query into datasources table in the first set of tables.

I also tried to change cascade="none" in deleted Query and deleted Query DataSources table and try to store the collection of DataSources explicitly into second DataSources table beofore I save the parent object Deleted Query. But it gives me hibernate exception LazyInitializationException when I try to detach the DataSources collection from my original Query object.