Save failing on object that is reffed by two collections

Paul Newport · **Posted:** Thu Apr 20, 2006 4:19 am

Hi

I have a problem which I guess is due to a mapping error on my part. I am modelling a simple generic database. I have a Table, which has a collection of Columns, and a collection of Records. Records have a collection of ColumnValues. A ColumnValue is associated with a Record, and also a Column. Therefore in theory a Column has a collection of ColumnValues (i.e. all values for the column on all records) though in practice we will not be using that collection in the application.

So essentially I have a ColumnValue class that has a many-to-one association with both Column and Record.

The problem I am getting in my test case is that I set up a table instance, add some columns to it, then add a record, then add some column values to the record, setting the column on the columnvalue and the record.

When I then do a save on the table, I am getting this:

Attempt to insert null into a non-nullable column: column: COLUMN_IDENTIFIER table: X_COLUMN_VALUE in statement [insert into X_COLUMN_VALUE (version, value, record_identifer, column_value_index, identifier) values (?, ?, ?, ?, null)]

because the insert statement does not include the column_identifier field.

Has anyone any idea what I am doing wrong ?

Also how does hibernate sort out the fact that a column must be written to the database before any thing that references the column, e.g. the column value ?

Hibernate version:

3.1.3

Mapping documents:

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

<hibernate-mapping package="net.targetgroup.generic.database.model">
<class name="Table" table="X_TABLE">
<id name="identifier" unsaved-value="-1">
<generator class="native" />
</id>
<version name="version" ></version>

<property name="description" />
<property name="code" />

<list name="records" cascade="all">

<key column="table_identifier" not-null="true"></key>
<list-index base="0" column="record_index"></list-index>
<one-to-many class="Record" />

</list>

<list name="columns" cascade="all">

<key column="table_identifier" not-null="true"></key>
<list-index base="0" column="column_index"></list-index>
<one-to-many class="Column" />

</list>

</class>

</hibernate-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">

<hibernate-mapping package="net.targetgroup.generic.database.model">
<class name="Record" table="X_RECORD">
<id name="identifier" unsaved-value="-1">
<generator class="native" />
</id>
<version name="version"></version>

<many-to-one name="table" column="table_identifier"
not-null="true" insert="false" update="false">

</many-to-one>
<list name="columnValues" cascade="all">

<key column="record_identifer" not-null="true"></key>
<list-index base="0" column="column_value_index"></list-index>
<one-to-many class="ColumnValue" />

</list>
</class>

</hibernate-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">

<hibernate-mapping package="net.targetgroup.generic.database.model">
<class name="ColumnValue" table="X_COLUMN_VALUE">
<id name="identifier" unsaved-value="-1">
<generator class="native" />
</id>
<version name="version" ></version>

<many-to-one name="column" column="column_identifier"
not-null="true" insert="false" update="false">

</many-to-one>

<many-to-one name="record" column="record_identifier"
not-null="true" insert="false" update="false">

</many-to-one>

<property name="value"></property>

</class>

</hibernate-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">

<hibernate-mapping package="net.targetgroup.generic.database.model">
<class name="Column" table="X_COLUMN" >
<id name="identifier" unsaved-value="-1">
<generator class="native" />
</id>

<version name="version"></version>

<many-to-one name="table" column="table_identifier"
not-null="true" insert="false" update="false">

</many-to-one>
<property name="description" />
<property name="code" />

<list name="columnValues" cascade="all" >

<key column="column_identifer" ></key>
<list-index base="0" column="column_index"></list-index>
<one-to-many class="ColumnValue" />

</list>

</class>

</hibernate-mapping>

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

(using Spring hibernate support) - the save is done by the Spring saveOrUpdate method in HibernateDAOSupport

DaoFactory daoFactory = (DaoFactory) getContext().getBean("daoFactory");

GenericDatabaseIO genericDatabaseIO = daoFactory.getGenericDatabaseIO();
Table table = new Table("MUPPETS", "Muppets");
Column name = new Column(table, "NAME", "Name of Muppet");
table.getColumns().add(name);

Column height = new Column(table, "HEIGHT", "Height of Muppet");
table.getColumns().add(height);

Record ernie = new Record(table);

ernie.addColumnValue(name, "Ernie");
ernie.addColumnValue(height, "12");
table.addRecord(ernie);

Record bert = new Record(table);

bert.addColumnValue(name, "Bert");
bert.addColumnValue(height, "14");
table.addRecord(bert);
genericDatabaseIO.saveTable(table);

Full stack trace of any exception that occurs:

006-04-20 08:59:43,301 [main] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.SQL - insert into X_TABLE (version, description, code, identifier) values (?, ?, ?, null)
Hibernate: insert into X_TABLE (version, description, code, identifier) values (?, ?, ?, null)
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [net.targetgroup.generic.database.model.Table#<null>]
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.type.IntegerType - binding '0' to parameter: 1
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.type.StringType - binding 'Muppets' to parameter: 2
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.type.StringType - binding 'MUPPETS' to parameter: 3
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-04-20 08:59:43,301 [main] DEBUG org.hibernate.SQL - call identity()
Hibernate: call identity()
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.id.IdentifierGeneratorFactory - Natively generated identity: 1
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.Cascade - processing cascade ACTION_SAVE_UPDATE for: net.targetgroup.generic.database.model.Table
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.Cascade - cascade ACTION_SAVE_UPDATE for collection: net.targetgroup.generic.database.model.Table.records
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.CascadingAction - cascading to saveOrUpdate: net.targetgroup.generic.database.model.Record
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.VersionValue - version unsaved-value strategy UNDEFINED
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.IdentifierValue - id unsaved-value: -1
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - transient instance of: net.targetgroup.generic.database.model.Record
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.DefaultSaveOrUpdateEventListener - saving transient instance
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - saving [net.targetgroup.generic.database.model.Record#<null>]
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - executing insertions
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.Cascade - processing cascade ACTION_SAVE_UPDATE for: net.targetgroup.generic.database.model.Record
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.Cascade - done processing cascade ACTION_SAVE_UPDATE for: net.targetgroup.generic.database.model.Record
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.Versioning - using initial version: 0
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.WrapVisitor - Wrapped collection in role: net.targetgroup.generic.database.model.Record.columnValues
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Inserting entity: net.targetgroup.generic.database.model.Record (native id)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Version: 0
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.SQL - insert into X_RECORD (version, table_identifier, record_index, identifier) values (?, ?, ?, null)
Hibernate: insert into X_RECORD (version, table_identifier, record_index, identifier) values (?, ?, ?, null)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [net.targetgroup.generic.database.model.Record#<null>]
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.type.IntegerType - binding '0' to parameter: 1
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.type.LongType - binding '1' to parameter: 2
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.type.IntegerType - binding '0' to parameter: 3
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.SQL - call identity()
Hibernate: call identity()
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.id.IdentifierGeneratorFactory - Natively generated identity: 1
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.Cascade - processing cascade ACTION_SAVE_UPDATE for: net.targetgroup.generic.database.model.Record
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.Cascade - cascade ACTION_SAVE_UPDATE for collection: net.targetgroup.generic.database.model.Record.columnValues
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.CascadingAction - cascading to saveOrUpdate: net.targetgroup.generic.database.model.ColumnValue
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.VersionValue - version unsaved-value strategy UNDEFINED
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.IdentifierValue - id unsaved-value: -1
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - transient instance of: net.targetgroup.generic.database.model.ColumnValue
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.DefaultSaveOrUpdateEventListener - saving transient instance
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - saving [net.targetgroup.generic.database.model.ColumnValue#<null>]
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - executing insertions
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.Versioning - using initial version: 0
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.VersionValue - version unsaved-value strategy UNDEFINED
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.engine.IdentifierValue - id unsaved-value: -1
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Inserting entity: net.targetgroup.generic.database.model.ColumnValue (native id)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Version: 0
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.SQL - insert into X_COLUMN_VALUE (version, value, record_identifer, column_value_index, identifier) values (?, ?, ?, ?, null)
Hibernate: insert into X_COLUMN_VALUE (version, value, record_identifer, column_value_index, identifier) values (?, ?, ?, ?, null)
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Dehydrating entity: [net.targetgroup.generic.database.model.ColumnValue#<null>]
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.type.IntegerType - binding '0' to parameter: 1
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.type.StringType - binding 'Ernie' to parameter: 2
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.type.LongType - binding '1' to parameter: 3
2006-04-20 08:59:43,316 [main] DEBUG org.hibernate.type.IntegerType - binding '0' to parameter: 4
2006-04-20 08:59:43,332 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-20 08:59:43,332 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
2006-04-20 08:59:43,332 [main] DEBUG org.hibernate.util.JDBCExceptionReporter - could not insert: [net.targetgroup.generic.database.model.ColumnValue] [insert into X_COLUMN_VALUE (version, value, record_identifer, column_value_index, identifier) values (?, ?, ?, ?, null)]
java.sql.SQLException: Attempt to insert null into a non-nullable column: column: COLUMN_IDENTIFIER table: X_COLUMN_VALUE in statement [insert into X_COLUMN_VALUE (version, value, record_identifer, column_value_index, identifier) values (?, ?, ?, ?, null)]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:1976)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2405)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:37)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:269)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:167)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:101)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:98)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:502)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:494)

Name and version of the database you are using:

HSQL latest

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Hibernate uses inverse="true/false" and key not-null="true/false" to dictate the ordering of inserts/updates. However that only works for direct associations: afaik, there's no way to require that Table.getRecords() be saved before Table.getColumns(), which is what you need in order to give the ColumnValue objects identifier values.

In your case, you should either be mapping this as a ternary relationship (see section 6.3.4 of the ref docs), or else do the save in two stages: first set everything except the Column.getColumnValues() collection. When you save the Table, all the Record.getColumnValues() will be saved and therefore will get identifier values. Now add the appropriate ColumnValues to the Column.getColumnValues() collection and re-save your Table. That will avoid the null value problems.

Paul Newport · **Posted:** Thu Apr 20, 2006 6:10 pm

Thanks for this. I had circumvented the problem by doing two saves as you had mentioned, and in the real world this would be the way things would happen anyway - you would not be altering the virtual table definitions at the same time as adding data, which is what I was doing in my test case.

I did not know about ternary relationships, and will look into that.

Thanks again.