Updating hstore fields with jooq
Does anyone know if it is possible to update the value by key in the hstore using JOOQ, other than doing a simple sql. For example, I have a table of 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 the value with key3 . For this I can execute a simple sql:
UPDATE products SET name='test1New', characs=characs || '"key3"=>"value3"'::hstore where id=1
Can JOOQ DSL be used ?
source to share
This is entirely possible, but not yet supported out of the box. To implement your own support for HSTORE, you need:
1. Submit org.jooq.Binding
This binding will be used for all HSTORE columns in your database. It will take care of your user type <U>
(for example Map<String, String>
) interacting with JDBC to create a database type <T>
(for example, String
or PGobject
).
An example of how to do this with a type JSON
can be seen here:
- How to insert an updatable record with a JSON column in PostgreSQL using JOOQ?
- http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/
The type HSTORE
will work in much the same way
2. Implementing your own statements via plain SQL
PostgreSQL has many vendor-specific operators that come with vendor-specific datatypes. These statements must be implemented through plain SQL. For example:
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...
}
source to share