How to use Hibernate Criteria objects for multiple and/or conditions

Question

I need to create a Hibernate criteria restriction that ors 3 Conditions. The problem is that the last condition is acutally to conditions using the AND operator.

My first condition:

Criterion startInRange = Restrictions.between("expectedStartCanonicDate", 
                                               rangeStart, rangeEnd);

My second condition:

Criterion endInRange = Restrictions.between("expectedCompletionCanonicDate", 
                                             rangeStart, rangeEnd);

MY third condition needs to AND the following two conditions together:

criteria.add(Restrictions.le("expectedStartCanonicDate", rangeStart));
criteria.add(Restrictions.ge("expectedCompletionCanonicDate", rangeEnd));

The restriction I want to do is condition1 or condition2 or condition3. I have found examples that get me close. I can use a LogicalExpression to or the first two conditions together, however, I'm not sure how to or 3 conditions, particularly when the last condition is really two separate conditions 'anded' together.

Any Thoughts? Fred

JB Nizet · Answer 1 · 2011-11-19T13:41:17.907

47

A sequence of restrictions linked with or is called a disjunction. A sequence of restrictions linked with and is called a conjunction.

So, what you need is

one conjunction for your third condition
one disjunction to link the first, second, and third conditions:

So here it goes:

Criterion startInRange = Restrictions.between("expectedStartCanonicDate", rangeStart, rangeEnd);

Criterion endInRange = Restrictions.between("expectedCompletionCanonicDate", rangeStart, rangeEnd);

Criterion thirdCondition = 
    Restrictions.conjunction().add(Restrictions.le("expectedStartCanonicDate", rangeStart))
                              .add(Restrictions.ge("expectedCompletionCanonicDate", rangeEnd));

Criterion completeCondition = 
    Restrictions.disjunction().add(startInRange)
                              .add(endInRange)
                              .add(thirdCondition);

criteria.add(completeCondition);

edited Nov 19 '11 at 13:41

answered Nov 19 '11 at 13:36

JB Nizet

678,734
91
1,224
1,255

thanks, those two really saved my life here. your example even covered the exact problem i had, with the addition that the `expectedCompletionCanonicDate` could be null as well in my case. So if your time-range should also fetch open frames you need to add a disjunction to the second add of the 'thirdCondition` _) line #7 would then be `.add(Restrictions.disjunction().add(Restrictions.ge("expectedCompletionCanonicDate", rangeEnd)).add(Restrictions.isNull("expectedCompletionCanonicDate")));` – Stoppal Feb 18 '14 at 08:36
@JBNizet Can you please help in to add multiple Criterion on run time? – Khatri Jan 10 '17 at 11:08
@JBNizet What is the default in add, is it and or or? – Akhil Dad Oct 01 '18 at 06:15
1

@AkhilDad if you're talking about Criteria.add(), it is "and". You could also just test it... – JB Nizet Oct 01 '18 at 06:18

score 1 · Answer 2 · answered Apr 25 '14 at 09:05

Criteria criteriaObj = sessionselectreply.createCriteria(TaskReplyVO.class,"taskreply")
                .createAlias("taskreply.objTaskView", "taskview")
                .createAlias("objMailTo", "mailto")
                .add(Restrictions.eq("taskview.longTaskId", taskid))
                .add(Restrictions.eq("mailto.longuserid", userid));

How to use Hibernate Criteria objects for multiple and/or conditions

2 Answers2

Linked