Hibernate 3.
I have a complex query I am trying to write using the createCriteria method. The problem comes in with using aliases in a sub query. Here is a SQL example
Code:
SELECT a.SUBNO, a.PERNO
FROM HMM a INNER JOIN HEE b on
(a.SUBNO = b.SUBNO) AND (a.PERNO = B.PERNO)
WHERE b.EENDT=(SELECT max(EENDT) FROM HEE t
WHERE t.SUBNO = a.SUBNO AND t.PERNO = a.PERNO)
AND a.NAME like var1
AND a.NUMBER = var2
Now, var1 and var2 are coming in from a web form. It is possible that we only have one var, and such, we would not want to add the restriction for that one if not there. Here is my code for that
Code:
List restrictions = new ArrayList();
if (form.getNumber() != null){
if (form.getNumber().length()>0){
restrictions.add(Restrictions.eq("number", form.getNumber()));
}
}
if (form.getName() != null){
if (form.getName().length() > 0 && !form.getName().equals(" ")){
restrictions.add(Restrictions.like("name", form.getName().toUpperCase() + "%"));
}
}
if (!restrictions.size()>0){
return null;
}else{
Conjuntion conj = Restrictions.conjunction();
Interator it = restrictions.iterator();
while (it.hasnext()){
conj.add((Restrictions) it.next());
}
This part seems fine. Now, how do I add the part in for the WHERE when I have a sub query in it that is referencing back to an aliais field in the parent query? (a.SUBNO & a.PERNO)
All mappings are in place and work fine. I have other Hibernate queries that are working, however, they do not have any subqueries in them
I have done this in a staight SQL, and could use that, however, I would like to use the built in functions (createCriteria) if possible.
Login
FAQ
Search
