Using Integer Array in postgres with Spring-boot

Question

I am attempting to accept from the browser a List and use this within a SQL query to a postgres database. I have the following code snippet that tries to show the function that I have made todo this. Some of the variables have been changed in case there appears to be discrepancies.

static public List<Map<String,Object>> fetch(NamedParameterJdbcTemplate jdbcTemplate, List<Integer> id){
    List<Map<String,Object>> result= new ArrayList<>();
    String sql = "select * from lookup where id && ARRAY[ :ids ]";
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("ids",id, Types.INTEGER);
    result= jdbcTemplate.query(sql,
            parameters,
            new RowMapper<Map<String,Object>>() { ...
            }
    )
}

The lookup tables id field is a postgress array hence me needing to use && and the array function

This function is called by many different endpoints and passes the NamedParameterJdbcTemplate as well as a list of Integers. The problem I am having is that if any integer in the list is < 100 I get the following message

Bad value for type int : {20}

Is there another way of doing this or a way around this error ?

EDIT:

It appears it was part of the problem mentioned as the answer but also using

rs.getInt(col) 

instead of

rs.getArray(col)

Answer 1

There's an error I can see in the SQL, and probably the wrong choice of API after that. First in the query:

select * from lookup where id && ARRAY[ :ids ]

To bind an array parameter, it must not be placed in the ARRAY constructor, but rather you need to use JDBC binding like this:

select * from lookup where id && ?

As you've noticed I'm not using a named parameter in these examples, because NamedParameterJdbcTemplate does not provide a route to obtaining the java.sql.Connection object or a proxy to it. You can access it through the PreparedStatementSetter if you use the JdbcOperations interface instead.

public static List<Map<String,Object>> fetch(NamedParameterJdbcTemplate jdbcTemplate, List<Integer> idlist){
    List<Map<String,Object>> result= new ArrayList<>();
    String sql = "select * from lookup where id && ?";
    final Integer[] ids = idlist.toArray(new Integer[0]);
    PreparedStatementSetter parameters = new PreparedStatementSetter() {
        @Override
        void setValues(PreparedStatement stmt) {
            Connection conn = stmt.getConnection();
            // this can only be done through the Connection
            java.sql.Array arr = conn.createArrayOf("integer", ids);
            // you can use setObject(1, ids, java.sql.Types.ARRAY) instead of setArray
            // in case the connection wrapper doesn't pass it on to the JDBC driver
            stmt.setArray(1, ids);
        }
    };
    JdbcOperations jdo = jdbcTemplate.getJdbcOperations();
    result= jdo.query(sql,
            parameters,
            new RowMapper<Map<String,Object>>() { ...
            }
    )
}

There might be errors in the code, since I normally use a different set of APIs, and you need a try-catch block for java.sql.SQLException in that setValues function, but you should be able to handle it from here on.