Hibernate Community • View topic - Criteria Query N+1 Collection Subqueries, HQL Runs 1 Query

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Criteria Query N+1 Collection Subqueries, HQL Runs 1 Query

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

mephesto

Post subject: Criteria Query N+1 Collection Subqueries, HQL Runs 1 Query

Posted: Mon Sep 21, 2009 6:53 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

I have a rather complex query that I wrote initially in HQL and found I had fetching problems. I solved those and the original HQL query runs and collects all my data in a single set of joins.

I recently changed that page so it now has more variables in the query and migrated my HQL to a Criteria query for ease of customizing. However I noticed that it's running N+1 sub queries to fetch data that it already has on the root objects child collections. I'm confused why the HQL is fetching everything properly and the Criteria query uses the OpenSesionInView filter to fetch the collections.

Here's the two queries w/ rendered SQL from SQL spy:

Code:

HQL
      return sessionFactory.getCurrentSession().createQuery("" +
            "SELECT DISTINCT t " + 
            "FROM Title t " +
            "JOIN FETCH t.titleLanguageDataInternal tld " +
            "JOIN FETCH t.assetBillingState " +
            "JOIN FETCH tld.language lang " +
            "JOIN FETCH t.categoriesInternal tc " +
            "LEFT JOIN FETCH tc.parentCategory " +
            "WHERE lang.abbr = :abbr ORDER BY tld.titleString")
            .setString("abbr", "en")
            .list();

select distinct title0_.title_pk as title1_7_0_, titlelangu1_.title_lang_data_pk as title1_8_1_, language3_.language_pk as language1_6_2_, assetbilli2_.asset_billing_state_pk as asset1_5_3_, category5_.category_pk as category1_2_4_, category6_.category_pk as category1_2_5_, title0_.asset_billing_state_fk as asset2_7_0_, title0_.adult as adult7_0_, title0_.length as length7_0_, title0_.box_office as box5_7_0_, title0_.status as status7_0_, title0_.director as director7_0_, title0_.producer as producer7_0_, title0_.actors as actors7_0_, title0_.macrovision as macrovi10_7_0_, title0_.rank as rank7_0_, title0_.year as year7_0_, titlelangu1_.title_fk as title2_8_1_, titlelangu1_.language_fk as language3_8_1_, titlelangu1_.title as title8_1_, titlelangu1_.synopsis as synopsis8_1_, titlelangu1_.title_fk as title2_0__, titlelangu1_.title_lang_data_pk as title1_0__, language3_.language as language6_2_, language3_.abbr as abbr6_2_, assetbilli2_.name as name5_3_, assetbilli2_.adult as adult5_3_, assetbilli2_.visible as visible5_3_, category5_.property_fk as property2_2_4_, category5_.parent_category as parent3_2_4_, category5_.name as name2_4_, category5_.list_order as list5_2_4_, category5_.active as active2_4_, category5_.adult as adult2_4_, categories4_.title_fk as title2_1__, categories4_.category_fk as category1_1__, category6_.property_fk as property2_2_5_, category6_.parent_category as parent3_2_5_, category6_.name as name2_5_, category6_.list_order as list5_2_5_, category6_.active as active2_5_, category6_.adult as adult2_5_ 
from title title0_ 
inner join title_lang_data titlelangu1_ on title0_.title_pk=titlelangu1_.title_fk 
inner join languages language3_ on titlelangu1_.language_fk=language3_.language_pk 
inner join asset_billing_state assetbilli2_ on title0_.asset_billing_state_fk=assetbilli2_.asset_billing_state_pk 
inner join title_x_category categories4_ on title0_.title_pk=categories4_.title_fk 
inner join category category5_ on categories4_.category_fk=category5_.category_pk 
left outer join category category6_ on category5_.parent_category=category6_.category_pk 
where language3_.abbr='en' 
order by titlelangu1_.title

Code:

Criteria Query

      Criteria crit = sessionFactory.getCurrentSession().createCriteria(Title.class);
      crit.createAlias("titleLanguageDataInternal", "tld", CriteriaSpecification.INNER_JOIN);
      crit.createAlias("assetBillingState", "assetBillingState", CriteriaSpecification.INNER_JOIN);
      crit.createAlias("tld.language", "lang", CriteriaSpecification.INNER_JOIN);
      crit.createAlias("categoriesInternal", "tc", CriteriaSpecification.INNER_JOIN);
      crit.createAlias("tc.parentCategory", "pc", CriteriaSpecification.LEFT_JOIN);
      crit.add(Restrictions.eq("lang.abbr", "en"));
      crit.addOrder(Order.asc("tld.titleString"));
      crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
      
      crit.setFetchMode("tld", FetchMode.JOIN);
      crit.setFetchMode("assetBillingState", FetchMode.JOIN);
      crit.setFetchMode("lang", FetchMode.JOIN);
      crit.setFetchMode("tc", FetchMode.JOIN);
      crit.setFetchMode("pc", FetchMode.JOIN);

      return crit.list();

select this_.title_pk as title1_7_5_, this_.asset_billing_state_fk as asset2_7_5_, this_.adult as adult7_5_, this_.length as length7_5_, this_.box_office as box5_7_5_, this_.status as status7_5_, this_.director as director7_5_, this_.producer as producer7_5_, this_.actors as actors7_5_, this_.macrovision as macrovi10_7_5_, this_.rank as rank7_5_, this_.year as year7_5_, assetbilli2_.asset_billing_state_pk as asset1_5_0_, assetbilli2_.name as name5_0_, assetbilli2_.adult as adult5_0_, assetbilli2_.visible as visible5_0_, tld1_.title_lang_data_pk as title1_8_1_, tld1_.title_fk as title2_8_1_, tld1_.language_fk as language3_8_1_, tld1_.title as title8_1_, tld1_.synopsis as synopsis8_1_, lang3_.language_pk as language1_6_2_, lang3_.language as language6_2_, lang3_.abbr as abbr6_2_, categories10_.title_fk as title2_, tc4_.category_pk as category1_, tc4_.category_pk as category1_2_3_, tc4_.property_fk as property2_2_3_, tc4_.parent_category as parent3_2_3_, tc4_.name as name2_3_, tc4_.list_order as list5_2_3_, tc4_.active as active2_3_, tc4_.adult as adult2_3_, pc5_.category_pk as category1_2_4_, pc5_.property_fk as property2_2_4_, pc5_.parent_category as parent3_2_4_, pc5_.name as name2_4_, pc5_.list_order as list5_2_4_, pc5_.active as active2_4_, pc5_.adult as adult2_4_ 
from title this_ 
inner join asset_billing_state assetbilli2_ on this_.asset_billing_state_fk=assetbilli2_.asset_billing_state_pk 
inner join title_lang_data tld1_ on this_.title_pk=tld1_.title_fk 
inner join languages lang3_ on tld1_.language_fk=lang3_.language_pk 
inner join title_x_category categories10_ on this_.title_pk=categories10_.title_fk 
inner join category tc4_ on categories10_.category_fk=tc4_.category_pk 
left outer join category pc5_ on tc4_.parent_category=pc5_.category_pk 
where lang3_.abbr='en' 
order by tld1_.title asc

Code:

Root Entity Java Bean

public class Title extends BaseEntity {

   private AssetBillingState assetBillingState;
   private Integer adult;
   private String length;
   private Integer boxOffice;
   private Integer status;
   private String director;
   private String producer;
   private String actors;
   private Boolean macrovision;
   private Integer rank = 5;
   private Integer year = Calendar.getInstance().get(Calendar.YEAR);
   private Set<TitleLanguageData> titleLanguageData;
   private Set<TitleImage> titleImages;
   private Set<Category> categories;
   private Set<Instance> instances;
   private TitleImage tempPosterV3;
   private TitleImage tempPosterV4;
   
   public Integer getTitleLanguageDataCount() {
      return this.titleLanguageData.size();
   }
   
   protected Set<TitleLanguageData> getTitleLanguageDataInternal() {
      if (this.titleLanguageData == null) {
         this.titleLanguageData = new HashSet<TitleLanguageData>();
      }
      return this.titleLanguageData;
   }
   
   public List<TitleLanguageData> getTitleLanguageData() {
      List<TitleLanguageData> sortedHistory = new ArrayList<TitleLanguageData>(getTitleLanguageDataInternal());
      PropertyComparator.sort(sortedHistory, new MutableSortDefinition("language.id", true, false));
      return Collections.unmodifiableList(sortedHistory);
   }
   
   public void addTitleLanguageData(TitleLanguageData titleLanguageData) {
      getTitleLanguageDataInternal().add(titleLanguageData);
   }
   
   public void clearTitleLanguageData() {
      this.titleLanguageData = null;
   }
   
   public boolean clearTitleLanguageData(TitleLanguageData tld) {
      return this.titleLanguageData.remove(tld);
   }
   
   public void setTitleLanguageData(Set<TitleLanguageData> definitions) {
      setTitleLanguageDataInternal(definitions);
   }
   
   protected void setTitleLanguageDataInternal(Set<TitleLanguageData> definitions) {
      this.titleLanguageData = definitions;
   }

   public Integer getTitleImagesCount() {
      return this.titleImages.size();
   }
   
   protected Set<TitleImage> getTitleImagesInternal() {
      if (this.titleImages == null) {
         this.titleImages = new HashSet<TitleImage>();
      }
      return this.titleImages;
   }
   
   public List<TitleImage> getTitleImages() {
      List<TitleImage> sortedHistory = new ArrayList<TitleImage>(getTitleImagesInternal());
      PropertyComparator.sort(sortedHistory, new MutableSortDefinition("thumbnailVersion", true, false));
      return Collections.unmodifiableList(sortedHistory);
   }
   
   public TitleImage getTitleImageByVersion(Integer version) {
      for (int i = 0; i < this.getTitleImagesCount(); i++)
         if (this.getTitleImages().get(i).getThumbnailVersion().equals(version))
            return this.getTitleImages().get(i);
      return new TitleImage();
   }
   
   public void addTitleImage(TitleImage titleImage) {
      getTitleImagesInternal().add(titleImage);
   }
   
   public void clearTitleImages() {
      this.titleImages = null;
   }
   
   public boolean clearTitleImages(TitleImage object) {
      return this.titleImages.remove(object);
   }
   
   public void setTitleImages(Set<TitleImage> objectSet) {
      setTitleImagesInternal(objectSet);
   }
   
   protected void setTitleImagesInternal(Set<TitleImage> objectSet) {
      this.titleImages = objectSet;
   }
   
   protected Set<Category> getCategoriesInternal() {
      if (this.categories == null) {
         this.categories = new HashSet<Category>();
      }
      return this.categories;
   }
   
   public List<Category> getCategories() {      
      List<Category> sortedHistory = new ArrayList<Category>(getCategoriesInternal());
      PropertyComparator.sort(sortedHistory, new MutableSortDefinition("name", true, false));
      return Collections.unmodifiableList(sortedHistory);
   }
   
   public void addCategory(Category category) {
      getCategoriesInternal().add(category);
   }
   
   public void clearCategories() {
      this.categories = null;
   }
   
   public void setCategories(Set<Category> categories) {
      setCategoriesInternal(categories);
   }
   
   protected void setCategoriesInternal(Set<Category> categories) {
      this.categories = categories;
   }
   
   protected Set<Instance> getInstancesInternal() {
      if (this.instances == null) {
         this.instances = new HashSet<Instance>();
      }
      return this.instances;
   }
   
   public List<Instance> getInstances() {
      List<Instance> sortedHistory = new ArrayList<Instance>(getInstancesInternal());
      PropertyComparator.sort(sortedHistory, new MutableSortDefinition("id", true, false));
      return Collections.unmodifiableList(sortedHistory);
   }
   
   public void addInstance(Instance instance) {
      getInstancesInternal().add(instance);
   }
   
   public void clearInstances() {
      this.instances = null;
   }
   
   public void setInstances(Set<Instance> instances) {
      setInstancesInternal(instances);
   }
   
   protected void setInstancesInternal(Set<Instance> instances) {
      this.instances = instances;
   }

....... additional simple getters/setters ........

}

Code:

My JSP (using displaytag framework)
<display:table name="titleList" id="row" class="display lg" requestURI="/title.do" defaultsort="2" pagesize="50">
   <display:column property="id" title="ID&nbsp;" sortable="true" headerClass="sortable"/>
   <display:column property="titleLanguageData[0].titleString" title="Title" sortable="true" headerClass="sortable"/>
   <display:column title="Adult&nbsp;" sortable="true" headerClass="sortable">
      <c:choose>
         <c:when test="${row.adult == 1}">YES</c:when>
         <c:otherwise>NO</c:otherwise>
      </c:choose>
   </display:column>
   <display:column title="Category" sortable="true" headerClass="sortable">
      <c:forEach var="category" items="${row.categories}">
         ${category.parentChildString}<br>
      </c:forEach>
   </display:column>
   <display:column property="boxOffice" title="Box Office" sortable="true" headerClass="sortable"/>
   <display:column property="assetBillingState.name" title="Billing State" sortable="true" headerClass="sortable"/>
   <display:column property="length" title="Length" sortable="true" headerClass="sortable"/>
   <display:column title="">
      <ul class="dropdown dropdown-vertical dropdown-vertical-rtl">
         <li class="dir">Action
            <ul>
               <li class="dir">Show Details
                  <ul>
                     <li><a href="<c:url value="javascript:showInstances('${row.id}')"/>">Assigned Instances</a></li>
                     <li><a href="<c:url value="javascript:showAvailableContracts('${row.id}', '${row.boxOffice}')"/>">Available Contracts</a></li>
                     <li><a href="<c:url value="javascript:showAvailableFiles('${row.id}')"/>">Available Files</a></li>
                  </ul>
               </li>
               <li><a href="<c:url value="/titleEdit.do?titleId=${row.id}"/>">Edit Title</a></li>
               <li><a href="javascript:initContract(${row.id}, null, ${row.boxOffice})">Add Contract</a></li>
               <li><a href="javascript:initFile(${row.id}, null)">Add File</a></li>
               <li><a href="javascript:initInstance(${row.id}, null)">Add Instance</a></li>
            </ul>
         </li>
      </ul>
   </display:column>
</display:table>

When I remove the columns for the titleLanguageData and categories members, it doesn't run the subqueries to fetch the data (I use spring with OpenSessionInViewFilter). Any assistance is appreciated, I can provide more details if required.

The subqueries that are generated look like this, one for each title object that is returned from the above Criteria query:

Code:

select categories0_.title_fk as title2_1_, categories0_.category_fk as category1_1_, category1_.category_pk as category1_2_0_, category1_.property_fk as property2_2_0_, category1_.parent_category as parent3_2_0_, category1_.name as name2_0_, category1_.list_order as list5_2_0_, category1_.active as active2_0_, category1_.adult as adult2_0_ from title_x_category categories0_ left outer join category category1_ on categories0_.category_fk=category1_.category_pk where categories0_.title_fk=?

select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_8_0_, titlelangu0_.title_fk as title2_8_0_, titlelangu0_.language_fk as language3_8_0_, titlelangu0_.title as title8_0_,titlelangu0_.synopsis as synopsis8_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?

Top

mephesto

Post subject: Criteria Query Doesn't Fetch Collections, But HQL Does?

Posted: Tue Sep 22, 2009 6:49 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

I have upgraded to Hibernate 3.3.2 (w/ Spring 2.5.6) in hopes that there may be a fix for this. Additionally I found this article which shows a similar fetching issue with Criteria queries:

http://omgili.com/jmp/.wHSUbtEfZS3_Sqsw ... oKpU1RSW0-

which also links to: http://opensource.atlassian.com/project ... tion_33727

My new criteria query looks like this:

Code:

      sessionFactory.getCurrentSession().setCacheMode(CacheMode.IGNORE); // per the bug report.

      Criteria crit = sessionFactory.getCurrentSession().createCriteria(Title.class);
      
      crit.createCriteria("titleLanguageDataInternal", "tld", JoinFragment.INNER_JOIN)
         .createCriteria("language", JoinFragment.INNER_JOIN)
            .add(Restrictions.eq("abbr", "en"));
      
      crit.createCriteria("assetBillingState", JoinFragment.INNER_JOIN);
      
      crit.createCriteria("categoriesInternal", JoinFragment.INNER_JOIN)
         .createCriteria("parentCategory", JoinFragment.LEFT_OUTER_JOIN);
      
      crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
      
      crit.addOrder(Order.asc("tld.titleString"));
      crit.setFetchMode("titleLanguageDataInternal", FetchMode.JOIN);
      crit.setFetchMode("assetBillingState", FetchMode.JOIN);
      crit.setFetchMode("titleLanguageDataInternal.language", FetchMode.JOIN);
      crit.setFetchMode("categoriesInternal", FetchMode.JOIN);
      crit.setFetchMode("categoriesInternal.parentCategory", FetchMode.JOIN);
      return crit.list();

Instead of using groups of aliases I try inline nested createCriteria() calls using the JoinFragments and then at the end I set the fetch mode to join.

Again, when I use the simpler HQL in the original post works fine fetching everything. The criteria query fetches the title langauge data and category collections when I request them in the view. (An extra 1500 queries each)

Any idea what I'm doing wrong in the Criteria API or is this a legitimate bug? I'm wondering if it's something to do with the column names and binding? When I run the SQL from each in my database the results look the same, with slight differences in the number of columns and their identifiers, but the key columns (the title and synopsis strings) are included.

For completeness, here's the rendered SQL on both HQL and Criteria calls:

HQL:
select distinct title0_.title_pk as title1_7_0_, titlelangu1_.title_lang_data_pk as title1_8_1_, language3_.language_pk as language1_6_2_, assetbilli2_.asset_billing_state_pk as asset1_5_3_, category5_.category_pk as category1_2_4_, category6_.category_pk as category1_2_5_, title0_.asset_billing_state_fk as asset2_7_0_, title0_.adult as adult7_0_, title0_.length as length7_0_, title0_.box_office as box5_7_0_, title0_.status as status7_0_, title0_.director as director7_0_, title0_.producer as producer7_0_, title0_.actors as actors7_0_, title0_.macrovision as macrovi10_7_0_, title0_.rank as rank7_0_, title0_.year as year7_0_, titlelangu1_.title_fk as title2_8_1_, titlelangu1_.language_fk as language3_8_1_, titlelangu1_.title as title8_1_, titlelangu1_.synopsis as synopsis8_1_, titlelangu1_.title_fk as title2_0__, titlelangu1_.title_lang_data_pk as title1_0__, language3_.language as language6_2_, language3_.abbr as abbr6_2_, assetbilli2_.name as name5_3_, assetbilli2_.adult as adult5_3_, assetbilli2_.visible as visible5_3_, category5_.property_fk as property2_2_4_, category5_.parent_category as parent3_2_4_, category5_.name as name2_4_, category5_.list_order as list5_2_4_, category5_.active as active2_4_, category5_.adult as adult2_4_, categories4_.title_fk as title2_1__, categories4_.category_fk as category1_1__, category6_.property_fk as property2_2_5_, category6_.parent_category as parent3_2_5_, category6_.name as name2_5_, category6_.list_order as list5_2_5_, category6_.active as active2_5_, category6_.adult as adult2_5_ from title title0_ inner join title_lang_data titlelangu1_ on title0_.title_pk=titlelangu1_.title_fk inner join languages language3_ on titlelangu1_.language_fk=language3_.language_pk inner join asset_billing_state assetbilli2_ on title0_.asset_billing_state_fk=assetbilli2_.asset_billing_state_pk inner join title_x_category categories4_ on title0_.title_pk=categories4_.title_fk inner join category category5_ on categories4_.category_fk=category5_.category_pk left outer join category category6_ on category5_.parent_category=category6_.category_pk where language3_.abbr='en' order by titlelangu1_.title

Criteria:
select this_.title_pk as title1_7_5_, this_.asset_billing_state_fk as asset2_7_5_, this_.adult as adult7_5_, this_.length as length7_5_, this_.box_office as box5_7_5_, this_.status as status7_5_, this_.director as director7_5_, this_.producer as producer7_5_, this_.actors as actors7_5_, this_.macrovision as macrovi10_7_5_, this_.rank as rank7_5_, this_.year as year7_5_, assetbilli3_.asset_billing_state_pk as asset1_5_0_, assetbilli3_.name as name5_0_, assetbilli3_.adult as adult5_0_, assetbilli3_.visible as visible5_0_, tld1_.title_lang_data_pk as title1_8_1_, tld1_.title_fk as title2_8_1_, tld1_.language_fk as language3_8_1_, tld1_.title as title8_1_, tld1_.synopsis as synopsis8_1_, language2_.language_pk as language1_6_2_, language2_.language as language6_2_, language2_.abbr as abbr6_2_, categories10_.title_fk as title2_, category4_.category_pk as category1_, category4_.category_pk as category1_2_3_, category4_.property_fk as property2_2_3_, category4_.parent_category as parent3_2_3_, category4_.name as name2_3_, category4_.list_order as list5_2_3_, category4_.active as active2_3_, category4_.adult as adult2_3_, category5_.category_pk as category1_2_4_, category5_.property_fk as property2_2_4_, category5_.parent_category as parent3_2_4_, category5_.name as name2_4_, category5_.list_order as list5_2_4_, category5_.active as active2_4_, category5_.adult as adult2_4_ from title this_ inner join asset_billing_state assetbilli3_ on this_.asset_billing_state_fk=assetbilli3_.asset_billing_state_pk inner join title_lang_data tld1_ on this_.title_pk=tld1_.title_fk inner join languages language2_ on tld1_.language_fk=language2_.language_pk inner join title_x_category categories10_ on this_.title_pk=categories10_.title_fk inner join category category4_ on categories10_.category_fk=category4_.category_pk left outer join category category5_ on category4_.parent_category=category5_.category_pk where language2_.abbr='en' order by tld1_.title asc

Top

mephesto

Post subject: Re: Criteria Query N+1 Collection Subqueries, HQL Runs 1 Query

Posted: Wed Sep 23, 2009 3:04 pm

Newbie

Joined: Fri Jan 23, 2009 8:01 pm
Posts: 13

This is definitely a Hibernate bug.

@see http://omgili.com/jmp/.wHSUbtEfZS3_Sqsw ... oKpU1RSW0-

When I setup my criteria like this:

Code:

Criteria crit = sessionFactory.getCurrentSession().createCriteria(Title.class);
      
crit.createAlias("titleLanguageDataInternal", "tld", JoinFragment.INNER_JOIN);

crit.add(Restrictions.ilike("tld.titleString", "%Test%"));

crit.setFetchMode("titleLanguageDataInternal", FetchMode.JOIN);
crit.setFetchMode("titleLanguageDataInternal.language", FetchMode.JOIN);
crit.setFetchMode("categoriesInternal", FetchMode.JOIN);
crit.setFetchMode("categoriesInternal.parentCategory", FetchMode.JOIN);

crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
crit.setCacheMode(CacheMode.IGNORE);

return crit.list();

Hibernate runs the N+1 select to populate the collections:

Code:

Hibernate: select this_.title_pk as title1_1184_4_, this_.asset_billing_state_fk as asset2_1184_4_, this_.adult as adult1184_4_, this_.length as length1184_4_, this_.box_office as box5_1184_4_, this_.status as status1184_4_, this_.director as director1184_4_, this_.producer as producer1184_4_, this_.actors as actors1184_4_, this_.macrovision as macrovi10_1184_4_, this_.rank as rank1184_4_, this_.year as year1184_4_, tld1_.title_lang_data_pk as title1_1185_0_, tld1_.title_fk as title2_1185_0_, tld1_.language_fk as language3_1185_0_, tld1_.title as title1185_0_, tld1_.synopsis as synopsis1185_0_, language4_.language_pk as language1_1183_1_, language4_.language as language1183_1_, language4_.abbr as abbr1183_1_, categories5_.title_fk as title2_6_, category6_.category_pk as category1_6_, category6_.category_pk as category1_1179_2_, category6_.property_fk as property2_1179_2_, category6_.parent_category as parent3_1179_2_, category6_.name as name1179_2_, category6_.list_order as list5_1179_2_, category6_.active as active1179_2_, category6_.adult as adult1179_2_, category7_.category_pk as category1_1179_3_, category7_.property_fk as property2_1179_3_, category7_.parent_category as parent3_1179_3_, category7_.name as name1179_3_, category7_.list_order as list5_1179_3_, category7_.active as active1179_3_, category7_.adult as adult1179_3_ from title this_ inner join title_lang_data tld1_ on this_.title_pk=tld1_.title_fk left outer join languages language4_ on tld1_.language_fk=language4_.language_pk left outer join title_x_category categories5_ on this_.title_pk=categories5_.title_fk left outer join category category6_ on categories5_.category_fk=category6_.category_pk left outer join category category7_ on category6_.parent_category=category7_.category_pk where tld1_.title ilike ?
Hibernate: select studio0_.studio_pk as studio1_1187_, studio0_.country as country1187_, studio0_.state as state1187_, studio0_.name as name1187_, studio0_.main_phone_number as main5_1187_, studio0_.street as street1187_, studio0_.city as city1187_, studio0_.zip as zip1187_, studio0_.active as active1187_, studio0_.studio_abbr as studio10_1187_ from studio studio0_ where studio0_.active=1 order by studio0_.name
Hibernate: select encoder0_.encoder_pk as encoder1_1189_, encoder0_.country as country1189_, encoder0_.state as state1189_, encoder0_.name as name1189_, encoder0_.main_phone_number as main5_1189_, encoder0_.street as street1189_, encoder0_.city as city1189_, encoder0_.zip as zip1189_, encoder0_.active as active1189_ from encoder encoder0_ where encoder0_.active=1 order by encoder0_.name
Hibernate: select encoding0_.encoding_pk as encoding1_1192_0_, definition1_.definition_pk as definition1_1191_1_, encoding0_.definition_fk as definition2_1192_0_, encoding0_.encoding as encoding1192_0_, definition1_.name as name1191_1_, definition1_.abbr as abbr1191_1_ from encoding encoding0_ inner join definition definition1_ on encoding0_.definition_fk=definition1_.definition_pk order by encoding0_.encoding
Hibernate: select language0_.language_pk as language1_1183_, language0_.language as language1183_, language0_.abbr as abbr1183_ from languages language0_ where language0_.language_pk>=? order by language0_.language_pk
Hibernate: select language0_.language_pk as language1_1183_, language0_.language as language1183_, language0_.abbr as abbr1183_ from languages language0_ where language0_.language_pk>=? order by language0_.language_pk
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?
Hibernate: select titlelangu0_.title_fk as title2_1_, titlelangu0_.title_lang_data_pk as title1_1_, titlelangu0_.title_lang_data_pk as title1_1185_0_, titlelangu0_.title_fk as title2_1185_0_, titlelangu0_.language_fk as language3_1185_0_, titlelangu0_.title as title1185_0_, titlelangu0_.synopsis as synopsis1185_0_ from title_lang_data titlelangu0_ where titlelangu0_.title_fk=?

However, when I change only the JOIN type from INNER_JOIN to LEFT_OUTER_JOIN, only the one query is run to fetch the collections:

Code:

Criteria crit = sessionFactory.getCurrentSession().createCriteria(Title.class);
      
crit.createAlias("titleLanguageDataInternal", "tld", JoinFragment.LEFT_OUTER_JOIN);

crit.add(Restrictions.ilike("tld.titleString", "%Test%"));

crit.setFetchMode("titleLanguageDataInternal", FetchMode.JOIN);
crit.setFetchMode("titleLanguageDataInternal.language", FetchMode.JOIN);
crit.setFetchMode("categoriesInternal", FetchMode.JOIN);
crit.setFetchMode("categoriesInternal.parentCategory", FetchMode.JOIN);

crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
crit.setCacheMode(CacheMode.IGNORE);

return crit.list();

Code:

Hibernate: select this_.title_pk as title1_1077_4_, this_.asset_billing_state_fk as asset2_1077_4_, this_.adult as adult1077_4_, this_.length as length1077_4_, this_.box_office as box5_1077_4_, this_.status as status1077_4_, this_.director as director1077_4_, this_.producer as producer1077_4_, this_.actors as actors1077_4_, this_.macrovision as macrovi10_1077_4_, this_.rank as rank1077_4_, this_.year as year1077_4_, tld1_.title_fk as title2_6_, tld1_.title_lang_data_pk as title1_6_, tld1_.title_lang_data_pk as title1_1078_0_, tld1_.title_fk as title2_1078_0_, tld1_.language_fk as language3_1078_0_, tld1_.title as title1078_0_, tld1_.synopsis as synopsis1078_0_, language4_.language_pk as language1_1076_1_, language4_.language as language1076_1_, language4_.abbr as abbr1076_1_, categories5_.title_fk as title2_7_, category6_.category_pk as category1_7_, category6_.category_pk as category1_1072_2_, category6_.property_fk as property2_1072_2_, category6_.parent_category as parent3_1072_2_, category6_.name as name1072_2_, category6_.list_order as list5_1072_2_, category6_.active as active1072_2_, category6_.adult as adult1072_2_, category7_.category_pk as category1_1072_3_, category7_.property_fk as property2_1072_3_, category7_.parent_category as parent3_1072_3_, category7_.name as name1072_3_, category7_.list_order as list5_1072_3_, category7_.active as active1072_3_, category7_.adult as adult1072_3_ from title this_ left outer join title_lang_data tld1_ on this_.title_pk=tld1_.title_fk left outer join languages language4_ on tld1_.language_fk=language4_.language_pk left outer join title_x_category categories5_ on this_.title_pk=categories5_.title_fk left outer join category category6_ on categories5_.category_fk=category6_.category_pk left outer join category category7_ on category6_.parent_category=category7_.category_pk where tld1_.title ilike ?
Hibernate: select studio0_.studio_pk as studio1_1080_, studio0_.country as country1080_, studio0_.state as state1080_, studio0_.name as name1080_, studio0_.main_phone_number as main5_1080_, studio0_.street as street1080_, studio0_.city as city1080_, studio0_.zip as zip1080_, studio0_.active as active1080_, studio0_.studio_abbr as studio10_1080_ from studio studio0_ where studio0_.active=1 order by studio0_.name
Hibernate: select encoder0_.encoder_pk as encoder1_1082_, encoder0_.country as country1082_, encoder0_.state as state1082_, encoder0_.name as name1082_, encoder0_.main_phone_number as main5_1082_, encoder0_.street as street1082_, encoder0_.city as city1082_, encoder0_.zip as zip1082_, encoder0_.active as active1082_ from encoder encoder0_ where encoder0_.active=1 order by encoder0_.name
Hibernate: select encoding0_.encoding_pk as encoding1_1085_0_, definition1_.definition_pk as definition1_1084_1_, encoding0_.definition_fk as definition2_1085_0_, encoding0_.encoding as encoding1085_0_, definition1_.name as name1084_1_, definition1_.abbr as abbr1084_1_ from encoding encoding0_ inner join definition definition1_ on encoding0_.definition_fk=definition1_.definition_pk order by encoding0_.encoding
Hibernate: select language0_.language_pk as language1_1076_, language0_.language as language1076_, language0_.abbr as abbr1076_ from languages language0_ where language0_.language_pk>=? order by language0_.language_pk
Hibernate: select language0_.language_pk as language1_1076_, language0_.language as language1076_, language0_.abbr as abbr1076_ from languages language0_ where language0_.language_pk>=? order by language0_.language_pk

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum