Hello all,

I've got an advanced search form going on, and therefore am trying to employe the Criteria API to meet my requirements.

I'm working with an inheritance hierachy mapped with a discriminator, each subclass having it's own table.

For the purposes of this question lets say it's a Document document hierachy...

I've got a Document parent class... and then there are subclasses:
XMLDocument.class
TextDocument.class
BinaryDocument.class

each with their own attributes...

I'd like to create a Critera query off of the Document.class and apply restrictions on some of the fields in subclass document types...some of which are associations in other tables.

The problem is that if I use .createAlias to join the tables I get a bunch of inner joins....well of course because they are subtypes you're never going to have an inner join across them because they are mutally exclusive.

If I was doing this with straight sql, I would utilize left outer joins along with inner joins where appropriate.

Is there a way to create a single Criteria query based off the parent class that will meet my requirements?

Thanks in Advance!

Yep. Don't use aliases, use disjunctions to OR the various queries together. You create complete Criteria or DetachedCritieria for each disjoint subclass, then or them together using disjunctions. Section 15 of the ref docs have all the info, though unfortunately no examples for exactly what you're doing.

Could you provide an example to show me how this might work?

DetachedCriteria dCritera = DetachedCriteria.forClass(Document.class);

dCritera.add(Restrictions.disjunction().add(????);

How do I specify fields that are in the subclasses without aliases...

I mean I could see how it would work if the element was directly contained within the subclass...but again...what about fields that are in Collections mapped accross several tables...where joins need to take place before a where clause will work.

Say XMLDocuments has a collection of Schema objects and Schema's have list of authors.

On the other hand, say

TextDocument has a collection of Authors.

When I do my advanced search I want to search for document authors....
I'd want it to return TextDocuments with that author as well as XMLDocuments who's got a schema that was authored by the person I'm searching for.

(I know it's an odd example, it's not the one I'm implementing... but it still should be valid for arguments sake.)

I don't see how that will work with, because I can't get tables to join without aliasing or creating new criteria which result in inner joins...and that would have no results because you'll never have a single document where a row in the underlying TextDocument table has a matching row in the table used to persist Schemas.

The way I've solved this for now is to create seperate criteria queries on the subclasses.

DetachedCriteria textCritera = DetachedCriteria.forClass(TextDocument.class);
DetachedCriteria xmlCritera = DetachedCriteria.forClass(XMLDocument.class);

xmlCriteria.createAlias("schemas","schema").createAlias("schema.authors","schemaAuthor").add(Restrictions.eq("schemaAuthor",authorString);

textCriteria.createAlias("authors","textAuthor").add(Restrictions.eq("textAuthor",authorString);

and then basically present a union of the results to the user. But that requires a lot more if()'s in my code....so if possible....I'd rather create a single DetachedCriteria against Document.class if it's possible.

You're on the right track, but after you've created the various DetachedCriteria (you can also use Criteria, it might be simpler if you do that), you or them together:There are many ways to or multiple criteria together, this is just one example. Have a look through the Criteria javadocs, along with Restrictions, Subqueries and Projections. The ultimate goal is to do exactly what you're doing already, just do it in a single SQL statement (which allows you to avoid all those conditionals).

I'm using a DetachedCriteria...because I'm using Spring's HibernateTemplate....and their findByCriteria (or whatever that method is called) although I'm sure I could get access to the Session directly if I wanted to....

I'll play around with it and report back...

The equivalent for DetachedCriteria throughout is:

I'm trying to use the technique you describe above and keep getting a null pointer exception. any ideas?




running
List records = aCriteria.getExecutableCriteria(session).list();
and
List records = bCriteria.getExecutableCriteria(session).list();
work.

Exception in thread "main" java.lang.NullPointerException
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:318)
at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:56)
at org.hibernate.criterion.Junction.toSqlString(Junction.java:58)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:334)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:82)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:68)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1517)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at us.sotech.test.hibernate.SubqueryTest.main(SubqueryTest.java:42)

Could you verify if the following code is working



If it works, then maybe 'this' usage is not applicable with DetachedCriteria. I dont know any reason as of yet but am researching more, so cant give you straightforward answer for now.

-- update
Sorry - there was similar post already above. Didnt noticed that one.

Sorry I didn't mention in the previous post but I did try that method also and received the same error.

I did try doing this based on another post.

This actually gets past the parsing phase and generates the SQL but still doesn't know what to do with the "this".

Exception in thread "main" java.lang.ClassCastException: java.lang.String
at org.hibernate.type.LongType.set(LongType.java:42)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:83)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:65)
at org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1514)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1576)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:95)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1536)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at us.sotech.test.hibernate.SubqueryTest.main(SubqueryTest.java:32)
2006-04-27 02:57:37,169 [main] DEBUG LongType:80 - binding 'this' to parameter: 1
2006-04-27 02:57:37,169 [main] INFO LongType:87 - could not bind value 'this' to parameter: 1; java.lang.String

OK, I accept, I am making wild guess.

Maybe you put aliases in the DetachedCriteria as like below

Actually, accept my apologies for posting very quickly without reading the exact error.

I came across the error what you posted. Basically the generated query was



in my case.

When I used aliases in DetachedCriteria and modified my code as

it worked for me.

Hi,

I'm having the same problem as genekhart

I have two classes

a super class Rent

class Rent {

Long idRent

Date dateRent

... }

and a subclass CarRent

class CarRent extends Rent {

Car car
String typeCar

....
}

class Car {

Long idCar

....

}
I would like to write this SQL request using criteria (in the sub query I look for the latest rent date for each car, and then I look for rent information related to the retrieved couple (idCar, dateRent)

select * from CarRent a, Rent b
where a.idRent = b.idRent
and (b.idCar,a.dateRent) in (
select b.idCar, max(a.dateRent)
from Rent a, CarRent b
where a.idRent = b.idRent
group by (idCar)
)


in criteria, here it is my code

DetachedCriteria inner = DetachedCriteria.forClass(CarRent.class, "inner")
.setProjection(Projections.projectionList().add(Projections.max("inner.dateRent")).add(Projections.groupProperty("inner.car")));

and

Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(Rent.getClass()).add(example);
criteria.createAlias("car", "car");

all I could do is the condition separately
criteria.add(Property.forName("car.idCar").in(inner));
criteria.add(Property.forName("dateRent").in(inner));

but it's obviously not what i want to do

Any ideas how to perform (property1, property2) in ( (v1,v2), (v1,v2)..... )?

Thanks guys for your help