I have the following design.

The table ap_step has a many to many relationship with table technology which is resolved with the join table ap_step_technology.

The table ap_step also has a one-to-one relation with table ap_detail_level
The table ap_step also has a one-to-one relation with table ap_step_category

In my application, I want to be able to retrieve steps based on some technologyId and I have accomplished this with the followin two methods:

List steps =
session.createCriteria(ApStepVO.class)
.setFetchMode("detail", FetchMode.LAZY)
.createAlias("technologies", "techs")
.add(Expression.eq("techs.technologyId", new Integer(techId)))
.list();

OR

List steps = session.getNamedQuery("findStepsByTechnologyId")
.setInteger("techId", techId)
.list();
As you will notice when using Criteria, I set the fetch mode for detail as lazy. But what ends up happening is that hibernate still queries the ap_detail_level table. In total hibernate does 7 queries for something that should only do one query. Here are the queries:

Hibernate: select this.ap_step_id as ap_step_id2_, this.ap_step as ap_step2_, this.ap_detail_level_id as ap_detai3_2_, this.ap_step_category_id as ap_step_4_2_, technologi1_.tech_id as tech_id__, technologi1_.ap_step_id as ap_step_id__, techs.tech_id as tech_id0_, techs.technology as technology0_, techs.description as descript3_0_, techs.ap_type_id as ap_type_id0_, aptypevo3_.ap_type_id as ap_type_id1_, aptypevo3_.ap_type as ap_type1_, aptypevo3_.description as descript3_1_ from ap_step this inner join ap_step_technology technologi1_ on this.ap_step_id=technologi1_.ap_step_id inner join technology techs on technologi1_.tech_id=techs.tech_id left outer join ap_type aptypevo3_ on techs.ap_type_id=aptypevo3_.ap_type_id where techs.tech_id=?

Hibernate: select apdetaille0_.level_id as level_id0_, apdetaille0_.level as level0_ from ap_detail_level apdetaille0_ where apdetaille0_.level_id=?

Hibernate: select apstepcate0_.ap_step_category_id as ap_step_1_0_, apstepcate0_.category as category0_, apstepcate0_.ap_category_desc as ap_categ3_0_ from ap_step_category apstepcate0_ where apstepcate0_.ap_step_category_id=?

Hibernate: select technologi0_.tech_id as tech_id__, technologi0_.ap_step_id as ap_step_id__, technology1_.tech_id as tech_id0_, technology1_.technology as technology0_, technology1_.description as descript3_0_, technology1_.ap_type_id as ap_type_id0_, aptypevo2_.ap_type_id as ap_type_id1_, aptypevo2_.ap_type as ap_type1_, aptypevo2_.description as descript3_1_ from ap_step_technology technologi0_ inner join technology technology1_ on technologi0_.tech_id=technology1_.tech_id left outer join ap_type aptypevo2_ on technology1_.ap_type_id=aptypevo2_.ap_type_id where technologi0_.ap_step_id=?

Hibernate: select steps0_.ap_step_id as ap_step_id__, steps0_.tech_id as tech_id__, apstepvo1_.ap_step_id as ap_step_id0_, apstepvo1_.ap_step as ap_step0_, apstepvo1_.ap_detail_level_id as ap_detai3_0_, apstepvo1_.ap_step_category_id as ap_step_4_0_ from ap_step_technology steps0_ inner join ap_step apstepvo1_ on steps0_.ap_step_id=apstepvo1_.ap_step_id where steps0_.tech_id=?

Hibernate: select steps0_.ap_step_id as ap_step_id__, steps0_.tech_id as tech_id__, apstepvo1_.ap_step_id as ap_step_id0_, apstepvo1_.ap_step as ap_step0_, apstepvo1_.ap_detail_level_id as ap_detai3_0_, apstepvo1_.ap_step_category_id as ap_step_4_0_ from ap_step_technology steps0_ inner join ap_step apstepvo1_ on steps0_.ap_step_id=apstepvo1_.ap_step_id where steps0_.tech_id=?

Hibernate: select steps0_.ap_step_id as ap_step_id__, steps0_.tech_id as tech_id__, apstepvo1_.ap_step_id as ap_step_id0_, apstepvo1_.ap_step as ap_step0_, apstepvo1_.ap_detail_level_id as ap_detai3_0_, apstepvo1_.ap_step_category_id as ap_step_4_0_ from ap_step_technology steps0_ inner join ap_step apstepvo1_ on steps0_.ap_step_id=apstepvo1_.ap_step_id where steps0_.tech_id=?

and here are my mappings:

<?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>
<class
name="com.securance.vo.ApStepVO"
table="ap_step"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="stepId"
column="ap_step_id"
type="int"
>
<generator class="native">
</generator>
</id>

<property
name="step"
type="java.lang.String"
update="true"
insert="true"
column="ap_step"
/>

<many-to-one
name="detail"
class="com.securance.vo.ApDetailLevelVO"
cascade="none"
outer-join="false"
update="true"
insert="true"
column="ap_detail_level_id"
/>

<many-to-one
name="stepCategory"
class="com.securance.vo.ApStepCategoryVO"
cascade="none"
outer-join="false"
update="true"
insert="true"
column="ap_step_category_id"
unique="true"
/>

<set
name="clientAssistance"
table="ap_step_client_assistance"
lazy="true"
inverse="false"
cascade="all"
sort="unsorted"
>

<key
column="ap_step_id"
/>

<many-to-many
class="com.securance.vo.ClientRequestAssistanceVO"
column="client_assist_id"
outer-join="auto"
/>

</set>

<set
name="technologies"
table="ap_step_technology"
lazy="false"
inverse="false"
cascade="none"
sort="unsorted"
>

<key
column="ap_step_id"
/>

<many-to-many
class="com.securance.vo.TechnologyVO"
column="tech_id"
outer-join="auto"
/>

</set>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-ApStepVO.xml
containing the additional properties and place it in your merge dir.
-->

</class>

<query name="findStepsByTechnologyId"><![CDATA[
select step from ApStepVO step join step.technologies techs where techs.technologyId = :techId
]]></query>

</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>
<class
name="com.securance.vo.ApDetailLevelVO"
table="ap_detail_level"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="levelId"
column="level_id"
type="int"
>
<generator class="native">
</generator>
</id>

<property
name="levelDesc"
type="java.lang.String"
update="true"
insert="true"
column="level"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-ApDetailLevelVO.xml
containing the additional properties and place it in your merge dir.
-->

</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>
<class
name="com.securance.vo.ApStepCategoryVO"
table="ap_step_category"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="categoryId"
column="ap_step_category_id"
type="int"
>
<generator class="native">
</generator>
</id>

<property
name="category"
type="java.lang.String"
update="true"
insert="true"
column="category"
/>

<property
name="categoryDesc"
type="java.lang.String"
update="true"
insert="true"
column="ap_category_desc"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-ApStepCategoryVO.xml
containing the additional properties and place it in your merge dir.
-->

</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>
<class
name="com.securance.vo.TechnologyVO"
table="technology"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="technologyId"
column="tech_id"
type="int"
>
<generator class="native">
</generator>
</id>

<property
name="technologyName"
type="java.lang.String"
update="true"
insert="true"
column="technology"
/>

<property
name="description"
type="java.lang.String"
update="true"
insert="true"
column="description"
/>

<many-to-one
name="apType"
class="com.securance.vo.ApTypeVO"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="ap_type_id"
/>

<set
name="steps"
table="ap_step_technology"
lazy="false"
inverse="true"
cascade="none"
sort="unsorted"
>

<key
column="tech_id"
/>

<many-to-many
class="com.securance.vo.ApStepVO"
column="ap_step_id"
outer-join="auto"
/>

</set>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-TechnologyVO.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>



I've read the sample chapters of the book and gone through the archives, but I can't seem to find the solution to this. Maybe I am not using the right search terms.

Can anyone tell me what I am doing wrong. This is a major performance penalty that I want/need to avoid.

Thanks

Your additional queries are coming from your many-to-ones that are not using outer joins and also your sets that are not being lazy loaded.

So while you executing what might be a single query to get a list of your main objects, it is the associations off of your main objects which are executing the additional queries.

Ok I fixed the part that has to do with the sets.

Can you please tell me what you mean when you say that my many-to-ones are not using outer joins. I see the outer-join option set in the mapping files.

So my question is if there is a way to eliminate the additional query coming from the many-to-one associations.

Thanks

take a look at fetch keyword

I know users don't like this kind of answer but I would recommand you to reread the reference guide to understand the (lazy) loading/caching mecanism and its relation to HQL.
And even better, read Hibernate In Action, it explains all that in a more detailed way.

Hello,
a question for juan110470;

I'm a beginner trying to use a many to many association like in your case.
The mappings files are useful to me,
but to really understand I would need the exact structures of your tables.
ap_step, technology and ap_step_technology

If you had 5 minutes, it would really help me as a beginner.
Thanks.

I have been reading Hibernate in Action and in chapter 4 section 4.4.6 it says:
For a <many-to-one> or <one-to-one> association, lazy fetching is possible only if
the associated class mapping enables proxying. For the Item class, we enable proxying
by specifying lazy="true":
<class name="Item" lazy="true">

So I tried the following in my class:

/**
@hibernate.class
* table="ap_step"
* lazy="true"
*
*@hibernate.query
* name="findStepsByTechnologyId"
* query="select step from ApStepVO step
* join step.technologies techs
* where techs.technologyId = :techId"
*/

But lazy="true" doesn't show up anywhere in the mapping document.

Can someone tell me what I am missing so that I can move on to try to figure this whole thing out.

Thanks

I don't think xdoclet supports lazy at the class level, although I am sure I saw another post asking if specifying the class as a proxy, just take a quick read through the post of the last day or so.

You could always hack up the xdoclet hibernate dtds yourself to add the functionality.

As for your extra queries due to to the many to ones, look at the following...

<many-to-one
name="stepCategory"
class="com.securance.vo.ApStepCategoryVO"
cascade="none"
outer-join="false"
update="true"
insert="true"
column="ap_step_category_id"
unique="true"
/>

<many-to-one
name="detail"
class="com.securance.vo.ApDetailLevelVO"
cascade="none"
outer-join="false"
update="true"
insert="true"
column="ap_detail_level_id"
/>

Both of those have the outer join functionality disabled which would result in extra queries.

Bleh, where's the edit function?

Anyways, my first statement of
"I don't think xdoclet supports lazy at the class level, although I am sure I saw another post asking if specifying the class as a proxy, just take a quick read through the post of the last day or so. "

Should read
I don't think xdoclet supports lazy at the class level, although I am sure I saw another post asking if specifying the class as a proxy was the same thing, just take a quick read through the post of the last day or so.

Maybe I am not understanding what the true meaning of outer-join is. I thught that if outer-join is set to false, then no extra queries would take place.

If outer join is TRUE then the system can select the data in a single (albeit possibly massive) SQL statement.