如何使用Spring JdbcTemplate更新PostgreSQL数组列？

Question

我正在使用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;
}

查询语句为"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.

我查看了Spring JDBC模板文档，但没有找到任何帮助，我会继续寻找，无论如何，能否有人指导我正确的方向？谢谢！

编辑：

显然顺序错了，是我的错...

我尝试了你们两个的解决方案，第一种情况下我得到了这个：

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

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

- Francesco

Answer 1

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

使用方法如下：

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

也可以在中使用，用于与一起使用。

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

本代码已在公共领域中提供

- squiddle

---

可以了。由于我的数组是 varchar(10)[]，所以我必须将 varchar 作为第二个参数传递。 - Frederic Leitenberger

Answer 2

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

参考博客

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

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

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

2) PostgreSQL内置函数string_to_array()

您可以在这里找到其他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

- Dhruvil Thaker

---

你的解决方案基于假设，即输入字符串中不允许使用逗号，但是OP并没有说明这种假设。为什么要在好的解决方案发布5年后发布一个糟糕的解决方案呢？ - 9ilsdx 9rvj 0lo

您的解决方案非常有效。非常感谢您。因为我知道我的输入可能包含逗号，所以我选择使用“*||*”作为分隔符。 - Mircea Stanciu

Answer 3

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

- Mikael Gueck

---

仅适用于普通的JdbcTemplate，对于NamedParameterJdbcTemplate，请投票https://jira.spring.io/browse/SPR-13178。 - Hendy Irawan

Answer 4

到目前为止，我发现最干净的方法是先将Collection转换为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 })

这是基于文档中的信息：https://jdbc.postgresql.org/documentation/server-prepare/#arrays

- Jens Schauder

Answer 5

参数类型和参数不匹配。

尝试更改参数类型顺序。

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

或者使用

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

看看它是否有效

- surajz

---

我编辑了我的回答，我尝试了你提供的两个解决方案。我想我需要一个java.sql.Array的实例，但是我该如何使用JdbcTemplate创建它呢？ - Francesco

Answer 6

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

创建数组后，您可以使用preparedstatement.setArray(...)从PreparedStatementCreator设置它，例如。

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

祝你好运。

- surajz

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

- user5351963

---

它是受到4年前的答案的“启发”。你只是试图复制它，做了一些混合搭配，使它变得更糟糕（例如，现在你需要处理SQLException，但你的“代码”中甚至没有显示）。 - 9ilsdx 9rvj 0lo