Search code examples
javapostgresqljdbc

Bind java array to postgres

I have the following table:

CREATE TABLE IF NOT EXISTS icon (
    id int8 GENERATED ALWAYS AS IDENTITY,
    "key" varchar(100) NOT NULL,
    description text NULL,
    keywords text[] NULL,
);

And I need to retrieve all the icons that contain a certain list of keywords. To do so, I'm using this query and it gets the results just fine:

public List<Icon> getIconsByKeywords(String[] keywords) {
    ...

    jdbcTemplate.query("select * from icon where keywords @> ARRAY['k4','k5','k6']", rs -> {
        Icon icon = new Icon();
        icon.setKey(rs.getString("key"));
        icon.setDescription(rs.getString("description"));
    });

    ...
}

Now I need to change the parameters inside the postgres array at runtime, but I can't figure out a way to bind the java string array to the postgres array. I've tried casting it to an Object array, passing it as a String inside of the ARRAY and creating a String array directly, but none of them worked.

I've seen in other threads some people using the connection to create an array or using prepared statements. Doing it this way works, like below.

Array array = jdbcTemplate.getDataSource().getConnection().createArrayOf("text", keywords);

jdbcTemplate.query("select * from icon where keywords @> ?", rs -> {
    Icon icon = new CustomIcon();
    Icon.setKey(rs.getString("key"));
    Icon.setDescription(rs.getString("description"));
}, array);

But is there no way of binding the keywords array using a jdbcTemplate and not having to deal with the database connection?

Solution

  • It seems that all constructors of PgArray require a connection so you will need it to bind an array.

    One possible workaround is to do it the Hibernate way with one statement for each array size

     con.prepareStatement("select * from jdbn.icon where keywords @> ARRAY[?,?]::text[]")
stmt.setString(1,'a')
stmt.setString(2,'b')

    other proposed in the comment by @Stefanov.sm is to pass a comma separated string of the keywords

    stmt = con.prepareStatement("select * from jdbn.icon where keywords @> string_to_array(?,',')")
stmt.setString(1,'a,b')