How Do I Pass an Array of Strings Query String with the JdbcTemplate Query For Object Function?

Question

I would like to pass an array of strings to a stored procedure with jdbcTemplate and am having trouble doing so. This is the query:

"SELECT * from stored_procedure(?::text[])"

This is how I am calling the stored procedure with the jdbcTemplate (where notes is a List):

jdbcTemplate.queryForObject(sql, Long.class, notes == null ? null : notes.toArray());

This is the error I get:

PreparedStatementCallback; bad SQL grammar [SELECT * from stored_procedure(?::text[])]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [Ljava.lang.Object;. Use setObject() with an explicit Types value to specify the type to use.

I haven't found anything online for fixing this problem.

I don't recognize that notation. What database are you using? — Sotirios Delimanolis, Mar 07 '14 at 22:16
I have answered this same kind of question in below link http://stackoverflow.com/a/39357138/2147560 — Dhruvil Thaker, Sep 08 '16 at 07:56

Pavel Horal · Accepted Answer · 2014-03-07T23:31:46.290

You might use NamedParameterJdbcTemplate which supports named parameter binding (using :foo instead of ?) and collection parameter expansion (expanding :foo to ?, ?, ?). Then you can use:

jdbcTemplate.queryForObject("SELECT * FROM stored_procedure({:notes})", 
    Collections.singletonMap("notes", notes));

I don't think PostgreSQL JDBC driver supports array parameter types (i.e. you can not bind array to a single ?).

How Do I Pass an Array of Strings Query String with the JdbcTemplate Query For Object Function?

1 Answers1