[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6042) To much columns in select queries after creating criteria with join

[Miguel Diaz (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-6042?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=41988#action_41988 ] 

Miguel Diaz commented on HHH-6042:
----------------------------------

I was just pointing that this is an issue that must be solved, since some comments in other issues (such as HHH-2150) suggest that this can be achived using the Criteria API (by means of using projections) but that's not true. Explaining the HQL equivalent of the Criteria API lacking functionality that this issue requests is just a clarification so the request is well understood by other people. I also use Criteria, and I really look forward to see your fix incorporated to Hibernate since currently I'm forced to use HQL for the solely purpose of circumventig this lacking functionality.

> To much columns in select queries after creating criteria with join
> -------------------------------------------------------------------
>
>                 Key: HHH-6042
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6042
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.2.6
>         Environment: <dependency>
>    <groupId>org.hibernate</groupId>
>    <artifactId>hibernate</artifactId>
>    <version>3.2.6.ga</version>
> </dependency>
> Any database
>            Reporter: Karol Kowalczyk
>         Attachments: AbstractEntityJoinWalker.java, Loader.java
>
>
> When I create criteria for any class with any value of property of association f.e.:
> java code:
>     eventDCriteria = DetachedCriteria.forClass(Event.class);
>     personDCriteria = eventDCriteria.createCriteria("participants");
>     personDCriteria.add(Expression.eq("age", 41));
>     List<Event> events=(List<Event>)getHibernateTemplate().findByCriteria(eventDCriteria, 0, 2);
> class mappings:
>     <class name="Event" table="EVENTS">
>         <id name="id" column="EVENT_ID">
>             <generator class="native"/>
>         </id>
>         <property name="date" type="timestamp" column="EVENT_DATE"/>
>         <property name="title"/>
>         <set name="participants" table="PERSON_EVENT" inverse="true">
>             <key column="EVENT_ID"/>
>             <many-to-many column="PERSON_ID" class="Person"/>
>         </set>
>     </class>
>     <class name="Person" table="PERSON">
>         <id name="id" column="PERSON_ID">
>             <generator class="native"/>
>         </id>
>         <property name="age"/>
>         <property name="firstname"/>
>         <property name="lastname"/>
>         <set name="events" table="PERSON_EVENT" >
>             <key column="PERSON_ID"/>
>             <many-to-many column="EVENT_ID" class="Event"/>
>         </set>
>         
>         <set name="emailAddresses" table="PERSON_EMAIL_ADDR" >
>             <key column="PERSON_ID"/>
>             <element type="string" column="EMAIL_ADDR"/>
>         </set>
>     </class>
> a Hibernate still creates select query f.e.:
> select e.EVENT_ID, e.EVENT_DATE, e.title, pe.EVENT_ID, pe.PERSON_ID, p.PERSON_ID, p.age, p.firstname, ...
>   from EVENTS e
>   inner join PERSON_EVENT pe on pe.EVENT_ID=e.EVENT_ID
>   inner join PERSON p on p.PERSON_ID=pe.PERSON_ID
>   where p.age=?;
> Why this hibernates query have UNNECESSARY list of columns: pe.EVENT_ID, pe.PERSON_ID, p.PERSON_ID, p.age, p.firstname, ...?
> This query is not INDEXONLY for any databases.
> More EFFECTIVE query is:
> select e.EVENT_ID, e.EVENT_DATE, e.title
>   from EVENTS e
>   inner join PERSON_EVENT pe on pe.EVENT_ID=e.EVENT_ID
>   inner join PERSON p on p.PERSON_ID=pe.PERSON_ID
>   where p.age=?;
> A prepared only for my two changes in hibernate: Loader.java and AbstractEntityJoinWalker.java to get more effective selects.

-- 
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