Data retrieval from multiple tables

saikrishna · **Joined:** Mon Mar 27, 2006 3:44 pm **Posts:** 26

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Ingres 2.6
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:
I am new to hibernate. I use a legacy database.
I have the following requirement
There are four tables
Table 1
col1,col2,col3,col4,col5 (key)
col6,col7,col8.col9,col10,serkey,delkey

Table 2
serkey,column1

Table 3
delkey,column2

table4
col1,col2

I need the following sql query

select table1.col1,table1.col2,table1.col3,table1.col4,table1.col5,table1.col6,
table2.column1,table3,column2
from tabl1,table2,table3,table4
where table1.serkey = table2.serkey
and table1.delkey = table3.delkey
and table1.col1 = table4.col1
and table1.col2 = table4.col2

Can somebody tell me how to go about it?

LaLiLuna · **Posted:** Sat May 06, 2006 6:47 pm

Read the reference chapter HQL.

Best Regards Sebastian

saikrishna · **Joined:** Mon Mar 27, 2006 3:44 pm **Posts:** 26

The problem here is the complex mapping
Table 1 has the following keys key1,key2,key3 (PK)
in addition to
key4(unique but not part of primary key)

Table2 has key4 as the primary key

How do I specify the mapping for this? Is is even possible?

LaLiLuna · **Posted:** Mon May 08, 2006 4:22 pm

from Table1 t where t.youridname.key1 = ? and ...

Regards Sebastian

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

saikrishna wrote:

The problem here is the complex mapping
Table 1 has the following keys key1,key2,key3 (PK)
in addition to
key4(unique but not part of primary key)

Table2 has key4 as the primary key

How do I specify the mapping for this? Is is even possible?

Will these mappings work for you

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="learn.hibernate">   
   <class name="Table1" table="table_1" > 
      <composite-id>
         <key-property name="key1" column="key_1"/>
         <key-property name="key2" column="key_2"/>
         <key-property name="key3" column="key_3"/>
      <composite-id>
      
     <!-- WRONG WILL BE UPDATED -->
   </class>
</hibernate-mapping>

<?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="learn.hibernate">   
   <class name="Table2" table="table_2" >
      <id name="key4" column="key_4"/>


      <!-- WRONG WILL BE UPDATED -->
      <set name="tableOneSet" inverse="true">
         <key column="key_4"/>
         <one-to-many class="Table1">
      </set>
   </class>
</hibernate-mapping>

saikrishna · **Joined:** Mon Mar 27, 2006 3:44 pm **Posts:** 26

Thanks for the guidance and help. I was able to take a clue from your sample mapping file and define my mapping. Thanks once again