How to insert Integer array into postgresql table using jdbcTemplate in Java Springboot?

Question

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

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

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)
)

And here is the Error from Springboot :

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.

Andrii Holets · Accepted Answer · 2016-12-24 12:42:40Z

10

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

edited Dec 24, 2016 at 12:42

answered Dec 24, 2016 at 12:37

Andrii Holets

1281 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amiko Over a year ago

Should have written this before. Thanks, a lot. I did accept your answer the next day but didn't write thanks. So, thanks again.

Hakan Jul 23 at 17:00

Thanks a lot, confirm that still working with spring-jdbc 6.0.10 Note that in my context I wrap connection in a try-with-resources Array arrayColumns; try (Connection conn = Objects.requireNonNull(jdbcTemplate.getJdbcTemplate().getDataSource()).getConnection()) { arrayColumns = conn.createArrayOf("text", sourceColumns.toArray()); } catch (SQLException e) { // your exception logic here }

Collectives™ on Stack Overflow

How to insert Integer array into postgresql table using jdbcTemplate in Java Springboot?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related