如何使用Spring在Postgresql中存储java.util.ArrayList

Question

我正在尝试对bean集合进行批量插入。bean的一个属性是ArrayList。批处理更新失败，出现以下异常:

 Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

我不知道使用哪种Postgresql数据类型的ArrayList是兼容的。是否有一种方法可以在不改变其属性的数据类型的情况下批量更新bean ?

---

Bean:

    import java.util.List;
    public class SomeBean {
        private int id;
        private List<String> names;
        @Override
        public String toString() {
            return "SomeBean [id=" + id + ", names=" + names + "]";
        }
        //Setters and getters

表模式:

CREATE TABLE arraylistexample
(
  id serial NOT NULL,
  names character varying[]
)

插入数据的方法:

    public void insert(List<SomeBean> beans){
        String sql = "INSERT INTO ARRAYLISTEXAMPLE (NAMES) VALUES (:names)";
        SqlParameterSource[] data = SqlParameterSourceUtils.createBatch(beans.toArray());
        pgTemplate.batchUpdate(sql, data);
    }

例外:

Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO ARRAYLISTEXAMPLE (NAMES) VALUES (?, ?)]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:303)
    at some.package.dao.GenericDao.insert(GenericDao.java:45)
    at some.package.runner.FileLogicTester.storingArrayListInDb(FileLogicTester.java:220)
    at some.package.runner.FileLogicTester.main(FileLogicTester.java:86)
Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1801)
    at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:37)
    at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:46)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:255)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
    at org.springframework.jdbc.core.BatchUpdateUtils.setStatementParameters(BatchUpdateUtils.java:63)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.access$000(NamedParameterBatchUpdateUtils.java:32)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils$1.setValues(NamedParameterBatchUpdateUtils.java:47)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:893)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:884)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
    ... 7 more

有什么建议吗?

Answer 1

我也面临着同样的问题。在这里人们讨论这个问题。看起来这是JDBC驱动程序或PostgeSQL本身的错误，所以您不能只是将数组传递给DB。作为一种变通方法，我不得不手动创建SQL(我有整数值，所以不关心SQL注入;您可能希望首先动态地准备SELECT * FROM MYTABLE WHERE ID IN (?,?,?)，然后处理语句)。

private static final String SQL_FIND_GOAL = "SELECT * FROM MYTABLE WHERE ID IN (:ids)";
public List<MyGoal> getAllMyGoals(Set<Integer> ids) {
// Work around of issue.
int i=0;
String mockInStatement = "";
for (int type: ids){
  if (i < ids.size()-1){
    mockInStatement = mockInStatement + type + ",";
  }
  else {
    mockInStatement = mockInStatement + type;
  }
  i++;
}
String refinedSQL = SQL_FIND_GOAL.replace(":ids",mockInStatement);
List<MyGoal> result = jdbcTemplate.query(
    refinedSQL, 
    new RowMapper<MyGoal>() {
      @Override
      public MyGoal mapRow(ResultSet rs, int rowNum) throws SQLException {
        MyGoal myGoal = new MyGoal();
        myGoal.setCode(rowNum);
        myGoal.setName(rs.getString("name"));
        return myGoal;
      }
    });
return result;  }

Answer 2

将其转换为类型化Java数组，它应该可以工作:

String insertSql = "INSERT INTO my_table(my_array) VALUES(?)";
jdbcTemplate.update(insertSql,myArrayList.toArray(new String[myArrayList.size()]));

Answer 3

我认为PGSQL的数据类型是不正确的，参考这个

可变字符(n) varchar(n)可变长度字符串

你可能要找的是PGSQL数组

Answer 4

不是OP的问题，但我在意外使用jdbcTemplate.update而不是jdbcTemplate.batchUpdate时遇到此错误