Convert Map to HStore

Question

I need to call Postgresql function that has hstore parameter.

I call it like this:

@Repository
public interface DocumentRepo extends JpaRepository<DokumentInstancja, Integer> {

    @Query(value = "SELECT * FROM gabinet.test(?)", nativeQuery = true)
    public Integer test(HStore mapa);
}

Where HStore is my wrapping class:

@Convert(converter = MyHStoreConverter.class)
public class HStore extends HashMap<String, String> {

    public HStore() {
        super();
    }

    public HStore(Map map) {
        super(map);
    }
}

I used Converter as suggested in other stackoverflow questions, but there it was used to fields not to class. But in my case map is not field of any other object.

Error i got:

2018-09-03 15:24:17.864 TRACE 13288 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARBINARY] - ["key1"=>"val1","key2"=>"val2"]
2018-09-03 15:24:17.865  WARN 13288 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42883
2018-09-03 15:24:17.865 ERROR 13288 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: function gabinet.test(bytea) does not exist

Can anybody suggest solution to my problem? Thanks in advance.

Answer 1

you can convert hstore parameter as string using

HStoreConverter.toString(Map<?, ?> map)

change your procedure argument using string

procedure_name(input_string_in character varying)

and inside the procedure you can cast it back from string into hstore

CAST (input_string_in as hstore)

Answer 2

If using a third party library is an option to you, you could be using jOOQ, which ships with built-in HSTORE data type support (via the jooq-postgres-extensions module), as well as code generation support for your stored functions. Using jOOQ, you could write:

Integer result = Routines.test(configuration, Hstore.valueOf(map));

In the above example, the Routines class is a generated stub for all of your stored procedures and functions. The test() method allows for calling the gabinet.test function, and the configuration just contains the JDBC connection, etc. required for that standalone routine call.

Disclaimer: I work for the company behind jOOQ.