How to map Integer List from result

Question

I am using java 7 and jdbc template to query a integer array from postgresql.My code is as below:

 @Autowired
 private JdbcTemplate jdbcTemp;

 String SQL = "select item_list from public.items where item_id=1";
 List<Integer> ListOfitems=jdbcTemp.queryForList(SQL , Integer.class);

My item_list column is integer[] in postgresql.But when I try like thid it throws an error as Bad value for type int psql exception.

Any help is appreciated

what is the type of `item_list` in your SQL-Statment. Is it a List of Integers? Then you should change the resultType from `List` to `List>` — Lino, Jul 31 '17 at 13:01

score 2 · Accepted Answer · answered Aug 01 '17 at 05:58

You can use java.sql.Array.

If you want to get only integer array you can try like this

String SQL = "select item_list from public.items where item_id=1";
Array l = template.queryForObject(SQL, Array.class);
List<Integer> list = Arrays.asList((Integer[]) l.getArray());

Or use RowMapper

Foo foo = template.queryForObject(SQL, new RowMapper<Foo>(){
        @Override
        public Foo mapRow(ResultSet rs, int rowNum) throws SQLException {
            Foo foo = new Foo();
            foo.setName(rs.getString("name"));
            foo.setIntegers(Arrays.asList((Integer[]) rs.getArray("item_list").getArray()));
            return foo;
        }
    });

Class Foo:

class Foo {
    private String name;
    private List<Integer> integers;

    public String getName() {
        return name;
    }
    // ...
}

score 1 · Answer 2 · answered Jul 31 '17 at 15:30

queryForList saves each row that you got as a result from your query as an element of a List. It doesn't take the list that is saved into a column and returns it for you.

From the documentation : "The results will be mapped to a List (one entry for each row) of result objects, each of them matching the specified element type."

At best you will get a List<List<Integer>> returned, but I'm not sure fi you can use List<Interger>.class as a parameter for the call.

How to map Integer List from result

2 Answers2