How to create a query with multiple OR/AND conditions

gauravtaneja · **Joined:** Tue Feb 20, 2007 10:30 pm **Posts:** 3

I need to create a query which looks like this:

select * from table where table.column1='something' OR table.column1='something else' OR table.column1='something else else' OR .... to n

i looked at Expression.or but it only lets me specify two criteria, how do i add more?

Any help will be appreciated.

dharmendra.pandey · **Posted:** Tue Feb 20, 2007 11:22 pm

Hi gauravtaneja,

Try

Restrictions.Or()

or you could use HQL.

gauravtaneja · **Joined:** Tue Feb 20, 2007 10:30 pm **Posts:** 3

but Restrictions.or(Criterion lhs, Criterion rhs) takes only two arguments. i want to be able to write n numbers of OR statements.

as i said in my original post. select * from table where table.column='foo' OR table.column='foobar' OR table.column='fooblah' and so on....

how do i achieve that?

somu · **Posted:** Wed Feb 21, 2007 12:31 am

Restrictions.or() returns Criterion. So you can call Restrictions.or multiple times.

Hope that helps.
Thanks

i92absaj · **Posted:** Wed Feb 21, 2007 3:53 am

Hi,
As somu said, you can call Restriction.or several times, so you may code something like that:

Code:

// 3 restrictions
criterio.add(Restrictions.or(
   Restrictions.or(Restrictions.eq("column1", "1"),Restrictions.eq("column1", "2")),
   Restrictions.eq("column1", "3")
));

or

Code:

//4 restrinctions
criterio.add(Restrictions.or(
   Restrictions.or(Restrictions.eq("column1", "1"),Restrictions.eq("column1", "2")),
   Restrictions.or(Restrictions.eq("column1", "3"),Restrictions.eq("column1", "4"))
));

Also I think you could try in restriction. If you are going to use the same column (column1).

Code:

select * from table where table.column1 IN ('something', 'something else' , 'something else else'...)

gauravtaneja · **Joined:** Tue Feb 20, 2007 10:30 pm **Posts:** 3

Thanks a ton guys. this was really helpful

manoj.k · **Joined:** Wed Aug 12, 2009 1:34 pm **Posts:** 1

What if i need to write a query that matches partial values and returns all the items.

Equivalent sql should be
select * from table where propName like '%value1% or propName like '%value2% or propName like '%value3% or propName like '%value4%

I need to write a Hibernate criteria for the above, how can i do that?