Use NamedParameterJdbcTemplate to update array field

Question

I have a double precision array field dblArrayFld in a table myTable and I'd like to update it using Spring's NamedParameterJdbcTemplate (I'm using Postgres).

I'm running code like this:

SqlParameterSource params = (new MapSqlParameterSource())
  .addValue("myarray", myDblArrayListVar)
  .addValue("myid", 123);

namedJdbcTemplate.update("UPDATE myTable SET dblArrayFld = :myarray WHERE idFld = :myid", params);

This returns an error that reads syntax error at or near "$2"

I'm assuming my syntax on :myarray is at fault here. I've also tried encasing :myarray in the following ways:

• dblArrayFld={:myarray}
• dblArrayFld={ :myarray }
• dblArrayFld=[:myarray]
• dblArrayFld=ARRAY[:myarray]
• dblArrayFld=(:myarray)

What's the correct syntax here?

Answer 1

Wehn you try to bind Collection or array as named parameter, NamedParameterJdbcTemplate explodes the appropriate named parameter in your statement into a number of positional parameters matching the length of your array / collection. This is useful for WHERE column IN (:param) statements, but is not going to work in this case.

In order to set an actual Postgres array you have to supply your parameter as java.sql.Array. You can create its instance using Connection.createArrayOf() method.