How to save java.util.ArrayList in Postgresql using Spring

Question

I am trying to batch insert a beans collection. One of the properties of a bean is an ArrayList. Batch update fails:

 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.

I do not know which Postgresql data type to use for ArrayList compatibility. Is there a way to do a batch update of beans without changing the data type of its properties?

---

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

Table layout:

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

Data Insertion Method:

    public void insert(List<SomeBean> beans){
        String sql = "INSERT INTO ARRAYLISTEXAMPLE (NAMES) VALUES (:names)";

        SqlParameterSource[] data = SqlParameterSourceUtils.createBatch(beans.toArray());
        pgTemplate.batchUpdate(sql, data);
    }

An exception:

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

Any suggestions?

Answer 1

. . , JDBC PostgeSQL, . 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

, PGSQL , . this

(n) varchar (n)

, , ? PGSQL