Hi All,
I am using hibernate 3.0.5 with JDK1.5 on oracle 9i.
I want to know how I can use Criteria API to have a self join.
Given below is the simplified example of what I want to achieve.
I have address table which has following columns
Address_ID, line, zip, country
I have Students table which has following columns
Student_ID, Name, Age, StudentClass, Division, Address_ID
So there is 1-1 relation between Student and Address
I have done the mapping for the same and also tested with basic HQL which is working fine.
Now, I want to get all the students in a given Class where one of the student's address zip is "1234"
For this I have written following code in by DAO implementation
Code:
public Collection<Student> find(String zip) {
DetachedCriteria dc = DetachedCriteria.forClass(Student.class);
dc.setProjection(Projections.property("studentClass"));
dc.createCriteria("address").add(Restrictions.eq("zip", zip));
Criteria c = getCurrSession().createCriteria(Student.class);
c.add(Property.forName("studentClass").in(dc));
return c.list();
}
But I keep on getting following error
ERROR org.hibernate.util.JDBCExceptionReporter - ORA-00904: "ADDRESSBEA1_"."ZIP": invalid identifier
When checked the logs the following query was generated
select this_.STUDENT_ID as STUDENT1_1_, this_.AGE as AGE3_1_, this_.NAME as NAME3_1_, this_.DIVISION as DIVISION3_1_, this_.CLASS as CLASS3_1_, addressbea2_.ADDRESS_ID as ADDRESS1_0_, addressbea2_.LINE1 as LINE2_2_0_, addressbea2_.ZIP as ZIP2_0_, addressbea2_.COUNTRY as COUNTRY2_0_ from Student this_, Address addressbea2_ where this_.STUDENT_ID=addressbea2_.ADDRESS_ID(+) and this_.CLASS in (select this0__.CLASS as y0_ from Student this0__ where addressbea1_.ZIP=?) the generated sub query is incorrect.
But, if run the following code without attaching the Criterias
Code:
DetachedCriteria dc = DetachedCriteria.forClass(Student.class);
dc.setProjection(Projections.property("studentClass"));
dc.createCriteria("address").add(Restrictions.eq("zip", zip));
return dc.getExecutableCriteria(getCurrSession()).list();
It runs fine. Ofcourse, its not what I want to achieve. The point is in this case query gets generated properly
Please let me know how I can get this working.
I want to use Criteria API only as this method may have other optional search parameters as well.
Thanks,
Sush
Login
FAQ
Search