Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
v3.0.5

Name and version of the database you are using:
DB2

Sorry in advance for any lack of detail, but I am trying to help a friend. He is wondering if it was possible to run a query with multiple outer left joins. A SQL query that would look something like this.

SELECT * FROM parent
left outer join child1 on key = key
left outer join child2 on key = key
left outer join child3 on key = key
WHERE parent.name like 'JI%'

His mapping file has multiple one-to-many mappings as Sets.

The documentation says one one collection role can be fetched in a query, however there are examples of multiple joins (although none with more than one left join).

Yes, you are able to do as many left outer join between objects/tables as you want.

You dont have to mention the "ON key=key2" because the "on clause" is already explicitly said in your mapping.

This is an example of my HQL:



Regards,

Etienne.

Thank you for your reply. I looked in the "advanced problems" section of the Hibernate FAQ and it says this about the left outer joins:

Note also that Hibernate does not support outer join fetching of more than one many-valued association in the same query.

How does your HQL query avoid/get around this?

As of 3.0.5, this last statement is no longer true. We now allow cartesian products, even if they perform worse than two separate queries.

In this situation, no matter how I handle it, hibernate is still generating all of the selects for the "child" entities. I've inherited the task of fixing a performance issue around hibernate. As is stated earlier in this discussion we have a Parent table and it has a 1 to many relationship with 4 child tables. So we started with a basic mapping indicating the relationship, when we do a find on the parent record the subsequent selects are killing the processer on our mainframe (don't laugh). It queries the parent table and then for each record returned runs a select against each of the four child tables.

So we started down the path of using outer join in it's various incantations in hibernate. No matter what we do the most we can do is eliminate one of the subsequent selects via the outer join and all the rest still generate queries. (Hibernate seems to ignore any subsequent outer joins.)

I tried using native SQL but it still generated the subselects because I had the relationships in the mapping. So I pulled the relationships out and it only generated the one outer join query I wanted...but of course had no idea how to build the collections within the parent object.

So thus far I've concluded that I can not invoke more than 1 mapped left outer join between a parent and it's children. We are currently using 3.0.5. Here's some of my mapping info.

It's DB2 on the z/OS platform.
Hibernate 3.0.5

The important pieces of the mapping file:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
...
<class name="com.excellus.mrp.model.MasterRatePool"
schema="mrp"
table="mstr_rate_pool">
<meta attribute="implement-equals">true</meta>

<id name="mrpUniqueId"
type="long"
column="mstr_rate_pool_id">
<generator class="identity"/>
</id>

...

<set name="facetsRatePools" inverse="true" lazy="false" >
<key column="mstr_rate_pool_id"/>
<one-to-many class="com.excellus.mrp.model.FacetsRatePool"/>
</set>

<set name="lrspRatePools" inverse="true" lazy="false">
<key column="mstr_rate_pool_id"/>
<one-to-many class="com.excellus.mrp.model.LrspRatePool"/>
</set>

<set name="mabilRatePools" inverse="true" lazy="false">
<key column="mstr_rate_pool_id"/>
<one-to-many class="com.excellus.mrp.model.MabilRatePool"/>
</set>

<set name="mhsRatePools" inverse="true" lazy="false">
<key column="mstr_rate_pool_id"/>
<one-to-many class="com.excellus.mrp.model.MhsRatePool"/>
</set>

</class>
<!--
<query name="com.excellus.mrp.dao.hibernate.findByName">
<![CDATA[
from com.excellus.mrp.model.MasterRatePool as rp
where ucase(rp.poolName) like :poolName
]]>
</query> -->
<query name="com.excellus.mrp.dao.hibernate.findByName">
<![CDATA[
select mrp, facetsRP, lrspRP, mabilRP, mhsRP
from com.excellus.mrp.model.MasterRatePool mrp
left join mrp.facetsRatePools facetsRP
left join mrp.lrspRatePools lrspRP
left join mrp.mabilRatePools mabilRP
left join mrp.mhsRatePools mhsRP
where ucase(mrp.poolName) like :poolName
]]>
</query>

Note that the query that's commented out is how we originally tried it.
The follow up query was an attempt to do the multiple outer joins that still does all the sub selects.
I tried native sql like this:

// org.hibernate.SQLQuery query = HibernateUtil.getSession().createSQLQuery(FIND_BY_NAME_QUERY);
// query.addEntity("MRP", MasterRatePool.class);
// query.addEntity("FACETS", FacetsRatePool.class);
// query.addEntity("LRSP", LrspRatePool.class);
// query.addEntity("MABIL", MabilRatePool.class);
// query.addEntity("MHS", MhsRatePool.class);
// query.setString("poolName", name.toUpperCase());

// query.addEntity("MRP", MasterRatePool.class);
// query.addJoin("FACETS", "MRP.facetsRatePools");
// query.addJoin("LRSP", "MRP.lrspRatePools");
// query.addJoin("MABIL", "MRP.mabilRatePools");
// query.addJoin("MHS", "MRP.mhsRatePools");
// query.setString("poolName", name.toUpperCase());


// Collection pools = query.list();


Oh yeah here's the query:

SELECT {MRP.*},
{FACETS.*},
{LRSP.*},
{MABIL.*},
{MHS.*}
FROM mrp.mstr_rate_pool MRP
LEFT OUTER JOIN mrp.facets_mrp_lookup FACETS ON
MRP.mstr_rate_pool_id = FACETS.mstr_rate_pool_id
LEFT OUTER JOIN mrp.lrsp_mrp_lookup LRSP ON
MRP.mstr_rate_pool_id = LRSP.mstr_rate_pool_id
LEFT OUTER JOIN mrp.mabil_mrp_lookup MABIL ON
MRP.mstr_rate_pool_id = MABIL.mstr_rate_pool_id
LEFT OUTER JOIN mrp.mhs_mrp_lookup MHS ON
MRP.mstr_rate_pool_id = MHS.mstr_rate_pool_id
WHERE UPPER(MRP.mstr_rate_pool_nm) LIKE :poolName

Where I first tried the addEntity and then the addJoin but the object doesn't populate right either way.

I feel like we've tried every flag but it's probably just a fundamental misunderstanding of the implementation details, but I don't see how you can do more than one left outer join and eliminate all the queries.

I apologize if this is too much detail but I want to make sure we're all on the same page. Any help is greatly appreciated. Thanks to chocodile for helping me hunt this down.
-g888

Looks to me like you have not understood the difference between "left join" and "left join fetch".