如何在Java Springboot中使用jdbcTemplate将Integer数组插入到postgresql表中? [英] How to insert Integer array into postgresql table using jdbcTemplate in Java Springboot?

问题描述

我在将Integer数组插入Postgresql Table时遇到问题，该如何解决?

I have problems with inserting Integer array into Postgresql Table, how do I go about this?

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位数字.

Where ballNumbers and ballNumbersMegaBall are ArrayList. Filled with 2 digit numbers.

这是PostgreSQL表:

Here is the PostgreSQL table:

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中的错误:

And here is the Error from 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()与>显式类型"值一起使用以指定要使用的类型.

There was an unexpected error (type=Internal Server Error, status=500). PreparedStatementCallback; bad SQL grammar [INSERT INTO draw_result (id, >ball_numbers, balls_with_mega_ball, draw_dates, mega_plier) 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.

推荐答案

最近，我遇到了类似的问题.我的解决方案:

Recently I had a similar problem. My solution:

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;
}

这篇关于如何在Java Springboot中使用jdbcTemplate将Integer数组插入到postgresql表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！