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

[Mindaugas Plukas (JIRA)]

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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira