How to update a postgresql array column with spring JdbcTemplate?

Question

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?

Irregularity

Answer 1

After struggling with many attempts, we settled to use a little helper ArraySqlValue to create Spring SqlValue objects for Java Array Types.

usage is like this

jdbcTemplate.update(
                "UPDATE sometable SET arraycolumn = ?",
                ArraySqlValue.create(arrayValue))

The ArraySqlValue can also be used in MapSqlParameterSource for use with NamedParameterJdbcTemplate.

import static com.google.common.base.Preconditions.checkNotNull;

import java.sql.Array;
import java.sql.JDBCType;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Locale;

import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.support.SqlValue;

public class ArraySqlValue implements SqlValue {
    private final Object[] arr;
    private final String   dbTypeName;

    public static ArraySqlValue create(final Object[] arr) {
        return new ArraySqlValue(arr, determineDbTypeName(arr));
    }

    public static ArraySqlValue create(final Object[] arr, final String dbTypeName) {
        return new ArraySqlValue(arr, dbTypeName);
    }

    private ArraySqlValue(final Object[] arr, final String dbTypeName) {
        this.arr = checkNotNull(arr);
        this.dbTypeName = checkNotNull(dbTypeName);
    }

    @Override
    public void setValue(final PreparedStatement ps, final int paramIndex) throws SQLException {
        final Array arrayValue = ps.getConnection().createArrayOf(dbTypeName, arr);
        ps.setArray(paramIndex, arrayValue);
    }

    @Override
    public void cleanup() {}

    private static String determineDbTypeName(final Object[] arr) {
        // use Spring Utils similar to normal JdbcTemplate inner workings
        final int sqlParameterType =
            StatementCreatorUtils.javaTypeToSqlParameterType(arr.getClass().getComponentType());
        final JDBCType jdbcTypeToUse = JDBCType.valueOf(sqlParameterType);
        // lowercasing typename for Postgres
        final String typeNameToUse = jdbcTypeToUse.getName().toLowerCase(Locale.US);
        return typeNameToUse;
    }
}

this code is provided in the Public Domain

Anaesthesia

Works. I had to pass varchar as second parameter since my array was a varchar(10)[]. – Leatherleaf

Answer 2

This solution is kind of workaround using postgreSQL built-in function, which definitely worked for me.

reference blog

1) Convert String Array to Comma Separated String

If you are using Java8, it's pretty easy. other options are here

String commaSeparatedString = String.join(",",stringArray); // Java8 feature

2) PostgreSQL built-in function string_to_array()

you can find other postgreSQL array functions here

// tableName ( name text, string_array_column_name text[] )

String query = "insert into tableName(name,string_array_column_name ) values(?, string_to_array(?,',') )";


int[] types = new int[] { Types.VARCHAR, Types.VARCHAR};

Object[] psParams = new Object[] {"Dhruvil Thaker",commaSeparatedString };

jdbcTemplate.batchUpdate(query, psParams ,types); // assuming you have jdbctemplate instance

Schmo

Your solution is based on asscumption, that commas are not legal in input string, the OP hasn't stated such assumption. Why posting a bad solution 5 years after a good one was posted? – Deserve

Your solution worked very well for me. Thank you very much. Because I know my inputs might contain commas, I chose to use ' || ' as separator. – Monogyny

Answer 3

The cleanest way I found so far is to first convert the Collection into an Integer[] and then use the Connection to convert that into an Array.

Integer[] idArray = ids.toArray(new Integer[0]);

Array idSqlArray = jdbcTemplate.execute(
        (Connection c) -> c.createArrayOf(JDBCType.INTEGER.getName(), idArray)
);

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

This is based on information in the documentation: https://jdbc.postgresql.org/documentation/server-prepare/#arrays

Maxma

Answer 4

private static final String ARRAY_DATATYPE = "int4";
private static final String SQL_UPDATE = "UPDATE foo SET arr = ? WHERE d = ?";
final Integer[] existing = ...;
final DateTime dt = ...;

getJdbcTemplate().update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(final Connection con) throws SQLException {
        final PreparedStatement ret = con.prepareStatement(SQL_UPDATE);
        ret.setArray(1, con.createArrayOf(ARRAY_DATATYPE, existing));
        ret.setDate(2, new java.sql.Date(dt.getMillis()));
        return ret;
    }
});

Fleur

Works only with plain JdbcTemplate, for NamedParameterJdbcTemplate please vote jira.spring.io/browse/SPR-13178 – Wiper

Answer 5

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

Gussie

I edited my answer, I tried both your solutions, I suppose I need an instance of java.sql.Array, but how can I create it using JdbcTemplate? – Irregularity

Answer 6

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 ..

Gussie

Answer 7

java.sql.Array intArray = connection.createArrayOf("int", existing);
List<Object> values= new ArrayList<Object>();
values.add(intArray);
values.add(dt);
getJdbcTemplate().update(SQL_UPDATE,values);

Catchword

It is 'inspired' by 4 years older answer. You simply attempt to copy it, doing some mish-mash that makes it much worse (for example, you need to handle SQLException now, what is even not shown in your 'code'). – Deserve