using joins in HQL

sahu_manas · **Joined:** Tue Jul 25, 2006 8:50 am **Posts:** 8

I am trying to retrieve some fields from multiple tables using join(i.e. multiple objects) and want to set that ResultSet into a single object by using hibernate and i don't want to use mapping in the hbm files for this join.
e.g.
(select a.x,b.y from AVO left outer join BVO b on a.z = b.z and a.m = 12;)

i want to get a.x,b.y in form of an object.

How can i do it?????

pepelnm · **Posted:** Tue Aug 01, 2006 4:33 am

Code:

select
   new packageName.SomeClass(
      a.x,
      b.y
   ) 
from 
   AVO a,
   BVO b
where
   a.z = b.z 
   and 
   a.m = 12

sahu_manas · **Joined:** Tue Jul 25, 2006 8:50 am **Posts:** 8

if i use left outer join in that query...this is not working???

cupaccino · **Joined:** Thu Aug 03, 2006 2:21 am **Posts:** 5

sahu_manas wrote:

I am trying to retrieve some fields from multiple tables using join(i.e. multiple objects) and want to set that ResultSet into a single object by using hibernate and i don't want to use mapping in the hbm files for this join.
e.g.
(select a.x,b.y from AVO left outer join BVO b on a.z = b.z and a.m = 12;)

i want to get a.x,b.y in form of an object.

How can i do it?????

I got exactly the same problem now
where AVO and BVO are 2 unrelated table which by some business rule a.z and b.z can link together, and z is only a part of the composit key AVO and BVO are having, so in short I can't construct a many-to-one relationship

I have tried the above apprach and no it doesn't do left join.

AntGod · **Joined:** Fri Sep 15, 2006 3:12 pm **Posts:** 2

Hi, has this been resolved already? I'm facing the same scenario.

Thanks in advance!

cupaccino · **Joined:** Thu Aug 03, 2006 2:21 am **Posts:** 5

well no, i am still watching this topic

my temperory resolution is using the most stone age methods

select a.1, a.2, a.3 from a

for (int i=0;i<a.size();i++)
select b.1, b.2, b.3 from b where b.1=a.1
.............

when the primary key of a is a.1,a.2

and the primaty key for b is b.1,b.2,b.3

however this yield to poor performance, but thats the only solution i can think of at the moment

also it is purely memory access so it is still acceptable

Steven.Kitchen · **Posted:** Mon Sep 18, 2006 7:29 am

The only way to do *outer* joins is to specify many-to-one or one-to-many associations in the mapping files.

Gropi · **Joined:** Wed Aug 03, 2005 11:04 am **Posts:** 10

Look in the Hibernate reference in "14.5. The select clause" at this example:

Code:

select new Family(mother, mate, offspr)
from DomesticCat as mother
    join mother.mate as mate
    left join mother.kittens as offspr

Please rate this post.

cupaccino · **Joined:** Thu Aug 03, 2006 2:21 am **Posts:** 5

well I know
the post is good, but if this is the only resolution, then hibernate is crab

there are a lot of scenerios or examples in this world which need to link up 2 unrelated tables

if my "temporory" (sounds more like permanent) solution is the only way to handle this kind of relationship, then the flexibility for design of the backend is basically moving backwards!!!

gskempes · **Joined:** Mon Nov 22, 2004 11:21 am **Posts:** 42

Well, there's another solution: use Native-SQL-Queries.

However, I can live with building associations. It takes only 2 Minutes. I just have to specify the new association in the mapping file and Hibernate Synchronizer generates all necessary code.

Regards,
Georg

cupaccino · **Joined:** Thu Aug 03, 2006 2:21 am **Posts:** 5

Hi gskempes

that's an idea, however 2 Qs regarding

Quote:

I just have to specify the new association in the mapping file and Hibernate Synchronizer generates all necessary code.

I think the problem we are having now is we can;t form any sort of association between the 2 tables

Hibernate Synchronizer is another beast that I have tried, maybe I am too green in hibernate, I used that and it generates something that works but not in the standard that we want
e.g. it went into other directories, dao........etc
any tutorial on how to "CONTROL" it?

gskempes · **Joined:** Mon Nov 22, 2004 11:21 am **Posts:** 42

Hi cupaccino,

ok, you can't do any kind of association but you're not bound to foreign key relations.

About Hibernate Synchronizer (HS): There's no documentation. And yes - it's a beast. Starting with the installation: each release of it must fit a specific combination of releases (Eclipse, JDK, HS). Newer or older versions generate totally different code fragments (mappings, Java).
So I'm still stuck to version 3.1.1. I have modified the code snippets to my specific needs (Spring, code style, etc.). But this was an "trial and error" process that took a lot of time.

When I'm using an Oracle database I prefer the Hibernate Tools from JBoss for the mapping generation. In this case I'm using HS only for the Java code generation. Note that Hibernate Tools has some compatibility issues with MyEclipse, but offers a very useful HQL scratchpad.

Regards,
Georg

cupaccino · **Joined:** Thu Aug 03, 2006 2:21 am **Posts:** 5

hi gskempes,

thx for the reply, this proves to me that at least i am not the only one struggling on hibernate, especially the "code generator" which takes more time to customise than copy and paste then tune it yourself

still quite worry about the potential performance issue regarding the first problem.........

I know foreign key is no go and no good,
so except using nest for loop what else I can do?

gskempes · **Joined:** Mon Nov 22, 2004 11:21 am **Posts:** 42

Hi cupaccino,

have you tried using a Native-SQL Query (as I posted earlier)? It's the only only one-statement-alternative to a HQL-Query using an association for the left outer join. In contrast to HQL you don't need an association.

Regards,
Georg

ahmetyetgin · **Posted:** Thu Nov 16, 2006 10:12 am

Hi everyone,

I get the point that outer joins requires mapping between objects, and if none exists native SQL can be a solution.

Has anyone succeeded using outer joins with <any mappings in HQL?

Is this possible?

Thanx