Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Performing joins on related tables in a mapping?
PostPosted: Wed Jun 25, 2008 12:12 am 
Newbie

Joined: Wed Jun 18, 2008 8:41 am
Posts: 8
Hi,

We are working with a legacy framework that permits us to manipulate DB schema to a degree, but the incoming data relationships and the outgoing data relationships are generally fixed. Basically, I'd like to express a simple inner join in my mappings like this:

Code:
select tier2.*,tier1.tier1_desc from tier2, tier1 where tier2.tier1_id=tier1.tier1_id


So the above is what I am really interested in representing in my mappings. The actual mappings I'm using are as follows:

Code:
<hibernate-mapping>
    <class
        entity-name="tier1"
        table="tier1"
        node="tier1">

      <id name="tier1_id" type="long"/>
      <property name="tier1_desc" type="string"/>
    </class>

    <class
        entity-name="tier2"
        table="tier2"
        node="tier2">

      <id name="id" type="long">
         <generator class="native"/>
      </id>
      <natural-id>
         <property name="tier2_id" type="long"/>
         <many-to-one name="tier1_id" entity-name="tier1"/>
      </natural-id>
      <property name="tier2_desc" type="string"/>
    </class>

    <class
        entity-name="tier3"
        table="tier3"
        node="tier3">

      <id name="id" type="long">
         <generator class="native"/>
      </id>
      <natural-id>
         <property name="tier3_id" type="long"/>
         <many-to-one name="tier1_id" entity-name="tier1"/>
         <many-to-one name="tier2_id" entity-name="tier2"/>
      </natural-id>
      <property name="tier3_desc" type="string"/>
    </class>
</hibernate-mapping>



My goal is to express joins in my mappings so that a query on tier2 pulls the description from tier1 and a query on tier3 pulls the description from tier1 and tier2.

If this is too complex for a mapping (ie. it is not within the conceptual scope of Hibernate's mapping framework), I may be forced to resort to direct HQL queries ... but it would be nice to represent these things as objects in a mapping, because then I get that DB -> XML/object relationship. :)


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 25, 2008 4:53 am 
Beginner
Beginner

Joined: Wed Sep 21, 2005 8:18 am
Posts: 31
I am not clear what exactly you want to do. If you solution in terms of mapping only, then create a view in database which has same columns and data which you want. Now in mapping file you can map this view to your class.

_________________
amer sohail


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 25, 2008 10:59 am 
Newbie

Joined: Wed Jun 18, 2008 8:41 am
Posts: 8
Yeah -- thanks for the suggestion -- I actually thought about the view option, but was curious if it could be expressed within the mapping itself.

Playing around with these mappings this morning, I realized that I hadn't really set up the class relationships correctly. A revised set of mappings:

Code:
<hibernate-mapping>
    <class
        entity-name="tier1"
        table="tier1"
        node="tier1">

      <id name="tier1_id" type="long"/>
      <property name="tier1_desc" type="string"/>
    </class>

    <class
        entity-name="tier2"
        table="tier2"
        node="tier2">

      <composite-id>
         <key-property name="tier2_id" type="long"/>
         <key-property name="tier1_id" type="long"/>
      </composite-id>

      <property name="id" type="long" insert="false" update="false"/>
      <property name="tier2_desc" type="string"/>

      <many-to-one name="tier1" entity-name="tier1" column="tier1_id" update="false" insert="false"/>

    </class>

    <class
        entity-name="tier3"
        table="tier3"
        node="tier3">

      <composite-id>
         <key-property name="tier3_id" type="long"/>
         <key-property name="tier2_id" type="long"/>
         <key-property name="tier1_id" type="long"/>
      </composite-id>

      <property name="id" type="long" insert="false" update="false"/>
      <property name="tier3_desc" type="string"/>

      <many-to-one name="tier2" entity-name="tier1" update="false" insert="false">
          <column name="tier2_id"/>
          <column name="tier1_id"/>
      </many-to-one>

    </class>
</hibernate-mapping>


The above is a more accurate representation of the relationship between tier1, tier2 and tier3. So the first problem is the join issue, where I wanted a nice way to express the join in the mapping. The <many-to-one> references in tier2 and tier3 sort of achieve this, in that I can now access the information I need through the many-to-one relationship. I could alternatively modify the classes slightly to support views for tier2 and tier3, which would also work.

However, now that I am using composite-id's, I no longer have my nice identity column "id" expressed as a <generator>. I still need that identity column, so it is now represented as a <property>, but I do not seem to have a way of expressing (in a mapping) that it is an identity column, but not a primary key.

Right now, I am generating the tables using SQL, where the "id" column is created as:
Code:
[id] [int] IDENTITY (1, 1) NOT NULL

but I do not see a way of expressing this in a mapping when "id" is not a primary key on the table.


Any suggestions are welcome.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.