Available in versions: Dev (3.21)
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Custom data type Formatter
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
When working with bindings for advanced, vendor-specific types, it is not unlikely that the default embeddings in JSON types or XML types is inadequate, or not implemented. For example, in PostgreSQL, when embedding a spatial type in a JSON or XML document, it simply doesn't behave:
SELECT
JSON_ARRAY(CAST('point (1 1)' AS geometry)),
XMLELEMENT(NAME e, CAST('point (1 1)' AS geometry))
This produces:
+------------------------------------------------+---------------------------------------------------+
| json_array | xmlelement |
+------------------------------------------------+---------------------------------------------------+
| "[{\"type\":\"Point\",\"coordinates\":[1,1]}]" | <e>0101000000000000000000F03F000000000000F03F</e> |
+------------------------------------------------+---------------------------------------------------+
This produces GeoJSON and WKB representations, respectively, because, why not. What if we wanted a WKT/WKB representation in both cases, though? A org.jooq.Formatter implementation can help:
public class SpatialFormatter implements Formatter {
@Override
public final void formatJSON(FormatterContext ctx) {
ctx.field(ctx.field().cast(CLOB));
}
@Override
public final void formatJSONB(FormatterContext ctx) {
formatJSON(ctx);
}
@Override
public final void formatXML(FormatterContext ctx) {
}
}
This simple Formatter, if attached to a org.jooq.Binding for GEOMETRY types, ensures that any reference to a org.jooq.Geometry typed Field will be wrapped in a CAST(.. AS TEXT) expression. So, the SQL generated instead would be:
SELECT
JSON_ARRAY(CAST(CAST('point (1 1)' AS geometry) AS text)),
XMLELEMENT(NAME e, CAST('point (1 1)' AS geometry))
Resulting in:
+----------------------------------------------------+---------------------------------------------------+ | json_array | xmlelement | +----------------------------------------------------+---------------------------------------------------+ | "[\"0101000000000000000000F03F000000000000F03F\"]" | <e>0101000000000000000000F03F000000000000F03F</e> | +----------------------------------------------------+---------------------------------------------------+
For this to work, just return the formatter from your Binding:
public class MyGeometryBinding implements Binding<Geometry, MyGeometryType> {
// ...
static final SpatialFormatter FORMATTER = new SpatialFormatter();
public Formatter formatter() {
return FORMATTER;
}
}
jOOQ offers such Formatter implementations out of the box for all Built-in data types (including GEOMETRY) and Extended data types.
Feedback
Do you have any feedback about this page? We'd love to hear it!