Step to use joins in Hibernate

chiku_78 · **Joined:** Wed Aug 24, 2005 12:07 pm **Posts:** 17

Hi All,
I am using Hibernate 3.0 in one of the sample application. I want to retrieve data from two tables implementing joins.I tried a lot to retrieve the desired result but it is giving wrong results. There are two tables defectdetails and lookupdetails and the respective POJOs are Defect and Lookup.The table structure in MYSQL is something like this:

Code:

lookupdetails:
Code         bigint(20) (Primary key)
Category                   varchar(20)
Description      varchar(30)

Sample data in lookupdetails table:

Code   Category   Description
3      priority        Low
4      priority        Medium
5      priority        High

defectdetails:
DefectID                   bigint(20) Primary key
PriorityCode         bigint(20)
AssignedTo        varchar(50)
Summary           varchar(150)
Description        text

Sample data in defectdetails table:
DefectID  PriorityCode        AssignedTo    Summary        Description
1           3                  Marsh        HTMl issues   HTML issues
2           3                  David        bla bla bla   bla bla bla
3           5                  Geoff         bla bla bla   bla bla bla
4           4                  Marsh        bla bla bla   bla bla bla

Now I want to have inner join between two tables on defectdetails.PriorityCode = lookupdetails.Code.

The desired result should be something like this:
DefectID  Priority      AssignedTo       Summary       Description
1        Low             Marsh         HTMl issues   HTML issues
2        Low             David         bla bla bla   bla bla bla
3        High            Geoff          bla bla bla   bla bla bla
4        Medium          Marsh        bla bla bla      bla bla bla

Can anyone please design the entry that are suppose to be there in defect.hbm.xml and lookup.hbm.xml apart from normal property entries in order to implement this join. Also mention the hibernate query.
All suggestions are welcome.
Thanx in Advance,
Chirag.

andresgr · **Posted:** Wed Nov 29, 2006 5:44 am

lookup.hbm.xml:

<hibernate-mapping package="...">

<class name="Lookup">
<id name="Code">
<generator class="native"/>
</id>
(...)
<set name="defects" inverse="true" cascade="all">
<key column="id_lookup"/>
<one-to-many class="Defect" />
</set>
</class>
</hibernate-mapping>

defect.hbm.xml:

<hibernate-mapping package="...">

<class name="Defect">
<id name="defectID">
<generator class="native"/>
</id>
(...)
<many-to-one name="lookup" not-null="true" column="id_lookup"/>
</class>

</hibernate-mapping>

Hibernate query-> Try this:

createQuery("select defect.defectID, lookup.description, defect.assignedto, defect.summary, defect.description from Defect defect join defect.lookup lookup")

Any way,, my suggestion is to read the reference documentation about joins etc.

persist_coder · **Posted:** Wed Nov 29, 2006 9:54 am

Hi,

You can use like this also,

<class name="LookupDetails" table="lookupdetails">
<id name='id' column='Code' type='long'>
<generator class='assigned'>
</generator>
</id>
<property name="Category" />
<property name="Description" />
<set name="defect" fetch="join">
<key column="Prioritycode" />
<one-to-many class="DefectDetails"/>
</set>
</class>

<class name="DefectDetails" table="defectdetails">
<id name='id' column='DefectID' type='long'>
<generator class='assigned'>
</generator>
</id>
<property name="Prioritycode" />
<property name="Assignedto" />
<property name="Summary" />
<property name="Description" />
</class>

and query is like
Query q = session.createQuery("select defect.id, lookup.Description, defect.Assignedto, defect.Summary, "
+ "defect.Description from LookupDetails lookup join lookup.defect defect");

and get like q.list();