I've been upgrading our sites DAO layer from Hibernate 2.1.7 to 3.0.4. I've had one big gotcha so far with my unit tests. A query that works fine in Hibernate 2.1.7 is breaking in Hibernate 3.0. The problem is with a primary key clause -> foreign key clause translation.
Normally Hibernate turns a
Code:
where contact.role.id is null
line into
Code:
where contact.fk_role_id is null
In one specific case this isn't happening with Hibernate 3 (even when I try and use the classic HQL parser -- although I might be getting something wrong there).
When I do a query like,
Code:
where contact.approval.role.id is null
(approval is an embedded object within contact -- ie translated to the same table -- and I think this is where the bug lies)
it comes out as
Code:
where (contact.fk_role_id=role.id) and (role.id is null)
this might work for some sql dialects but it doesn't work in postgresql and isn't even what hibernate2 used to generate.
Hibernate version: 3.0.4
Code between sessionFactory.openSession() and session.close():Code:
return getHibernateTemplate().find(
"select tr from TestResult tr " +
"join tr.role role " +
"join tr.test test " +
"inner join tr.testSchedule testSchedule " +
"join tr.role.user.contact contact " +
"where role.associationGrouping.id=? " +
"and tr.approval.role.id is null " +
"and contact.approval.approved = 't' " +
"order by tr.test.name asc, tr.testSchedule.name asc, " +
"contact.lastName asc, contact.firstName", new Object[] {
associationGrouping.getId()
});
Name and version of the database you are using:
PostGRESql 7.4.8
The generated SQL (show_sql=true):
With Hibernate3 (incorrect)
Quote:
select testresult0_.id as id, testresult0_.version as version5_, testresult0_.fk_role_id as fk3_5_, testresult0_.fk_test_id as fk4_5_, testresult0_.approval_time_stamp as approval5_5_, testresult0_.fk_approval_role_id as fk6_5_, testresult0_.approved as approved5_, testresult0_.time_taken_timestamp as time8_5_, testresult0_.calculated_score as calculated9_5_, testresult0_.fk_test_schedule_id as fk10_5_ from test_result testresult0_ inner join Role role1_ on testresult0_.fk_role_id=role1_.id inner join app_user user5_ on role1_.fk_user_id=user5_.id inner join Address contact6_ on user5_.fk_contact_id=contact6_.id inner join test test2_ on testresult0_.fk_test_id=test2_.id inner join test_schedule testschedu3_ on testresult0_.fk_test_schedule_id=testschedu3_.id, Role role7_ where testresult0_.fk_approval_role_id=role7_.id and role1_.fk_association_grouping_id=? and (role7_.id is null) and contact6_.approved='t' order by test2_.name asc , testschedu3_.name asc , contact6_.last_name asc , contact6_.first_name
With Hibernate2 (correct)
Quote:
select testresult0_.id as id, testresult0_.version as version, testresult0_.fk_role_id as fk_role_id, testresult0_.fk_test_id as fk_test_id, testresult0_.approval_time_stamp as approval5_, testresult0_.fk_approval_role_id as fk_appro6_, testresult0_.approved as approved, testresult0_.time_taken_timestamp as time_tak8_, testresult0_.calculated_score as calculat9_, testresult0_.fk_test_schedule_id as fk_test10_ from test_result testresult0_ inner join role role1_ on testresult0_.fk_role_id=role1_.id inner join test test2_ on testresult0_.fk_test_id=test2_.id inner join test_schedule testschedu3_ on testresult0_.fk_test_schedule_id=testschedu3_.id inner join app_user user4_ on role1_.fk_user_id=user4_.id inner join address contact5_ on user4_.fk_contact_id=contact5_.id where (role1_.fk_association_grouping_id=? )and(testresult0_.fk_approval_role_id is null )and(contact5_.approved='t' ) order by test2_.name asc , testschedu3_.name asc , contact5_.last_name asc , contact5_.first_name
[/quote]
Login
FAQ
Search
