Join multiple tables with foreign keys in primary table

frosi · **Joined:** Tue Apr 17, 2007 4:22 am **Posts:** 1

Hi All,
I have a problem mapping a legacy datamodel to a classmodel (which I am also not allowed to change). The issue is this:

I have three tables. The main table here is PRIVILEGE, which has two foreign keys pointing to two other tables, PRIVILEGE_TYPE and BUSINESS_OBJECT. On the Java side, I need to map fields from these three different tables to one class (PrivilegeVO, also shown).

These are the tables (FK/PK constraints omitted for brevity):

Code:

CREATE TABLE "PRIVILEGE" 
(
  "SYSTEM_ID"    INTEGER NOT NULL,
  "BUSINESS_OBJECT_SYSTEMID"    INTEGER NOT NULL,
  "PRIVILEGE_TYPE_SYSTEMID"    INTEGER NOT NULL);

CREATE TABLE "PRIVILEGE_TYPE" 
(
  "SYSTEM_ID"    INTEGER NOT NULL,
  "NAME"    VARCHAR(20),
  "DESCRIPTION"    VARCHAR(100));

CREATE TABLE "BUSINESS_OBJECT" 
(
  "SYSTEM_ID"    INTEGER NOT NULL,
  "NAME"    VARCHAR(50),
  "DESCRIPTION"    VARCHAR(100));

public class PrivilegeVO {
   private int systemId;

   private String privilegeType;   // should contain the name from the 
                  // privilege type table

   private String businessObject;   // should contain the name from the
                  // business object table 

   /* getters and setters omitted */
}

Now, I would like Hibernate to do something like this:

Code:

select priv.SYSTEM_ID, priv_type.NAME, bo.NAME from PRIVILEGE priv
join PRIVILEGE_TYPE priv_type on priv.PRIVILEGE_TYPE_SYSTEMID = priv_type.SYSTEM_ID
join BUSINESS_OBJECT bo on priv.BUSINESS_OBJECT_SYSTEMID = bo.SYSTEM_ID

And then map the NAME columns to the String fields in the PrivilegeVO class.

Preferably with a basic mapping using join:

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 package="com.tntlb.tms.model.privilege.vo">
   <class name="PrivilegeVO" table="PRIVILEGE">
      <id name="systemId" column="SYSTEM_ID">
         <generator class="com.tntlb.core.model.HibernateIdGenerator" />
      </id>
      
      <join table="PRIVILEGE_TYPE">
         <key column="SYSTEM_ID" />
         <property name="privilegeType" column="NAME" />
      </join>
   </class>
</hibernate-mapping>

I appear not to be able to join the tables based on foreign keys in the primary table (PRIVILEGE in this case). Does anyone know if this mapping is possible using standard mapping features (without resorting to formula / subselect / DB-level views / hack)?

I am using Hibernate version 3.2.1.ga. My database is Firebird 1.5.4.

Any suggestions? Thanks a lot in advance!

kees-van-dieren · **Posted:** Fri Apr 20, 2007 1:54 pm

Hi Friso,

What you can do is add two many-to-one relations to privilegeVO.

Mapping code for this looks like this:

Code:

<!-- basic mappings for PRIVILEGE_TYPE, BUSINESS_OBJECT tables -->


   <many-to-one name="shipmentItemVO" unique-key="TMS_PI_UK"
         class="com.tntlb.tms.model.shipmentorder.vo.ShipmentItemVO"
         column="SHIPMENT_ITEM_SYSTEMID" lazy="false" cascade="delete" not-null="true" />


<class name="PrivilegeVO" table="PRIVILEGE">
      <id name="systemId" column="SYSTEM_ID">
         <generator class="com.tntlb.core.model.HibernateIdGenerator" />
      </id> 

   <many-to-one name="businessObjectVO" class="your.package.to.BusinessObjectVO"
         column="BUSINESS_OBJECT_SYSTEMID" lazy="false" not-null="true" />

   <many-to-one name="privilegeTypeVO" class="your.package.to.PrivilegeTypeVO"
         column="PRIVILEGE_TYPE_SYSTEMID" lazy="false" not-null="true" />

</class>

In PrivilegeVO method String getPrivilegeType() can be added which delegates to getPrivilegeTypeVO().getName() (same can be done for Business Object)

Ananasi · **Posted:** Fri Apr 20, 2007 4:22 pm

I agree with the above poster, and your POJO should look like:

Code:

public class PrivilegeVO {
   private int systemId;

   private PrivilegeTypeVO privilegeTypeVO;

   private BusinessObjectVO businessObjectVO;

   /* getters and setters omitted */
} 

This way, the mappings reflect your raw data. This will be much more flexible in the long run.

Keep the code from your original PrivilegeVO, and rename the class PrivilegeDTO, with a constructor that initializes the fields. Then use projections in your queries to populate your list of PrivilegeDTO objects thusly:

Code:

select new PrivilegeDTO(priv.systemId, priv_type.name, bo.name) 
from Privilege priv
  left join PrivilegeType priv_type
  left join BusinessObject bo

I think you'll be happier in the long run with that type of hierarchy.