Hibernate inserting NULL values for combiner foreign keys

Question

I have the following table structures.

Region table {
`RegionId` int(11) NOT NULL AUTO_INCREMENT,
`RegionName` varchar(45) DEFAULT NULL,
PRIMARY KEY (`RegionId`) }

Client table {
`RegionId` int(11) NOT NULL,
`ClientName` varchar(45) NOT NULL,
PRIMARY KEY (`RegionId`, `ClientName`) ,
KEY `Client_RegionId_FK` (`RegionId`),
CONSTRAINT `Client_RegionId_FK` FOREIGN KEY (`RegionId`) REFERENCES `Region` (`RegionId`) ON DELETE NO ACTION ON UPDATE NO ACTION
}

Product table {
`RegionId` int(11) NOT NULL ,
`ProductId` varchar(45) NOT NULL,
`ProductName` varchar(45) DEFAULT NULL, 
PRIMARY KEY (`RegionId`, `ProductId`),
KEY `Product_RegionId_FK` (`RegionId`),
CONSTRAINT `Product_RegionId_FK` FOREIGN KEY (`RegionId`) REFERENCES `Region` (`RegionId`) ON DELETE NO ACTION ON UPDATE NO ACTION
}

Order table {
`OrderId` int(11) NOT NULL AUTO_INCREMENT,
`RegionId` int(11) DEFAULT NULL,
  `ClientName` varchar(45) DEFAULT NULL,
`ProductId` int(11) DEFAULT NULL,
`OrderDate` datetime DEFAULT NULL,
 `OrderValue` int(11) DEFAULT NULL,
PRIMARY KEY (`OrderId`) ,
 KEY `Order_RegionId_FK` (`RegionId`),
KEY `Order_ClientName_FK` (`RegionId`,`ClientName`),
KEY `Order_ProductId_FK` (`RegionId`,`ProductId`),
CONSTRAINT `Order_RegionId_FK` FOREIGN KEY (`RegionId`) REFERENCES `Region` (`RegionId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Order_ClientName_FK` FOREIGN KEY (`RegionId`, `ClientName`) REFERENCES `Client` (`RegionId`, `ClientName`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Order_ProductId_FK` FOREIGN KEY (`RegionId`, `ProductId`) REFERENCES `Product` (`RegionId`, `ProductId`) ON DELETE NO ACTION ON UPDATE NO ACTION
}

The order table has 3 different foreign key constraints that reference RegionId. One is directly and the other 2 thru Client and Product.

Below is the hibernate mapping file for the Order table

<hibernate-mapping>
<class name="com.test.model.Order" table="Order">
    <id name="orderId" type="java.lang.Integer">
        <column name="OrderId" />
        <generator class="identity" />
    </id>
    <many-to-one name="Region" class="com.test.model.Region" fetch="select">
        <column name="RegionId" />
    </many-to-one>
    <many-to-one name="product" class="com.test.model.Product" update="false" insert="false" fetch="select">
        <column name="RegionId" />
        <column name="ProductId" />
    </many-to-one>
    <many-to-one name="client" class="com.test.model.Client" update="false" insert="false" fetch="select">
        <column name="RegionId" />
        <column name="ClientName" length="45" />
    </many-to-one>
    <property name="orderDate" type="timestamp">
        <column name="OrderDate" length="19" />
    </property>
    <property name="orderValue" type="java.lang.Integer">
        <column name="OrderValue"  />
    </property>
</class>
</hibernate-mapping>

The issue I have is that, when I insert a new Order in to the DB NULL is the inserted for clientName and ProductId. I have verified that I am supplying the Client and Product objects to the Order Model object before persist. But for some reason NULLs are inserted. I don't have any problem with Region information, that is added correctly.

I tried to debug and check the SQL of Hibernate, and I see that hibernate is trying to read the Client and Product tables before the insert, but surprisingly the clientName and productId are not part of the insert order statement.

Below is the out put of Hibernate sql

Hibernate: 
/* get current state com.test.model.Product */ select
    product_.RegionId,
    product_.ProductId,
    product_.ProductName as ProductName12_
from
    testdb.product product_ 
where
    product_.RegionId=? 
    and product_.ProductId=?
Hibernate: 
/* get current state com.test.model.Client */ select
    client_.RegionId,
    client_.ClientName 
from
    testdb.client client_ 
where
    client_.RegionId=? 
    and client_.ClientName=?
Hibernate: 
/* insert com.test.model.Order
    */ insert 
    into
        testdb.order
        (RegionId, OrderDate, OrderValue) 
    values
        (?, ?, ?)

Please let me know what could be wrong and suggestions to fix the problem.

I have the same problem, have you found the solution to this issue? Thanks — aumanets, Mar 17 '13 at 20:27
@aumanets Based on lot of google search, I came to the conclusion that this is a hibernate limitation at this point, hence I redesigned by DB to eliminate the combined PKs. — Murali D, Mar 18 '13 at 14:35
I don't that it is a good approach to redesign DB because of the Hiberante framework. Probably you have introduced redundancy to you data, I assume you have added an auto increment column instead of the composite key. If you solved this in a different manner, could you please provide some hints? Thanks — aumanets, Mar 19 '13 at 09:27
Yes. I introduced an auto increment column and made that PK. I thought that hibernate is probably right to insist. I am confident that, thru program logic, I can ensure that the RegionId is the same both for Region and Client, but at the DB level there is no way to ensure. Hence decided to decouple that by creating a table with clientId (auto_increment) for Client and add a relationTable between Region and Client. — Murali D, Mar 19 '13 at 09:40

Hibernate inserting NULL values for combiner foreign keys

0 Answers0