-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 
  Previous topic | Next topic 
Author Message
UltimoAmore
 Post subject: 3 joins instead of a plain select
PostPosted: Wed Mar 10, 2004 5:56 am 
Beginner
Beginner

Joined: Tue Jan 27, 2004 8:33 am
Posts: 29
Having:
Code:
<class name="Instrument" table="INSTRUMENTS">
   <id name="id" column="ID" type="long">
      <generator class="native"/>
   </id>
   <property name="code" column="CODE" type="string" length="5" not-null="true"/>
   <property name="description" column="DESCRIPTION" type="string" length="50" not-null="true"/>
   <set name="artists" table="ARTISTS_INSTRUMENTS" inverse="true" lazy="true">
      <key column="INSTRUMENT_ID" />
      <many-to-many class="Artist" column="ARTIST_ID" />
   </set>
   
</class>

<class name="Artist" table="ARTISTS" lazy="true">
   <id name="id" column="ID" type="long" unsaved-value="0">
      <generator class="native"/>
   </id>
   <property name="lastName" column="LASTNAME" type="string" length="50"/>
   <property name="name" column="NAME" type="string" length="50"/>
   <property name="birthYear" column="BIRTH_YEAR" type="integer" length="11"/>
   <property name="deathYear" column="DEATH_YEAR" type="integer" length="11"/>
   <set name="albums" inverse="true" lazy="true">
      <key column="ARTIST_ID" />
      <one-to-many class="Album" />
   </set>
   <set name="instruments" table="ARTISTS_INSTRUMENTS" inverse="true" lazy="true">
      <key column="ARTIST_ID" />
      <many-to-many class="Instrument" column="INSTRUMENT_ID" />
   </set>

</class>


where ARTISTS_INSTRUMENTS is a many-to-many table with ARTISTS and INSTRUMENTS, when I execute this query (to get the list of instrument for an artist with id = 2):

Code:
select elements(artist.instruments) from Artist artist where artist.id=2


Hibernate issues these queries:

1) select instrument2_.ID as x0_0_ from ARTISTS artist0_, ARTISTS_INSTRUMENTS instrument1_, INSTRUMENTS instrument2_ where artist0_.ID=instrument1_.ARTIST_ID and instrument1_.INSTRUMENT_ID=instrument2_.ID and ((artist0_.ID=2 ))
2) select instrument0_.ID as ID0_, instrument0_.CODE as CODE0_, instrument0_.DESCRIPTION as DESCRIPT3_0_ from INSTRUMENTS instrument0_ where instrument0_.ID=?
3) select instrument0_.ID as ID0_, instrument0_.CODE as CODE0_, instrument0_.DESCRIPTION as DESCRIPT3_0_ from INSTRUMENTS instrument0_ where instrument0_.ID=?

I think 2) and 3) are right, but I cannot understand the 1). Why 3 joins? It would have been enough looking into just 1 table (ARTISTS_INSTRUMENTS). Maybe Hibernate has to look into ARTISTS because I asked "from Artist", but why the join with INSTRUMENTS?
Top
 Profile  
 
steve
 Post subject:
PostPosted: Thu Mar 11, 2004 12:06 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
The following would be more efficient in HQL:

from Instrument as i join i.artists as a where a.id = 2
Top
 Profile  
 
UltimoAmore
 Post subject:
PostPosted: Thu Mar 11, 2004 5:09 am 
Beginner
Beginner

Joined: Tue Jan 27, 2004 8:33 am
Posts: 29
steve wrote:
The following would be more efficient in HQL:

from Instrument as i join i.artists as a where a.id = 2


Ok, but why the weird 3 joins behaviour? Is this expected? What if I didn't have the relation

Quote:
<set name="artists" table="ARTISTS_INSTRUMENTS" inverse="true" lazy="true">


?
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Thu Mar 11, 2004 8:27 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
I don't see 3 joins! I see two. Which makes perfect sense for a many-to-many association. Why is this "wierd"?? Looks perfect to me...
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Thu Mar 11, 2004 8:29 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Oh I get it. You are using iterate() instead of list() or find(). (This information should have been included.) Yes, fair enough, its a little inefficient with iterate(). I'm not especially fussed about this, the other HQL is preferrable anyway.
Top
 Profile  
 
anthony
 Post subject:
PostPosted: Thu Mar 11, 2004 8:38 am 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
Gavin,
i remember that iterate() method is hitting the cache and find() & list() does not.
If we're deploying the app
- first without a cache system, list & find are better
- but if we decide later to "plug" a cache, we have to recode all the list() & find() to iterate() to take advantage of cache.

Am i right?
Is there a way to prevent this?


Thanks,
Anthony
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Thu Mar 11, 2004 8:44 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Aaaah yeah, I guess....

Note that the cache will still be used for resolving associations and loading collections. Usually, iterate() is to be used sparingly for very specific queries. Not all through the whole application....
Top
 Profile  
 
UltimoAmore
 Post subject:
PostPosted: Thu Mar 11, 2004 9:34 am 
Beginner
Beginner

Joined: Tue Jan 27, 2004 8:33 am
Posts: 29
gavin wrote:
I don't see 3 joins! I see two. Which makes perfect sense for a many-to-many association. Why is this "wierd"?? Looks perfect to me...


Yes, you are right: I meant "3 tables", that is 2 joins.
What I don't understand is the join with INSTRUMENTS: what is the purpose of that join? Hibernate has the ids from ARTISTS_INSTRUMENTS, and fetches each instrument later with the other 2 selects.

So why 2 joins?

Actually, I would be happier if I got only the select from ARTISTS_INSTRUMENTS and then the 2 selects from INSTRUMENTS (because I'm interested only in instruments, and I don't need the artist), but I think hibernate needs that join (the join with ARTIST) because of the "from Artist" clause.
Top
 Profile  
 
steve
 Post subject:
PostPosted: Thu Mar 11, 2004 9:58 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
Again, use the HQL I posted earlier. It does exactly what you are looking for.
Top
 Profile  
 
UltimoAmore
 Post subject:
PostPosted: Thu Mar 11, 2004 10:10 am 
Beginner
Beginner

Joined: Tue Jan 27, 2004 8:33 am
Posts: 29
Quote:
Again, use the HQL I posted earlier. It does exactly what you are looking for.

Ok, but I cannot look at every sql query generated by hibernate and "try and see".
Is there some documentation/wiki I can read to know how to decide which
query method is better when using hql?
Top
 Profile  
 
steve
 Post subject:
PostPosted: Thu Mar 11, 2004 10:38 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
Quote:
Ok, but I cannot look at every sql query generated by hibernate and "try and see".


I disagree and would say that expirimentation is the best way to learn any new tool, hibernate or otherwise. You can read all the docs you want, but until you actually play with it yourself all the doc discussions are simply academic. Docs are great for getting a basic level of understanding of a tool. The docs for HQL can be found at http://www.hibernate.org/hib_docs/reference/html/query-language.html

In general, HQL is relatively thin wrapper over SQL. Just think about the HQL you write and what Hibernate has to do in order to resolve this to a SQL query. In your example, you want information from the instrument table. In SQL you'd use that table as the base for your query; the same principle usually holds true in HQL.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.