My configuration:
Database: DB2
Hibernate version: 3.0
Java version: 1.5

I have a fairly complicated class structure and must work with a legacy schema that is not exactly to my liking. However, I have been able to create mapping specs that create ddl that looks exactly like my schema. I was hopeful that the application would work but when I try to save my objects I get this SQL exception:

WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -407, SQLState: 23502
ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=1, TABLEID=108, COLNO=5

Basically this is saying that I am trying to insert a null into a column that is defined to not take nulls.

For this post I've trimmed the hierarchy down to just the three classes that are involved and their related tables and mapping specs.

Classes
-------
public class AbstractPerson {
Long personId; // the primary key
int isPop; // a discriminator
...
}

public class Appearance {
NormalizedRecord normRec;
...
}

public class NormalizedRecord {
Long normRecId; // primary key
Set<Appearance> appearances;
...
}

Tables
------
PERSON
PERSONID
ISPOP

This next table is basically an association table between Person and Norm_Rec. I would like to not have it because it's not really needed, but I am stuck with it.

PERSON_APP
PERSONID
NORMRECID

NORM_REC
NORMRECID

Mappings
--------
AbstractPerson
--------------
<hibernate-mapping default-cascade="none">
<class name="org.egcrc.cfr.examples.common.AbstractPerson"
table="PERSON" lazy="true" discriminator-value="-1">
<id name="personId" column="PERSONID" type="long" unsaved-value="null">
<generator class="native"/>
</id>
<discriminator column="ISPOP" type="integer"/>
</class>
</hibernate-mapping>

Appearance
----------
In this mapping I try to join the PERSON_APP table into the PERSON table.

<hibernate-mapping default-cascade="none">
<subclass name="org.egcrc.cfr.examples.Appearance"
extends="org.egcrc.cfr.examples.common.AbstractPerson"
discriminator-value="0"
lazy="true">
<join table="PERSON_APP">
<key column="PERSONID" not-null="true"/>
<many-to-one
name="myNormalizedRecord"
column="NORMRECID"
class="org.egcrc.cfr.examples.NormalizedRecord"
cascade="none"
not-null="true"
outer-join="false"
update="false"
insert="false"/>
</join>
</subclass>
</hibernate-mapping>

NormalizedRecord
----------------
In this mapping I treat the PERSON_APP table as an association table.

<hibernate-mapping default-cascade="none">
<class name="org.egcrc.cfr.examples.NormalizedRecord" table="NORMREC" lazy="true">
<id name="normRecId" column="NORMRECID" type="long" unsaved-value="null">
<generator class="native"/>
</id>
<set name="appearances" cascade="all" table="PERSON_APP">
<key column="NORMRECID" not-null="true"/>
<many-to-many class="org.egcrc.cfr.examples.Appearance" column="PERSONID"/>
</set>
</class>
</hibernate-mapping>


I've tried a large number of variations of the mapping specs, most of which do not even compile. This one at least generates a schema that looks exactly like the one I am stuck with. But as I said I get the error above when I try to save one of the NormalizedRecords and its associated Appearances.

TIA

Larry


P.S.
Here's the debugging messages with show_sql="true"
Hibernate: insert into CFR.NORM_REC (CREATE_DATE, JOB_ID, EVENT_DATE_IS_APPROX, EVENT_MAX_DATE, EVENT_MIN_DATE, EVENT_DATE, SOURCE_REC_FK, NORM_REC_PK) values (?, ?, ?, ?, ?, ?, ?, default)
Hibernate: values identity_val_local()
Hibernate: insert into CFR.PERSON (BIRTH_DATE_ISAPPROX, BIRTH_DATE_MAX_DATE, BIRTH_DATE_MIN_DATE, BIRTH_DATE, BIRTH_CITY, BIRTH_COUNTY, BIRTH_COUNTRY, BIRTH_STATE, BIRTH_ORDER, CURRENT_CITY, CURRENT_COUNTY, CURRENT_COUNTRY, CURRENT_STATE, DEATH_DATE_ISAPPROX, DEATH_DATE_MAX_DATE, DEATH_DATE_MIN_DATE, DEATH_DATE, DEATH_CITY, DEATH_COUNTY, DEATH_COUNTRY, DEATH_STATE, SEX, BIRTH_NAME, FIRST_NAME, LAST_NAME, MIDDLE_NAME, IS_POPULATION, PERSON_PK) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, default)
Hibernate: values identity_val_local()
Hibernate: insert into CFR.PERSON_APP_RELATIONSHIP (FATHER_FK, MOTHER_FK, HUSBAND_FK, WIFE_FK, PERSON_PK) values (?, ?, ?, ?, ?)
WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -407, SQLState: 23502
ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=1, TABLEID=108, COLNO=5
Hibernate: insert into CFR.PERSON (BIRTH_DATE_ISAPPROX, BIRTH_DATE_MAX_DATE, BIRTH_DATE_MIN_DATE, BIRTH_DATE, BIRTH_CITY, BIRTH_COUNTY, BIRTH_COUNTRY, BIRTH_STATE, BIRTH_ORDER, CURRENT_CITY, CURRENT_COUNTY, CURRENT_COUNTRY, CURRENT_STATE, DEATH_DATE_ISAPPROX, DEATH_DATE_MAX_DATE, DEATH_DATE_MIN_DATE, DEATH_DATE, DEATH_CITY, DEATH_COUNTY, DEATH_COUNTRY, DEATH_STATE, SEX, BIRTH_NAME, FIRST_NAME, LAST_NAME, MIDDLE_NAME, IS_POPULATION, PERSON_PK) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, default)
Hibernate: values identity_val_local()
Hibernate: insert into CFR.PERSON_APP_RELATIONSHIP (FATHER_FK, MOTHER_FK, HUSBAND_FK, WIFE_FK, PERSON_PK) values (?, ?, ?, ?, ?)
WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -407, SQLState: 23502
ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=1, TABLEID=108, COLNO=5

It's a little different than my example because it' using the actual schema and objects.


And here is the stack trace:

org.hibernate.exception.ConstraintViolationException: could not insert: [org.egcrc.cfr.normalized.Appearance]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:63)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1859)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2170)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:159)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:96)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:432)
at org.hibernate.engine.Cascades$5.cascade(Cascades.java:153)
at org.hibernate.engine.Cascades.cascade(Cascades.java:721)
at org.hibernate.engine.Cascades.cascade(Cascades.java:817)
at org.hibernate.event.def.AbstractSaveEventListener.cascadeBeforeSave(AbstractSaveEventListener.java:331)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:213)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:159)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:96)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:432)
at org.hibernate.engine.Cascades$5.cascade(Cascades.java:153)
at org.hibernate.engine.Cascades.cascade(Cascades.java:721)
at org.hibernate.engine.Cascades.cascadeCollection(Cascades.java:860)
at org.hibernate.engine.Cascades.cascade(Cascades.java:739)
at org.hibernate.engine.Cascades.cascade(Cascades.java:817)
at org.hibernate.event.def.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:121)
at org.hibernate.event.def.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:112)
at org.hibernate.event.def.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:59)
at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:39)
at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:659)
at org.hibernate.impl.SessionImpl.prepareQueries(SessionImpl.java:833)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:823)
at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:858)
at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:41)
at org.egcrc.cfr.persistence.HibernateObjectManager.getAllNormRecs(HibernateObjectManager.java:102)
at org.egcrc.cfr.common.CFRTestCase.removeAllNormRecsUsing(CFRTestCase.java:436)
at org.egcrc.cfr.persistence.NormalizedRecordPersistenceTest.tearDown(NormalizedRecordPersistenceTest.java:25)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:474)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:342)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:194)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=1, TABLEID=108, COLNO=5
at com.ibm.db2.jcc.a.hd.d(hd.java:1392)
at com.ibm.db2.jcc.c.jb.l(jb.java:366)
at com.ibm.db2.jcc.c.jb.a(jb.java:64)
at com.ibm.db2.jcc.c.w.a(w.java:48)
at com.ibm.db2.jcc.c.dc.c(dc.java:312)
at com.ibm.db2.jcc.a.id.cb(id.java:1685)
at com.ibm.db2.jcc.a.id.d(id.java:2276)
at com.ibm.db2.jcc.a.id.Y(id.java:537)
at com.ibm.db2.jcc.a.id.executeUpdate(id.java:520)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1846)
... 48 more

Remove the table="PERSON_APP" from the set in NormalizedRecord. That's for specifying the join table in many-to-many mappings, but the fact that you've got table="PERSON_APP" in the Appearance class tells me that the table is the actual appearance table, not a join table between PERSON and some other APPEARANCE table.

Thanks for the quick reply. I tried your suggestion and now I get a new table in addition to the three described before:

create table CFR.appearances (
NORM_REC_PK bigint not null,
PERSON_PK bigint not null,
primary key (NORM_REC_PK, PERSON_PK)
);

But I want Hibernate to use the existing PERSON_APP table.

Oops, it's a many-to-many, I missed that. Ok, you'll need a table on the set, but I'm pretty sure you don't want it to be PERSON_APP. Is PERSON_APP where you store the appearances? Assuming that it is, you'll need a join table (the one that hibernate has called appearances). Otherwise you'll need to change your many-to-many to one-to-many, to avoid the join table, but that means that a PERSON_APP can describe one and only one person.

...

Just had another look at the mapping. The Appearance mapping extends the AbstractPerson mapping? Is that right? Inferring a lot from the names of the classes (and that can be misleading, I know), that seems to break object-oriented rules: an appearance is not a kind-of person, it's a property of a person. I'm afraid that I may be getting confused by the names of your classes...

It's probably not that useful for this example to explain my domain model, but briefly this is what it is: I am processing birth, death, and marriage records. Each logical person who appears in one of those records results in an Appearance being created. At some point appearances are reviewed and those that represent the same person are merged. The result of that merge is a CompositePerson. Both CompositePerson and Appearance are direct subclasses of AbstractPerson.

So the answer to your question is yes, Appearance is a subclass of AbstractPerson.

In any case, after reading your suggestions and talking it over with a colleague I was able to solve the problem. The superclass of Appearance, AbstractPerson, also specified a join to the Person_App table. I finally figured out that this join was causing the generation of the not-null constraint. So I added inverse="true" to the join and voila it worked!



I should have put this in my original example but I didn't think it was significant at the time. Thanks again for you help.

Larry