Hibernate Community • View topic

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Left Join

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

BigJim

Post subject: Left Join

Posted: Sun Mar 29, 2009 3:22 pm

Newbie

Joined: Sun Mar 29, 2009 3:10 pm
Posts: 3

I have two tables:
table_1
id,
column_1
column_2
column_3
column_4

table_2
id,
table_1_key
column_1
column_b
column_c
column_d

For every row in table_1 there are 0 to n rows in table_2. The column table_1_key in table_2 is a foreign key pointing to table_1.id. I want to select the contents of both table always retrieving the contents of table_1 even if there are no rows in table_2. In regular SQL I would write the folloiwing querry:

select t1.*, t2.* from table_1 t1 left outer join table_2 t2 on t2.table_1_key = t1.id

How do I write this query in HQL?

Top

nordborg

Post subject:

Posted: Sun Mar 29, 2009 4:55 pm

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

Assuming that you have everything mapped properly, it is not very different from SQL. There are several examples in the Hibernate documentation: http://www.hibernate.org/hib_docs/v3/re ... joins.html

Top

BigJim

Post subject:

Posted: Sun Mar 29, 2009 7:49 pm

Newbie

Joined: Sun Mar 29, 2009 3:10 pm
Posts: 3

When I tried "left join table_2" Hibernate said table_1 didn't contain a table_2 which it doesn't. Hibernate did not like "left join table_2 on table_1_key = id" either. Guess I don't see what the syntax is.

Top

nordborg

Post subject:

Posted: Mon Mar 30, 2009 2:15 am

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

It is not possible to write a HQL without knowing how you have mapped the tables. Can you please post your mappings files or code with annotations?

Top

BigJim

Post subject:

Posted: Mon Mar 30, 2009 10:39 am

Newbie

Joined: Sun Mar 29, 2009 3:10 pm
Posts: 3

Code:

<hibernate-mapping package="mypackage">
  <class name="Table1" table="Table_1">
    <id name="Id" type="long"><column name="id" /><generator class="native"/></id>
    <property name="Column1" type="string"><column name="column_1" /></property>
    <property name="Column2" type="string"><column name="column_2" /></property>
    <property name="Column3" type="string"><column name="column_3" /></property>
    <property name="Column4" type="string"><column name="column_4" /></property>
  </class>
</hibernate-mapping>

<hibernate-mapping package="mypackage">
  <class name="Table2" table="Table_2">
   <id name="Id" type="long"><column name="id" /><generator class="native"/></id>
    <property name="Column1" type="string"><column name="column_1" /></property>
    <property name="Column2" type="string"><column name="column_2" /></property>
    <property name="Column3" type="string"><column name="column_3" /></property>
    <property name="Column4" type="string"><column name="column_4" /></property>
    <many-to-one name="Table1Key" class="Table1" column="table_1_key" /> 
  </class>
</hibernate-mapping>

Top

nordborg

Post subject:

Posted: Mon Mar 30, 2009 12:00 pm

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

With this mapping you can for example do:

Code:

select t1, t2 from Table2 t2 right join t2.Table1Key t1

If you want to have Table1 as the main entity and 'left join' to Table2 you need to map the inverse association in the mapping for Table1. For example, if you have something like:

Code:

<set name="Table2" lazy="true" inverse="true">
   <key column="table_1_key">
   <one-to-many class="Table2" />
</set>

you would be able to do:

Code:

select t1, t2 from Table1 t1 left join t1.Table2 t2

Top

Page 1 of 1

[ 6 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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