Hibernate Community • View topic - Lazy mode retrieving data twice.

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Lazy mode retrieving data twice.

Page 1 of 1

[ 7 posts ]

Previous topic | Next topic

Author

Message

bruno.melo

Post subject: Lazy mode retrieving data twice.

Posted: Tue Sep 02, 2008 3:46 pm

Newbie

Joined: Mon Feb 25, 2008 11:49 pm
Posts: 6

Hi everyone, hope someone could help me on this issue with Hibernate.

Hibernate-Version: 3.1.3

Name and version of the database you are using: DB2 9

The problem I'm having is the following :

* I have a User who has some Roles
* The Roles are related to entity types like : Cities, Countries, etc.

When I query for users that have roles for example :
Role : "Supervisor"
Entity : "City"
Entity Name : "New York"

I created the following Criteria :

The generated SQL (show_sql=true):

Code:

// Force the load of all entities by using FetchMode.JOIN
Criteria criteria = hbSession.createCriteria(UserHB.class, "user").
   createAlias("user.userRoles", "roles").
   createAlias("roles.businessRole", "buRole").
   createAlias("roles.businessEntityType", "buEntityType").
   add(Restrictions.eq("buEntityType.comp_id.businessEntityTypeId", new Short(businessEntityTypeId))).
   add(Restrictions.eq("roles.comp_id.businessEntityId", businessEntityId)).
   add(Restrictions.eq("user.comp_id.rptgQtr", reportingQuarter)).
   setFetchMode("roles", FetchMode.JOIN).
   setFetchMode("buRole", FetchMode.JOIN).
   setFetchMode("buEntityType", FetchMode.JOIN);

   Short[] ids = new Short[((List)businessRoleId).size()];
   int count = 0;
   for (Iterator i = ((List) businessRoleId).iterator(); i.hasNext(); count++) {
      ids[count] = new Short((String)i.next());
   }
   result = criteria.add(Restrictions.in("buRole.comp_id.businessRoleId", ids)).list();
}

This is the resulting SQL query :

Code:

Hibernate:
select this_.RPTG_QTR as RPTG1_165_3_, this_.USER_ID as USER2_165_3_, this_.EMAIL as EMAIL165_3_, this_.FIRST_NAME as FIRST4_165_3_, this_.LAST_NAME as LAST5_165_3_, this_.DIVISION as DIVISION165_3_, this_.DEPARTMENT as DEPARTMENT165_3_, this_.INVALID_DATE as INVALID8_165_3_, roles1_.RPTG_QTR as RPTG1_162_0_, roles1_.USER_ID as USER2_162_0_, roles1_.BUSINESS_ROLE_ID as BUSINESS3_162_0_, roles1_.BUSINESS_ENTITY_ID as BUSINESS4_162_0_, roles1_.RPTG_QTR_BUS_ENT_TYPE as RPTG6_162_0_, roles1_.RPTG_QTR_BUS_ROLE as RPTG7_162_0_, roles1_.RPTG_QTR_USER as RPTG8_162_0_, roles1_.BUSINESS_ENTITY_TYPE as BUSINESS5_162_0_, burole2_.RPTG_QTR as RPTG1_16_1_, burole2_.BUSINESS_ROLE_ID as BUSINESS2_16_1_, burole2_.BUSINESS_ROLE_NAME as BUSINESS3_16_1_, burole2_.RETIRED as RETIRED16_1_, buentityty3_.RPTG_QTR as RPTG1_15_2_, buentityty3_.BUSINESS_ENTITY_TYPE_ID as BUSINESS2_15_2_, buentityty3_.BUSINESS_ENTITY_TYPE_KEY as BUSINESS3_15_2_, buentityty3_.BUSINESS_ENTITY_TYPE_NAME as BUSINESS4_15_2_, buentityty3_.RETIRED as RETIRED15_2_
from
USER this_ inner join USER_ROLE roles1_ on this_.RPTG_QTR=roles1_.RPTG_QTR and this_.USER_ID=roles1_.USER_ID
inner join BUSINESS_ROLE burole2_ on roles1_.RPTG_QTR=burole2_.RPTG_QTR and roles1_.BUSINESS_ROLE_ID=burole2_.BUSINESS_ROLE_ID
inner join BUSINESS_ENTITY_TYPE buentityty3_ on roles1_.RPTG_QTR=buentityty3_.RPTG_QTR and roles1_.BUSINESS_ENTITY_TYPE=buentityty3_.BUSINESS_ENTITY_TYPE_ID
where
buentityty3_.BUSINESS_ENTITY_TYPE_ID=? and
roles1_.BUSINESS_ENTITY_ID=? and this_.RPTG_QTR=? and
burole2_.BUSINESS_ROLE_ID in (?, ?, ?)

As you can see, it is querying all fields from the tables, as expected since I'm forcing it using FetchMode.JOIN.

But when I do this piece of code just for testing, right after the criteria.list() call :

Code:

// TODO : TESTING ONLY (REMOVE LATER)
UserHB user = null;
for (Iterator i = result.iterator(); i.hasNext(); ) {
   user = (UserHB)i.next();
   user.getUserRoles().size();
}

Hibernate queries the database again, for the same data, check the SQL output that is outputed when the user.getUserRoles().size(); is executed.

Code:

Hibernate:
select userroles0_.RPTG_QTR as RPTG1_1_, userroles0_.USER_ID as USER2_1_, userroles0_.BUSINESS_ROLE_ID as BUSINESS3_1_, userroles0_.BUSINESS_ENTITY_ID as BUSINESS4_1_, userroles0_.RPTG_QTR as RPTG1_162_0_, userroles0_.USER_ID as USER2_162_0_, userroles0_.BUSINESS_ROLE_ID as BUSINESS3_162_0_, userroles0_.BUSINESS_ENTITY_ID as BUSINESS4_162_0_, userroles0_.RPTG_QTR_BUS_ENT_TYPE as RPTG6_162_0_, userroles0_.RPTG_QTR_BUS_ROLE as RPTG7_162_0_, userroles0_.RPTG_QTR_USER as RPTG8_162_0_, userroles0_.BUSINESS_ENTITY_TYPE as BUSINESS5_162_0_
from USER_ROLE userroles0_
where userroles0_.RPTG_QTR=? and userroles0_.USER_ID=?

Hibernate:
select businessro0_.RPTG_QTR as RPTG1_16_0_, 
businessro0_.BUSINESS_ROLE_ID as BUSINESS2_16_0_, businessro0_.BUSINESS_ROLE_NAME as BUSINESS3_16_0_, businessro0_.RETIRED as RETIRED16_0_
from BUSINESS_ROLE businessro0_
where businessro0_.RPTG_QTR=? and businessro0_.BUSINESS_ROLE_ID=?

I don't undestand why Hibernate is querying for the same data that is already loaded inside the UserHB bean.

Here are the mapping documents.

Mapping documents:

Mapping for UserHB.xml

Code:

    <!-- bi-directional one-to-many association to UserRoleHB -->
    <bag
        name="userRoles"
        inverse="true"
    >
        <key>
            <column name="RPTG_QTR" />
            <column name="USER_ID" />
        </key>
        <one-to-many 
            class="hibernate.UserRoleHB"
        />
    </bag>

Mapping for UserRoleHB.xml

Code:

    <many-to-one
        name="User"
   class="hibernate.UserHB"
   insert="false"
   update="false"
        lazy="false"
   >
       <column name="RPTG_QTR" />
       <column name="USER_ID" />
   </many-to-one>

Top

bruno.melo

Post subject:

Posted: Wed Sep 03, 2008 10:11 am

Newbie

Joined: Mon Feb 25, 2008 11:49 pm
Posts: 6

Hi everyone, just a bit of more information on this problem.

I just tested with the latest version of hibernate : 3.3.0.SP1

The problem persists.

I can't understand why Hibernate queries all data from all the tables needed, and doesn't save the info on the java bean, making it necessary to query the database again to get the same information it queried for a moment before.

Top

bruno.melo

Post subject:

Posted: Wed Sep 03, 2008 3:14 pm

Newbie

Joined: Mon Feb 25, 2008 11:49 pm
Posts: 6

Turned DEBUG logging on these classes :

Code:

log4j.logger.net.sf.ehcache=DEBUG, HibernateAppender
log4j.logger.org.hibernate.cache=DEBUG, HibernateAppender
log4j.logger.org.hibernate.proxy=DEBUG, HibernateAppender
log4j.logger.org.hibernate.loader=DEBUG, HibernateAppender
log4j.logger.org.hibernate.intercept=DEBUG, HibernateAppender
log4j.logger.org.hibernate.criterion=DEBUG, HibernateAppender

Noticed by looking at the logs that Hibernate is actually creating the child entities correctly when querying the table on the first time.
But it ignores that fact and queries the database again when you try to read them using the size() method.

Here are the logs I have from this issue :

This is the first criteria code :

Code:

         // Force the load of all entities by using FetchMode.JOIN
         Criteria criteria = hbSession.createCriteria(UserHB.class, "user").
            createAlias("user.userRoles", "roles").
            createAlias("roles.businessRole", "buRole").
            createAlias("roles.businessEntityType", "buEntityType").
            add(Restrictions.eq("buEntityType.comp_id.businessEntityTypeId", new Short(businessEntityTypeId))).
            add(Restrictions.eq("roles.comp_id.businessEntityId", businessEntityId)).
            add(Restrictions.eq("user.comp_id.rptgQtr", reportingQuarter)).
            add(Restrictions.eq("user.comp_id.userId", "632744897")).
            setFetchMode("roles", FetchMode.JOIN).
            setFetchMode("buRole", FetchMode.JOIN).
            setFetchMode("buEntityType", FetchMode.JOIN).
            setCacheable(false);
         
            Short[] ids = new Short[((List)businessRoleId).size()];
            int count = 0;
            for (Iterator i = ((List) businessRoleId).iterator(); i.hasNext(); count++) {
               ids[count] = new Short((String)i.next());
            }
            result = criteria.add(Restrictions.in("buRole.comp_id.businessRoleId", ids)).list();

Result SQL generated from the criteria above :

Code:

Hibernate: select this_.RPTG_QTR as RPTG1_165_3_, this_.USER_ID as USER2_165_3_, this_.EMAIL as EMAIL165_3_, this_.FIRST_NAME as FIRST4_165_3_, this_.LAST_NAME as LAST5_165_3_, this_.DIVISION as DIVISION165_3_, this_.DEPARTMENT as DEPARTMENT165_3_, this_.INVALID_DATE as INVALID8_165_3_, roles1_.RPTG_QTR as RPTG1_162_0_, roles1_.USER_ID as USER2_162_0_, roles1_.BUSINESS_ROLE_ID as BUSINESS3_162_0_, roles1_.BUSINESS_ENTITY_ID as BUSINESS4_162_0_, roles1_.RPTG_QTR_BUS_ENT_TYPE as RPTG6_162_0_, roles1_.RPTG_QTR_BUS_ROLE as RPTG7_162_0_, roles1_.RPTG_QTR_USER as RPTG8_162_0_, roles1_.BUSINESS_ENTITY_TYPE as BUSINESS5_162_0_, burole2_.RPTG_QTR as RPTG1_16_1_, burole2_.BUSINESS_ROLE_ID as BUSINESS2_16_1_, burole2_.BUSINESS_ROLE_NAME as BUSINESS3_16_1_, burole2_.RETIRED as RETIRED16_1_, buentityty3_.RPTG_QTR as RPTG1_15_2_, buentityty3_.BUSINESS_ENTITY_TYPE_ID as BUSINESS2_15_2_, buentityty3_.BUSINESS_ENTITY_TYPE_KEY as BUSINESS3_15_2_, buentityty3_.BUSINESS_ENTITY_TYPE_NAME as BUSINESS4_15_2_, buentityty3_.RETIRED as RETIRED15_2_ from USER this_ inner join USER_ROLE roles1_ on this_.RPTG_QTR=roles1_.RPTG_QTR and this_.USER_ID=roles1_.USER_ID inner join BUSINESS_ROLE burole2_ on roles1_.RPTG_QTR=burole2_.RPTG_QTR and roles1_.BUSINESS_ROLE_ID=burole2_.BUSINESS_ROLE_ID inner join BUSINESS_ENTITY_TYPE buentityty3_ on roles1_.RPTG_QTR=buentityty3_.RPTG_QTR and roles1_.BUSINESS_ENTITY_TYPE=buentityty3_.BUSINESS_ENTITY_TYPE_ID where buentityty3_.BUSINESS_ENTITY_TYPE_ID=? and roles1_.BUSINESS_ENTITY_ID=? and this_.RPTG_QTR=? and this_.USER_ID=? and burole2_.BUSINESS_ROLE_ID in (?, ?, ?)

This is the logging of this query's execution :

Code:

[DEBUG] org.hibernate.loader.Loader getRow {result row: EntityKey[hibernate.UserRoleHB#component[rptgQtr,userId,businessRoleId,businessEntityId]{userId=632744897, rptgQtr=2007 Q2, businessRoleId=1, businessEntityId=GPC300000162}], EntityKey[hibernate.BusinessRoleHB#component[rptgQtr,businessRoleId]{rptgQtr=2007 Q2, businessRoleId=1}], EntityKey[hibernate.BusinessEntityTypeHB#component[rptgQtr,businessEntityTypeId]{businessEntityTypeId=1, rptgQtr=2007 Q2}], EntityKey[hibernate.UserHB#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader getRow {result row: EntityKey[hibernate.UserRoleHB#component[rptgQtr,userId,businessRoleId,businessEntityId]{userId=632744897, rptgQtr=2007 Q2, businessRoleId=1, businessEntityId=GPC300000162}], EntityKey[hibernate.BusinessRoleHB#component[rptgQtr,businessRoleId]{rptgQtr=2007 Q2, businessRoleId=1}], EntityKey[hibernate.BusinessEntityTypeHB#component[rptgQtr,businessEntityTypeId]{businessEntityTypeId=1, rptgQtr=2007 Q2}], EntityKey[hibernate.UserHB#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader loadEntity {done entity load}
[DEBUG] org.hibernate.loader.Loader loadEntity {done entity load}

The first line on the log output reveals that Hibernate loaded the UserRoleHB entity :

Code:

[DEBUG] org.hibernate.loader.Loader getRow {result row: EntityKey[hibernate.UserRoleHB#component[rptgQtr,userId,businessRoleId,businessEntityId]{userId=632744897, rptgQtr=2007 Q2, businessRoleId=1, businessEntityId=GPC300000162}]
...
...
[DEBUG] org.hibernate.loader.Loader loadEntity {done entity load}

Okay, here comes the problem in a more detailed explanation.

This is the code for testing if the entities are cached/loaded correctly :

Code:

         UserHB user = null;
         for (Iterator i = result.iterator(); i.hasNext(); ) {
            user = (UserHB)i.next();
            user.getUserRoles().size();
         }

This is the log generated when the user.getUserRoles().size(); line is executed :

Code:

[DEBUG] org.hibernate.loader.Loader loadCollection {loading collection: [hibernate.UserHB.userRoles#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader loadCollection {loading collection: [hibernate.UserHB.userRoles#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
Hibernate: select userroles0_.RPTG_QTR as RPTG1_1_, userroles0_.USER_ID as USER2_1_, userroles0_.BUSINESS_ROLE_ID as BUSINESS3_1_, userroles0_.BUSINESS_ENTITY_ID as BUSINESS4_1_, userroles0_.RPTG_QTR as RPTG1_162_0_, userroles0_.USER_ID as USER2_162_0_, userroles0_.BUSINESS_ROLE_ID as BUSINESS3_162_0_, userroles0_.BUSINESS_ENTITY_ID as BUSINESS4_162_0_, userroles0_.RPTG_QTR_BUS_ENT_TYPE as RPTG6_162_0_, userroles0_.RPTG_QTR_BUS_ROLE as RPTG7_162_0_, userroles0_.RPTG_QTR_USER as RPTG8_162_0_, userroles0_.BUSINESS_ENTITY_TYPE as BUSINESS5_162_0_ from USER_ROLE userroles0_ where userroles0_.RPTG_QTR=? and userroles0_.USER_ID=?
[DEBUG] org.hibernate.loader.Loader handleEmptyCollections {result set contains (possibly empty) collection: [hibernate.UserHB.userRoles#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader handleEmptyCollections {result set contains (possibly empty) collection: [hibernate.UserHB.userRoles#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader getRow {result row: EntityKey[hibernate.UserRoleHB#component[rptgQtr,userId,businessRoleId,businessEntityId]{userId=632744897, rptgQtr=2007 Q2, businessRoleId=1, businessEntityId=GPC300000162}]}
[DEBUG] org.hibernate.loader.Loader getRow {result row: EntityKey[hibernate.UserRoleHB#component[rptgQtr,userId,businessRoleId,businessEntityId]{userId=632744897, rptgQtr=2007 Q2, businessRoleId=1, businessEntityId=GPC300000162}]}
[DEBUG] org.hibernate.loader.Loader readCollectionElement {found row of collection: [hibernate.UserHB.userRoles#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader readCollectionElement {found row of collection: [hibernate.UserHB.userRoles#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader getRow {result row: EntityKey[hibernate.UserRoleHB#component[rptgQtr,userId,businessRoleId,businessEntityId]{userId=632744897, rptgQtr=2007 Q2, businessRoleId=1, businessEntityId=GPC300000161}]}
[DEBUG] org.hibernate.loader.Loader getRow {result row: EntityKey[hibernate.UserRoleHB#component[rptgQtr,userId,businessRoleId,businessEntityId]{userId=632744897, rptgQtr=2007 Q2, businessRoleId=1, businessEntityId=GPC300000161}]}
[DEBUG] org.hibernate.loader.Loader readCollectionElement {found row of collection: [hibernate.UserHB.userRoles#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader readCollectionElement {found row of collection: [hibernate.UserHB.userRoles#component[rptgQtr,userId]{userId=632744897, rptgQtr=2007 Q2}]}
[DEBUG] org.hibernate.loader.Loader loadCollection {done loading collection}
[DEBUG] org.hibernate.loader.Loader loadCollection {done loading collection}

So, why is it querying the DB again for the same information it saved on the first criteria ?

From the logs I don't see any calls to caching or related methods, shouldn't we see some caching method calls on the second piece of logs ?

Should I add logging to any other hibernate package to get more info ?

Hope this info help us to solve this problem.

Thank you.

Top

Alon

Post subject:

Posted: Tue Oct 07, 2008 4:57 am

Newbie

Joined: Mon Oct 06, 2008 4:30 am
Posts: 4

Hi Bruno.

It seems that you are ahead of us in a month or so...
We also encountered this problem and while wandering the internet came across with your post.
Did you find anything about this issue? Can you share it with us?

Thanks,
(and sorry that this reply was not an answer...)
Alon

Top

bruno.melo

Post subject:

Posted: Tue Oct 07, 2008 11:32 am

Newbie

Joined: Mon Feb 25, 2008 11:49 pm
Posts: 6

Alon wrote:

Hi Alon, sure I can help.

The problem I found was that you can't use Criteria.createAlias() calls in queries you want the lazy-loading to be disabled, and the data retrieved all at once with only one query.

Also, use the setFetchMode(FetchMode.JOIN) at the Criterias you wish to disable lazy-loading.

That solved my problem, so now it does one query instead of 30-40 !!

I got some insights reading this post http://forum.hibernate.org/viewtopic.php?t=944439.

Way better.

Please rate the answer.

Thank you.

Top

Alon

Post subject:

Posted: Wed Oct 08, 2008 5:20 am

Newbie

Joined: Mon Oct 06, 2008 4:30 am
Posts: 4

bruno.melo wrote:

Hi Bruno.
Thanks for your reply.
We are not using alias so it is not the cause in our case.

I looked again at your initial post and I think that the difference between what you do and what we do is that we use a "sub criteria".

In our BpmBusinessTxGroupConfigJpaImpl (long name I admit...) we
annotate a @oneToMany bpmWebtrace field with a a lazy fetch mode and then we query:

Code:

Criteria criteria = session.createCriteria(BpmBusinessTxGroupConfigJpaImpl.class);        
criteria.setFetchMode("bpmWebtraces", org.hibernate.FetchMode.JOIN);

(this code is working and we are getting the web traces in the single query with no need for additional select to fetch the web traces.)

But there are cases that we don't need all the web traces only a small portion of them, so we are adding a sub criteria:

Code:

Criteria webTraceCriteria = criteria.createCriteria(BpmBusinessTxGroupConfigJpaImpl.bpmWebtraces);

To this sub criteria we add some restrictions in order to "filter" the retrieved web traces.

In this case I see that a join is being called on the criteria.list() but Hibernate will fetch ALL web traces using a secondary select (when we invoke the getter).
This is bad for us since there might be a lot of web traces and we wanted to fetch only part of them.

I see in the link you attached that someone considered our approach a workaround. It hinted that at least it worked for him, on the other hand I am not sure Hibernate will support such a need (eager load only for a part of the collection).

Again,
Thank for your interest.
Alon

Top

Alon

Post subject:

Posted: Sun Oct 26, 2008 11:24 am

Newbie

Joined: Mon Oct 06, 2008 4:30 am
Posts: 4

Hi,

It seems We manage to solve our problem by passing the CriteriaSpecification when creating the criteria and its sub criteria.

Alon.

Top

Page 1 of 1

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