如何使用Spring在Postgresql中存储java.util.ArrayList
如何使用Spring在Postgresql中存储java.util.ArrayList
提问于 2014-07-03 12:20:00
我正在尝试一次批量插入一组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进行批处理更新？
豆子：
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有什么建议吗？
回答 4
Stack Overflow用户
发布于 2015-06-07 08:57:41
我也面临着同样的问题。这里的人们对此进行了讨论。看起来这是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; }Stack Overflow用户
发布于 2020-12-01 16:21:34
将其转换为类型化的Java数组，它应该可以工作：
String insertSql = "INSERT INTO my_table(my_array) VALUES(?)";
jdbcTemplate.update(insertSql,myArrayList.toArray(new String[myArrayList.size()]));页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24553536
复制相关文章
相似问题
腾讯云开发者
