[hibernate-issues] [Hibernate-JIRA] Created: (HHH-4658) Possibility to control projection of entities in SQL level when using Criteria queries

Possibility to control projection of entities in SQL level when using Criteria queries -------------------------------------------------------------------------------------- Key: HHH-4658 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4658 Project: Hibernate Core Issue Type: Improvement Components: query-criteria Affects Versions: 3.3.2 Environment: Oracle 10g Reporter: Mindaugas Plukas It is possible to project only desired(root) entity using HQL queries and this projection is reflected into real SQL (when fetching startegy "join fetch" is not employed). HQL (1): select [distinct] i from Item i [left|inner] join i.bids b where b.amount > 100 produces SQL (2): select [distinct] i.* from Item i [left|inner] join BID b on i.ITEM_ID = b.ITEM_ID where b.AMOUNT > 100 On the other hand Criteria query allways selects both entities, even when fetch strategy is "select". Criteria (3): session.createCriteria(Item.class).setFetchMode("bids", FetchMode.SELECT) .createCriteria("bids").add( Restrictions.gt("amount", new BigDecimal(100))) produces SQL (4): select i.*, b.* from Item i inner join BID b on i.ITEM_ID = b.ITEM_ID where b.AMOUNT > 100 (and then second SQL to fetch bids, as FetchMode.SELECT implies) In Criteria query case it seems there is no way to get SQL to select "i.*" only. This precludes effective selection of distinct Items on SQL level. Also query (1) (with distinct) and select (2) is suitable for setFirstResult()/setMaxResult() processing, however it turns out that paging can not be used with Criteria (3). Suggestion would be to introduce special kind of projection "project entity" (equivalent of HQL "select i"). Or maybe setting of result transformer "ROOT_ENTITY" could impact the resulting SQL by limiting SQL's projection to root entity columns (if possible). This issue may be related to http://opensource.atlassian.com/projects/hibernate/browse/HHH-2480 http://opensource.atlassian.com/projects/hibernate/browse/HHH-1499 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira