Hey,
I know this issue has already been discussed several times. I am looking for an optimal way to solve the problem.
I have 3 tables with relations like this : A1--*B1--*C
I need to just fetch all items from A with all related items from B with all related items from C. I already know that this is not possible using a single query since i can have only one fetch per query. I just need to get it done in a minumum number of queries possible. I have a huge number of rows in the C table and a big optimization would be to fetch them into B type objects. Is there a way to separately create the B - C object structure and then attach it to A ?
I have Hibernate 2.1.3 :
hibernate.cfg.xml
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost/webPath</property>
<property name="hibernate.connection.username">lpd</property>
<property name="hibernate.connection.password"></property>
<property name="dialect">net.sf.hibernate.dialect.PostgreSQLDialect</property>
<property name="show_sql">true</property>
<property name="transaction.factory_class">
net.sf.hibernate.transaction.JDBCTransactionFactory
</property>
<property name="hibernate.cache.provider_class">
net.sf.hibernate.cache.HashtableCacheProvider
</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<mapping resource="ro/code/webpath/common/UserSession.hbm.xml"/>
<mapping resource="ro/code/webpath/common/UserTransaction.hbm.xml"/>
<mapping resource="ro/code/webpath/common/UserRequest.hbm.xml"/>
</session-factory>
</hibernate-configuration>
hibernate.properties
hibernate.connection.provider_class net.sf.hibernate.connection.C3P0ConnectionProvider
hibernate.c3p0.max_size 4
hibernate.c3p0.min_size 2
hibernate.c3p0.timeout 5000
hibernate.c3p0.max_statements 100
hibernate.c3p0.idle_test_period 3000
hibernate.c3p0.acquire_increment 2
hibernate.c3p0.validate false
hibernate.connection.pool_size 5
hibernate.cache.use_query_cache true
hibernate.max_fetch_depth 3
hibernate.use_outer_join true
UserSession.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="ro.code.webpath.common.UserSession" table="user_sessions">
<id name="id" column="id" type="long" unsaved-value="0" >
<generator class="native"/>
</id>
<property name="ip" />
<property name="key" />
<property name="client" />
<property name="startTime" />
<property name="endTime" />
<property name="siteId" />
<list name="transactions" cascade="save-update" outer-join="true">
<key column="sessionId"/>
<index column="posn"/>
<one-to-many class="ro.code.webpath.common.UserTransaction"/>
</list>
</class>
</hibernate-mapping>
UserTransaction.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="ro.code.webpath.common.UserTransaction" table="user_transactions">
<id name="id" column="id" type="long" unsaved-value="0">
<generator class="native"/>
</id>
<property name="startTime" />
<property name="endTime"/>
<many-to-one cascade="save-update" name="session" class="ro.code.webpath.common.UserSession" column="sessionId" not-null="true"/>
<list name="requests" cascade="save-update" outer-join="true">
<key column="transactionId"/>
<index column="posn"/>
<one-to-many class="ro.code.webpath.common.UserRequest"/>
</list>
</class>
</hibernate-mapping>
UserRequest.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="ro.code.webpath.common.UserRequest" table="user_requests" >
<id name="id" column="id" type="long" unsaved-value="0" >
<generator class="native"/>
</id>
<property name="date" />
<property name="duration"/>
<property name="method"/>
<property name="url" length="512"/>
<property name="protocol"/>
<property name="code"/>
<property name="size"/>
<property name="referer" length="512"/>
<property name="client"/>
<many-to-one cascade="save-update" name="transaction" class="ro.code.webpath.common.UserTransaction"
column="transactionId" not-null="true"/>
</class>
</hibernate-mapping>
If i do something like this
List result=session.createCriteria(UserSession.class).setMaxResults(150).setCacheable(true).list();
...I get one query for each session, multiple queries for UserSession's UserTransactions, multiple queries for UserTransaction's UserRequests( I have noticed that if i have 50 UserRequests in one UserTransaction i will get 50 queries which is very bad).
If i run something like this :
Query q = session.createQuery("SELECT userSession FROM UserSession userSession left fetch join userSession.transactions");
List result=q.setMaxResults(100).setCacheable(true).list();
...the UserTransaction objects are fetched in one query (very nice), but, of course, I still have one query for each UserRequest. It would be great if i could fetch the UserTransactions and the fetch the UserRequests for each UserTransaction. This will reduce the query count to UserSession count + UserTransaction count, which is quite decent.
I really need the entire tree built so i do not think lazy="true" is an option. I don't mind using more than one query, i am just trying minimize the number.
Thank you very much for reading this and for the help.
Login
FAQ
Search
