Hibernate Community • View topic

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

Hi
I have 5 Tables (A,A1,B,B1,C,C1)

Table A One to many with Table A1

Table A One to many with Table B

Table B One to many with Table B1

Table B One to many with Table C

Table C One to many with Table C1

I need to Write an Hql So that I get Data From Only Table A, Table B, Table C.

Can you please help me

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Assuming that all those collections are lazy="true" in your mapping file, then this is something like what you want:

Code:

select a
from A a
join fetch a.B b
join fetch b.C c

You can't make hibernate "refuse to load" the other tables: if you call a.getA1(), then the data from A1 will be loaded.

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

Thanks.. Actually My .HBM Files contains fetch="select" even thoug i dont need from A1 but I am getting from table A1. So finally I removed fetch and excuted the query then its working fine
Thanks

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

I am sorry to Mislead you by saying its working fine.. But its not working properly.. Its not consistent at the level of collections.. Its not at all consistant.. Sometimes it has A1 data some times it has B1 data..or so... It seems its a BUG.
Thanks

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

hi
The query which u explained is not works in Hibernate Latest version.. Can you please let me know,.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Can you post the error(s) that you're getting?

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

I am not getting any Error but When I get the size of the list then it shows "More than one But It should be 1".

My query is

Code:

select a 
from A a 
join fetch a.B b 
join fetch a.A1 a1 where a.id =? 

I really appreciate your help

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

It seems its working as result set. I dont need a result set.. I need a Single Object of A. Coz I know A contains list of B and List of A1.

Is there any query? which does that..
Thanks

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

What's coming back from the query? Is it a list of many As? Do they all have the speficied ID? Are they all the same? If there's more than one A with a given ID (presumably because it's not a PK), then your query is going to return them all. If there's only one, then it's going to return only that one instance.

As a matter of interest, why do you feel that you have to explicitly eagerly fetch the associated collections in HQL? Can't you wait for them to be lazily initialized?

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

Good to see your reply. In 3.2cr2 (..its the latest version and its in development.) If you use that Jar then You can't do Lazy = false. U can only go In one direction. If u do that It will throw an error stating that cannot fetch Multiple bags. So I thought of Writting Joins.

But Joins has problem. It will return same A's and I am querying with A.id(Which Is PK) and it doesnt even include A1 and B thats what bugs me.

It's working Like Jdbc result set . U r getting more than once of same object because u mentioned that object in select ..Its duplicating the JDBC result set..

My Aim is to get the single Object Of A. this u can do By making LAZY = False (In older versions but not now.).I want to do that with joins but I can't.

I am waiting to see if hibernate can do that..other wise I can only do Using Hibernate Method Interceptor COncept.
Thanks

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You have two options:

1) Use "select distinct", HQL supports that.
2) Switch to Criteria, and use the DISTINCT_ROOT_ALIAS result transformer.

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

Yeah But I cant use joinfetch for all the collections.. if used outer join Its not getting populated?

select distinct a from A a join fetch a.b left join a.a1

then a.a1 is not populating.. I will try criteria..even in criteria if i use setfetchmode() then also its not populating.

thanks

prashantjain · **Posted:** Thu May 25, 2006 2:52 am

did u try this:

Code:

select distinct a from A a join fetch a.b left join fetch a.a1

nag · **Joined:** Tue Nov 08, 2005 1:30 pm **Posts:** 50

U cant use join fetch 2 times in your query. If u use left join or left outer or Join or inner join then you dont get data.

This is the error I am getting

Code:

org.hibernate.HibernateException: cannot simultaneously fetch multiple bags
   at org.hibernate.loader.BasicLoader.postInstantiate(BasicLoader.java:66)
   at org.hibernate.loader.hql.QueryLoader.<init>(QueryLoader.java:100)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:110)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1612)
   at com.main.TestClient.main(TestClient.java:46)

thanks

prashantjain · **Posted:** Thu May 25, 2006 9:04 am

Ok then try using criteria query :

Code:

List cats = sess.createCriteria(A.class)
    .add( Restrictions.like("id", id) )
    .setFetchMode("A1", FetchMode.EAGER)
    .setFetchMode("B", FetchMode.EAGER)
    .list();