Hello,

This subject has been beat to death on many threads so here is one more. I have been experimenting with loading an object graph having multiple collections on it. Per HIA you can only eagerly load a single collection per query. Given this restriction I was trying to load 2 collections in 2 queries per code below. Given that I am getting duplicate rows I am wrapping results in a Set.

I am using the example caveatemptor app to test this out. Everyone should be very familiar with this example.

I am attempting to load all items with bids and categorizedItems collections. This is done in 2 steps:

HashSet list = new HashSet();

// Step 1:
Criteria crit = HibernateUtil.getSession().createCriteria(Item.class);
itemsPart1 = crit.setFetchMode("bids", FetchMode.EAGER).list();

System.out.println("List with bids has " + itemsPart1.size() + " items");
list.addAll(itemsPart1);

// Step 2:
itemsPart2 = crit.setFetchMode("categorizedItems", FetchMode.EAGER).list();

System.out.println("List with CatItems has " + itemsPart2.size() + " items");
list.addAll(itemsPart2);


However, later as I iterate thru the list and print out sizes of bids and categorizedItems collections I get hits to the database. My assumption was that once a collection is retrieved eagerly all necessary data will be loaded.

Can someone take a look and let me know if I am missing something?

Thanks


2.1.6

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<!--

Mapping file for the Item class of CaveatEmptor.

An Item is the central entity of an auction. One interesting
aspect of this mapping is the <bag> used for the collection
of Bids. The Item Java class uses a List for this collection,
and Hibernate will order the collection on load by the
creation date of the bids.

Again, notice the 4000 character limit of Oracle VARCHAR
columns for the text description.

@author Christian Bauer <christian@hibernate.org>

-->
<hibernate-mapping package="org.hibernate.auction.model">

<class name="Item"
table="ITEM" lazy="true">

<!-- Common id property. -->
<id name="id"
type="long"
column="ITEM_ID"
unsaved-value="null"
access="org.hibernate.auction.persistence.DirectSetAccessor">
<generator class="native"/>
</id>

<!-- A versioned entity. -->
<version name="version"
column="VERSION"
access="net.sf.hibernate.property.DirectPropertyAccessor"/>

<!-- Name of the item is immutable. -->
<property name="name"
type="string"
column="NAME"
length="255"
not-null="true"
update="false"
access="org.hibernate.auction.persistence.DirectSetAccessor"/>

<!-- Limit item description to 4000 characters, Oracle. -->
<property name="description"
type="string"
column="DESCRIPTION"
length="4000"
not-null="true"/>

<!-- UserTpe for prices, length is precision of decimal field for DDL. -->
<property name="initialPrice"
type="org.hibernate.auction.persistence.MonetaryAmountCompositeUserType"
access="org.hibernate.auction.persistence.DirectSetAccessor">
<column name="INITIAL_PRICE" not-null="true" length="2"/>
<column name="INITIAL_PRICE_CURRENCY" not-null="true"/>
</property>

<!-- UserTpe for prices, length is precision of decimal field for DDL. -->
<property name="reservePrice"
type="org.hibernate.auction.persistence.MonetaryAmountCompositeUserType"
access="org.hibernate.auction.persistence.DirectSetAccessor">
<column name="RESERVE_PRICE" length="2"/>
<column name="RESERVE_PRICE_CURRENCY"/>
</property>

<!-- We can't change the startDate. -->
<property name="startDate"
column="START_DATE"
type="java.util.Date"
update="false"
not-null="true"
access="org.hibernate.auction.persistence.DirectSetAccessor"/>

<!-- We can't change the endDate. -->
<property name="endDate"
column="END_DATE"
type="java.util.Date"
update="false"
not-null="true"
access="org.hibernate.auction.persistence.DirectSetAccessor"/>

<!-- Simple property mapped with a custom usertype.-->
<property name="state"
column="STATE"
type="org.hibernate.auction.persistence.ItemStateUserType"
access="org.hibernate.auction.persistence.DirectSetAccessor"
not-null="true"/>

<!-- Simple property. -->
<property name="approvalDatetime"
column="APPROVAL_DATETIME"
type="java.util.Date"
not-null="false"/>

<!-- We can't change the creation time, so map it with update="false". -->
<property name="created"
column="CREATED"
type="timestamp"
update="false"
not-null="true"
access="org.hibernate.auction.persistence.DirectSetAccessor"/>

<!-- Who approved this auction. -->
<many-to-one name="approvedBy"
class="User"
cascade="none"
column="APPROVED_BY_USER_ID"
not-null="false"
outer-join="false"
foreign-key="FK1_APPROVED_BY_USER_ID"/>

<!-- We can't change the seller. -->
<many-to-one name="seller"
class="User"
cascade="none"
column="SELLER_ID"
update="false"
not-null="true"
outer-join="false"
access="org.hibernate.auction.persistence.DirectSetAccessor"
foreign-key="FK2_SELLER_ID"/>

<!-- Mark the successful bid. -->
<many-to-one name="successfulBid"
class="Bid"
cascade="none"
column="SUCCESSFUL_BID_ID"
not-null="false"
outer-join="false"
foreign-key="FK3_SUCCESSFUL_BID_ID"/>

<!-- We use a one-to-many association to express the relationship
to a set of categories. There is an intermediate entity class,
CategorizedItem, which in fact makes this a many-to-many
association between Item and Category.
-->
<set name="categorizedItems"
cascade="all-delete-orphan"
inverse="true"
lazy="true"
access="org.hibernate.auction.persistence.DirectSetAccessor">
<key foreign-key="FK2_CATEGORIZED_ITEM_ID">
<column name="ITEM_ID" not-null="true" length="16"/>
</key>
<one-to-many class="CategorizedItem"/>
</set>

<!-- Use a standard parent/child relationship for bids. -->
<bag name="bids"
cascade="all"
inverse="true"
order-by="CREATED desc"
lazy="true"
access="org.hibernate.auction.persistence.DirectSetAccessor">
<key>
<column name="ITEM_ID" not-null="true"/>
</key>
<one-to-many class="Bid"/>
</bag>

</class>

<!-- TODO: This completely ignores currency -->
<query name="minBid"><![CDATA[
select b from Bid b where b.amount.value =
(select min(b.amount.value) from Bid b where b.item.id = :itemid)
]]></query>

<!-- TODO: This completely ignores currency -->
<query name="maxBid"><![CDATA[
select b from Bid b where b.amount.value =
(select max(b.amount.value) from Bid b where b.item.id = :itemid)
]]></query>

</hibernate-mapping>

Code
public Collection findItemsWithBidsAndCatItems() throws InfrastructureException {

Collection itemsPart1 = null;
Collection itemsPart2 = null;
HashSet list = new HashSet();

try {
Criteria crit = HibernateUtil.getSession().createCriteria(Item.class);
itemsPart1 = crit.setFetchMode("bids", FetchMode.EAGER).list();

System.out.println("List with bids has " + itemsPart1.size() + " items");
list.addAll(itemsPart1);

itemsPart2 = crit.setFetchMode("categorizedItems", FetchMode.EAGER).list();

System.out.println("List with CatItems has " + itemsPart2.size() + " items");
list.addAll(itemsPart2);

}
return list;
}

MySQL 4.0.13

Running test...
14:44:12,593 INFO Environment: Hibernate 2.1.6
14:44:12,593 INFO Environment: hibernate.properties not found
14:44:12,609 INFO Environment: using CGLIB reflection optimizer
14:44:12,625 INFO Configuration: configuring from resource: /hibernate.cfg.xml
14:44:12,625 INFO Configuration: Configuration resource: /hibernate.cfg.xml
14:44:12,984 INFO Configuration: Mapping resource: org/hibernate/auction/model/User.hbm.xml
14:44:13,406 INFO Binder: Mapping class: org.hibernate.auction.model.User -> USERS
14:44:14,562 INFO Configuration: Mapping resource: org/hibernate/auction/model/Item.hbm.xml
14:44:14,796 INFO Binder: Mapping class: org.hibernate.auction.model.Item -> ITEM
14:44:14,859 INFO Configuration: Mapping resource: org/hibernate/auction/model/Category.hbm.xml
14:44:15,046 INFO Binder: Mapping class: org.hibernate.auction.model.Category -> CATEGORY
14:44:15,062 INFO Configuration: Mapping resource: org/hibernate/auction/model/CategorizedItem.hbm.xml
14:44:15,250 INFO Binder: Mapping class: org.hibernate.auction.model.CategorizedItem -> CATEGORIZED_ITEM
14:44:15,312 INFO Configuration: Mapping resource: org/hibernate/auction/model/Bid.hbm.xml
14:44:15,468 INFO Binder: Mapping class: org.hibernate.auction.model.Bid -> BID
14:44:15,468 INFO Configuration: Mapping resource: org/hibernate/auction/model/BillingDetails.hbm.xml
14:44:15,640 INFO Binder: Mapping class: org.hibernate.auction.model.BillingDetails -> BILLING_DETAILS
14:44:15,796 INFO Binder: Mapping joined-subclass: org.hibernate.auction.model.CreditCard -> CREDIT_CARD
14:44:15,828 INFO Binder: Mapping joined-subclass: org.hibernate.auction.model.BankAccount -> BANK_ACCOUNT
14:44:15,828 INFO Configuration: Mapping resource: org/hibernate/auction/model/Comment.hbm.xml
14:44:16,015 INFO Binder: Mapping class: org.hibernate.auction.model.Comment -> COMMENTS
14:44:16,031 INFO Configuration: Mapping resource: org/hibernate/auction/persistence/audit/AuditLogRecord.hbm.xml
14:44:16,234 INFO Binder: Mapping class: org.hibernate.auction.persistence.audit.AuditLogRecord -> AUDIT_LOG
14:44:16,234 INFO Configuration: Configured SessionFactory: null
14:44:16,234 INFO Configuration: processing one-to-many association mappings
14:44:16,234 INFO Binder: Mapping collection: org.hibernate.auction.model.User.items -> ITEM
14:44:16,234 INFO Binder: Mapping collection: org.hibernate.auction.model.User.billingDetails -> BILLING_DETAILS
14:44:16,234 INFO Binder: Mapping collection: org.hibernate.auction.model.Item.categorizedItems -> CATEGORIZED_ITEM
14:44:16,234 INFO Binder: Mapping collection: org.hibernate.auction.model.Item.bids -> BID
14:44:16,234 INFO Binder: Mapping collection: org.hibernate.auction.model.Category.childCategories -> CATEGORY
14:44:16,234 INFO Binder: Mapping collection: org.hibernate.auction.model.Category.categorizedItems -> CATEGORIZED_ITEM
14:44:16,250 INFO Configuration: processing one-to-one association property references
14:44:16,250 INFO Configuration: processing foreign key constraints
14:44:16,343 INFO Dialect: Using dialect: net.sf.hibernate.dialect.MySQLDialect
14:44:16,359 INFO SettingsFactory: Maximim outer join fetch depth: 2
14:44:16,359 INFO SettingsFactory: Use outer join fetching: true
14:44:16,390 INFO DriverManagerConnectionProvider: Using Hibernate built-in connection pool (not for production use!)
14:44:16,390 INFO DriverManagerConnectionProvider: Hibernate connection pool size: 1
14:44:16,406 INFO DriverManagerConnectionProvider: using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost/caveatemptor
14:44:16,406 INFO DriverManagerConnectionProvider: connection properties: {user=caveatemptor, password=caveatemptor}
14:44:16,437 INFO TransactionManagerLookupFactory: No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
14:44:17,109 INFO SettingsFactory: Use scrollable result sets: true
14:44:17,109 INFO SettingsFactory: Use JDBC3 getGeneratedKeys(): true
14:44:17,109 INFO SettingsFactory: Optimize cache for minimal puts: false
14:44:17,109 INFO SettingsFactory: echoing all SQL to stdout
14:44:17,109 INFO SettingsFactory: Query language substitutions: {}
14:44:17,109 INFO SettingsFactory: cache provider: net.sf.ehcache.hibernate.Provider
14:44:17,125 INFO Configuration: instantiating and configuring caches
14:44:17,218 INFO SessionFactoryImpl: building session factory
14:44:20,515 INFO SessionFactoryObjectFactory: Not binding factory to JNDI, no JNDI name configured
Hibernate: select this.ITEM_ID as ITEM_ID2_, this.VERSION as VERSION2_, this.NAME as NAME2_, this.DESCRIPTION as DESCRIPT4_2_, this.INITIAL_PRICE as INITIAL_5_2_, this.INITIAL_PRICE_CURRENCY as INITIAL_6_2_, this.RESERVE_PRICE as RESERVE_7_2_, this.RESERVE_PRICE_CURRENCY as RESERVE_8_2_, this.START_DATE as START_DATE2_, this.END_DATE as END_DATE2_, this.STATE as STATE2_, this.APPROVAL_DATETIME as APPROVA12_2_, this.CREATED as CREATED2_, this.APPROVED_BY_USER_ID as APPROVE14_2_, this.SELLER_ID as SELLER_ID2_, this.SUCCESSFUL_BID_ID as SUCCESS16_2_, bids1_.ITEM_ID as ITEM_ID__, bids1_.BID_ID as BID_ID__, bids1_.BID_ID as BID_ID0_, bids1_.AMOUNT as AMOUNT0_, bids1_.AMOUNT_CURRENCY as AMOUNT_C3_0_, bids1_.CREATED as CREATED0_, bids1_.ITEM_ID as ITEM_ID0_, bids1_.BIDDER_ID as BIDDER_ID0_, user2_.USER_ID as USER_ID1_, user2_.VERSION as VERSION1_, user2_.FIRSTNAME as FIRSTNAME1_, user2_.LASTNAME as LASTNAME1_, user2_.USERNAME as USERNAME1_, user2_.`PASSWORD` as y6_1_, user2_.EMAIL as EMAIL1_, user2_.RANKING as RANKING1_, user2_.IS_ADMIN as IS_ADMIN1_, user2_.CREATED as CREATED1_, user2_.DEFAULT_BILLING_DETAILS_ID as DEFAULT11_1_, user2_.STREET as STREET1_, user2_.ZIPCODE as ZIPCODE1_, user2_.CITY as CITY1_ from ITEM this left outer join BID bids1_ on this.ITEM_ID=bids1_.ITEM_ID left outer join USERS user2_ on bids1_.BIDDER_ID=user2_.USER_ID where 1=1
List with bids has 6 items
Hibernate: select this.ITEM_ID as ITEM_ID1_, this.VERSION as VERSION1_, this.NAME as NAME1_, this.DESCRIPTION as DESCRIPT4_1_, this.INITIAL_PRICE as INITIAL_5_1_, this.INITIAL_PRICE_CURRENCY as INITIAL_6_1_, this.RESERVE_PRICE as RESERVE_7_1_, this.RESERVE_PRICE_CURRENCY as RESERVE_8_1_, this.START_DATE as START_DATE1_, this.END_DATE as END_DATE1_, this.STATE as STATE1_, this.APPROVAL_DATETIME as APPROVA12_1_, this.CREATED as CREATED1_, this.APPROVED_BY_USER_ID as APPROVE14_1_, this.SELLER_ID as SELLER_ID1_, this.SUCCESSFUL_BID_ID as SUCCESS16_1_, categorize1_.ITEM_ID as ITEM_ID__, categorize1_.CATEGORY_ID as CATEGORY1___, categorize1_.CATEGORY_ID as CATEGORY1_0_, categorize1_.ITEM_ID as ITEM_ID0_, categorize1_.DATE_ADDED as DATE_ADDED0_, categorize1_.USERNAME as USERNAME0_, categorize1_.CATEGORY_ID as CATEGORY1_0_, categorize1_.ITEM_ID as ITEM_ID0_ from ITEM this left outer join CATEGORIZED_ITEM categorize1_ on this.ITEM_ID=categorize1_.ITEM_ID where 1=1
List with CatItems has 5 items
Total list has 4 items
Really, not even luxury SUVs.
Hibernate: select bids0_.ITEM_ID as ITEM_ID__, bids0_.BID_ID as BID_ID__, bids0_.BID_ID as BID_ID1_, bids0_.AMOUNT as AMOUNT1_, bids0_.AMOUNT_CURRENCY as AMOUNT_C3_1_, bids0_.CREATED as CREATED1_, bids0_.ITEM_ID as ITEM_ID1_, bids0_.BIDDER_ID as BIDDER_ID1_, user1_.USER_ID as USER_ID0_, user1_.VERSION as VERSION0_, user1_.FIRSTNAME as FIRSTNAME0_, user1_.LASTNAME as LASTNAME0_, user1_.USERNAME as USERNAME0_, user1_.`PASSWORD` as y6_0_, user1_.EMAIL as EMAIL0_, user1_.RANKING as RANKING0_, user1_.IS_ADMIN as IS_ADMIN0_, user1_.CREATED as CREATED0_, user1_.DEFAULT_BILLING_DETAILS_ID as DEFAULT11_0_, user1_.STREET as STREET0_, user1_.ZIPCODE as ZIPCODE0_, user1_.CITY as CITY0_ from BID bids0_ left outer join USERS user1_ on bids0_.BIDDER_ID=user1_.USER_ID where bids0_.ITEM_ID=? order by bids0_.CREATED desc
Item has 0 bids
Item has 2 catItems
An item in the carsLuxury category.
Item has 3 bids
Item has 1 catItems
Another item in the carsLuxury category.
Hibernate: select bids0_.ITEM_ID as ITEM_ID__, bids0_.BID_ID as BID_ID__, bids0_.BID_ID as BID_ID1_, bids0_.AMOUNT as AMOUNT1_, bids0_.AMOUNT_CURRENCY as AMOUNT_C3_1_, bids0_.CREATED as CREATED1_, bids0_.ITEM_ID as ITEM_ID1_, bids0_.BIDDER_ID as BIDDER_ID1_, user1_.USER_ID as USER_ID0_, user1_.VERSION as VERSION0_, user1_.FIRSTNAME as FIRSTNAME0_, user1_.LASTNAME as LASTNAME0_, user1_.USERNAME as USERNAME0_, user1_.`PASSWORD` as y6_0_, user1_.EMAIL as EMAIL0_, user1_.RANKING as RANKING0_, user1_.IS_ADMIN as IS_ADMIN0_, user1_.CREATED as CREATED0_, user1_.DEFAULT_BILLING_DETAILS_ID as DEFAULT11_0_, user1_.STREET as STREET0_, user1_.ZIPCODE as ZIPCODE0_, user1_.CITY as CITY0_ from BID bids0_ left outer join USERS user1_ on bids0_.BIDDER_ID=user1_.USER_ID where bids0_.ITEM_ID=? order by bids0_.CREATED desc
Item has 0 bids
Item has 1 catItems
Don't drive SUVs.
Hibernate: select bids0_.ITEM_ID as ITEM_ID__, bids0_.BID_ID as BID_ID__, bids0_.BID_ID as BID_ID1_, bids0_.AMOUNT as AMOUNT1_, bids0_.AMOUNT_CURRENCY as AMOUNT_C3_1_, bids0_.CREATED as CREATED1_, bids0_.ITEM_ID as ITEM_ID1_, bids0_.BIDDER_ID as BIDDER_ID1_, user1_.USER_ID as USER_ID0_, user1_.VERSION as VERSION0_, user1_.FIRSTNAME as FIRSTNAME0_, user1_.LASTNAME as LASTNAME0_, user1_.USERNAME as USERNAME0_, user1_.`PASSWORD` as y6_0_, user1_.EMAIL as EMAIL0_, user1_.RANKING as RANKING0_, user1_.IS_ADMIN as IS_ADMIN0_, user1_.CREATED as CREATED0_, user1_.DEFAULT_BILLING_DETAILS_ID as DEFAULT11_0_, user1_.STREET as STREET0_, user1_.ZIPCODE as ZIPCODE0_, user1_.CITY as CITY0_ from BID bids0_ left outer join USERS user1_ on bids0_.BIDDER_ID=user1_.USER_ID where bids0_.ITEM_ID=? order by bids0_.CREATED desc
Item has 0 bids
Item has 1 catItems

Just to clarify, using test data from the caveatemptor application there are 4 rows in the items table, query for items with bids returns 6 rows (some are duplicates), first 3 are initialized, last 3 are not.

you execute the same criteria query twice and on the second run the fetchmode is now set for two collections.

pre hibernate 3.0.5 this would just make only one of the collections being fetched (as you see)

3.0.5 and onwards you are actually allowed to fetch multiple collections in one fetch but that is still not recommended since you easily can get very inefficient queries out of it.

Max,

Thanks for a reply and your note on the same criteria object. At this point however I noticed this and added an additional criteria object to test it out, so now my code looks like this:

Code
Collection itemsPart1 = null;
Collection itemsPart2 = null;
HashSet list = new HashSet();

try {
Criteria crit = HibernateUtil.getSession().createCriteria(Item.class);
itemsPart1 = crit.setFetchMode("bids", FetchMode.EAGER).list();
System.out.println("List with bids has " + itemsPart1.size() + " items");
list.addAll(itemsPart1);

Criteria crit2 = HibernateUtil.getSession().createCriteria(Item.class);
itemsPart2 = crit2.setFetchMode("categorizedItems",FetchMode.EAGER).list();
System.out.println("List with CatItems has " + itemsPart2.size() + " items");
list.addAll(itemsPart2);
}

return list;

With Hibernate 2.1.6 I got the same result as before, bids for some items were initialized, bids for other items were not initialized !!!

At this point I decided to try out Hibernate 2.1.8, and that seemed to of solved the problem. I am still testing it out, but if you know of any bug that might have been resolved I would appreciate your feedback.

Thanks

Regarding your comment about setting the same Criteria object with an additional collection, with Hibernate 2.1.8 it does not seem to matter that I set a second collection on the same criteria object, both collections are fetched eagerly, and I do not get hits to the database again when I iterate. With Hibernate 2.1.6 I do get hits when iterating.

Still testing...

as always use the recommend production version of you hibernate branch - in this case 2.1.8

you can see in the changelog all recorded changes.

Max,

I checked the changelog for 2.1.8, and 2.1.7, cannot really see anything related to the issue I am seeing in 2.1.6. I understand your point to use the "latest/greatest", but at this point I was experimenting with 2.1.6. Would be nice to know what the issue is in case I have to refer to it in the future.

In any case, with the latest it seems to work, so right now I am happy.