Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Data type bindings
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The previous sections discussed the case where your custom data type is mapped onto a standard JDBC type as contained in org.jooq.impl.SQLDataType
. In some cases, however, you want to map your own type onto a type that is not explicitly supported by JDBC, such as for instance, PostgreSQL's various advanced data types (though do check out the jooq-postgres-extensions
module to see what we're already offering in this area). For this, you can register an org.jooq.Binding
for relevant columns in your code generator. Consider the following trivial implementation of a binding for PostgreSQL's JSON data type, which binds the JSON string in PostgreSQL to a Google GSON object:
import java.sql.*; import java.util.*; import org.jooq.*; import org.jooq.conf.*; import org.jooq.impl.DSL; import com.google.gson.*; // We're binding <T> = JSON (or JSONB), and <U> = JsonElement (user type) // Alternatively, extend org.jooq.impl.AbstractBinding to implement fewer methods. public class PostgresJSONGsonBinding implements Binding<JSON, JsonElement> { private final Gson gson = new Gson(); // The converter does all the work @Override public Converter<JSON, JsonElement> converter() { return new Converter<JSON, JsonElement>() { @Override public JsonElement from(JSON t) { return t == null ? JsonNull.INSTANCE : gson.fromJson(t.data(), JsonElement.class); } @Override public JSON to(JsonElement u) { return u == null || u == JsonNull.INSTANCE ? null : JSON.json(gson.toJson(u)); } @Override public Class<JSON> fromType() { return JSON.class; } @Override public Class<JsonElement> toType() { return JsonElement.class; } }; } // Rending a bind variable for the binding context's value and casting it to the json type @Override public void sql(BindingSQLContext<JsonElement> ctx) throws SQLException { // Depending on how you generate your SQL, you may need to explicitly distinguish // between jOOQ generating bind variables or inlined literals. if (ctx.render().paramType() == ParamType.INLINED) ctx.render().visit(DSL.inline(ctx.convert(converter()).value())).sql("::json"); else ctx.render().sql(ctx.variable()).sql("::json"); } // Registering VARCHAR types for JDBC CallableStatement OUT parameters @Override public void register(BindingRegisterContext<JsonElement> ctx) throws SQLException { ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR); } // Converting the JsonElement to a String value and setting that on a JDBC PreparedStatement @Override public void set(BindingSetStatementContext<JsonElement> ctx) throws SQLException { JSON json = ctx.convert(converter()).value(); ctx.statement().setString(ctx.index(), json == null ? null : json.data()); } // Getting a String value from a JDBC ResultSet and converting that to a JsonElement @Override public void get(BindingGetResultSetContext<JsonElement> ctx) throws SQLException { ctx.convert(converter()).value(JSON.json(ctx.resultSet().getString(ctx.index()))); } // Getting a String value from a JDBC CallableStatement and converting that to a JsonElement @Override public void get(BindingGetStatementContext<JsonElement> ctx) throws SQLException { ctx.convert(converter()).value(JSON.json(ctx.statement().getString(ctx.index()))); } // Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types) @Override public void set(BindingSetSQLOutputContext<JsonElement> ctx) throws SQLException { throw new SQLFeatureNotSupportedException(); } // Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types) @Override public void get(BindingGetSQLInputContext<JsonElement> ctx) throws SQLException { throw new SQLFeatureNotSupportedException(); } }
Registering bindings to the code generator
The above org.jooq.Binding
implementation intercepts all the interaction on a JDBC level, such that jOOQ will never need to know how to correctly serialise / deserialise your custom data type. Similar to what we've seen in the previous section about how to register Converters to the code generator, we can now register such a binding to the code generator. Note that you will reuse the same types of XML elements (<forcedType/>
):
<configuration> <generator> <database> <forcedTypes> <forcedType> <!-- Specify the Java type of your custom type. This corresponds to the Binding's <U> type. --> <userType>com.google.gson.JsonElement</userType> <!-- Associate that custom type with your binding. --> <binding>com.example.PostgresJSONGsonBinding</binding> <!-- A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions. --> <includeExpression>.*JSON.*</includeExpression> <!-- A Java regex matching data types to be forced to have this type. Data types may be reported by your database as: - NUMBER regexp suggestion: NUMBER - NUMBER(5) regexp suggestion: NUMBER\(5\) - NUMBER(5, 2) regexp suggestion: NUMBER\(5,\s*2\) - any other form It is thus recommended to use defensive regexes for types. If provided, both "includeExpressions" and "includeTypes" must match. --> <includeTypes>(?i:JSON)</includeTypes> </forcedType> </forcedTypes> </database> </generator> </configuration>
See the configuration XSD, standalone code generation, and maven code generation for more details.
new org.jooq.meta.jaxb.Configuration() .withGenerator(new Generator() .withDatabase(new Database() .withForcedTypes( new ForcedType() // Specify the Java type of your custom type. This corresponds to the Binding's <U> type. .withUserType("com.google.gson.JsonElement") // Associate that custom type with your binding. .withBinding("com.example.PostgresJSONGsonBinding") // A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions. .withIncludeExpression(".*JSON.*") // A Java regex matching data types to be forced to // have this type. // // Data types may be reported by your database as: // - NUMBER regexp suggestion: NUMBER // - NUMBER(5) regexp suggestion: NUMBER\(5\) // - NUMBER(5, 2) regexp suggestion: NUMBER\(5,\s*2\) // - any other form // // It is thus recommended to use defensive regexes for types. // // If provided, both "includeExpressions" and "includeTypes" must match. .withIncludeTypes("(?i:JSON)") ) ) )
See the configuration XSD and programmatic code generation for more details.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
generationTool { generator { database { forcedTypes { forcedType { // Specify the Java type of your custom type. This corresponds to the Binding's <U> type. userType = "com.google.gson.JsonElement" // Associate that custom type with your binding. binding = "com.example.PostgresJSONGsonBinding" // A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions. includeExpression = ".*JSON.*" // A Java regex matching data types to be forced to // have this type. // // Data types may be reported by your database as: // - NUMBER regexp suggestion: NUMBER // - NUMBER(5) regexp suggestion: NUMBER\(5\) // - NUMBER(5, 2) regexp suggestion: NUMBER\(5,\s*2\) // - any other form // // It is thus recommended to use defensive regexes for types. // // If provided, both "includeExpressions" and "includeTypes" must match. includeTypes = "(?i:JSON)" } } } } }
See the configuration XSD and gradle code generation for more details.
For more details about how to match columns, please refer to the section about matching columns for forced types.
The above configuration will lead to AUTHOR.CUSTOM_DATA_JSON
being generated like this:
public class TAuthor extends TableImpl<TAuthorRecord> { // [...] public final TableField<TAuthorRecord, JsonElement> CUSTOM_DATA_JSON = // [...] // [...] }
Feedback
Do you have any feedback about this page? We'd love to hear it!