How to set list of parameters on prepared statement?

Question

i have a list of names e.g.:

List<String> names = ...
names.add('charles');
...

and a statement:

PreparedStatement stmt = 
  conn.prepareStatement('select * from person where name in ( ? )');

how to do the following:

stmt.setParameterList(1,names);

Is there a workaround? can someone explain why this method is missing?

using: java, postgresql, jdbc3

Answer 1

This question is very old, but nobody has suggested using setArray

This answer might help https://stackoverflow.com/a/10240302/573057

Answer 2

There's no clean way to do this simply by setting a list on the PreparedStatement that I know of.

Write code that constructs the SQL statement (or better replaces a single ? or similar token) with the appropriate number of questions marks (the same number as in your list) and then iterate over your list setting the parameter for each.

Answer 3

this method is missing due to type erasure the parameter type of the List is lost at runtime. Therefore the need to add several methods arires: setIntParameters, setLongParameters, setObjectParameters, etc

Answer 4

For postgres 9 I have used this approach:

 jdbcTemplate.query(getEmployeeReport(), new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setTimestamp(1, new java.sql.Timestamp(from.getTime()));
            ps.setTimestamp(2, new java.sql.Timestamp(to.getTime()));
            StringBuilder ids = new StringBuilder();
            for (int i = 0; i < branchIds.length; i++) {
                ids.append(branchIds[i]);
                if (i < branchIds.length - 1) {
                    ids.append(",");
                }
            }
            // third param is inside IN clause
            // Use Types.OTHER avoid type check while executing query  
            ps.setObject(3, ids.toString(), **Types.OTHER**);
        }
    }, new PersonalReportMapper());

Answer 5

In case the questions' meaning is to set several params in a single call...

Because the type validation is already defined in a higher level, I think the only need is for setObject(...).

Thus, a utility method can be used:

public static void addParams(PreparedStatement preparedStatement, Object... params) throws SQLException {
    for (int i = 0; i < params.length; i++) {
        Object param = params[i];
        preparedStatement.setObject(i+1, param);
    }
}

Usage:

SqlUtils.addParams(preparedStatement, 1, '2', 3d);

Feel free converting this to a Java 8 lambda :)

Answer 6

I was reviewing code this morning and one of my colleagues had a different approach, just pass the parameter using setString("name1','name2','name3").

Note: I skipped the single quote at the beginning and end because these are going to be added by the setString.

Answer 7

After examining various solutions in different forums and not finding a good solution, I feel the below hack I came up with, is the easiest to follow and code. Note however that this doesn't use prepared query but gets the work done anyway:

Example: Suppose you have a list of parameters to pass in the 'IN' clause. Just put a dummy String inside the 'IN' clause, say, "PARAM" do denote the list of parameters that will be coming in the place of this dummy String.

    select * from TABLE_A where ATTR IN (PARAM);

You can collect all the parameters into a single String variable in your Java code. This can be done as follows:

    String param1 = "X";
    String param2 = "Y";
    String param1 = param1.append(",").append(param2);

You can append all your parameters separated by commas into a single String variable, 'param1', in our case.

After collecting all the parameters into a single String you can just replace the dummy text in your query, i.e., "PARAM" in this case, with the parameter String, i.e., param1. Here is what you need to do:

    String query = query.replaceFirst("PARAM",param1); where we have the value of query as 

    query = "select * from TABLE_A where ATTR IN (PARAM)";

You can now execute your query using the executeQuery() method. Just make sure that you don't have the word "PARAM" in your query anywhere. You can use a combination of special characters and alphabets instead of the word "PARAM" in order to make sure that there is no possibility of such a word coming in the query. Hope you got the solution.

Answer 8

Other method :

public void setValues(PreparedStatement ps) throws SQLException {
    // first param inside IN clause with myList values
    ps.setObject(1 , myList.toArray(), 2003); // 2003=array in java.sql.Types
}