-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 12 posts ] 
Author Message
 Post subject: Criteria Query accross multiple subclasses
PostPosted: Mon Apr 17, 2006 5:26 pm 
Newbie

Joined: Mon Apr 17, 2006 5:10 pm
Posts: 3
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!


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 18, 2006 7:33 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 18, 2006 9:32 pm 
Newbie

Joined: Mon Apr 17, 2006 5:10 pm
Posts: 3
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 18, 2006 10:44 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
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:
Code:
DetachedCriteria textCriteria = createTextDocCriteria(textParams);
DetachedCriteria xmlCriteria = createXMLDocCriteria(xmlParams);

Criteria mainCrit = session.createCriteria(Document.class);
mainCrit.add(Restrictions.disjunction()
             .add(Subqueries.eq("this", textCriteria))
             .add(Subqueries.eq("this", xmlCriteria));
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).


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 18, 2006 10:48 pm 
Newbie

Joined: Mon Apr 17, 2006 5:10 pm
Posts: 3
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...


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 19, 2006 12:31 am 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
The equivalent for DetachedCriteria throughout is:
Code:
DetachedCriteria textCriteria = createTextDocCriteria(textParams);
DetachedCriteria xmlCriteria = createXMLDocCriteria(xmlParams);

DetachedCriteria mainCrit = DetachedCriteria.forClass(Document.class);
mainCrit.add(Restrictions.disjunction()
             .add(Subqueries.eq("this", textCriteria))
             .add(Subqueries.eq("this", xmlCriteria));


Top
 Profile  
 
 Post subject: null pointer exception using subqueries with subclasses
PostPosted: Thu Apr 27, 2006 2:18 am 
Beginner
Beginner

Joined: Thu Jun 23, 2005 4:11 pm
Posts: 24
I'm trying to use the technique you describe above and keep getting a null pointer exception. any ideas?


Code:
Record
RecordA : Record {
  String aprop;
}
RecordB : Record {
  String bprop;
}
...
      DetachedCriteria aCriteria = DetachedCriteria.forClass(RecordA.class).add(Restrictions.isNotNull("aprop"));
      DetachedCriteria bCriteria = DetachedCriteria.forClass(RecordB.class).add(Restrictions.isNotNull("bprop"));
      DetachedCriteria allCriteria = DetachedCriteria.forClass(Record.class);
      allCriteria.add(Restrictions.disjunction().add(Subqueries.eq("this", aCriteria)).add(Subqueries.eq("this", bCriteria)));
      List records = allCriteria.getExecutableCriteria(session).list();
...


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)


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 27, 2006 2:51 am 
Expert
Expert

Joined: Tue Apr 25, 2006 12:04 pm
Posts: 260
Could you verify if the following code is working

Code:
DetachedCriteria aCriteria = DetachedCriteria.forClass(RecordA.class).add(Restrictions.isNotNull("aprop"));
DetachedCriteria bCriteria = DetachedCriteria.forClass(RecordB.class).add(Restrictions.isNotNull("bprop"));

Criteria allCriteria = session.createCriteria(Record.class);
allCriteria.add(Restrictions.disjunction().add(Subqueries.eq("this", aCriteria)).add(Subqueries.eq("this", bCriteria)));
List records = allCriteria.list();


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.


Top
 Profile  
 
 Post subject: same error
PostPosted: Thu Apr 27, 2006 3:00 am 
Beginner
Beginner

Joined: Thu Jun 23, 2005 4:11 pm
Posts: 24
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.
Code:
DetachedCriteria aCriteria = DetachedCriteria.forClass(RecordA.class).add(Restrictions.isNotNull("aprop")).setProjection(Projections.id();
DetachedCriteria bCriteria = DetachedCriteria.forClass(RecordB.class).add(Restrictions.isNotNull("bprop")).setProjection(Projections.id();
DetachedCriteria allCriteria = DetachedCriteria.forClass(Record.class);
allCriteria.add(Restrictions.disjunction().add(Subqueries.eq("this", aCriteria)).add(Subqueries.eq("this", bCriteria)));
List records = allCriteria.getExecutableCriteria(session).list();

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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 27, 2006 3:10 am 
Expert
Expert

Joined: Tue Apr 25, 2006 12:04 pm
Posts: 260
OK, I accept, I am making wild guess.

Maybe you put aliases in the DetachedCriteria as like below

Code:
DetachedCriteria aCriteria = DetachedCriteria.forClass(RecordA.class, "a").add(Restrictions.isNotNull("aprop"));
DetachedCriteria bCriteria = DetachedCriteria.forClass(RecordB.class, "b").add(Restrictions.isNotNull("bprop"));

Criteria allCriteria = session.createCriteria(Record.class);
allCriteria.add(Restrictions.disjunction().add(Subqueries.eq("this", aCriteria)).add(Subqueries.eq("this", bCriteria)));
List records = allCriteria.list();


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 27, 2006 3:17 am 
Expert
Expert

Joined: Tue Apr 25, 2006 12:04 pm
Posts: 260
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

Code:
select columns from tableA a where ? in ( select columnb from tableb )


in my case.

When I used aliases in DetachedCriteria and modified my code as
Code:
Property.forName( "alias.column" ).in( DetachedCriteria )

it worked for me.


Top
 Profile  
 
 Post subject: Re: Criteria Query accross multiple subclasses
PostPosted: Mon Aug 18, 2014 11:40 am 
Newbie

Joined: Mon Aug 18, 2014 10:35 am
Posts: 3
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 12 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.