如何使用 spring JdbcTemplate 更新 postgresql 数组列？

How to update a postgresql array column with spring JdbcTemplate?

提问by Francesco

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 :

我正在使用 Spring JdbcTemplate，但我遇到了一个更新列的查询，该列实际上是一个 int 数组。数据库是 postgres 8.3.7。这是我正在使用的代码：

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 :

查询是“update table_name set array_column = ? where id_user = ? and id_device = ?”。我得到这个例外：

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.

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [更新 acotel_msp.users_mau 设置 denied_sub_client = ? id_users = 在哪里？和 id_mau = ?]; 列索引超出范围：4，列数：3。嵌套异常是 org.postgresql.util.PSQLException：列索引超出范围：4，列数：3。

引起：org.postgresql.util.PSQLException：列索引超出范围：4，列数：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!

我已经查看了 spring jdbc 模板文档，但找不到任何帮助，我会继续寻找，无论如何有人可以指出我正确的方向吗？谢谢！

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

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 错误的 SQL 语法 [更新用户设置 denied_sub_client = ? id_users = 在哪里？和 id_device = ?]; 嵌套异常是 org.postgresql.util.PSQLException：无法将 java.util.ArrayList 的实例转换为 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

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 错误的 SQL 语法 [更新用户设置 denied_sub_client = ? id_users = 在哪里？和 id_device = ?]; 嵌套异常是 org.postgresql.util.PSQLException：无法转换 [Ljava.lang.Object; 的实例；键入 Types.ARRAY

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

我想我需要一个 java.sql.Array 的实例，但是如何使用 JdbcTemplate 创建它？

回答by Mikael Gueck

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;
    }
});

回答by squiddle

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

经过多次尝试，我们决定使用一个小助手 ArraySqlValue 来为 Java 数组类型创建 Spring SqlValue 对象。

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.

ArraySqlValue 也可以在 MapSqlParameterSource 中与 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

此代码在公共领域提供

回答by Dhruvil Thaker

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

这个解决方案是一种使用 postgreSQL 内置函数的解决方法，这对我来说绝对有用。

reference blog

参考博客

1) Convert String Array to Comma Separated String

1) 将字符串数组转换为逗号分隔的字符串

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

如果您使用的是 Java8，这很容易。其他选项在这里

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

2) PostgreSQL built-in function string_to_array()

2) PostgreSQL 内置函数 string_to_array()

you can find other postgreSQL array functions here

您可以在此处找到其他 postgreSQL 数组函数

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

回答by Jens Schauder

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

到目前为止，我发现的最简洁的方法是先将 转换Collection为 an Integer[]，然后使用 将Connection其转换为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/head/arrays.html

这是基于文档中的信息：https: //jdbc.postgresql.org/documentation/head/arrays.html

回答by surajz

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

看看它是否有效

回答by surajz

http://valgogtech.blogspot.com/2009/02/passing-arrays-to-postgresql-database.htmlexplains 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

http://valgogtech.blogspot.com/2009/02/passing-arrays-to-postgresql-database.html解释了如何创建 java.sql.Array postgresql 基本上 Array.getBaseTypeName 应该返回 int 和 Array.toString 应该返回数组“{1,2,3}”格式的内容

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

创建数组后，您可以使用 PreparedStatementCreator 中的 PreparedStatement.setArray (...)设置它，例如

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

Good Luck ..

祝你好运 ..

回答by user5351963

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);