I am using the Criteria API and am having trouble building a where clause like the following.

WHERE
(col1 = ? AND col2 IN (?) AND col3 = ?) OR
(col1 = ? AND col2 IN (?) ) AND col3 = ?) OR
(col1 = ? AND col2 IN (?) ) AND col3 = ?)

I need to use “AND” to evaluate 3 columns and “OR” for each line. The number of rows will be built dynamically within a loop.

I’ve tried this.

criteria.add(Restrictions.or(Restrictions.and(
Restrictions.eq(“col1”, col1 ),
Restrictions.eq(“col2”, col2)),
Restrictions.eq(“col3”, col3)));

But the SQL where clause it produced was this:

AND ( (this_.col1 = ? AND this_.col2 = ?) OR this_.col3 = ?

I’ve also tried using the conjunction and disjunction but couldn’t get that working correctly either.

I was able to find examples that show how to evaluate 2 columns but couldn’t get this to work for 3 columns.

disjunction.add(Restrictions.and(
Restrictions.eq(“cole1”, col1 ),
Restrictions.eq(“col2”, col2) ));

Is it possible to extend this example to include 3 restrictions?


Thanks for any help you can offer in solving this.

Restrictions.conjunction() and disjunction() let you add as many Criterions as you want, e.q.:

Results in
Which is what you want.

Rating appreciated. ;-)

double post

Thanks for the example but I am still having problems.

I am starting to wonder if this isn't a bug in my version of Hibernate - version 3.2.5



Session session = HibernatePersistenceManager.getInstance()
.getSession();

Criteria c = session.createCriteria(QuoteLinePSPartResalePR.class);

Conjunction c1 = Restrictions.conjunction();



// in my loop add to the criteria

c1.add(Restrictions.eq(quoteCol, quoteId)).add(
Restrictions.eq(lineCol, quoteLineNoId)).add(
Restrictions.eq(requestedQtyCol, requestedQty));
c.add(Restrictions.disjunction().add(c1));

// end loop



// then outside of the loop I execute

c.list();





Produces this SQL where clause:

WHERE (( this_.quote_id = ?
AND this_.quote_line_no = ?
AND this_.requested_qt = ?
)
)
AND (( this_.quote_id = ?
AND this_.quote_line_no = ?
AND this_.requested_qt = ?
)
)
AND (( this_.quote_id = ?
AND this_.quote_line_no = ?
AND this_.requested_qt = ?
)
)

Am I doing something wrong here?

thanks.

I found my mistake.

I needed to add the disjunction to the criteria outside of the loop. Everything looks good now.

Thanks again for your help.