How to query for an postgres integer array in spring

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 this it throws an error as:

Bad value for type int psql exception

I also tried:

List<List<Integer>> ListOfitems=jdbcTemp.queryForList(SQL , Integer.class);

But it still throws the same exception.

Any help is appreciated.

Thanks But I know how to retreive by prepared statement and connection.The issue I am facing here is to map to the correct class type when I use JDBC Termplate — Ricky
– Ricky, Commented Aug 1, 2017 at 1:07

Egor · Accepted Answer · 2017-08-01 06:24:05Z

You can use java.sql.Array.

If you want to get only integer array you can try like this (it works if result contains one row):

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

Ricky · Accepted Answer · 2017-08-01 01:20:53Z

4

After lots of trial and error this made the trick.

List<Array>  ListOfitems=jdbcTemp.queryForList(SQL , java.sql.Array.class);

So basically the postgres array is equal to java sql array type.Hope it helps anyone :)

answered Aug 1, 2017 at 1:20

Ricky

2,7605 gold badges30 silver badges60 bronze badges

Comments

Sergey Vyacheslavovich Brunov · Accepted Answer · 2017-08-01 01:34:42Z

1

To get the array field of the single row it should be something like:

import java.sql.Array;

...

final String sqlQuery = "...";
final Array itemListArray = jdbcTemplate.queryForObject(sqlQuery, Array.class);

Please note that the method call may throw the exception:

IncorrectResultSizeDataAccessException - if the query does not return exactly one row, or does not return exactly one column in that row

— JdbcTemplate (Spring Framework 4.3.10.RELEASE API) class, public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException method.

answered Aug 1, 2017 at 1:34

Sergey Vyacheslavovich Brunov

18.2k7 gold badges53 silver badges85 bronze badges

2 Comments

Ricky Over a year ago

Just a quick question is itemListArray Array or List<Array>?

Sergey Vyacheslavovich Brunov Over a year ago

Array (java.sql.Array). Because it seems you would like to select the array field (item_list) of the single row found by its primary key (the item_id field).

Collectives™ on Stack Overflow

How to query for an postgres integer array in spring

3 Answers 3

Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Linked

Related