Query question (fetching a collection of value types)

ukchucktown · **Posted:** Wed Jul 12, 2006 11:52 am

I'm using hibernate 3.1 and have the following mapping file:

Code:

<hibernate-mapping package="gov.seahawk.model">
    <class name="DataSource" table="DATA_SOURCE">
        <id name="key" type="string" column="ID">
            <generator class="guid"/>
        </id>

        <property name="name" type="string" column="NAME"/>
        <property name="shortName" type="string" column="SHORT_NAME"/>        
        <property name="description" type="string" column="DESCRIPTION"/>

        <set name="dataSourceRanks" table="DATA_SOURCE_RANKS">
            <key column="DATA_SOURCE_ID" foreign-key="DATA_SOURCE_RANKS_DATA_SOURCE_ID_FK"/>
            <composite-element class="DataSourceRank">
                <property name="className" type="string" column="CLASS_NAME"/>
                <property name="propertyName" type="string" column="PROPERTY_NAME"/>
                <property name="rank" type="integer" column="RANK"/>
            </composite-element>
        </set>
    </class>
</hibernate-mapping>

And the following HQL query:

Code:

<hibernate-mapping>
    <query name="dbo.DataSource.findByShortName">
        <![CDATA[
            from DataSource as ds
            join fetch ds.dataSourceRanks
            where ds.shortName = :shortName
        ]]>
    </query>
</hibernate-mapping>

My java code:

Code:

Query query = sessionFactory.getCurrentSession()
                .getNamedQuery("dbo.DataSource.findByShortName");
query.setString("shortName", name);
resultSet = query.list();
if (resultSet.size() == 1) {
  ds = (DataSource) resultSet.get(0);
  vessel = assignDataSource(ds, vessel);
  return vessel;
}

What I expected was a single DataSource object in the list with its dataSourceRanks collection initialized. What I received was a list of 28 records. I'm confused by the query results. It appears to be more like a SQL result set and not the object with its value collection initialized. I need some help understanding the behavior I'm observing and what I must do to return a DataSource object with its value collection intialized.

Thanks,
Grant

steve · **Posted:** Wed Jul 12, 2006 2:12 pm

select distinct ds
from DataSource as ds
join fetch ds.dataSourceRanks
where ds.shortName = :shortName

* only works on 3.2 ...

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

I was similarly confused when I started making HQL queries. You need to specify what object you actually want to select if you want to do join queries. Still, much cleaner than SQL though.

Try this:

Code:

<hibernate-mapping>
    <query name="dbo.DataSource.findByShortName">
        <![CDATA[
            select ds from DataSource as ds
            join fetch ds.dataSourceRanks
            where ds.shortName = :shortName
        ]]>
    </query>
</hibernate-mapping>

Please rate posts...[/quote]

ukchucktown · **Posted:** Wed Jul 12, 2006 6:34 pm

I added the select distinct clause to my query but the list of results still included 28 records. This appears to be correct so I called uniqueResult instead of list on the Query object. I'm not sure if I can actually take the list returned by Hibernate and create a DataSource object with a collection of 28 DataSourceRank objects, the result when I call uniqueResult and cast the object returned to a DataSource. It worked but I'm still a bit confused on how to handle cases when I'm not expecting one record to be returned.

Grant

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

You could also do:

Code:

<hibernate-mapping>
    <query name="dbo.DataSource.findByShortName">
        <![CDATA[
            select ds from DataSource as ds
            where ds.shortName = :shortName
        ]]>
    </query>
</hibernate-mapping>

And have use the mapping file to set the fetch:

Code:

<hibernate-mapping package="gov.seahawk.model">
    <class name="DataSource" table="DATA_SOURCE">
        <id name="key" type="string" column="ID">
            <generator class="guid"/>
        </id>

        <property name="name" type="string" column="NAME"/>
        <property name="shortName" type="string" column="SHORT_NAME"/>       
        <property name="description" type="string" column="DESCRIPTION"/>

        <set name="dataSourceRanks" table="DATA_SOURCE_RANKS" [color=red]fetch="join"[/color]>
            <key column="DATA_SOURCE_ID" foreign-key="DATA_SOURCE_RANKS_DATA_SOURCE_ID_FK"/>
            <composite-element class="DataSourceRank">
                <property name="className" type="string" column="CLASS_NAME"/>
                <property name="propertyName" type="string" column="PROPERTY_NAME"/>
                <property name="rank" type="integer" column="RANK"/>
            </composite-element>
        </set>
    </class>
</hibernate-mapping>

ukchucktown · **Posted:** Wed Jul 12, 2006 9:56 pm

I'm aware of the changes I can make to my mapping file and I now realize the collection returned by list will have duplicates of the root object when I eager fetch persistent collection properties of the root object. I suppose what I really want is behavior similar to setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY,) on Criteria. The solution I read in the docs said to assign the list to a set and let the pojo's equals method filter the duplicates.

Grant

steve · **Posted:** Wed Jul 12, 2006 11:13 pm

adding distinct will essentially operate like DISTINCT_ROOT_ENTITY result transaformer; but as i mentioned, this is only on 3.2 (you said you are using 3.1)

3.2 adds the ability to apply result transformers to hql and native sql queries also, btw...

ukchucktown · **Posted:** Thu Jul 13, 2006 12:06 am

I was on 3.1 until I read your post. We are in active development so I updated our ant build to support multiple versions of hibernate and downloaded version 3.2 to give it a try. I did observe the behavior you describe until I did an eager fetch involving 3 tables, then I ended up with dups again. Maybe I'm missing something, not sure, but I learned something about HQL today.

Grant