我在将Integer数组插入Postgresql Table时遇到问题,该如何解决?
String sql = "INSERT INTO draw_result (id, ball_numbers, balls_with_mega_ball, draw_dates, mega_plier) VALUES(?, ?, ?, ?, ?)";
Object[] params = {randomNumbers, ballNumbers, ballNumbersMegaBall, drawDates, megaPlier};
jdbcTemplate.update(sql, params);
其中ballNumbers和ballNumbersMegaBall是ArrayList。填充2位数字。
这是PostgreSQL表:
CREATE TABLE public.draw_result
(
id bigint NOT NULL,
draw_dates date,
ball_numbers bigint[],
balls_with_mega_ball bigint[],
mega_plier bigint,
CONSTRAINT draw_result_pkey PRIMARY KEY (id)
)
这是Springboot的错误:
发生意外错误(类型=内部服务器错误,状态= 500)。PreparedStatementCallback; 错误的SQL语法[INSERT INTO
draw_result(id,>
ball_numbers,balls_with_mega_ball,draw_dates,mega_plier)VALUES(?,?,?,?,>?)];嵌套异常是org.postgresql.util.PSQLException:无法推断用于java.util.ArrayList实例的SQL类型。将setObject()与>
explicit Types值一起使用以指定要使用的类型。
最近我有一个类似的问题。我的解决方案:
public void setDrawResult(BigInteger id, List<BigInteger> ballNumbers, List<BigInteger> ballsWithMegaBall, Date drawDates,BigInteger megaPlier){
String sql = "INSERT INTO draw_result (id, ball_numbers, balls_with_mega_ball, draw_dates, mega_plier) VALUES(?, ?, ?, ?, ?)";
jdbcTemplate.update(sql
, id
, createSqlArray(ballNumbers)
, createSqlArray(ballsWithMegaBall)
, drawDates
, megaPlier
);
}
private java.sql.Array createSqlArray(List<BigInteger> list){
java.sql.Array intArray = null;
try {
intArray = jdbcTemplate.getDataSource().getConnection().createArrayOf("bigint", list.toArray());
} catch (SQLException ignore) {
}
return intArray;
}