How to update a postgresql array column with spring JdbcTemplate?

Question

Hi, I'm using Spring JdbcTemplate, and I'm stuck at the point where I have a query that updates a column that is actually an array of int. The database is postgres 8.3.7. This is the code I'm using :

public int setUsersArray(int idUser, int idDevice, Collection<Integer> ids) {

 int update = -666;

 int[] tipi = new int[3];
 tipi[0] = java.sql.Types.INTEGER;
 tipi[1] = java.sql.Types.INTEGER;
 tipi[2] = java.sql.Types.ARRAY;

 try {
  update = this.jdbcTemplate.update(setUsersArrayQuery, new Object[] {
    ids, idUser, idDevice }, tipi);
 } catch (Exception e) {
  e.printStackTrace();
 }
 return update;
}

The query is "update table_name set array_column = ? where id_user = ? and id_device = ?". I get this exception :

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [update acotel_msp.users_mau set denied_sub_client = ? where id_users = ? and id_mau = ?]; The column index is out of range: 4, number of columns: 3.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3.

Caused by: org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3.

I've looked into spring jdbc template docs but I can't find any help, I'll keep looking, anyway could someone point me to the right direction? Thanks!

EDIT :

Obviously the order was wrong, my fault...

I tried both your solutions, in the first case I had this :

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update users set denied_sub_client = ? where id_users = ? and id_device = ?]; nested exception is org.postgresql.util.PSQLException: Cannot cast an instance of java.util.ArrayList to type Types.ARRAY

Trying the second solution I had this :

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update users set denied_sub_client = ? where id_users = ? and id_device = ?]; nested exception is org.postgresql.util.PSQLException: Cannot cast an instance of [Ljava.lang.Object; to type Types.ARRAY

I suppose i need an instance of java.sql.Array, but how can I create it using JdbcTemplate?

Answer 1

The argument type and argument is not matching.

Try changing the argument type order

int[] tipi = new int[3];
tipi[0] = java.sql.Types.ARRAY;
tipi[1] = java.sql.Types.INTEGER;
tipi[2] = java.sql.Types.INTEGER;

or use

update = this.jdbcTemplate.update(setUsersArrayQuery, new Object[] {
                                ids.toArray(), idUser, idDevice })

and see if it works

Answer 2

http://valgogtech.blogspot.com/2009/02/passing-arrays-to-postgresql-database.html explains how to create java.sql.Array postgresql basically Array.getBaseTypeName should return int and Array.toString should return the array content in "{1,2,3}" format

after you create the array you can set it using preparedstatement.setArray(...) from PreparedStatementCreator e.g.

jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

Good Luck ..