Problem with lazy fetching (left outer join)

sharinganJounin · **Joined:** Tue Apr 12, 2005 11:25 am **Posts:** 2

Hi, I'm learning hibernate because I need it for a project in my work. I have the next problem. Currently in my work we have a database in oracle 9 and they asked me to progam a persistence layer using hibernate. I made some tests before and I ran into this:

I have 4 tables Offering, Lot, User and Bid. The relationship is:
- Offering has one or more Lots and one or more Users
- User has one or more Bids
- Lot has one or more Bids

As you can see User and Lot has a relationship many-to-many but the link table is Bid and has more data so I can't use <many-to-many>

When I try to navigate (inside a session): Offering -> Lot -> Bid I get an exception (Failed to lazily initialize a collection) with another exception (ORA-00933: SQL command not properly ended). I can make the navigation: Offering -> Lot and Offering -> Usuario but I can't make it to Bid =S

Hibernate version: 2.1.7

The mappings are here:
Mapping documents:

Code:

---Offering.hbm.xml
<hibernate-mapping package="com.rmmi.hibernate">

   <class name="com.rmmi.hibernate.Offering" table="OFFERING">
   
      <meta attribute="extra-import">java.util.ArrayList</meta>
      
      <id name="offeringId" column="OFFERINGID" type="java.lang.Long">
         <generator class="sequence" />
      </id>
   
      <property name="nombreSubasta" column="NOMBRESUBASTA" type="java.lang.String" />
      <property name="lenguage" column="LENGUAGE" type="java.lang.String" />
      
      <bag name="users" cascade="save-update" inverse="true" lazy="true">
         <key column="OFFERINGID" />
         <one-to-many class="com.rmmi.hibernate.Usuario" />
      </bag>
      
      <bag name="lots" cascade="save-update" inverse="true" lazy="true">
         <key column="OFFERINGID" />
         <one-to-many class="com.rmmi.hibernate.Lot" />
      </bag>
      
   </class>

</hibernate-mapping>


---Lot.hbm.xml
<hibernate-mapping package="com.rmmi.hibernate">
   <class name="com.rmmi.hibernate.Lot" table="LOT">
      <id name="lotId" column="LOTID" type="java.lang.Long">
         <generator class="sequence" />
      </id>
      <property name="item" column="ITEM" type="java.lang.String" />
      <property name="units" column="UNITS" type="java.lang.String" />
      <many-to-one 
         name="offering" 
         column="OFFERINGID" 
         class="com.rmmi.hibernate.Offering" 
         cascade="none" />
      <bag name="bids" cascade="save-update" inverse="true" lazy="true">
         <key column="LOTID" />
         <one-to-many class="com.rmmi.hibernate.Bid" />
      </bag>
   </class>
</hibernate-mapping>

---Usuario.hbm.xml
<hibernate-mapping package="com.rmmi.hibernate">
   <class name="com.rmmi.hibernate.Usuario" table="USUARIO">
      <id name="userId" column="USERID" type="java.lang.Long">
         <generator class="sequence" />
      </id>
      <property name="userName" column="USARNAME" type="java.lang.String" />
      <property name="password" column="PASSWORD" type="java.lang.String" />
      <many-to-one 
         name="offering" 
         column="OFFERINGID" 
         class="com.rmmi.hibernate.Offering" 
         cascade="none" />
      <bag name="bids" cascade="save-update" inverse="true" lazy="true">
         <key column="USERID" />
         <one-to-many class="com.rmmi.hibernate.Usuario" />
      </bag>
   </class>
</hibernate-mapping>

---Bid.hbm.xml
<hibernate-mapping package="com.rmmi.hibernate">
   <class name="com.rmmi.hibernate.Bid" table="BID">
      <id name="bidId" column="BIDID" type="java.lang.Long">
         <generator class="sequence" />
      </id>
      <property name="value" column="VAL" type="double" not-null="true" />
      <property name="comment" column="COMMNT" type="java.lang.String" />
      <many-to-one 
         name="lot" 
         column="LOTID" 
         class="com.rmmi.hibernate.Lot" 
         cascade="none" />
      <many-to-one
         name="user"
         column="USERID"
         class="com.rmmi.hibernate.Usuario"
         cascade="none" />
   </class>
</hibernate-mapping>

Here is the code of the navigation
Code between sessionFactory.openSession() and session.close():

Code:

Offering offering = (Offering)session.get(Offering.class, new Long(1));
      System.out.println(offering);
      
      List lots = offering.getLots();
      
      for (Iterator iterador = lots.iterator(); iterador.hasNext();) {
         Lot lot = (Lot) iterador.next();
         System.out.println(lot);
         List bids = lot.getBids();
         for (Iterator iterador2 = bids.iterator(); iterador2.hasNext();) {
            Bid bid = (Bid) iterador2.next();
            System.out.println(bid);
         }
      }

Full stack trace of any exception that occurs:

Code:

net.sf.hibernate.LazyInitializationException: Failed to lazily initialize a collection
   at net.sf.hibernate.collection.PersistentCollection.initialize(PersistentCollection.java:201)
   at net.sf.hibernate.collection.PersistentCollection.read(PersistentCollection.java:71)
   at net.sf.hibernate.collection.Bag.iterator(Bag.java:256)
   at com.rmmi.pruebas.Seleccionar.main(Seleccionar.java:47)
Caused by: net.sf.hibernate.exception.GenericJDBCException: could not initialize collection: [com.rmmi.hibernate.Lot.bids#3]
   at net.sf.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:90)
   at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:79)
   at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
   at net.sf.hibernate.collection.AbstractCollectionPersister.convert(AbstractCollectionPersister.java:728)
   at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:291)
   at net.sf.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:3315)
   at net.sf.hibernate.collection.PersistentCollection.initialize(PersistentCollection.java:195)
   ... 3 more
Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended

   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
   at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
   at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1870)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
   at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314)
   at com.mchange.v2.sql.filter.FilterPreparedStatement.executeQuery(FilterPreparedStatement.java:68)
   at com.mchange.v2.c3p0.impl.C3P0PooledConnection$2.executeQuery(C3P0PooledConnection.java:567)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
   at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:1020)
   at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:995)
   at net.sf.hibernate.loader.OneToManyLoader.initialize(OneToManyLoader.java:93)
   at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:288)
   ... 5 more
Exception in thread "main" 

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

The generated SQL (show_sql=true):

Code:

2005-04-14 16:54:21,000 - DEBUG [main]                  net.sf.hibernate.SQL     - select offering0_.OFFERINGID as OFFERINGID0_, offering0_.NOMBRESUBASTA as NOMBRESU2_0_, offering0_.LENGUAGE as LENGUAGE0_ from OFFERING offering0_ where offering0_.OFFERINGID=?
2005-04-14 16:54:21,062 - DEBUG [main]                  net.sf.hibernate.SQL     - select lots0_.OFFERINGID as OFFERINGID__, lots0_.LOTID as LOTID__, lots0_.LOTID as LOTID0_, lots0_.ITEM as ITEM0_, lots0_.UNITS as UNITS0_, lots0_.OFFERINGID as OFFERINGID0_ from LOT lots0_ where lots0_.OFFERINGID=?
2005-04-14 16:54:21,078 - DEBUG [main]                  net.sf.hibernate.SQL     - select bids0_.LOTID as LOTID__, bids0_.BIDID as BIDID__, bids0_.BIDID as BIDID2_, bids0_.VAL as VAL2_, bids0_.COMMNT as COMMNT2_, bids0_.LOTID as LOTID2_, bids0_.USERID as USERID2_, usuario1_.USERID as USERID0_, usuario1_.USARNAME as USARNAME0_, usuario1_.PASSWORD as PASSWORD0_, usuario1_.OFFERINGID as OFFERINGID0_, offering2_.OFFERINGID as OFFERINGID1_, offering2_.NOMBRESUBASTA as NOMBRESU2_1_, offering2_.LENGUAGE as LENGUAGE1_ from BID bids0_ left outer join USUARIO usuario1_ on bids0_.USERID=usuario1_.USERID left outer join OFFERING offering2_ on usuario1_.OFFERINGID=offering2_.OFFERINGID where bids0_.LOTID=?
2005-04-14 16:54:21,093 - WARN  [main] net.sf.hibernate.util.JDBCExceptionReporter     - SQL Error: 933, SQLState: 42000
2005-04-14 16:54:21,093 - ERROR [main] net.sf.hibernate.util.JDBCExceptionReporter     - ORA-00933: SQL command not properly ended

2005-04-14 16:54:21,093 - WARN  [main] net.sf.hibernate.util.JDBCExceptionReporter     - SQL Error: 933, SQLState: 42000
2005-04-14 16:54:21,093 - ERROR [main] net.sf.hibernate.util.JDBCExceptionReporter     - ORA-00933: SQL command not properly ended

As you can see the query to obtain the bids is using left outer join and the worst thing is that I'm trying to get there by Lot and it's using the Usuario table. How can I fix this? I've been working on this two days and I have like 1 week to finish this. Please help ='(

sharinganJounin · **Joined:** Tue Apr 12, 2005 11:25 am **Posts:** 2

Hi, I've already fixed the problem =D I don't undestand completely where's the problem with the left outer-joins. The documentation says that using the outer-joins will increase the speed of my application but I deactivated the use of outer-joins (hibernate.max_fetch_depth=0) and my app worked =D It now uses una select for each call to a <many-to-one> or <one-to-many> (if it isn't already loaded) and I think that's ok for my app. If any one knows how to run this app with the outer-joins correctly, please post here the answer. thanks =D

tferraz · **Posted:** Mon Sep 12, 2005 1:33 pm

with max_fetch_depth=0 you a setting hibernate tu just retrieve the ovjects in the first level of the tree, if your aplication needs any objects associated with it, then you need to set max_fetch_depth to 1 or as deep as you need to go in the tree (something like a factory has departments, a department has emploies, where the factory objects are at first level and so on.)
you may want to use "left join fetch" if you dont need to go too deep or dont have a great number of relations.