Hibernate association via foreign key

developer · **Joined:** Thu Mar 13, 2008 12:37 pm **Posts:** 8

I am trying to create a one-to-many association between two tables (no join table), where the column used to link the two tables is not primary key for the first table, and is only part of the primary key for the second table. I always get exception (refer below for details). Could someone please help me with the Hibernate mapping file?

Details:

* Table structures in psedu-SQL
Table 1: SOLUTION_STAFFING
CREATE TABLE SOLUTION_STAFFING (
SOLUTION_ID BIGINT NOT NULL REFERENCES SOLUTION (SOLUTION_ID),
SKILL_ID INTEGER NOT NULL,
AMOUNT INTEGER,
PRIMARY KEY (SOLUTION_ID, SKILL_ID)
)

Table 2: ITEM_MONTHLY_REPORT
CREATE TABLE ITEM_MONTHLY_REPORT (
ITEM_ID BIGINT NOT NULL REFERENCES ITEM(ITEM_ID),
YEAR SMALLINT,
MTH SMALLINT,
ITEM_SOL_ID BIGINT REFERENCES SOLUTION(SOLUTION_ID),
PRIMARY KEY(ITEM_ID, YEAR, MTH)
)

* Hibernate mapping file
In the mapping file for ITEM_MONTH_REPORT, I am trying to include a set to hold solution staffing information, using ITEM_SOL_ID to look up in the SOLUTION_STAFFING table. Not sure how I can do it. If I include the following in my mapping file (I know it is not correct because of the usage of key, but not sure how to do it):
<set name="solutionStaffingSet" lazy="true" >
<key column="SOLUTION_ID" not-null="true" />
<many-to-many class="SolutionStaffing" unique="true" property-ref="ITEM_SOLUTION_ID"/>
</set>

I get the following exception:
Caused by: org.hibernate.MappingException: Foreign key (FKCBB1F9FA3786535D:solutionStaffingSet [SOLUTION_ID])) must have same number of columns as the referenced primary key (ITEM_MONTHLY_REPORT [ITEM_ID,YEAR,MTH])

Could someone help me with the mapping file? How I can map a foreign key that is not part of the primary key? Thanks in advance.

developer · **Joined:** Thu Mar 13, 2008 12:37 pm **Posts:** 8

Can anybody help please?

farzad · **Posted:** Fri Mar 14, 2008 11:50 am

developer wrote:

Can anybody help please?

Hmmm, I think you mixed things up a bit but I am not sure if I understand you properly. If you want to have a one-to-many from Item to solutions the you might have to try this:

Code:

<set name="solutionStaffingSet" lazy="true" >
  <key column="SOLUTION_ID " not-null="true" property-ref="solutionId" />
<one-to-many class="SolutionStaffing"/>
</set> 

  <property name="solutionId" column="ITEM_SOL_ID"/>

Give this a try. However, you might get the same exception. If so then you will need to look into writing a custom loader.

Farzad-

ferozz · **Posted:** Fri Mar 14, 2008 12:53 pm

i would recommend u to change tables so that u would have one column primary key like auto_increment, serial or sequncer ... and from your current composite primary key u could make alternative key.

from my experience, it much more easier to work by this way rather then create composite primary keys.

here is some citation from hibernate reference manual:

Quote:

Chapter 24. Best Practices

Declare identifier properties on persistent classes.

Hibernate makes identifier properties optional. There are all sorts of reasons why you should use them. We recommend that identifiers be 'synthetic' (generated, with no business meaning).

Identify natural keys.

Identify natural keys for all entities, and map them using <natural-id>. Implement equals() and hashCode() to compare the properties that make up the natural key.

developer · **Joined:** Thu Mar 13, 2008 12:37 pm **Posts:** 8

Thanks for your reply, farzad and ferozz.

Farzad, I tried with your mapping, I got a different exception as below
Caused by: org.hibernate.MappingException: Repeated column in mapping for entity: com.ibm.wfm.its.data.SolutionStaffing column: SOLUTION_ID (should be mapped with insert="false" update="false")

Ferozz, unfortunately, I can not change the existing table structure, because we have an application running on the current db structure.

Basically, what I try to do is to join two tables with a given column. Is it possible to do it in Hibernate mapping file?

Another reason that I am trying to do this in the Hibernate mapping file, instead of using HQL or SQL is that a complicated Hibernate DetachedCriteria was already built to look up the qualified ITEM_MONTHLY_REPORT records, if I can do the join in the mapping file, the set of solutionStaffings will be retrieved as part of the query. Otherwise, I have to compose the HQL or SQL from scratch. Or am I right? Do I have to compose the HQL or SQL from scratch? Can I reuse the search criteria in the DetachedCriteria class?

Thanks in advance for any help.

farzad · **Posted:** Mon Mar 17, 2008 2:39 pm

developer wrote:

Caused by: org.hibernate.MappingException: Repeated column in mapping for entity: com.ibm.wfm.its.data.SolutionStaffing column: SOLUTION_ID (should be mapped with insert="false" update="false")

hmm, you shouldn't get this at least. Show me the whole mapping for both classes. Please use a code block so that it is more readable. Thanks.

Farzad-

waswani · **Posted:** Mon Mar 17, 2008 3:41 pm

You need to declare the below mapping in the Item.hbm.xml (mapping file for Item entity) with some correction.

<set name="solutionStaffingSet" lazy="true" >
<key column="SOLUTION_ID" not-null="true" />
<many-to-many class="SolutionStaffing" unique="true" property-ref="ITEM_SOLUTION_ID"/>
</set>

The correction is ----------------------

<set name="solutionStaffingSet" lazy="true" table="item_monthly_report">
<key column="item_id" />
<many-to-many class="SolutionStaffing" unique="true" property-ref="ITEM_SOLUTION_ID"/>
</set>

item_monthly_report is the association table between Item and SolutionStaffing it seems.

Try it out.

developer · **Joined:** Thu Mar 13, 2008 12:37 pm **Posts:** 8

Farzad,

Thanks for your reply. Attached below is the table structure (simplified from the real application) and the hibernate mapping files. I still get the exception:

Code:

Caused by: org.hibernate.MappingException: Repeated column in mapping for entity: SolutionStaffing column: SOLUTION_ID (should be mapped with insert="false" update="false")

Table structure (Note that column name and type may be slightly different from the original post. In the real application, the ITEM_MONTHLY_REPORT is a view, and has many more columns):

Code:

CREATE TABLE TEMP.SOLUTION_STAFFING (
SOLUTION_ID BIGINT NOT NULL REFERENCES SOLUTION (SOLUTION_ID),
JR_SS_ID INTEGER NOT NULL,
RATIO   DOUBLE,
PRIMARY KEY (SOLUTION_ID, JR_SS_ID)
);

CREATE TABLE TEMP.ITEM_MONTHLY_REPORT (
ITEM_ID BIGINT NOT NULL REFERENCES ITEM(ITEM_ID),
YEAR SMALLINT NOT NULL,
MTH SMALLINT NOT NULL,
ITEM_SOL_ID BIGINT REFERENCES SOLUTION(SOLUTION_ID),
PRIMARY KEY(ITEM_ID, YEAR, MTH)
);

Hibernate mapping file for ITEM_MONTHLY_REPORT:

Code:

<?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>

   <class name="ItemMonthlyReport" table="ITEM_MONTHLY_REPORT" mutable="false" dynamic-insert="false" dynamic-update="false" schema="TEMP">
   <!-- common starts -->
       <composite-id name="id" class="IdMonth">
            <key-property name="ItemId" type="long">
                <column name="ITEM_ID" />
            </key-property>
            <key-property name="mth" type="int">
                <column name="MONTH" />
            </key-property>
            <key-property name="year" type="int">
                <column name="YEAR" />
            </key-property>
        </composite-id>
        
        <property name="itemSolutionId" type="java.lang.Long">
            <column name="ITEM_SOL_ID" />
        </property>

      <set name="solutionStaffingSet" lazy="true" >
               <key column="SOLUTION_ID" not-null="true" property-ref="itemSolutionId" />
               <one-to-many class="SolutionStaffing"  />
          </set>                       
       
</class>
</hibernate-mapping>

Hibernate mapping file for Solution_Staffing:

Code:

<?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>
    <class name="SolutionStaffing" table="SOLUTION_STAFFING">
        <composite-id name="id" class="SolutionStaffingId">
            <key-property name="solutionId" type="long">
                <column name="SOLUTION_ID" />
            </key-property>
            <key-property name="jrSsId" type="int">
                <column name="JR_SS_ID" />
            </key-property>
        </composite-id>
        <property name="ratio" type="java.lang.Double">
            <column name="RATIO" precision="53" scale="0" />
        </property>
    </class>
</hibernate-mapping>

farzad · **Posted:** Tue Mar 18, 2008 11:34 am

You should have duplicate in your code. I don't see it here. I did a similar test and it works fine for me:

Code:

<hibernate-mapping>
    <class name="test.model.data.Entity1" table="ENT1" lazy="true">

        <id name="id" type="long" unsaved-value="null">
            <column name="ID"/>
            <generator class="native"/>
        </id>

        <property name="name" type="string" lazy="true" column="Name"/>
        <property name="otherId" type="long" column="THE_OTHER_ID"/>

        <set name="entity2">
            <key column="THE_OTHER_ID" property-ref="otherId"/>
            <one-to-many class="test.model.data.Entity2" />
        </set>
    </class>

</hibernate-mapping>

Code:

<hibernate-mapping>
    <class name="test.model.data.Entity2" table="ENT2" lazy="true">

        <composite-id  mapped="true">
            <key-property name="id" column="THE_OTHER_ID"/>
            <key-property name="key" column="EntKey"/>
        </composite-id>

        <property name="name" type="string" column="Name"/>
    </class>

</hibernate-mapping>

and the Table structure is:

Code:

ENT1
------------------------------------------------
[ID] [int] IDENTITY(1,1) NOT NULL,   (<-- PK)
[THE_OTHER_ID] [int] NULL,
[Name] [varchar](50) NULL


ENT2
-----------------------------------------------
[THE_OTHER_ID] [int] NOT NULL,     
[EntKey] [varchar](50) NOT NULL,    
[Name] [nchar](10) NULL

(THE_OTHER_ID, EntKey) <-- PK

Tell me if you see any difference.

Farzad-

developer · **Joined:** Thu Mar 13, 2008 12:37 pm **Posts:** 8

Farzad,

Thanks again for your reply.

I tried the exact mapping you have, and it seems fine. However, if I insert some sample values into the table ENT1 and ENT2, and if I try to find ENT1 by ID column, the returned records are not correct. For example, if I have sample records as below:

Code:

insert into TEMP.ENT1 values
(101, 201, 'ent1-001'),
(102, 202, 'ent1-002');

insert into TEMP.ENT2 values
(202, 'ENTKEY-001', 'ent2_001'),
(202, 'ENTKEY-002', 'ent2_002'),
(202, 'ENTKEY-003', 'ent2_003');

When I looks up ENT1 by ID 102, I got 3 records (identical) back, and each has 3 ENT2 in the set. Can you please try this out and see whether you get the same problem?

Here are the codes that I retrieve the records for ENT1:

Code:

   public List getEntity1List(Integer id, boolean loadSet) {
      
      DetachedCriteria criteria = DetachedCriteria.forClass(Entity1.class);
      
      if (id != null) 
         criteria.add(Expression.eq("id", id));
      
      if (loadSet)
         criteria.setFetchMode("entity2", FetchMode.JOIN);

      List list = null;
      
      HibernateTemplate template = hibernateDaoHelper.getHibernateTemplate();
      if (template == null) 
         debug("template is null");
      else list = template.findByCriteria(criteria);

      return list;
   }

Thanks for your help.

farzad · **Posted:** Thu Mar 20, 2008 4:43 pm

You do need to tell hibernate to give you distinct entity1s. This is automatically done when you look for Entity1 by load(...) method but in a sql query hibernate does not really know what your criteria is so it has no way to know Entity1 instances need to be unique. It would be better if hibernate was more intelligent but hey still it does a lot of work for us :)

Farzad-

developer · **Joined:** Thu Mar 13, 2008 12:37 pm **Posts:** 8

Farzad,

Thanks again for your reply. The mappings in your sample where the first table has one column as primary key work as expected. However, when I tried to apply the similar mapping to my original tables where both tables have composite primary keys, I still see problems. I got the same exception I mentioned in an earlier post when I tried to fetch the records, and insert and update are not attributes of one-to-many tag:

Code:

Caused by: org.hibernate.MappingException: Repeated column in mapping for entity: test.model.data.SolutionStaffing column: SOLUTION_ID (should be mapped with insert="false" update="false")

Sample mapping files:
Mapping file for ITEM_MONTHLY_REPORT table

Code:

<?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>

   <class name="test.model.data.ItemMonthlyReport" table="ITEM_MONTHLY_REPORT" mutable="false" dynamic-insert="false" dynamic-update="false" schema="TEMP">
   <!-- common starts -->
       <composite-id name="id" class="test.model.data.ItemMthId">
            <key-property name="itemId" type="long">
                <column name="ITEM_ID" />
            </key-property>
            <key-property name="mth" type="int">
                <column name="MTH" />
            </key-property>
            <key-property name="year" type="int">
                <column name="YEAR" />
            </key-property>
        </composite-id>
        
        <property name="itemSolutionId" type="java.lang.Long">
            <column name="ITEM_SOL_ID" />
        </property>

      <set name="solutionStaffingSet" lazy="true" >
               <key column="SOLUTION_ID" not-null="true" property-ref="itemSolutionId" />
               <one-to-many class="test.model.data.SolutionStaffing"  />
          </set>    
          
</class>
</hibernate-mapping>

Mapping file for Solution_Staffing table:

Code:

<?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>
    <class name="test.model.data.SolutionStaffing" table="SOLUTION_STAFFING" schema="TEMP">
        <composite-id name="id" class="test.model.data.SolutionStaffingId">
            <key-property name="solutionId" type="long">
                <column name="SOLUTION_ID" />
            </key-property>
            <key-property name="jrSsId" type="int">
                <column name="JR_SS_ID" />
            </key-property>
        </composite-id>
       <property name="ratio" type="java.lang.Double">
            <column name="RATIO" precision="53" scale="0" />
        </property>
    </class>
</hibernate-mapping>

farzad · **Posted:** Mon Mar 31, 2008 1:32 pm

Which hibernate version are you using? I am getting a different error, and I don't think you should get repeated column error at all. My error more looks like I have screwed up something very obvious or I am running into a hibernate bug.

Farzad-

developer · **Joined:** Thu Mar 13, 2008 12:37 pm **Posts:** 8

I am using Hibernate 3.2.4.