[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2101) Can't use outer join in table with composite-id, key-many-to-one

[Hugo Zhu (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2101?page=comments#action_24604 ] 

Hugo Zhu commented on HHH-2101:
-------------------------------

Just found "select this from BlogUserUrl this inner join fetch this.url where this.blog=?" is a much better way.

Hugo

> Can't use outer join in table with composite-id, key-many-to-one
> ----------------------------------------------------------------
>
>          Key: HHH-2101
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2101
>      Project: Hibernate3
>         Type: Improvement

>   Components: core
>     Versions: 3.1.3
>  Environment: MYSQL: 5.0.15-nt-log
> mysql-connector-java-3.0.16-ga
> Hibernate 3.1.3
>     Reporter: Hugo Zhu

>
>
> MYSQL 5 database;
> HBM like below: 
>   <class name="Blog" table="blog" dynamic-update="true">
>         <id column="id" name="id" type="long">
>             <generator class="native"/>
>         </id>
>         ...
>   </class>
>   <class name="BlogUrl" table="blog_url" dynamic-update="true">
>         <id column="id" name="id" type="long">
>             <generator class="native"/>
>         </id>
>         <property name="url" type="string" length="255" not-null="true"/>
>         <property name="urlHash" type="string" length="32" unique="true" not-null="true"/>
>         <property name="numOfHits" type="integer"/>
>         <property name="created" type="timestamp"/>
>     </class>
>     <class name="BlogUserUrl" table="blog_user_url" dynamic-update="true">
>         <composite-id>
>             <key-many-to-one name="url" class="BlogUrl"/>
>             <key-many-to-one name="blog" class="Blog"/>
>         </composite-id>
>         <property name="title" type="string" length="255" not-null="true"/>
>         <property name="description" type="string" length="255"/>
>         <property name="created" type="timestamp"/>
>     </class>
> But:
> getSession().createCriteria(BlogUserUrl.class).setFetchMode("url",FetchMode.JOIN).add(Restrictions.eq("blog", blog).setMaxResults(25).list();
> Still generates 2 queries - query 1: query out BlogUserUrl, then query 2: possible 25 sub queries for earch BlogUrl
> The better way should be just 1 outer join.
> I have set: <property name="max_fetch_depth">1</property> in Hibernate configuration, but there is no join attribute in element: key-many-to-one.
> The only way to solve this issue I can find out is:
> HQL: select this,this.url from BlogUserUrl this where this.blog=?, but I have to add a loop in results to conver the Object[] list to BlogUserUrl only  list. Not ideal
> Thanks for looking into this issue.
> Hugo

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