Any ways to have single select query instead of multiple select queries in Hibernate?

I am using Spring + Hibernate(HQL).

I have a requirement where I need to fire select query multiple times as parameters are different each time.

For Example

SELECT * FROM MY_TABLE WHERE name=? and age=?

inputs will be

"John", 30
"Nick", 29
"Joe", 32

etc.. there could be any number of them.

This is leading to n number of queries where n is the number of inputs

Ex:

SELECT * FROM  MY_TABLE  WHERE name=John and age=30
SELECT * FROM  MY_TABLE  WHERE name=Nick and age=29 
SELECT * FROM  MY_TABLE  WHERE name=Joe and age=32

is there a way in hibernate where I can have only one query开发者_开发技巧 for this instead of multiple select queries

Ex:

SELECT * FROM MY_TABLE WHERE (name=John and age=30) or (name=Nick and age=29) or (name=Joe and age=32)

Or any other optimized way?

you could use UNION, though as far as I know, UNION was not supported in HQL. If this restriction still holds, then you may have to fall back to native SQL to use this

SELECT * FROM  MY_TABLE  WHERE name=John and age=30
UNION
SELECT * FROM  MY_TABLE  WHERE name=Nick and age=29 
UNION
SELECT * FROM  MY_TABLE  WHERE name=Joe and age=32

If you use a Criteria query, you could use a Disjunction:

Disjunction d = Restrictions.disjunction(); 
for(param p : params) {
   Conjunction c = Restrictions.conjunction();
   c.add(Restrictions.eq("name", p.getName()));
   c.add(Restrictions.eq("age", p.getAge()));
   d.add(c);    
}
criteria.add(d);