-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: How to fine tune lazy fetching on a query basis?
PostPosted: Wed Jul 20, 2005 6:03 am 
Beginner
Beginner

Joined: Fri Jul 08, 2005 8:09 am
Posts: 28
Hi,

for the cats model, let's assume that a cat not only has a one-to-many property kittens, but also a one-to-many property favfoods. What HQL / criteria can I use to load all cats for certain conditions, e.g. name starts with A, and have kittens populated for these cats while favfoods is not populated?

Right now I am doing this with a second query to retrieve the kittens only for those cats which are loaded in the first step. Which works. But then those kittens are not (automatically) connected to the cat.kittens property and if I access cat.kittens it loads them again. This results in a lot of extra SQL.

In general, is there a way how can I specify on a query level which part of the object graph is to be populated?

Also, what is required to make the cache work on full objects retrieved by an HQL query?


Hibernate version: 3.05
Name and version of the database you are using: Oracle 8-10

This is my second try, after http://forum.hibernate.org/viewtopic.php?t=944836.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 21, 2005 9:05 am 
Beginner
Beginner

Joined: Fri Jul 08, 2005 8:09 am
Posts: 28
While the HQL syntax is still a bit cloudy for me (a formal grammar or automaton would be nice) I could achieve this with a criteria query. The key is .setFetchMode(), which is documented in the Hibernate reference documentation. Something like the following would do the trick (untested):

Code:
      cats = session.createCriteria(Cat.class)
          .add(Expression.eq("name", "Fred"))
          .setFetchMode("kittens", FetchMode.JOIN)
          .list();


Can I now get the credit for the question back, please? :-)


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 21, 2005 12:27 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Here is the grammar:

http://cvs.sourceforge.net/viewcvs.py/h ... iew=markup


To the first poster: there are many, many, many examples of use of LEFT JOIN FETCH in the test dir of your hb install.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 21, 2005 1:20 pm 
Beginner
Beginner

Joined: Fri Jul 08, 2005 8:09 am
Posts: 28
Thanks for the grammar. Too bad it is not simply a BNF style grammar ;-) About the setFetchMode(). It appears as if this only works for direct properties although the JavaDoc for setFetchMode() says that the first parameter can be a dot separated property path.

Assume a table "course", with composity id ("schedule", "request"). "Request" has a one-to-many property "requestCustomers", and so on. If I now specify the required property paths like this...

Code:
    courses = session.createCriteria(Course.class)
        .add(Restrictions.ge("courseEnd", startDate))
        .add(Restrictions.lt("courseEnd", endDate))
        .setFetchMode("id.request", FetchMode.JOIN)
        .setFetchMode("id.request.requestCustomers", FetchMode.JOIN)
        .setFetchMode("id.request.courseTypeVersion.courseType.title",
            FetchMode.JOIN)
        .setMaxResults(15)
        .list();
   
    Course c = (Course) courses.get(0);
    Request r = c.getId().getRequest();
    Set rc = r.getRequestCustomers();
    CourseType ct = r.getCourseTypeVersion().getCourseType();
    String t = ct.getTitle();


...I still get extra selects when I access the data via these property paths - which is what I wanted to avoid (the list is to be looped and this would result in hundreds or thousands of separate select statements). What am I doing wrong? The SQL created by the above statement looks like this (it does not even reference the other tables, e.g. "Request"):

Code:
SELECT *
  FROM ( /* criteria query */SELECT this_.schedule_key AS schedule1_0_,
                                    this_.request_key AS request2_0_,
                                    ...
                               FROM course this_
                              WHERE this_.course_end >= ?
                                AND this_.course_end < ?)
WHERE ROWNUM <= ?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 21, 2005 1:35 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
It is a BNF.

Criteria queries don't understand <key-many-to-one>, which, btw, we don't recommend using anyway.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 21, 2005 9:15 pm 
Beginner
Beginner

Joined: Fri Jul 08, 2005 8:09 am
Posts: 28
gavin wrote:
Criteria queries don't understand <key-many-to-one>, which, btw, we don't recommend using anyway.


Then what do you recommend in such a case? The (probably common) requirement is to read the specifed property paths with as few database hits as possible...


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 21, 2005 9:36 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
http://hibernate.org/117.html#A34


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 22, 2005 6:18 am 
Beginner
Beginner

Joined: Fri Jul 08, 2005 8:09 am
Posts: 28
Thank you! I was relying on the eclipse plugin generated mappings which in two cases produced <key-many-to-one> <composite-id>s. After manually changing this as outlined in the FAQ it works now. The SQL statement is so big that even my SQL code formatter refuses to format it ;-)

I must say, however, that the FAQ text...

Quote:
The <key-many-to-one> mapping has some minor limitations in current versions of Hibernate.


...would have never led me to this clue... Thanks again.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

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.