composite PK with a component of a composite FK

ReX · **Posted:** Thu May 22, 2008 10:53 am

Hi

I'm using hibernate tools for reverse engineering from a customer data model. One of it relationship isn't parsing correctly for hb-tools.

The follow example explain the situation: suppose a table A with a composed primary key (aa, ab, ac ), and a B table with a foreign key to A, and the B primary key is composed of (A->aa, bd, be).

A [aa PK, ab PK, ac PK, ad, ae]
B [A->aa PK, bd PK, be PK, bf, A->ab, A->ac]

The constructor of the generated class B receive a class A object with the aa, ab & ac values setted in a non null value. I apply the persist method to B, but when the transaction is commited it produce an exception:
"Cannot insert the value NULL into column A->ab, table B; column does not allow nulls. INSERT fails."

Please, if any body resolve a similar problem I appreciate your suggestion.

Thanks.

ReX · **Posted:** Thu May 22, 2008 2:57 pm

Hi, looking the hibernate SQL generated when insert, I see it's exclude the not null column in the insert sentence. Exists any way for include this column when hibernate generate it SQL??

max · **Posted:** Fri May 23, 2008 5:02 am

mapping files ?

exception ?

...something concrete please ;)

ReX · **Posted:** Fri May 23, 2008 9:40 am

Now more information.

The table with problem has a non-common characteristic, it have one column as reference two foreign key (the some by two ways).

The table is named IDENTITY and one component of it primary key is `ID_GROUP`, the table and the foreign key declared how:

Code:

CREATE TABLE IDENTITY (
       ID_GROUP            int NOT NULL,
       ID_ITEM_PS          varchar(14) NOT NULL,
       ID_ITEM             char(32) NOT NULL,
       EF_TIME             datetime
);

ALTER TABLE IDENTITY
       ADD PRIMARY KEY CLUSTERED (ID_GROUP ASC, ID_ITEM_PS ASC);

ALTER TABLE IDENTITY
           ADD FOREIGN KEY (ID_GROUP)
                             REFERENCES GROUP (ID_GROUP);

ALTER TABLE IDENTITY
       ADD FOREIGN KEY (ID_GROUP, ID_ITEM, EF_TIME)
                             REFERENCES GROUP_ITEM  (ID_GROUP, 
                                      ID_ITEM, EF_TIME);

The GROUP_ITEM.ID_GROUP is a foreign key to GROUP.ID_GROUP.

The insert sentence generated for hibenate look like:

Code:

insert into ENTITY (ID_GROUP, ID_ITEM_PS) values (?, ?)

Observe it's only using two columns of the table, missing the not null ID_ITEM column.

The HBM generated look like:

Code:

<hibernate-mapping>
    <class name="domain.Identity" table="IDENTITY" schema="dbo" catalog="base">
        <composite-id name="id" class="domain.IdentityId">
            <key-property name="GroupId" type="java.lang.Integer">
                <column name="ID_GROUP" />
            </key-property>
            <key-property name="psItemId" type="string">
                <column name="ID_ITEM_PS" length="14" />
            </key-property>
        </composite-id>
        <many-to-one name="GroupItem" class="domain.GroupItem" update="false" insert="false" fetch="select">
            <column name="ID_GROUP" not-null="true" />
            <column name="ID_ITEM" length="32" not-null="true" />
            <column name="EF_TIME" length="23" />
        </many-to-one>
        <many-to-one name="Group" class="domain.Group" update="false" insert="false" fetch="select">
            <column name="ID_GROUP" not-null="true" />
        </many-to-one>
        <set name="LineItems" inverse="true">
            <key>
                <column name="ID_GROUP" />
                <column name="ID_ITEM_PS" length="14" />
            </key>
            <one-to-many class="domain.LineItem" />
        </set>
    </class>
</hibernate-mapping>

And the Exception is:

Code:

org.hibernate.exception.ConstraintViolationException: could not insert: [domain.Identity]
...
...
Caused by: java.sql.SQLException: Cannot insert the value NULL into column 'ID_ITEM', table 'IDENTITY'; column does not allow nulls. INSERT fails.
...

The exception occurs when the `commit` transaction method is called. When I create domain.Identity instances and then apply the `persist` method of it Dao class, this pass without errors.

In the debuging process I check if the Identity.id_item property is correctly setted, and is OK.

I think in the double foreign key in the some column causes a hibernate confusion. I not have posibility of change the datamodel.

Thanks.

kalisharma007 · **Joined:** Wed May 14, 2008 3:45 pm **Posts:** 8

Since you are using the hibernate tool to generate the code, hence I assume that you must have a separate java file for the Id that contains the primary key properties, namely, IdGroup and IdItemPS.
Similarly, you must also have a manytoone mapping from Identity to Group.

If you are using annotation then you must have mapping like this

@ManyToOne(targetEntity="Group")
@JoinColumn(name="ID_GROUP",insertable=false,updatable=false,nullable=false)
public Group getGroup(){
..
}

Now you can modify the setter method of this Group as follows

public void setGroup(Group group){
this.group = group;

if(this.id == null){
this.id = new IdentityId(); // here IdentityId is assumed to be class name of the primary key class
}

if(group != null){
this.id.setIdGroup(group.getId());
}

}

When you persist the Group object you do like this

Group g = new Group();
//set other property of the g
Identity identity = new Identity();
IdentityId iid = new IdentityId();
iid.setIdItemPS(123213); // set the value
//just set the value for itemIdPs. Hibernate will set the value of the group id when it persists the group
identity.setGroup(g);
g.getIdentities().add(identity);
em.merge(g);

ReX · **Posted:** Fri May 23, 2008 6:17 pm

Hi

I'm restricted to use JDK 1.4, therefore i can not use annotations, also we have expectations of use all HB-Tools generated files (HBM, POJOs and DAOs) and only uses reverse engineering controllers for fix problems in the generated code. (the reveng file in this case).

The follow SQL code reproduces the error (MS SQL Server 2000 SP3):

Code:

CREATE TABLE dbo.ITEM (
   id_item      int NOT NULL,
   name_item   varchar(32)
)
go

ALTER TABLE dbo.ITEM ADD PRIMARY KEY CLUSTERED (id_item ASC)
go

CREATE TABLE dbo.UNIT_GROUP (
   id_UNIT_GROUP   int NOT NULL,
   name_UNIT_GROUP   varchar(32)
)
go

ALTER TABLE dbo.UNIT_GROUP ADD PRIMARY KEY CLUSTERED (id_UNIT_GROUP ASC)
go

CREATE TABLE dbo.UNIT_GROUP_ITEM (
   id_UNIT_GROUP   int NOT NULL,
   id_item      int NOT NULL,
   effective   datetime NOT NULL,
   price      decimal(8,3) NOT NULL,
)
go

ALTER TABLE dbo.UNIT_GROUP_ITEM
   ADD PRIMARY KEY CLUSTERED (id_UNIT_GROUP ASC, id_item ASC)
go

CREATE TABLE dbo.PS_IDENTITY (
   id_UNIT_GROUP   int NOT NULL,
   id_item_ps   varchar(14) NOT NULL,
   id_item      int NOT NULL,
   effective   datetime,
)
go

ALTER TABLE dbo.PS_IDENTITY
   ADD PRIMARY KEY CLUSTERED (id_UNIT_GROUP ASC, id_item_ps ASC)
go

ALTER TABLE dbo.UNIT_GROUP_ITEM
   ADD FOREIGN KEY (id_UNIT_GROUP) REFERENCES dbo.UNIT_GROUP (id_UNIT_GROUP)
go

ALTER TABLE dbo.UNIT_GROUP_ITEM
   ADD FOREIGN KEY (id_item) REFERENCES dbo.ITEM (id_item)
go

ALTER TABLE dbo.PS_IDENTITY
   ADD FOREIGN KEY (id_UNIT_GROUP) REFERENCES dbo.UNIT_GROUP (id_UNIT_GROUP)
go

ALTER TABLE dbo.PS_IDENTITY
   ADD FOREIGN KEY (id_UNIT_GROUP, id_item)
      REFERENCES dbo.UNIT_GROUP_ITEM (id_UNIT_GROUP, id_item)
go

The reverse engineering process generated POJOs and DAO (Home) classes. With this classes I try to persist an object in PS_Identity:

Code:

...
UnitGroup group = lookupUnitGroup (new Integer (0));
Item item = lookupItem (new Integer (0));
UnitGroupItem group_item = lookupUnitGroupItem (group, item);
PsIdentityId identity_id = new PsIdentityId (group.getIdUnitGroup(), "12345");
PsIdentity identity = new PsIdentity (identity_id, group, group_item);
PsIdentityHome identity_dao = new PsIdentityHome ();
identity_dao.persist(identity);

hb_tran.commit();

and this is the output:

Code:

...
Hibernate: select unitgroup0_.id_UNIT_GROUP as id1_0_0_, unitgroup0_.name_UNIT_GROUP as name2_0_0_ from bopos.dbo.UNIT_GROUP unitgroup0_ where unitgroup0_.id_UNIT_GROUP=?
Hibernate: select item0_.id_item as id1_2_0_, item0_.name_item as name2_2_0_ from bopos.dbo.ITEM item0_ where item0_.id_item=?
Hibernate: select unitgroupi0_.id_UNIT_GROUP as id1_1_0_, unitgroupi0_.id_item as id2_1_0_, unitgroupi0_.effective as effective1_0_, unitgroupi0_.price as price1_0_ from bopos.dbo.UNIT_GROUP_ITEM unitgroupi0_ where unitgroupi0_.id_UNIT_GROUP=? and unitgroupi0_.id_item=?
Hibernate: insert into bopos.dbo.UNIT_GROUP (name_UNIT_GROUP, id_UNIT_GROUP) values (?, ?)
Hibernate: insert into bopos.dbo.ITEM (name_item, id_item) values (?, ?)
Hibernate: insert into bopos.dbo.UNIT_GROUP_ITEM (effective, price, id_UNIT_GROUP, id_item) values (?, ?, ?, ?)
Hibernate: insert into bopos.dbo.PS_IDENTITY (effective, id_UNIT_GROUP, id_item_ps) values (?, ?, ?)
...
...
org.hibernate.exception.ConstraintViolationException: could not insert: [org.hibernate.test.PsIdentity]
...
Caused by: java.sql.SQLException: Cannot insert the value NULL into column 'id_item', table 'bopos.dbo.PS_IDENTITY'; column does not allow nulls. INSERT fails.
...

Note the code generated by hibernate for insert into PS_IDENTITY, it code not include the non null field id_item.

Code:

Hibernate: insert into bopos.dbo.PS_IDENTITY (effective, id_UNIT_GROUP, id_item_ps) values (?, ?, ?)

Please, any suggestion was appreciate.

Thanks.

max · **Posted:** Mon May 26, 2008 1:15 pm

you guys do realize that most code generate by hibernate tools is considered as a *starting point* that is meant to be adjusted ?

About you specifik bug it looks to me that hibernate core is failing since it should just work.