java - 如何使用 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.

我不知道要使 ArrayList 兼容使用哪种 Postgresql 数据类型。有没有一种方法可以在不更改 bean 属性的数据类型的情况下批量更新 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

我遇到了同样的问题。 Here人们对此进行了讨论。看起来这是 JDBC 驱动程序或 PostgeSQL 本身的错误，因此您不能只将数组传递给数据库。作为一种解决方法，我不得不手动创建 SQL(我有整数值，所以不关心 sql 注入(inject)；你可能想动态准备 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;  }