JDBC: in set condition: can I pass a set as single param?

Question

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?

Answer 1

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.

Answer 2

//---

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.

Answer 3

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 (?)");
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).