Use NamedParameterJdbcTemplate to update array field
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?
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.
精彩评论