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

Solution 1

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.

Solution 2

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

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

Solution 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

Solution 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());

View more solutions