Hi All,
I am using
hibernate 3.0.5 with JDK1.5 on oracle 10G.
I want to know how I can nest Criteria within another Criteria to form subqueries
Given below is a very basic 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, Class, 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) {
//This is nested criteria to get the class of student where student's zip is 1234
DetachedCriteria dc = DetachedCriteria.forClass(Student.class);
dc.setProjection(Projections.property("studentClass"));
dc.createCriteria("address").add(Restrictions.eq("zip", zip));
//This is outer criteria to get all the students from the class returned by inner query
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.
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 criteria as well.
Thanks,
Sush
Login
FAQ
Search