Gavin,
Thanks for the reply. When we looked at the docs and HiA (and experimented) we didn't see a way to load multiple collections for an object and any properties and collections of those collections, n levels deep. We could have totally missed it, but as an example:
Criteria crit = sess.createCriteria(Advertisement.class);
returnList = crit.setFetchMode(Advertisement.PROP_BID, FetchMode.EAGER)
.setFetchMode(Advertisement.COL_ADVERTISEMENTKEYPHRASES_BY_ADVERTISEMENT_ID, FetchMode.EAGER)
.setFetchMode(Advertisement.COL_CREATIVES_BY_ADVERTISEMENT_ID, FetchMode.EAGER)
.list()
As far as we can tell, this loads the Advertisement(s), its Bid property (and we could load any other "top" level properties of Advertisement we'd want -- and it would load them using left join fetch) and the creatives collection but it does *not* load the advertisementkeyphrases collection. To accomplish this we had to add:
Hibernate.initialize(ad.getAdvertisementkeyphrasesByAdvertisementId()) which ran into the n+1 issue.
We did not find a way to load a collection or property of creatives and filter them (at least none of the examples / our code seem to do it and returnMaps show as deprecated?). Other minor things our code does is to filter items in the collections we are loading by any where statement (filters in H3?) and it deals with the distinct issue. But the major thing was still the ability to load an entire tree completely, not just the branch of one collection, n levels deep, avoiding the n + 1 problem.
Did we miss something? I'll be optimistic and hope we didn't reinvent the wheel, but if we did, it was a fun exercise :)
Adrian
On Feb 6, 2005, at 10:46 PM, Gavin King wrote:
<x-tad-bigger>How is this different to the Criteria query API?</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>From:</x-tad-bigger><x-tad-bigger> [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] </x-tad-bigger><x-tad-bigger>On Behalf Of </x-tad-bigger><x-tad-bigger>Adrian Ridner</x-tad-bigger>
<x-tad-bigger>Sent:</x-tad-bigger><x-tad-bigger> Monday, 7 February 2005 5:41 PM</x-tad-bigger>
<x-tad-bigger>To:</x-tad-bigger><x-tad-bigger> 'hibernate-devel@lists.sourceforge.net'</x-tad-bigger>
<x-tad-bigger>Cc:</x-tad-bigger><x-tad-bigger> [EMAIL PROTECTED]</x-tad-bigger>
<x-tad-bigger>Subject:</x-tad-bigger><x-tad-bigger> [Hibernate] Is it worth contributing this code or posting it somewhere?</x-tad-bigger>
I would like your opinion if this code is the type of thing you would ever add to Hibernate or if we should take the time to post it in our website for download. I saw some postings in the User Forum asking for similar things, but you guys would know better. It is currently working with Hibernate 2.1.8. The code deals with having everything in the mapping files be lazy="true" and allowing the user to load an object and specify the object graph to load for that object in the code. Instead of using Hibernate.initialize() (n+1 queries approach), it optimizes said queries by using left join fetch on all the properties and all collections, being "smart" enough to issue only one query per collection. Note the number of properties loaded does not affect the number of queries issued.
The code below is a sample from one of our DAOs, which extends from our BaseHibernateDAO (it has convenience methods and object graph loading methods). I indented it one more level every time an object is added for a deeper node in the graph (I doubt all mail clients will maintain the indentation, and it helps quite a bit, so I posted it here also http://remilon.com/maintainindentation.html ). WIthout getting into a lot schema specifics, it loads an 'Advertisement' bean and a variety of its collections and their objects. This is a particularly deep object graph, as an illustration.
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>public Advertisement getAdvertisementTree(int advertisementId) {</x-tad-bigger>
<x-tad-bigger> LazyInitializer init = new LazyInitializer();</x-tad-bigger>
<x-tad-bigger> init</x-tad-bigger>
<x-tad-bigger> .addCollection(Advertisement.COL_ADVERTISEMENTKEYPHRASES_BY_ADVERTISEMENT_ID, "advkp")</x-tad-bigger>
<x-tad-bigger> .addNamedProperty("advkp", Advertisementkeyphrase.PROP_KEYPHRASE, "kp")</x-tad-bigger>
<x-tad-bigger> .addNamedProperty("kp", Keyphrase.PROP_PHRASE)</x-tad-bigger>
<x-tad-bigger> .addNamedCollection("advkp", Advertisementkeyphrase.COL_ARTIFACTS, "art1")</x-tad-bigger>
<x-tad-bigger> .addNamedCollection("art1", Artifact.COL_BIDS)</x-tad-bigger>
<x-tad-bigger> .addNamedCollection("art1", Artifact.COL_DESTINATION_PAGES, "destpg1")</x-tad-bigger>
<x-tad-bigger> .addNamedProperty("destpg1", DestinationPage.PROP_SITE)</x-tad-bigger>
<x-tad-bigger> .addNamedCollection("art1", Artifact.COL_ROUTINGS, "routings1")</x-tad-bigger>
<x-tad-bigger> .addNamedProperty("routings1", Routing.PROP_SITE)</x-tad-bigger>
<x-tad-bigger> .addCollection(Advertisement.COL_CREATIVES_BY_ADVERTISEMENT_ID, "ct")</x-tad-bigger>
<x-tad-bigger> .addNamedProperty("ct", Creative.PROP_ARTIFACT, "art2")</x-tad-bigger>
<x-tad-bigger> .addNamedCollection("art2", Artifact.COL_BIDS)</x-tad-bigger>
<x-tad-bigger> .addNamedCollection("art2", Artifact.COL_DESTINATION_PAGES, "destpg2")</x-tad-bigger>
<x-tad-bigger> .addNamedProperty("destpg2", DestinationPage.PROP_SITE);</x-tad-bigger>
<x-tad-bigger> .addNamedCollection("art2", Artifact.COL_ROUTINGS, "routings2")</x-tad-bigger>
<x-tad-bigger> .addNamedProperty("routings2", Routing.PROP_SITE);</x-tad-bigger>
<x-tad-bigger> Map wMap = new HashMap();</x-tad-bigger>
<x-tad-bigger> wMap.put("id", new Integer(advertisementId));</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>// This specifies the where conditions, order by, paging and if it is loading a collection</x-tad-bigger>
<x-tad-bigger> // or single object as the root node</x-tad-bigger>
<x-tad-bigger> FetchRestrictionParameters params = new FetchRestrictionParameters(init);</x-tad-bigger>
<x-tad-bigger> params.setWhereCondition("ad.advertisementId = :id");</x-tad-bigger>
<x-tad-bigger> params.setWhereParameters(wMap);</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>return (Advertisement)loadClassFetchByRestriction(Advertisement.class, "ad", params);</x-tad-bigger>
<x-tad-bigger> }</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>The constants in the different beans (ie. Advertisementkeyphrase.PROP_KEYPHRASE) represent the java names for the properties and collections (obviously prefixed...). We actually have an ant task that runs after hbm2java that reads the mapping files and automatically adds all those to the base beans, because we are lazy ;) The possible signatures are:
<x-tad-bigger>.addNamedProperty(String aliasOfObjectToLoadPropertyFrom, String propertyName, String aliasForThisNewProperty)</x-tad-bigger>
<x-tad-bigger> .addNamedCollection(String aliasOfObjectToLoadCollectionFrom, String collectionName, String aliasForThisNewCollection)</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>// alias not needed if you won't be loading objects of the node</x-tad-bigger>
<x-tad-bigger> .addNamedProperty(String aliasOfObjectToLoadPropertyFrom, String propertyName)</x-tad-bigger>
<x-tad-bigger> .addNamedCollection(String aliasOfObjectToLoadCollectionFrom, String collectionName)</x-tad-bigger>
Does this make sense? Is this something that would interest anyone and how should we proceed?
For those who made it this far and care, here is some specific background:
We have worked with Hibernate for a while now, and it is a great product! We develop both web apps and standalone apps, dealing with medium-sized databases: ~40-70 tables (largest table holding ~5-10 million rows). After 6 to 9 months though, we started noticing the following 'deficiencies' in our code base:
1- Multiple apps that access the same DBs duplicated mapping files, POJOs and queries/ dao code which could be re-used.
2- We were not automatically generating said mapping files and POJOs.
3- There were a lot of queries being issued for deeper object graphs with lots of collections, regardless if we used lazy="false" or Hibernate.initialize() (n+1 issue...)
We quickly realized the best strategy *for us* was to have all the the files auto-generated from the schema (bottom up approach). This was easily achieved by customizing some of the middlegen velocity scripts (fixing some minor conflicts, adding a few options, etc.) and then running hbm2java. I have not fully read the changes in the Hibernate 3 toolset, but it appears these tools are getting better (and integrated with IDEs). This fixed problems #1 & #2 but not #3. Not only that, when each app had its own mapping files, some used lazy="false", but for shared mapping files, this was not an option. So we were left with the option of initializing an object graph with the join queries as described above, but re-writing them every time for different graphs made no sense ;) That is when LazyInitializer and related objects were borned...
(Note: we are thinking about extending it to allow where restrictions on collections loaded using the graph).
Thanks for the input,
Adrian Ridner
----
Adrian Ridner
Senior Developer
Remilon LLC
o: 805.541.0938
c: 805.441.0312
[EMAIL PROTECTED]
----
PS: For completeness sake, the code above generates the following hql -- note there are exactly 9 queries, 1 query per collection specified in the object graph above:
<x-tad-bigger>Hibernate: select advertisem0_.advertisement_id as advertis1_0_, artifact2_.artifact_id as artifact1_1_, creativesb1_.creative_id as creative1_2_, advertisem0_.name as name0_, advertisem0_.level as level0_, advertisem0_.daily_budget_in_cents as daily_bu4_0_, advertisem0_.migration_status as migratio5_0_, advertisem0_.status as status0_, advertisem0_.account_status as account_7_0_, advertisem0_.default_bid_id as default_8_0_, advertisem0_.parent_advertisement_id as parent_a9_0_, advertisem0_.account_id as account_id0_, artifact2_.guid as guid1_, artifact2_.date_created as date_cre3_1_, artifact2_.date_executed as date_exe4_1_, artifact2_.date_used as date_used1_, artifact2_.status as status1_, creativesb1_.headline as headline2_, creativesb1_.description_line_1 as descript3_2_, creativesb1_.description_line_2 as descript4_2_, creativesb1_.display_url as display_5_2_, creativesb1_.status as status2_, creativesb1_.account_status as account_7_2_, creativesb1_.default_artifact_id as default_8_2_, creativesb1_.advertisement_id as advertis9_2_, creativesb1_.advertisement_id as advertis9___, creativesb1_.creative_id as creative1___ from Advertisement advertisem0_ left outer join Creative creativesb1_ on advertisem0_.advertisement_id=creativesb1_.advertisement_id left outer join Artifact artifact2_ on creativesb1_.default_artifact_id=artifact2_.artifact_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Hibernate: select artifact2_.artifact_id as artifact1_0_, site5_.site_id as site_id1_, routing4_.routing_id as routing_id2_, artifact2_.guid as guid0_, artifact2_.date_created as date_cre3_0_, artifact2_.date_executed as date_exe4_0_, artifact2_.date_used as date_used0_, artifact2_.status as status0_, site5_.site_base_url as site_bas2_1_, site5_.site_name as site_name1_, site5_.quinstreet_account_name as quinstre4_1_, site5_.is_ppc as is_ppc1_, site5_.uses_search_pantry as uses_sea6_1_, site5_.date_created as date_cre7_1_, site5_.entry_page_servlet as entry_pa8_1_, site5_.quinstreet_channel_id as quinstre9_1_, site5_.vertical_id as vertica10_1_, site5_.quinstreet_account_id as quinstr11_1_, routing4_.routing_guid as routing_2_2_, routing4_.date_created as date_cre3_2_, routing4_.status as status2_, routing4_.site_id as site_id2_, routings3_.artifact_id as artifact1___, routings3_.routing_id as routing_id__ from Advertisement advertisem0_ inner join Creative creativesb1_ on advertisem0_.advertisement_id=creativesb1_.advertisement_id inner join Artifact artifact2_ on creativesb1_.default_artifact_id=artifact2_.artifact_id left outer join Routing_To_Artifact routings3_ on artifact2_.artifact_id=routings3_.artifact_id left outer join Routing routing4_ on routings3_.routing_id=routing4_.routing_id left outer join Site site5_ on routing4_.site_id=site5_.site_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Hibernate: select artifact2_.artifact_id as artifact1_0_, bid4_.bid_id as bid_id1_, artifact2_.guid as guid0_, artifact2_.date_created as date_cre3_0_, artifact2_.date_executed as date_exe4_0_, artifact2_.date_used as date_used0_, artifact2_.status as status0_, bid4_.max_cents as max_cents1_, bids3_.artifact_id as artifact1___, bids3_.bid_id as bid_id__ from Advertisement advertisem0_ inner join Creative creativesb1_ on advertisem0_.advertisement_id=creativesb1_.advertisement_id inner join Artifact artifact2_ on creativesb1_.default_artifact_id=artifact2_.artifact_id left outer join Bid_For_Artifact bids3_ on artifact2_.artifact_id=bids3_.artifact_id left outer join Bid bid4_ on bids3_.bid_id=bid4_.bid_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Hibernate: select artifact2_.artifact_id as artifact1_0_, site5_.site_id as site_id1_, destinatio4_.destination_page_id as destinat1_2_, artifact2_.guid as guid0_, artifact2_.date_created as date_cre3_0_, artifact2_.date_executed as date_exe4_0_, artifact2_.date_used as date_used0_, artifact2_.status as status0_, site5_.site_base_url as site_bas2_1_, site5_.site_name as site_name1_, site5_.quinstreet_account_name as quinstre4_1_, site5_.is_ppc as is_ppc1_, site5_.uses_search_pantry as uses_sea6_1_, site5_.date_created as date_cre7_1_, site5_.entry_page_servlet as entry_pa8_1_, site5_.quinstreet_channel_id as quinstre9_1_, site5_.vertical_id as vertica10_1_, site5_.quinstreet_account_id as quinstr11_1_, destinatio4_.page_url as page_url2_, destinatio4_.site_id as site_id2_, destinatio3_.artifact_id as artifact1___, destinatio3_.destination_page_id as destinat2___ from Advertisement advertisem0_ inner join Creative creativesb1_ on advertisem0_.advertisement_id=creativesb1_.advertisement_id inner join Artifact artifact2_ on creativesb1_.default_artifact_id=artifact2_.artifact_id left outer join Destination_Page_For_Artifact destinatio3_ on artifact2_.artifact_id=destinatio3_.artifact_id left outer join Destination_Page destinatio4_ on destinatio3_.destination_page_id=destinatio4_.destination_page_id left outer join Site site5_ on destinatio4_.site_id=site5_.site_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Hibernate: select advertisem0_.advertisement_id as advertis1_0_, phrase3_.phrase_id as phrase_id1_, advertisem1_.advertisementkeyphrase_id as advertis1_2_, keyphrase2_.keyphrase_id as keyphras1_3_, advertisem0_.name as name0_, advertisem0_.level as level0_, advertisem0_.daily_budget_in_cents as daily_bu4_0_, advertisem0_.migration_status as migratio5_0_, advertisem0_.status as status0_, advertisem0_.account_status as account_7_0_, advertisem0_.default_bid_id as default_8_0_, advertisem0_.parent_advertisement_id as parent_a9_0_, advertisem0_.account_id as account_id0_, phrase3_.phrase as phrase1_, phrase3_.date_created as date_cre3_1_, phrase3_.last_edited_date as last_edi4_1_, phrase3_.status as status1_, phrase3_.score as score1_, phrase3_.human_rating as human_ra7_1_, phrase3_.vertical_id as vertical8_1_, phrase3_.added_by_user_id as added_by9_1_, advertisem1_.account_status as account_2_2_, advertisem1_.keyphrase_id as keyphras3_2_, advertisem1_.advertisement_id as advertis4_2_, keyphrase2_.match_type as match_type3_, keyphrase2_.phrase_id as phrase_id3_, advertisem1_.advertisement_id as advertis4___, advertisem1_.advertisementkeyphrase_id as advertis1___ from Advertisement advertisem0_ left outer join Advertisementkeyphrase advertisem1_ on advertisem0_.advertisement_id=advertisem1_.advertisement_id left outer join Keyphrase keyphrase2_ on advertisem1_.keyphrase_id=keyphrase2_.keyphrase_id left outer join Phrase phrase3_ on keyphrase2_.phrase_id=phrase3_.phrase_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Hibernate: select advertisem1_.advertisementkeyphrase_id as advertis1_0_, artifact3_.artifact_id as artifact1_1_, advertisem1_.account_status as account_2_0_, advertisem1_.keyphrase_id as keyphras3_0_, advertisem1_.advertisement_id as advertis4_0_, artifact3_.guid as guid1_, artifact3_.date_created as date_cre3_1_, artifact3_.date_executed as date_exe4_1_, artifact3_.date_used as date_used1_, artifact3_.status as status1_, artifacts2_.advertisementkeyphrase_id as advertis1___, artifacts2_.artifact_id as artifact2___ from Advertisement advertisem0_ inner join Advertisementkeyphrase advertisem1_ on advertisem0_.advertisement_id=advertisem1_.advertisement_id left outer join Artifact_For_Advertisementkeyphrase artifacts2_ on advertisem1_.advertisementkeyphrase_id=artifacts2_.advertisementkeyphrase_id left outer join Artifact artifact3_ on artifacts2_.artifact_id=artifact3_.artifact_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Hibernate: select artifact3_.artifact_id as artifact1_0_, routing5_.routing_id as routing_id1_, site6_.site_id as site_id2_, artifact3_.guid as guid0_, artifact3_.date_created as date_cre3_0_, artifact3_.date_executed as date_exe4_0_, artifact3_.date_used as date_used0_, artifact3_.status as status0_, routing5_.routing_guid as routing_2_1_, routing5_.date_created as date_cre3_1_, routing5_.status as status1_, routing5_.site_id as site_id1_, site6_.site_base_url as site_bas2_2_, site6_.site_name as site_name2_, site6_.quinstreet_account_name as quinstre4_2_, site6_.is_ppc as is_ppc2_, site6_.uses_search_pantry as uses_sea6_2_, site6_.date_created as date_cre7_2_, site6_.entry_page_servlet as entry_pa8_2_, site6_.quinstreet_channel_id as quinstre9_2_, site6_.vertical_id as vertica10_2_, site6_.quinstreet_account_id as quinstr11_2_, routings4_.artifact_id as artifact1___, routings4_.routing_id as routing_id__ from Advertisement advertisem0_ inner join Advertisementkeyphrase advertisem1_ on advertisem0_.advertisement_id=advertisem1_.advertisement_id inner join Artifact_For_Advertisementkeyphrase artifacts2_ on advertisem1_.advertisementkeyphrase_id=artifacts2_.advertisementkeyphrase_id inner join Artifact artifact3_ on artifacts2_.artifact_id=artifact3_.artifact_id left outer join Routing_To_Artifact routings4_ on artifact3_.artifact_id=routings4_.artifact_id left outer join Routing routing5_ on routings4_.routing_id=routing5_.routing_id left outer join Site site6_ on routing5_.site_id=site6_.site_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Hibernate: select artifact3_.artifact_id as artifact1_0_, bid5_.bid_id as bid_id1_, artifact3_.guid as guid0_, artifact3_.date_created as date_cre3_0_, artifact3_.date_executed as date_exe4_0_, artifact3_.date_used as date_used0_, artifact3_.status as status0_, bid5_.max_cents as max_cents1_, bids4_.artifact_id as artifact1___, bids4_.bid_id as bid_id__ from Advertisement advertisem0_ inner join Advertisementkeyphrase advertisem1_ on advertisem0_.advertisement_id=advertisem1_.advertisement_id inner join Artifact_For_Advertisementkeyphrase artifacts2_ on advertisem1_.advertisementkeyphrase_id=artifacts2_.advertisementkeyphrase_id inner join Artifact artifact3_ on artifacts2_.artifact_id=artifact3_.artifact_id left outer join Bid_For_Artifact bids4_ on artifact3_.artifact_id=bids4_.artifact_id left outer join Bid bid5_ on bids4_.bid_id=bid5_.bid_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Hibernate: select artifact3_.artifact_id as artifact1_0_, site6_.site_id as site_id1_, destinatio5_.destination_page_id as destinat1_2_, artifact3_.guid as guid0_, artifact3_.date_created as date_cre3_0_, artifact3_.date_executed as date_exe4_0_, artifact3_.date_used as date_used0_, artifact3_.status as status0_, site6_.site_base_url as site_bas2_1_, site6_.site_name as site_name1_, site6_.quinstreet_account_name as quinstre4_1_, site6_.is_ppc as is_ppc1_, site6_.uses_search_pantry as uses_sea6_1_, site6_.date_created as date_cre7_1_, site6_.entry_page_servlet as entry_pa8_1_, site6_.quinstreet_channel_id as quinstre9_1_, site6_.vertical_id as vertica10_1_, site6_.quinstreet_account_id as quinstr11_1_, destinatio5_.page_url as page_url2_, destinatio5_.site_id as site_id2_, destinatio4_.artifact_id as artifact1___, destinatio4_.destination_page_id as destinat2___ from Advertisement advertisem0_ inner join Advertisementkeyphrase advertisem1_ on advertisem0_.advertisement_id=advertisem1_.advertisement_id inner join Artifact_For_Advertisementkeyphrase artifacts2_ on advertisem1_.advertisementkeyphrase_id=artifacts2_.advertisementkeyphrase_id inner join Artifact artifact3_ on artifacts2_.artifact_id=artifact3_.artifact_id left outer join Destination_Page_For_Artifact destinatio4_ on artifact3_.artifact_id=destinatio4_.artifact_id left outer join Destination_Page destinatio5_ on destinatio4_.destination_page_id=destinatio5_.destination_page_id left outer join Site site6_ on destinatio5_.site_id=site6_.site_id where (advertisem0_.advertisement_id=? )</x-tad-bigger>
