Problem Description:
Im using Hibernate 3.1.3 and creating a fairly large criteria. Involved in this criteria are a few situations where I do something like:
Code:
List ids = new ArrayList(products.size());
Iterator iter = products.iterator();
while (iter.hasNext()) { ids.add(new Integer((Product) iter.next().getId()))); }
criteria.createCriteria("project.products", "prod").add(Expression.in("prod.id", ids));
Where there is a many-to-many relationship between Project and Product and I want to return only those Projects who have at least one of the products supplied as an argument to the search.
Now, this works fine so long as I dont have more than one of these many-to-many filtering situations.
For example, If I create a criteria with the following snip of code
Code:
List prodIds = new ArrayList(products.size());
Iterator iter = products.iterator();
while (iter.hasNext()) { prodIds.add(new Integer((Product) iter.next().getId()))); }
criteria.createCriteria("project.products", "prod").add(Expression.in("prod.id", prodIds));
List benIds = new ArrayList(benefits.size());
Iterator iter = benefits.iterator();
while (iter.hasNext()) { benIds.add(new Integer((Benefit) iter.next().getId()))); }
criteria.createCriteria("project.benefits", "ben").add(Expression.in("ben.id", benIds));
I get the sql below (Note: This is actual sql while the code above only represents the relevent portions of the criteria creation contributing to the problem).
Code:
select * from (
select this_.id as id22_9_
, this_.title as title22_9_
, this_.project_status_id as project3_22_9_
, this_.keywords as keywords22_9_
, this_.description as descript5_22_9_
, this_.problem_statemnet as problem6_22_9_
, this_.business_case as business7_22_9_
, this_.objectives as objectives22_9_
, this_.strategic_focus_id as strategic9_22_9_
, this_.risks as risks22_9_
, this_.department_id as department11_22_9_
, this_.kfa_id as kfa12_22_9_
, this_.qit_id as qit13_22_9_
, this_.facility_id as facility14_22_9_
, this_.project_initiative_id as project15_22_9_
, this_.priority_id as priority16_22_9_
, this_.rank as rank22_9_
, this_.leader_user_id as leader18_22_9_
, this_.sponsor_user_id as sponsor19_22_9_
, this_.mbb_user_id as mbb20_22_9_
, this_.mentor_user_id as mentor21_22_9_
, this_.team_memebers as team22_22_9_
, this_.other_resource as other23_22_9_
, this_.dependencies as depende24_22_9_
, this_.start_date as start25_22_9_
, this_.target_date as target26_22_9_
, this_.completion_date as completion27_22_9_
, this_.beneficiary_id as benefic28_22_9_
, this_.notes as notes22_9_
, this_.charter_approved as charter30_22_9_
, this_.auditing as auditing22_9_
, this_.status_id as status32_22_9_
, this_.created_by_user_id as created33_22_9_
, this_.created_date as created34_22_9_
, this_.modified_by_user_id as modified35_22_9_
, this_.modified_date as modified36_22_9_
, projectsta1_.id as id33_0_
, projectsta1_.name as name33_0_
, strategicg4_.id as id37_1_
, strategicg4_.name as name37_1_
, products13_.project_id as project1_
, prod8_.id as product2_
, prod8_.id as id21_2_
, prod8_.name as name21_2_
, orgunit6_.id as id18_3_
, orgunit6_.name as name18_3_
, kfa7_.id as id13_4_
, kfa7_.facility_id as facility2_13_4_
, kfa7_.department_id as department3_13_4_
, kfa7_.status_id as status4_13_4_
, kfa7_.name as name13_4_
, facility5_.id as id9_5_
, facility5_.name as name9_5_
, priority2_.id as id20_6_
, priority2_.name as name20_6_
, leader3_.id as id38_7_
, leader3_.division_id as division2_38_7_
, leader3_.facility_id as facility3_38_7_
, leader3_.status_id as status4_38_7_
, leader3_.username as username38_7_
, leader3_.first_name as first6_38_7_
, leader3_.last_name as last7_38_7_
, benefits20_.project_id as project1_
, ben9_.id as benefit2_
, ben9_.id as id3_8_
, ben9_.name as name3_8_
from
projects this_
left outer join project_status projectsta1_ on this_.project_status_id=projectsta1_.id
left outer join strategic_focuses strategicg4_ on this_.strategic_focus_id=strategicg4_.id
inner join project_products products13_ on this_.id=products13_.project_id
inner join products prod8_ on products13_.product_id=prod8_.id
left outer join departments orgunit6_ on this_.department_id=orgunit6_.id
left outer join key_focus_areas kfa7_ on this_.kfa_id=kfa7_.id
left outer join facilities facility5_ on this_.facility_id=facility5_.id
left outer join priorities priority2_ on this_.priority_id=priority2_.id
left outer join user_data leader3_ on this_.leader_user_id=leader3_.id
inner join project_benefits benefits20_ on this_.id=benefits20_.project_id
inner join benefits ben9_ on benefits20_.benefit_id=ben9_.id
where
1=1
and prod8_.id in (?, ?, ?)
and ben9_.id in (?, ?, ?, ?, ?)
and this_.status_id<>?
order by
lower(this_.title) asc
, this_.id asc
) where rownum <= ?
If I strip out the select * from () where rownum <= ? and execute the inner query, it works fine. Adding the wrapped query fails as Hibernate duplicates the column name used to map the linking table between the many to many attributes (did that make sense?).
Code:
...
, products13_.project_id as project1_
...
, benefits20_.project_id as project1_
...
where products13_ is project_products and benefits20_ is project_benefits.
Is this a known bug or is there some other workaround for avoiding this naming duplication? Im using Hibernate 3.1.3 and Oracle9Dialect (I've also tried OracleDialect with the same results).
I'll be happy to provide additional information if that would be helpful. As you can tell from the generated sql, there are quite a lot of mapping files and associations going on here, so hopefully the information I've provided will be sufficient to get a response.
Thanks
Login
FAQ
Search