Fetching ALL data from a table and dependent tables

mock1010 · **Joined:** Tue Jun 08, 2004 8:24 am **Posts:** 57

I'm trying to fetch all records from a database table, as well as all required records from a subtable (implemented as a parent class with a collection of children) in order to generate a flat file of all records, but once I get out of my DAO, I get a LazyInitializationException (no session or session was closed) if I try to iterate through the children.

I figured I could get around this issue by simply iterating through all children within the DAO's fetch routine like so:

Code:

   public List getAllDeep()
   {
      List result = getHibernateTemplate().find("from ParentTable");
      for(Iterator recordIter = result.iterator(); recordIter.hasNext(); )
      {
         ParentClass record = (ParentClass)recordIter.next();
         for(Iterator childIter = record.getChildren().iterator(); childIter.hasNext();)
         {
            ChildClass cc = ((ChildClass)childIter.next());
            cc.getValue();
         }
      }
      return result;
   }

The above code works, but if I try to do the same thing again from the calling method, I get a LazyInitializationException. Shouldn't the data already be there since I manually iterated through every record, forcing the child to load?

Is there another way to do this? I can't use OpenSessionInView because it doesn't work with one of the frameworks we use.

My mapping files (simplified for illustration purposes):

Code:

<hibernate-mapping>
  <class name="ParentClass" table="ParentTable">

   <composite-id>
       <key-property name="a" type="int" column="a"/>
       <key-property name="b" type="int" column="b"/>
   </composite-id>

    <property name="name" type="string" column="name"/>

   <set name="children" inverse="true">
      <key>
         <column name="a" />
         <column name="b" />
      </key>
      <one-to-many class="ChildClass"/>
   </set>

  </class>

</hibernate-mapping>

<hibernate-mapping>
  <class name="ChildClass" table="ChildTable">

   <composite-id>
       <key-many-to-one name="parent" class="ParentClass" lazy="false">
          <column name="a"/>
          <column name="b"/>
       </key-many-to-one>
       <key-property name="value" type="string" column="value"/>
   </composite-id>

    <property name="anotherValue" type="string" column="anotherValue"/>

  </class>

</hibernate-mapping>

thorstenschaefer · **Joined:** Fri Nov 28, 2003 6:57 am **Posts:** 20

The problem is that Hibernate fetches the associations lazy (actually, its a great feature :). If you close the session (in your DAO), there is no way to query the children. Hence, you have to tell Hibernate to eagerly load the children. You can do this using the lazy attribute in your association mapping (set it to false). I think there's also a way to specify eager loading for a single HQL query, but I'm not sure about this. Just search the documentation for lazy and eager loading.

HTH,

Thorsten

mock1010 · **Joined:** Tue Jun 08, 2004 8:24 am **Posts:** 57

Ok, I tried the following:

Code:

   Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
   Criteria criteria = session.createCriteria("from ParentTable");
   criteria.setFetchMode("ParentTable.children", FetchMode.JOIN);
   criteria.setFetchMode("ParentTable.children.parent", FetchMode.JOIN);

but I still get a lazy exception once I leave the transaction.

I thought that if you manually accessed each child, then the child's parent manually inside the transaction, the data would be loaded once you left the transaction...

thorstenschaefer · **Joined:** Fri Nov 28, 2003 6:57 am **Posts:** 20

As far as I know, you only specified how Hibernate should generate the SQL statement, i.e. using a join or using subselects. If you can live with always fething the children eagerly, then use the lazy attribute in your mapping. Otherwise, take a look into the docs.

vinodhts · **Posted:** Wed Jan 03, 2007 2:23 pm

You can use Hibernate.Intialize() which will instantiate the associated Objects.

Or you can keep the session in ThreadLocal from the calling Object

Code:

public class BusinessDelegate{
    private static ThreadLocal localSession = new ThreadLocal();
     

   public static void setSession(Session session) {
        localSession.set(session);
    }

  public Object doMYJOB(){
        boolean participate = false;
        SessionFactory sessionFactory = null;
        Session session = null;
    sessionFactory =// Get your Session Factory

 if (TransactionSynchronizationManager.hasResource(sessionFactory)) {
  // Do not modify the Session: just set the participate flag.
                participate = true;
 } else {
       session = getSession(sessionFactory);
setSession(session)
       TransactionSynchronizationManager.bindResource(sessionFactory,
                        new SessionHolder(session));
       }
           
//  Do your business logic here.. Call DAO etc.. or Load the Object from Database. 
        } finally {
            if (! participate) {
                // single session mode
                if (sessionFactory != null) {
                    TransactionSynchronizationManager
                            .unbindResource(sessionFactory);
                    if (session != null) {
                        closeSession(session, sessionFactory); // Handle your close 
                    }
                }
            }
        }
        return ;
    }

mock1010 · **Joined:** Tue Jun 08, 2004 8:24 am **Posts:** 57

According to the API dox:

FetchMode EAGER = Fetch eagerly, using an outer join. Equivalent to outer-join="true".
However, it is deprecated:
Deprecated. use FetchMode.JOIN

So FetchMode.JOIN should work the same as EAGER used to, even though the dox don't specify whether it is an inner or outer join for FetchMode.JOIN.

I can't have it always fetch eagerly, because that would cause every operation in the CRUD editor to load the entire table.
I only need to load the whole table for a single function. I could easily do it with straight JDBC, but I was hoping that I wouldn't have to crack open the innards for what should be a simple task.

I'm also using Spring's AOP to manage sessions and inject them into my DAOs, so fiddling with sessions isn't going to help much, either (I already tried turning the business bean into a transaction bean, to no effect).

What I was hoping for was some kind of call that would fetch all rows, joined to foreign keys on another table, to be returned fully intact to my program so that I can dump them all out as a flat file for a legacy system.

jazir1979 · **Joined:** Wed Aug 03, 2005 2:13 am **Posts:** 16

Hi,

It is my understanding too that your above code should work, since it would initialise the child collection when you request getChildren().iterator().

In fact, I would have thought that you don't need to iterate, and a single call to getChildren().size() before returning would do the trick. Have you tried that?

Are we completely wrong?? The other posters seem to be ignoring the fact that you have iterated through the collection of children?

I also believe that FetchMode.JOIN should be doing what you want. I'm less familiar with doing this via a Criteria though. Maybe try it using HQL instead of a criteria, ie-

Code:

from Parent as parent
    left join fetch parent.children

Hibernate.initialize() is another option as mentioned above.

confused,
D.

mock1010 · **Joined:** Tue Jun 08, 2004 8:24 am **Posts:** 57

Yes, this is why I can't understand why it's not working. Is it a bug?

My code is now:

Code:

   public List getAllDeep()
   {
      Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
      Criteria criteria = session.createCriteria("from ParentTable");
      criteria.setFetchMode("ParentTable.children", FetchMode.JOIN);
      criteria.setFetchMode("ParentTable.children.parent", FetchMode.JOIN); 

      List result = criteria.list();
      Hibernate.initialize(result);
      for(Iterator recordIter = result.iterator(); recordIter.hasNext(); )
      {
         ParentClass record = (ParentClass)recordIter.next();
         Hibernate.initialize(record);
         for(Iterator childIter = record.getChildren().iterator(); childIter.hasNext();)
         {
            ChildClass cc = ((ChildClass)childIter.next());
            Hibernate.initialize(cc);
            cc.getValue();
         }
      }
      return result;
   }

I'm using every conceivable way I can think of to force it to load all links, and yet it still throws a lazy exception once I try to access the returned result :-/

mock1010 · **Joined:** Tue Jun 08, 2004 8:24 am **Posts:** 57

Bah of course I forgot to update one of the method calls, so it was calling the regular getAll() method :P

The final working code is:

Code:

   public List getAllDeep()
   {
      Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
      Criteria criteria = session.createCriteria(ParentTable.class);

      List result = criteria.list();
      Hibernate.initialize(result);
      for(Iterator recordIter = result.iterator(); recordIter.hasNext(); )
      {
         ParentClass record = (ParentClass)recordIter.next();
         Hibernate.initialize(record);
         for(Iterator childIter = record.getChildren().iterator(); childIter.hasNext();)
         {
            ChildClass cc = ((ChildClass)childIter.next());
            Hibernate.initialize(cc);
         }
      }
      return result;
   }

Things of note:
- FetchMode.JOIN has absolutely no effect whatsoever on the query. The generated SQL is identical.
- Setting hibernate.max_fetch_depth doesn't seem to have any effect, either.
- I have to call Hibernate.initialize on every class in the returned graph or it will throw a lazy exception. Simply iterating through collections is not enough.
- This generates a LOT of queries and really hammers the database. I really wish I could somehow force Hibernate to do an outer join and fill the graph in 1 query :(