This is a common condition which I think I do understand. What I don't understand is the mapping syntax I need to address it. I have a very simple demo with two tables. One contains test items with a compound key and a data field. The other is an associative table with a compound key and foreign keys for two test item records. Note that KEY_FIELD_1 will always match for all records in the same series and need not be cuplicated in the associative table. Here is the DDL for the two tables:
Code:
CREATE TABLE devel.TESTITEM
(
KEY_FIELD_1 CHAR(20) NOT NULL,
KEY_FIELD_2 CHAR(20) NOT NULL,
DATA_FIELD CHAR(20) NOT NULL,
CONSTRAINT TESTITEM_PK PRIMARY KEY
(KEY_FIELD_1, KEY_FIELD_2)
);
CREATE TABLE devel.TESTASSN
(
KEY_FIELD_1 CHAR(20) NOT NULL,
KEY_FIELD_3 CHAR(20) NOT NULL,
KEY_FIELD_2A CHAR(20) NOT NULL,
KEY_FIELD_2b CHAR(20) NOT NULL,
CONSTRAINT TESTASSN_PK PRIMARY KEY
(KEY_FIELD_1, KEY_FIELD_3),
CONSTRAINT TESTASSN_FK0 FOREIGN KEY
(KEY_FIELD_1, KEY_FIELD_2A) REFERENCES devel.TESTITEM
(KEY_FIELD_1, KEY_FIELD_2) MATCH FULL,
CONSTRAINT TESTITEM_FK1 FOREIGN KEY
(KEY_FIELD_1, KEY_FIELD_2B) REFERENCES devel.TESTITEM
(KEY_FIELD_1, KEY_FIELD_2 ) MATCH FULL
);
The mapping files, generated by Eclipse reverse engineering, are shown below, followed by the requested bits of information for a posting. I'll continue with what I've tried at the end of the post.
Hibernate version: Hibernate-3.2.1
Mapping documents:Test Item:
Code:
<?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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.xxxxx.engine.hibernate.Testitem" table="testitem" schema="devel">
<composite-id name="id" class="com.xxxxx.engine.hibernate.TestitemId">
<key-property name="keyField1" type="java.lang.String">
<column name="key_field_1" length="20" />
</key-property>
<key-property name="keyField2" type="java.lang.String">
<column name="key_field_2" length="20" />
</key-property>
</composite-id>
<property name="dataField" type="java.lang.String">
<column name="data_field" length="20" not-null="true" />
</property>
<set name="testassnsForTestassnFk0" inverse="true">
<key>
<column name="key_field_1" length="20" not-null="true" />
<column name="key_field_2a" length="20" not-null="true" />
</key>
<one-to-many class="com.xxxxx.engine.hibernate.Testassn" />
</set>
<set name="testassnsForTestitemFk1" inverse="true">
<key>
<column name="key_field_1" length="20" not-null="true" />
<column name="key_field_2b" length="20" not-null="true" />
</key>
<one-to-many class="com.xxxxx.engine.hibernate.Testassn" />
</set>
</class>
</hibernate-mapping>
Test Association (reformatted some to shorten the line lengths for readability)
Code:
<?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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.xxxx.engine.hibernate.Testassn" table="testassn" schema="devel">
<composite-id name="id" class="com.xxxx.engine.hibernate.TestassnId">
<key-property name="keyField1" type="java.lang.String">
<column name="key_field_1" length="20" />
</key-property>
<key-property name="keyField3" type="java.lang.String">
<column name="key_field_3" length="20" />
</key-property>
</composite-id>
<many-to-one name="testitemByTestassnFk0"
class="com.xxxx.engine.hibernate.Testitem"
update="false" insert="false" fetch="select">
<column name="key_field_1" length="20" not-null="true" />
<column name="key_field_2a" length="20" not-null="true" />
</many-to-one>
<many-to-one name="testitemByTestitemFk1"
class="com.xxxx.engine.hibernate.Testitem"
update="false" insert="false" fetch="select">
<column name="key_field_1" length="20" not-null="true" />
<column name="key_field_2b" length="20" not-null="true" />
</many-to-one>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():Here is the code that creates and inserts two item records, and then creates and attempts to insert an association record linking the two of them.
Code:
Session session = sessionFactory.getCurrentSession();
transaction = session.beginTransaction();
// Clear any existing records
session.createQuery("delete from Testitem").executeUpdate();
session.createQuery("delete from Testassn").executeUpdate();
// Create the first item record.
TestitemId testitemId1 = new TestitemId("Series 1", "Item 1");
Testitem testitem1 = new Testitem (testitemId1, "Data Item 1");
session.save(testitem1);
// Create the second item record.
TestitemId testitemId2 = new TestitemId("Series 1", "Item 2");
Testitem testitem2 = new Testitem (testitemId2, "Data Item 2");
session.save(testitem2);
// Create an association record.
TestassnId testassnId = new TestassnId ("Series 1", "Association 1");
Testassn testassn = new Testassn(testassnId, testitem1, testitem2);
session.save(testassn);
transaction.commit();
session.close();
Full stack trace of any exception that occurs:Because the foreign keys are marked as inssert-"false", their columns are left off the associative table's insert statement, and the insert fails with null values for non-nulable columns.
Code:
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:69)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:230)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1009)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:356)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at com.xxxx.engine.HibernateTest.insertRows(HibernateTest.java:68)
at com.xxxx.engine.HibernateTestTest.testHibernateTest(HibernateTestTest.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:168)
at junit.framework.TestCase.runBare(TestCase.java:134)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into devel.testassn (key_field_1, key_field_3) values (Series 1, Association 1) was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2534)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1328)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:352)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2596)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
... 28 more
Name and version of the database you are using:PostgreSQL 8.1
The generated SQL (show_sql=true):Code:
Hibernate: delete from devel.testitem
Hibernate: delete from devel.testassn
Hibernate: insert into devel.testitem (data_field, key_field_1, key_field_2) values (?, ?, ?)
Hibernate: insert into devel.testitem (data_field, key_field_1, key_field_2) values (?, ?, ?)
Hibernate: insert into devel.testassn (key_field_1, key_field_3) values (?, ?)
Debug level Hibernate log excerpt:Sorry, not at debug level, but we know what the problem is.
Code:
WARN logExceptions, SQL Error: 0, SQLState: null
ERROR logExceptions, Batch entry 0 insert into devel.testassn (key_field_1, key_field_3) values (Series 1, Association 1) was aborted. Call getNextException to see the cause.
WARN logExceptions, SQL Error: 0, SQLState: 23502
ERROR logExceptions, ERROR: null value in column "key_field_2a" violates not-null constraint
ERROR performExecutions, Could not synchronize database state with session
Given all this and a few days searching the Hibernate doc, the forums, and Google, it seems that the solution is to manually set a property for insert="true" and update="true" for the missing column. Unfortunately, all the examples I found were for single field keys that used something like this:
Code:
<many-to-one name="testitemByTestassnFk0"
class="com.xxxx.engine.hibernate.Testitem"
update="false" insert="false" fetch="select">
<column name="key_field_1" length="20" not-null="true" />
<column name="key_field_2a" length="20" not-null="true" />
</many-to-one>
<property name="keyField2" type="java.lang.String" insert="true" update="true" />
This does not work because there are no accessors in "testitemByTestassnFk0" for values inside the key object (com.xxxx.engine.hibernate.TestitemId). I may have gotten closer with something like this:
Code:
<many-to-one name="testitemByTestassnFk0"
class="com.xxxx.engine.hibernate.Testitem"
update="false" insert="false" fetch="select">
<column name="key_field_1" length="20" not-null="true" />
<column name="key_field_2a" length="20" not-null="true" />
</many-to-one>
<component name="id" class="com.xxx.engine.hibernate.TestitemId">
<property name="keyField2" type="java.lang.String" insert="true" update="true" />
</component>
however, there is no accessor for "id" in the overall class of Testassn. If I try to wrap another component tag around this, Hibernate compalins that I am defining duplicate property mapping for testitemByTestassnFk0, which is in fact exactly what I am trying to do!
So, given all this background, how do I define a "property" for a field inside an ID object that is a foreign key on a related table? Alternatively, is there some other approach that I have missed completely. This is such a simple and, I would think, common condition that I am amazed I haven't seen anything that addresses this specific structure.
I'd welcome any suggestions or pointers to appropriate doc or other materials that I have obviously missed about this.
Thanks for any suggestions that would get this going again.
JFE.
Login
FAQ
Search