New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5
Custom data type binding
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The previous section 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 like JSON or HSTORE, or PostGIS types. 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 static org.jooq.tools.Convert.convert; import java.sql.*; import org.jooq.*; import org.jooq.impl.DSL; import com.google.gson.*; // We're binding <T> = Object (unknown JDBC type), and <U> = JsonElement (user type) public class PostgresJSONGsonBinding implements Binding<Object, JsonElement> { // The converter does all the work @Override public Converter<Object, JsonElement> converter() { return new Converter<Object, JsonElement>() { @Override public JsonElement from(Object t) { return t == null ? JsonNull.INSTANCE : new Gson().fromJson("" + t, JsonElement.class); } @Override public Object to(JsonElement u) { return u == null || u == JsonNull.INSTANCE ? null : new Gson().toJson(u); } @Override public Class<Object> fromType() { return Object.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("?::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 { ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null)); } // 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(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(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 xmlns="http://www.jooq.org/xsd/jooq-codegen-3.11.0.xsd"> <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. If provided, both "expressions" and "types" must match. --> <expression>.*JSON.*</expression> <!-- 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 "expressions" and "types" must match. --> <types>.*</types> </forcedType> </forcedTypes> </database> </generator> </configuration>
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. // // If provided, both "expressions" and "types" must match. .withExpression(".*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 "expressions" and "types" must match. .withTypes(".*") ) ) )
myConfigurationName(sourceSets.main) { 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. // // If provided, both "expressions" and "types" must match. expression = '.*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 "expressions" and "types" must match. types = '.*' } } } } }
See also the section about data type rewrites to learn about an alternative use of <forcedTypes/>.
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!