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

Question

I am trying to do a batch-insert of a collection of beans. One of the properties of the bean is an ArrayList. The batch update fails with the exception:

 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 don't know which Postgresql data type to use for the ArrayList to be compatible. Is there a way I can do the batch update of the beans without changing the data type of its properties?

---

The 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 schema:

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

Method to insert the data:

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

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

The 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

I was facing the same problem. Here people discussed about that. Looks like it's the fault of the JDBC driver or PostgeSQL itself, so you can't just pass an array to the DB. As a workaround I had to manually create SQL (I had integer values, so didn't care about sql injections; you may want to prepare dynamically SELECT * FROM MYTABLE WHERE ID IN (?,?,?) first and then process the statement).

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

Convert it to a typed Java array and it should work:

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