-->
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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 14 posts ] 
  Previous topic | Next topic 
Author Message
Miccy
 Post subject: Subselects: How to avoid HSQL-String-creating during runtime
PostPosted: Mon Jul 18, 2005 7:14 am 
Newbie

Joined: Mon Jul 18, 2005 6:59 am
Posts: 5
Hi folks,

just a question:

There are 2 entities: Employee and Qualification (Association: one to many).
One Qualification represents a composite of one skill and one level/rating.
Now I like to query over all employees to get employees, which match to a list of qualifications.

I think the approach is in HSQL:

e.g.: I like to get all employees which match minimum the qualifications Java-level 2 and C++-level 3:

from employee e where e in
(select e from Employee e join e.qualifications as q
where (q.skill.name = 'Java' and q.level.rating >= 2)) and e in
(select e from Employee e join e.qualifications as q
where (q.skill.name = 'C++' and q.level.rating >= 3)) and e in
...

What do you think? Is there a better way? First of all to avoid HSQL-String-creating during runtime?

Thanks,
Miccy.
Top
 Profile  
 
sergeya
 Post subject: Re: Subselects: How to avoid HSQL-String-creating during run
PostPosted: Mon Jul 18, 2005 7:28 am 
Regular
Regular

Joined: Thu Dec 02, 2004 7:11 am
Posts: 85
Miccy wrote:
First of all to avoid HSQL-String-creating during runtime?


Criteria API: http://www.hibernate.org/hib_docs/v3/re ... teria.html
Top
 Profile  
 
Miccy
 Post subject:
PostPosted: Mon Jul 18, 2005 8:00 am 
Newbie

Joined: Mon Jul 18, 2005 6:59 am
Posts: 5
I know Criteria API - but would you like to give me a hint how to bind the criterias:

Creating one qualification-criteria to restrict the result set is clear, but how to bind several criterias like the following...???

Criteria critEmp = session.createCriteria(Employee.class);
Criteria critQual = critEmp.createCriteria("qualifications");
Criteria critSkill = critQual.createCriteria("skill");
critSkill.add(Expression.eq("name", "Java"));
Criteria critLevel = critQual.createCriteria("level");
critLevel.add(Expression.ge("rating", new Integer(2)));
Top
 Profile  
 
sergeya
 Post subject:
PostPosted: Mon Jul 18, 2005 8:11 am 
Regular
Regular

Joined: Thu Dec 02, 2004 7:11 am
Posts: 85
Miccy wrote:
I know Criteria API - but would you like to give me a hint how to bind the criterias:

Creating one qualification-criteria to restrict the result set is clear, but how to bind several criterias like the following...???

Criteria critEmp = session.createCriteria(Employee.class);
Criteria critQual = critEmp.createCriteria("qualifications");
Criteria critSkill = critQual.createCriteria("skill");
critSkill.add(Expression.eq("name", "Java"));
Criteria critLevel = critQual.createCriteria("level");
critLevel.add(Expression.ge("rating", new Integer(2)));


Use DetachedCriteria & Subqueries. Something like this:

DetachedCriteria javaSkill = DetachedCriteria.forClass(Employee.class)
...
DetachedCriteria cppSkill = DetachedCriteria.forClass(Employee.class)
...


Criteria critEmp = session.createCriteria(Employee.class);
critEmp.add( Subqueries.in(javaSkill) );
critEmp.add( Subqueries.in(cppSkill) );


http://www.hibernate.org/hib_docs/v3/re ... hedqueries
Top
 Profile  
 
Miccy
 Post subject:
PostPosted: Mon Jul 18, 2005 8:20 am 
Newbie

Joined: Mon Jul 18, 2005 6:59 am
Posts: 5
Thanks a lot!

DetachedCriteria and Subqueries are available since v3.
My mistake, working with Hibernate V2-documentation...

Miccy.
Top
 Profile  
 
eagle79
 Post subject:
PostPosted: Mon Jul 18, 2005 8:44 am 
Senior
Senior

Joined: Tue Jun 21, 2005 10:18 am
Posts: 135
Location: South Carolina, USA
aside from the HQL creation at runtime, but wouldn't the following be the same, only better performance?

Code:
select e from Employee e join e.qualifications q
where (q.skill.name = 'Java' and q.level.rating >= 2) and
      (q.skill.name = 'C++' and q.level.rating >= 3)


Of course, you can improve this with various fetching strategies as needed.
Top
 Profile  
 
sergeya
 Post subject:
PostPosted: Mon Jul 18, 2005 9:25 am 
Regular
Regular

Joined: Thu Dec 02, 2004 7:11 am
Posts: 85
eagle79 wrote:
Code:
(q.skill.name = 'Java' and q.level.rating >= 2) and
      (q.skill.name = 'C++' and q.level.rating >= 3)



This expression would be always 'false'
Top
 Profile  
 
pksiv
 Post subject:
PostPosted: Mon Jul 18, 2005 10:12 am 
Expert
Expert

Joined: Mon Feb 14, 2005 12:32 pm
Posts: 609
Location: Atlanta, GA - USA
Miccy wrote:
I know Criteria API - but would you like to give me a hint how to bind the criterias:

Creating one qualification-criteria to restrict the result set is clear, but how to bind several criterias like the following...???

Criteria critEmp = session.createCriteria(Employee.class);
Criteria critQual = critEmp.createCriteria("qualifications");
Criteria critSkill = critQual.createCriteria("skill");
critSkill.add(Expression.eq("name", "Java"));
Criteria critLevel = critQual.createCriteria("level");
critLevel.add(Expression.ge("rating", new Integer(2)));




Look at the Restrictions.or() and Restrictions.and() methods for doing this with Criteria.

But your initial question about String creating can be handled by defining your strings as static final "constant" fields.
Code:
...
public static final String JAVA = "Java";
public static final String CPP = "C++";
...
...
Collection results =
         session.createCriteria(Employee.class)
            .createAlias("qualifications","qual")
            .createAlias("qual.skill","skill")
            .createAlias("qual.level","level")
            .add(Restrictions.or(
                      Restrictions.and(
                          Restrictions.eq("skill.name",JAVA),     
                          Restrictions.eq("level.rating",new Integer(2))
                      ),
                      Restrictions.and(
                          Restrictions.eq("skill.name",CPP),     
                          Restrictions.eq("level.rating",new Integer(3))
                      )))
            .list();
Top
 Profile  
 
Miccy
 Post subject:
PostPosted: Mon Jul 18, 2005 10:28 am 
Newbie

Joined: Mon Jul 18, 2005 6:59 am
Posts: 5
Quote:
But your initial question about String creating can be handled by defining your strings as static final "constant" fields.


Yes of course, but this was only an example. In reality maybe you will have a collection of needed qualifications...

Code:
            .add(Restrictions.or(
                      Restrictions.and(
                          Restrictions.eq("skill.name",JAVA),     
                          Restrictions.eq("level.rating",new Integer(2))
                      ),
                      Restrictions.and(
                          Restrictions.eq("skill.name",CPP),     
                          Restrictions.eq("level.rating",new Integer(3))
                      )))
            .list();


In this case you will not get the same as in my example -
here you will get all employees which match minimum one qualification...
Top
 Profile  
 
pksiv
 Post subject:
PostPosted: Mon Jul 18, 2005 10:39 am 
Expert
Expert

Joined: Mon Feb 14, 2005 12:32 pm
Posts: 609
Location: Atlanta, GA - USA
Miccy wrote:
Yes of course, but this was only an example. In reality maybe you will have a collection of needed qualifications...


If you have a more detailed question, please post it. We can't answer questions you don't ask. ;-)

With H3, you should be able to combine Example Qualifications with Projections and make it work for a collection of needed qualifications.


Quote:
In this case you will not get the same as in my example -
here you will get all employees which match minimum one qualification...


Ah, I misread that, just change the Restrictions.or() to Restrictions.and() and it gives you the correct results.
Top
 Profile  
 
sergeya
 Post subject:
PostPosted: Mon Jul 18, 2005 11:13 am 
Regular
Regular

Joined: Thu Dec 02, 2004 7:11 am
Posts: 85
pksiv wrote:
Ah, I misread that, just change the Restrictions.or() to Restrictions.and() and it gives you the correct results.


eagle79 already did this mistake, and you are repeating it here. Result would be wrong. Restrictions should be evaluated at different rows - simple AND is not suitable. Only subselects give you correct result.
Top
 Profile  
 
Miccy
 Post subject:
PostPosted: Tue Jul 19, 2005 3:12 am 
Newbie

Joined: Mon Jul 18, 2005 6:59 am
Posts: 5
@sergeya

I tried your example code, but unfortunetly is doesn't work,
maybe you can give me an additional hint?

Code:
ArrayList<DetachedCriteria> qualCrits = new ArrayList<DetachedCriteria>();
      Iterator<QualificationCriteria> minQualsIt = minQuals.iterator();
      while (minQualsIt.hasNext()) {
         QualificationCriteria crit = minQualsIt.next();
         DetachedCriteria empCrit = DetachedCriteria.forClass(Employee.class);
         DetachedCriteria qualCrit = empCrit.createCriteria("qualifications");
         DetachedCriteria skillCrit = qualCrit.createCriteria("skill");
         DetachedCriteria levelCrit = qualCrit.createCriteria("level");         
         skillCrit.add(Expression.eq("name", crit.getSkillName()));
         levelCrit.add(Expression.ge("rating", crit.getMinLevelRating()));
         qualCrits.add(empCrit);               
      }
            
      Criteria mainCrit = session.createCriteria(Employee.class);
      Iterator<DetachedCriteria> qualCritIt = qualCrits.iterator();
      while (qualCritIt.hasNext()) {
         mainCrit.add(Subqueries.in("userName", qualCritIt.next()));
      }
      return (Collection<Employee>)mainCrit.list();


I get following stack:

Code:
java.lang.NullPointerException
   at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:298)
   at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:56)
   at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:314)
   at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:85)
   at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:67)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1310)
   at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
   at de.mf.pm.Helper.findEmployeesByQualifications(Helper.java:74)
   at de.mf.pm.TestQuery.runAux(TestQuery.java:24)
   at de.mf.pm.HibernateBase.run(HibernateBase.java:25)
   at de.mf.pm.TestQuery.main(TestQuery.java:41)


Maybe one error is usage of subqueries, i am not really sure, that 'userName' is the right parameter...
DetachedCriteria work - I have tested it by calling
Code:
empCrit.getExecutableCriteria(session).list();
Top
 Profile  
 
sergeya
 Post subject:
PostPosted: Tue Jul 19, 2005 7:06 am 
Regular
Regular

Joined: Thu Dec 02, 2004 7:11 am
Posts: 85
Looks like there is needed an projection to correlate main query and subquery

Code:
   empCrit.setProjection(Projections.property("id"));
...

   mainCrit.add(Subqueries.in("id", qualCritIt.next()));


See also this message: http://forum.hibernate.org/viewtopic.php?t=945194
Top
 Profile  
 
sergeya
 Post subject:
PostPosted: Tue Jul 19, 2005 7:14 am 
Regular
Regular

Joined: Thu Dec 02, 2004 7:11 am
Posts: 85
sergeya wrote:
...Restrictions should be evaluated at different rows - simple AND is not suitable. Only subselects give you correct result.


Actualy there is another way - use multiply joins instead of subqueries. This may help in case of MySQL (which may doesn't support subqueries). Other side of coin - many DB have limits on numbers of joins in one query...
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 14 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.