condition on join in HQL

kve · **Joined:** Mon Dec 05, 2005 10:25 am **Posts:** 16

Hibernate version: 3.0.5

Can I rewrite this SQL to HQL?

Code:

select t1.* from table1 t1 left outer join table2 t2 on t1.id=t2.id AND t2.year=2005;

For example I can have class

Code:

class Table1
{
  Integer id;
  Set tables2;
.....//get/set
}

class Table2
{
  Integer id;
  int year;
...///get/set
}

so in HQL it can be ....

Code:

select t From Table1 t left join t.tables2 ?????? what now?

axismundi · **Joined:** Mon Aug 22, 2005 5:45 am **Posts:** 146

kve wrote:

Hibernate version: 3.0.5

Can I rewrite this SQL to HQL?

Code:

select t1.* from table1 t1 left outer join table2 t2 on t1.id=t2.id AND t2.year=2005;

For example I can have class

Code:

class Table1
{
  Integer id;
  Set tables2;
.....//get/set
}

class Table2
{
  Integer id;
  int year;
...///get/set
}

so in HQL it can be ....

Code:

select t From Table1 t left join t.tables2 ?????? what now?

you must use WHERE clause.

kve · **Joined:** Mon Dec 05, 2005 10:25 am **Posts:** 16

Quote:

you must use WHERE clause.

WHERE will not be used for join, but for select

Code:

select t1.* from table1 t1 left outer join table t2 on t1.id=t2.id AND t2.year=2005;

is not the same as

Code:

select t1.* from table1 t1 left outer join table t2 on t1.id=t2.id WHERE t2.year=2005;

axismundi · **Joined:** Mon Aug 22, 2005 5:45 am **Posts:** 146

kve wrote:

Quote:

you must use WHERE clause.

WHERE will not be used for join, but for select

Code:

select t1.* from table1 t1 left outer join table t2 on t1.id=t2.id AND t2.year=2005;

is not the same as

Code:

select t1.* from table1 t1 left outer join table t2 on t1.id=t2.id WHERE t2.year=2005;

I did not say it is the same.
Either use HQL with WHERE clause or use plain SQL as your DB supports it.

kve · **Joined:** Mon Dec 05, 2005 10:25 am **Posts:** 16

Quote:

I did not say it is the same.
Either use HQL with WHERE clause or use plain SQL as your DB supports it.

Could you rewrite the select to HQL?

I cannot use SQL (in HQL) becouse ... http://forum.hibernate.org/viewtopic.ph ... highlight=

axismundi · **Joined:** Mon Aug 22, 2005 5:45 am **Posts:** 146

kve wrote:

Quote:

I did not say it is the same.
Either use HQL with WHERE clause or use plain SQL as your DB supports it.

Could you rewrite the select to HQL?

I cannot use SQL (in HQL) becouse ... http://forum.hibernate.org/viewtopic.ph ... highlight=

I don't know your schema in detail.
But everything you need for HQL you can find here:
http://www.hibernate.org/hib_docs/v3/re ... ryhql.html

if that does not fit your needs use session.createSQLQuery() for plain sql

kve · **Joined:** Mon Dec 05, 2005 10:25 am **Posts:** 16

axismundi wrote:

I don't know your schema in detail.
But everything you need for HQL you can find here:
http://www.hibernate.org/hib_docs/v3/re ... ryhql.html

if that does not fit your needs use session.createSQLQuery() for plain sql

thanx for reference, I read all Hibernate-reference, but I cannot find nothing for my help.

now, I am trying filters in hbm.xml
<filter-def name="myFilter">
<filter-param name="myFilterParam" type="string"/>
</filter-def>

problem is:
I have Employee, and employee can have got bonus for year, so one Employee has some bonuses (one for one year)

Now I need select all Employees with their bonus which have or have not bonus for the year.

Employee has id as private key
Bonus has private key employee_id and year.

in sql is

select e.id,b.value from Employee e left outer join Bonus on e.id=b.employee_id AND b.year=2005;

in HQL ????

select e,b from Employee e left join e.bonuses b where b.year=2005;

but the selects is not the same.

franknelsen · **Joined:** Fri Nov 04, 2005 3:51 pm **Posts:** 32

Hi kve,

Try something like this:

select t1 from table1 t1 left join t.tables2 t2 with t2.year = 2005

There is a brief mention of it in the HQL reference guide. But be careful, there appears to be a bug when using it with many-to-many's. For your simple example though it should work.

From the doc:

Quote:

You may supply extra join conditions using the HQL with keyword.

from Cat as cat
left join cat.kittens as kitten
with kitten.bodyWeight > 10.0

[/quote]

franknelsen · **Joined:** Fri Nov 04, 2005 3:51 pm **Posts:** 32

NOTE: I'm on 3.1. Don't remember if the 'with' clause is available in 3.0.

kve · **Joined:** Mon Dec 05, 2005 10:25 am **Posts:** 16

franknelsen wrote:

Hi kve,

Try something like this:

select t1 from table1 t1 left join t.tables2 t2 with t2.year = 2005

There is a brief mention of it in the HQL reference guide. But be careful, there appears to be a bug when using it with many-to-many's. For your simple example though it should work.

From the doc:

Quote:

You may supply extra join conditions using the HQL with keyword.

from Cat as cat
left join cat.kittens as kitten
with kitten.bodyWeight > 10.0

[/quote]

Thanx that the one what I need
(in 3.0.5 it is not but in 3.1 it is and it is functional)

kve · **Joined:** Mon Dec 05, 2005 10:25 am **Posts:** 16

other way for this is use filter in hbm.xml file

Code:

.....
   <set name="tables2"  inverse="true" lazy="true">
         <key column="id" />
         <one-to-many class="Table1" />
         <filter name="my_filter" condition="(year=:year)"/>
   </set>
...

</class>

<filter-def name="my_filter">
  <filter-param name="year" type="integer"/>
</filter-def>

And in java code ....

Code:

...
session.enableFilter("my_filter").setParam(year,new Integer(2005));
Query q=session.createQuery("Select t1,t2 from Table1 t1 left join t1.tables2");
q.list();
session.disableFilter("my_filter");
...