In JDBC I can use question marks for query parameters, like this:
"SELECT * FROM users WHERE login = ?"
and then
ps.setString(1, "vasya");
But how can I query for list of logins:
"SELECT * FROM users WHERE login IN ?"
suppose, I have
List<String> logins = ...
What should I type there:
ps.setWhat(1, what);
I could rewrite query as:
"SELECT * FROM users WHERE login = ? OR login = ? OR login = ?"
and then call setString in loop, but I'd like to know if it is possible to pass a set of elements as single param in query.
Maybe there are vendor-specific extensions?
There are vendor specific ways to do that, therefore it would be good to know what database you use. I know solutions for PostgreSQL and H2. I implemented this feature in the H2 database, so that's what I know best:
H2 Database
PreparedStatement prep = conn.prepareStatement(
"select * from users where login in (select * from table(x int = ?))");
prep.setObject(1, new Object[] { "1", "2" });
ResultSet rs = prep.executeQuery();
PostgreSQL
WHERE login = ANY(?)
Then set the parameter to an array of values using PreparedStatement.setArray(..) (not setObject as for H2).
Look here for an overview of available options. As far as I can tell you, everyone is dynamically generating the necessary number of placeholder characters (with some optimizations).
There's a setArray method in PreparedStatement, but sometimes using it is not feasible. You might give it a try though.
If Spring's JDBCTemplate is an option, you could use automatic collection expansion as described here.
//---
String query = "SELECT * FROM users WHERE login = ?";
List<Login> list = new ArrayList<Login>();
Login login = null;
for(String param : conditions){
pStmt.setString(1,param);
rSet = pStmt.executeQuery();
if(rSet.next()){
login = new Login();
login.setName(rSet.getString(1));
list.add(login);
}
}
return list;
//---
conditions will be the list of item on basis of which you want to retrieve fields.
