2.1.1:

Hello,

I am trying to tune the SQL that Hibernate runs when I'm loading a persistent instance. This instance (MiningResult below) has four one-to-one relationships to other classes using foreign keys. My goal is to have Hibernate execute one SQL select statement to pull in the fields from the main class (MiningResult) and the four one-to-one related classes together.

I know how to get Hibernate to pull all of the data in one select. However, Hibernate seems to insist upon ALSO selecting again for each sub-object. This means that 5 select statements are executed instead of just the one and I haven't been able to figure out the combination of mapping attributes for it to NOT issue the redundant extra four selects. Here is the mapping for MiningResult and one of the mappings (ViewInfo) for one of the other four (they are each similar):

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="org.cas.commerce.manageditem.mining">
<class name="MiningResult" table="c3s_mining_result">
<id name="id" type="string">
<column name="id" sql-type="CHAR(36)" />
<generator class="org.cas.commerce.util.HibernateUUIDGenerator"/>
</id>

<property name="name" type="string">
<column name="name"/>
</property>

<property name="annotation" type="string">
<column name="annotation"/>
</property>

<one-to-one name="clusterMap" class="ClusterMap" property-ref="miningResult" cascade="all" outer-join="true"/>

<one-to-one name="groupings" class="Groupings" property-ref="miningResult" cascade="all" outer-join="true"/>

<one-to-one name="stopwords" class="Stopwords" property-ref="miningResult" cascade="all" outer-join="true"/>

<one-to-one name="viewInfo" class="ViewInfo" property-ref="miningResult" cascade="all" outer-join="true"/>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="org.cas.commerce.manageditem.mining">
<class name="ViewInfo" table="c3s_view_info">
<id name="id" type="string">
<column name="id" sql-type="CHAR(36)" />
<generator class="org.cas.commerce.util.HibernateUUIDGenerator"/>
</id>

<property name="data" type="binary">
<column name="data"/>
</property>

<!-- Interestingly enough, this is actually a one-to-one relationship
via a foreign key with the MiningResult Take note of the 'unique' attribute. -->
<many-to-one name="miningResult" class="MiningResult" column="mining_result_id" unique="true" outer-join="false" cascade="none" />

</class>
</hibernate-mapping>



...and here is the generated sql (lots of joins):

11:17:49,905 DEBUG SQL:237 - select miningresu0_.id as id4_, miningresu0_.answer_set_id as answer_s2_4_, miningresu0_.login_id as login_id4_, miningresu0_.user_id as user_id4_, miningresu0_.product_name as product_5_4_, miningresu0_.create_time as create_t6_4_, miningresu0_.last_activity as last_act7_4_, miningresu0_.name as name4_, miningresu0_.annotation as annotation4_, clustermap1_.id as id0_, clustermap1_.annotations as annotati2_0_, clustermap1_.mining_result_id as mining_r3_0_, groupings2_.id as id1_, groupings2_.data as data1_, groupings2_.mining_result_id as mining_r3_1_, stopwords3_.id as id2_, stopwords3_.data as data2_, stopwords3_.mining_result_id as mining_r3_2_, viewinfo4_.id as id3_, viewinfo4_.data as data3_, viewinfo4_.mining_result_id as mining_r3_3_ from c3s_mining_result miningresu0_ left outer join c3s_cluster_map clustermap1_ on miningresu0_.id=clustermap1_.mining_result_id left outer join c3s_groupings groupings2_ on miningresu0_.id=groupings2_.mining_result_id left outer join c3s_stopwords stopwords3_ on miningresu0_.id=stopwords3_.mining_result_id left outer join c3s_view_info viewinfo4_ on miningresu0_.id=viewinfo4_.mining_result_id where miningresu0_.id=?

11:17:49,955 DEBUG SQL:237 - select viewinfo0_.id as id0_, viewinfo0_.data as data0_, viewinfo0_.mining_result_id as mining_r3_0_ from c3s_view_info viewinfo0_ where viewinfo0_.mining_result_id=?


I don't understand why this second query needs to run when all of the same data is pulled using the first query.

I know this is a lot of information, but any help would be appreciated.

Thanks!