-->
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
  [ 10 posts ] 
  Previous topic | Next topic 
Author Message
DavidAlves
 Post subject: Hibernate Querys and consecutive filtering
PostPosted: Fri May 12, 2006 7:34 am 
Newbie

Joined: Fri May 12, 2006 7:27 am
Posts: 4
Hibernate version: 2

I have the following problem. I have a complex filter (or set of filters) that I may (or may not, this is done according to method args) apply to a given collection.

It starts out with a standard query done in all instances that is something of the type.

Collection myFilteredResults = new HashSet();
Collection result1 = session.createQuery("query1");
myFilteredResults.addAll(result1);
Collection result2 = session.createQuery("query2);
myFilteredResults.addAll(result2)
...
...

After this standard query (and btw all the elements of the given collection are of the same type) I would like to apply consecutive filters (like a given date property between two dates or more importantly filter the collection by properties of associated persistent objects) that may or may not be active.

Is this possible to do???? (I tried with createFilter() and to my undestanding it is impossible to do it this way)

Any sugestions would be very much appreciated.

Thanks in advance.
Top
 Profile  
 
martindobias
 Post subject: use Criteria class
PostPosted: Fri May 12, 2006 10:52 am 
Newbie

Joined: Tue Feb 21, 2006 10:34 am
Posts: 7
I can recommend you the class Criteria (Session.createCriteria(...)). Then create filters by using Restrictions class and output projection by using Projections class (see Criteria class javadoc help).

I have found it much better when creating complex filter based on user input (number of conditions differ in each user request) then creating text querries.
Top
 Profile  
 
DavidAlves
 Post subject:
PostPosted: Mon May 15, 2006 4:22 am 
Newbie

Joined: Fri May 12, 2006 7:27 am
Posts: 4
Hi! thanks for the reply.

I'll try what you sugested. My problem is exactly that the querys are so dinamic/complex that string concatenation/processing would become a nightmare.

Btw... If you could post a very simple example it would be very much appreciated since I never seen the classes (Restrictions and Projections) at work although I have the hibernate in action book.
Top
 Profile  
 
martindobias
 Post subject: Criteria example
PostPosted: Mon May 15, 2006 5:02 am 
Newbie

Joined: Tue Feb 21, 2006 10:34 am
Posts: 7
The Criteria approach is very simple and straightforward (although it has still some limitations that I had to resolve by looking into hibernate source file and ended up with creating custom order class for example; but do not be scared, everything is possible)

See a very simplified example
Code:
Session session = sessionFactory.openSession(); // create session first

Criteria criteria = session.createCriteria(CallSummary.class, "call"); // criteria will start of table CALL_SUMMARY (AS call)
criteria.createAlias("call.iface", "fc", Criteria.LEFT_JOIN); // LEFT JOIN with table IFACE (AS fc)

criteria.add(Restrictions.ne("call.duration", new Long(0))); // first filter in WHERE clause => duration is not equal to 0

Disjunction disjunction = Restrictions.disjunction(); // another filter is disjunction - logical OR
disjunction.add(Restrictions.gt("call.min", new Float(10))); // CALL_SUMMARY.min > 10
disjunction.add(Restrictions.eq("fc.label", "Interface A"); // IFACE.label="Interface A"
criteria.add(disjunction); // set another filter - logical AND with previous filter

criteria.setProjection(Projections.rowCount()); // first I am interested only in number of rows that pass
int count = ((Integer)criteria.uniqueResult()).intValue();

criteria.addOrder(Order.asc("callId")); // order by CALL_SUMMARY.callId
criteria.setMaxResults(100); // get only first 100 results

criteria.setProjection(Projections.projectionList() // set custom projection (in SQL it is the SELECT clause definition)
  .add(Projections.min("score"))
  .add(Projections.avg("ratio"))); // SELECT MIN(CALL_SUMMARY.score), AVG(CALL_SUMMARY.ratio)

List list = criteria.list(); // get result

Simple, isn't it?
Top
 Profile  
 
DavidAlves
 Post subject:
PostPosted: Mon May 15, 2006 6:16 am 
Newbie

Joined: Fri May 12, 2006 7:27 am
Posts: 4
Big problem. I can't seam to have access to certain methods/classes/properties you named.

Like Criteria.LEFT_JOIN

maybe these are only present in hibernate 3?
Top
 Profile  
 
martindobias
 Post subject:
PostPosted: Mon May 15, 2006 6:27 am 
Newbie

Joined: Tue Feb 21, 2006 10:34 am
Posts: 7
DavidAlves wrote:
Big problem. I can't seam to have access to certain methods/classes/properties you named.

Like Criteria.LEFT_JOIN

maybe these are only present in hibernate 3?


Ups, sorry, this is my fault, I didn't see you are using hibernate 2. Yes you are right, this is new thing introduced in 3.1.3. Until this release you can only use INNER JOIN (default behaviour) - that is quite limiting, but maybe it is OK for you.
Top
 Profile  
 
DavidAlves
 Post subject:
PostPosted: Mon May 15, 2006 9:55 am 
Newbie

Joined: Fri May 12, 2006 7:27 am
Posts: 4
Yap the not allowing left joins makes it impossible to do it that way.

StringBuffer dynamic query here I come .. :(

Thanks for all the help though. Learned a lot of nice stuff.
Hope I can migrate to hibernate 3 soon.
Top
 Profile  
 
ouzo
 Post subject:
PostPosted: Mon May 29, 2006 6:30 am 
Regular
Regular

Joined: Wed May 05, 2004 8:01 am
Posts: 53
martindobias wrote:
DavidAlves wrote:
Big problem. I can't seam to have access to certain methods/classes/properties you named.

Like Criteria.LEFT_JOIN

maybe these are only present in hibernate 3?


Ups, sorry, this is my fault, I didn't see you are using hibernate 2. Yes you are right, this is new thing introduced in 3.1.3. Until this release you can only use INNER JOIN (default behaviour) - that is quite limiting, but maybe it is OK for you.


My question is: why isn't there CriteriaSpecification.RIGHT_JOIN. There is no way to do:

select select t, max(h.date) from Heartbeat h right join Terminal t group by t

using Criteria API. I could add Terminal.getHeartbeats() but I do not want to do that because there are thousands of heartbeats for every terminal.
Top
 Profile  
 
martindobias
 Post subject:
PostPosted: Mon May 29, 2006 11:43 am 
Newbie

Joined: Tue Feb 21, 2006 10:34 am
Posts: 7
I am sorry, I am not database professional at all. My database and SQL knowledge is quite limited.

Isn't "a right join b" same to "b left join a"?
Top
 Profile  
 
ouzo
 Post subject:
PostPosted: Mon May 29, 2006 12:03 pm 
Regular
Regular

Joined: Wed May 05, 2004 8:01 am
Posts: 53
It does in Hibernate if you have unidirectional association.
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
  [ 10 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.