CODEWITHSUNDEEP
javasqlpostgresqljooqhstore
Dmytro Kryvenko
Dmytro Kryvenko

Reputation: 343

Update hstore fields using jooq

Does anybody know is it possible to update value by key in hstore using JOOQ, except executing plain sql. For example, I have table products

 id |                                  characs                                   |         name         
----+----------------------------------------------------------------------------+--------------
  1 | "key"=>"value", "key3"=>"test2"                                            | test1    
  2 | "key"=>"value"                                                             | test3               
  3 | "keyNew"=>"valueNew"                                                       | test3               
  4 | "keyNew"=>"valueNew"                                                       | test4               
  5 | "date"=>"Dec 2012", "price"=>"500", "author"=>"Dave", "currency"=>"dollar" | test5

And I want to update value by key key3. For that I can execute plain sql:

UPDATE products SET name='test1New', characs=characs || '"key3"=>"value3"'::hstore where id=1

Is it possible to do using JOOQ's DSL?

Upvotes: 3

Views: 1230

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

It's quite possible, but not yet supported out-of-the box. In order to implement your own HSTORE support, you will need to:

1. Implement an org.jooq.Binding

This binding will be used for all HSTORE columns in your database. It will take care of handling the interaction of your user type <U> (e.g. Map<String, String>) with JDBC, in order to produce a database type <T> (e.g. String, or PGobject).

An example of how to do this with the JSON type can be seen here:

The HSTORE type will work roughly the same way

2. Implement your own operators via plain SQL

PostgreSQL has a lot of vendor-specific operators that go with the vendor-specific data types. These operators will need to be implemented via plain SQL. For instance:

public class DSLExtensions {
    public static Field<String> get(
            Field<Map<String, String>> hstore, String key) {
        return DSL.field("{0} -> {1}", String.class, hstore, DSL.val(key));
    }

    public static Field<String[]> get(
            Field<Map<String, String>> hstore, String[] keys) {
        return DSL.field("{0} -> {1}", String[].class, hstore, DSL.val(keys));
    }

    public static Field<Map<String, String>> concat(
            Field<Map<String, String>> f1,
            Field<Map<String, String>> f2) {
        return DSL.field("{0} || {1}", f1.getDataType(), f1, f2);
    }

    // etc...
}

Upvotes: 3

Related Questions