How to write Criteria Query that checks for multiple values in same column along with other WHERE conditions?

Question

Please convert the following SQL Query to a Criteria Query--------- I have the following table PEOPLE-

ID	Name	Code
1	Tom	A
2	Harry	B
3	Tom	C
4	John	A
5	Sally	C
6	Tom	B
7	Tom	D

The query must return the list of records that satisfies the conditions- Name='Tom' AND Code='A' or 'B' or 'C' ----In this Case only two records-----

ID	Name	Code
1	Tom	A
3	Tom	C
6	Tom	B

The SQL query will be something like this----

Select * from PEOPLE
where   NAME='TOM' and CODE in ('A' ,'B','C') ;

How do I convert this SQL query to a CriteriaQuery?

I could write the the criteriaQuery till the 1st where condition--

Criteria cr = session.createCriteria(People.class);
         cr.add(Restrictions.eq("name","Tom"));

Try pr restriction `Restrictions.or(Restrictions.eq("CODE", "A"),(Restrictions.eq("CODE", "B"));` — Amine ABBAOUI, Jun 16 '21 at 08:55
If possible please give the solution for the same but using 'NOT IN' instead of 'IN'-- — Arpan Gorai, Jun 16 '21 at 08:56
For not in : `Restrictions.and(Restrictions.ne("CODE", "A"),(Restrictions.ne("CODE", "B"));` — Amine ABBAOUI, Jun 16 '21 at 09:04
The fix is throwing error when I am adding more than 2 restrictions in OR. I have updated my questions description. Can you please go through it once again — Arpan Gorai, Jun 16 '21 at 11:59

score 0 · Accepted Answer · answered Jun 16 '21 at 13:58

You can try this code :

ArrayList<String> values= new ArrayList<String>();
list.add("A");
list.add("B");
list.add("C");

//Your creteria
Criteria cr = session.createCriteria(People.class);

//Array of conditions
List<Criterion> conditions = new ArrayList<Criterion>();

Criterion nameCr= Restrictions.eq("name", "Tom"); 
conditions.add(nameCr);

Criterion codeCr =Restrictions.in("code", values) ;
conditions.add(codeCr);


Conjunction conjunction = Restrictions.conjunction();

for (Criterion criterion : conditions) {
conjunction.add(criterion);
}

cr.add(conjunction);

How to write Criteria Query that checks for multiple values in same column along with other WHERE conditions?

1 Answers1