Databases
StackOverflow2013
Postsdbo
POHibernate Criteria limit select
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate Criteria limit select
    text
    copied!<p>So, I have a rather complex query I am trying to make using the Hibernate Criteria API. I have the following entity classes: </p> <blockquote> <p>Code<br> Gift<br> GiftVendor<br> GiftVendorStatus </p> </blockquote> <p>with the following relationships: </p> <blockquote> <p>Code 1&lt;>1 Gift<br> Gift 1&lt;>* GiftVendor<br> GiftVendor 1&lt;>1 GiftVendorStatus </p> </blockquote> <p>I need to build a Criteria query that returns a List of Code objects, but that restricts it to only Codes that have a Gift that have at least one GiftVendor with a GiftVendorStatus of Online. Here is the code I am using to build the criteria:</p> <pre><code>Criteria base = CodeDao.getBaseCriteria(); base.createAlias("gift","gift"); base.createAlias("gift.giftVendor","giftVendor"); base.createAlias("giftVendor.giftVendorStatus","giftVendorStatus"); base.add(Restrictions.like("giftVendorStatus.description", "Online%")); return base.list(); </code></pre> <p>This gives me a List of Code objects, restricted as I would expect. However, it also does additional queries to build out all of the unused relationships of the Gift object, even though I have all of the mappings set up with a fetch mode of Lazy. This results in 4 additional, separate queries for each of my 10000+ results.</p> <p>I have code to do the query using HQL that works as expected:</p> <pre><code>String hql = "select c FROM Code c inner join c.gift g inner join g.giftVendors gv inner join gv.giftVendorStatus gvs" + " WHERE gvs.description like :desc"; HashMap&lt;String,Object&gt; params = new HashMap&lt;String, Object&gt;(); params.put("desc", "Online%"); return performQuery(hql, params); </code></pre> <p>That code gives me a List of Code objects, as expected, without doing all of the extra queries to populate the Gift object. How do I tell Hibernate not to do those extra queries with the Criteria API?</p> <p>UPDATE: The problem here is not the retrieval of the Gift table, but rather unrelated one-to-one relationships from the Gift table. For example, Gift has a one-to-one relationship to GiftCommentAggregateCache. This table is in no way related to this particular query, so I would expect lazy initialization rules to apply, and the query to GiftCommentAggregateCache to not occur unless a read is attempted. However, with the Criteria query written out as above, it makes that separate query to populate the model object for GiftCommentAggregateCache.</p> <p>If I use:</p> <pre><code>base.setFetchMode("gift.giftCommentAggregateCache", FetchMode.JOIN); </code></pre> <p>then I do not have any problems. However, that means that in order for this to work as I would expect, I need to add that line for every single unused one-to-one relationship that Gift has. Any ideas as to why the Lazy rules specified in the mappings are not coming into play here?</p> <p>There are a few different things I have tried:</p> <pre><code>base.setFetchMode("gift", FetchMode.LAZY); // Still does additional queries </code></pre> <p>and</p> <pre><code>base.setFetchMode("gift", FetchMode.SELECT); // Still does additional queries </code></pre> <p>and</p> <pre><code>base.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); // Still does additional queries </code></pre> <p>and</p> <pre><code>base.setResultTransformer(Criteria.ROOT_ENTITY); // Still does additional queries </code></pre> <p>and</p> <pre><code>base.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); // Still does additional queries </code></pre> <p>and</p> <pre><code>base.setProjection(Projections.property("gift")); // Does not do additional queries, but incorrectly returns a List of Gift objects, instead of a List of Code objects </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload