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)

score 1 · Accepted Answer · answered Oct 18 '18 at 16:23

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.

The way I use is the method spring shows how to use an array and it replaces the parameter with the correct number of ? is my understanding. although this way looks more like exactly what I want so I will try this — Whinis, Oct 18 '18 at 18:14
Actually just attempted to use this code and I get the same error as before — Whinis, Oct 18 '18 at 18:48
Then your id field is not an array as you stated, but an integer or even bigint. Alter the where clause to `id = any( ? )` — coladict, Oct 18 '18 at 19:01
It is certainly an array, this is the error I get with any operator does not exist: integer[] = integer — Whinis, Oct 18 '18 at 19:40
Hmm... according to https://stackoverflow.com/q/50818649/3841161 then the problem is not when adding the values, it's when extracting them. The error is in your RowMapper. You're likely calling `rs.getInt(col)` on the id column, when you should use `getArray(col)`. But still your original code was is probably not going to work for more than one value in the array. — coladict, Oct 18 '18 at 19:49
That makes sense but I wonder why it only errors if the integer is < 100 ? — Whinis, Oct 19 '18 at 02:41

Using Integer Array in postgres with Spring-boot

1 Answers1