Chapter 7: Types, Converters, and Bindings
Data types, converters, and bindings represent major aspects of working with a database via a Java-based Domain-Specific Language (DSL) Application Programming Interface (API). Sooner or later, standard Structured Query Language (SQL)/Java Database Connectivity (JDBC) data types will not be enough, or the default mappings between Java types and JDBC types will raise some shortcomings in your specific scenarios. At that moment, you'll be interested in creating new data types, working with custom data types, type conversion, and type-binding capabilities of your DSL API. Fortunately, the jOOQ Object Oriented Querying (jOOQ) DSL provides versatile and easy-to-use APIs dedicated to the following agenda that represents the subject of this chapter:
- Default data type conversion
- Custom data types and type conversion
- Custom data types and type binding
- Manipulating enums
- Data type rewrites
- Handling embeddable types
Let's get started!
Technical requirements
The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter07.
Default data type conversion
One of the aspects of jOOQ that allows us to use it in a smooth manner is its default data type conversion. Most of the time, jOOQ hides from us the ceremony of converting between JDBC and Java types. For instance, have you wondered how the following explicit conversions work? Take a look:
Record1<Integer> fiscalYear = ctx.select(field("fiscal_year", Integer.class)).from(table("sale")).fetchAny();// Offtake is a POJO
Offtake offtake = ctx.select(field("fiscal_year"), field("sale"), field("employee_number")).from(table("sale")) .fetchAnyInto(Offtake.class);
Both conversions are resolved via default data type conversion or auto-conversions. Behind the scenes, jOOQ relies on its own API that is capable of performing soft type-safe conversions for Object types, arrays, and collections.
You can check out this example in the ConvertUtil application.
Custom data types and type conversion
In jOOQ, the common interface for all dialect-specific data types is named org.jooq.DataType<T>, where T represents the Java type associated with an SQL data type. Each association of a T Java data type with an SQL data type (generic SQL types, called standard JDBC types) represented by java.sql.Types is present in jOOQ's org.jooq.impl.SQLDataType API. The jOOQ Code Generator automatically maps Java types to this SQLDataType API, which has an almost 1:1 matching to databases' data types for most dialects. Of course, we are not including here some of the vendor-specific data types, such as spatial data types, PostgreSQL's INET/HSTORE, nor other non-standard JDBC types (data types not explicitly supported by JDBC).
Roughly, any data type that is not associated in the jOOQ API with a standard JDBC type is considered and treated as a custom data type. However, as Lukas Eder mentions: "There are some data types that I think *should* be standard JDBC types, but are not. They're also listed in SQLDataType, including: JSON, JSONB, UUID, BigInteger (!), unsigned numbers, intervals. These don't require custom data types."
Whenever your custom data type needs to be mapped onto a standard JDBC type—that is, an org.jooq.impl.SQLDataType type—you need to provide and explicitly specify an org.jooq.Converter implementation. This converter does the hard work of performing a conversion between the involved types.
Important Note
When we want to map a type onto a non-standard JDBC type (a type that is not in org.jooq.impl.SQLDataType), we need to focus on the org.jooq.Binding API, which is covered later. So, if this is your case, don't try to shoehorn your conversion logic onto a Converter. Just use a Binding (we'll see this later in this chapter).
Pay attention that attempting to insert values/data of a custom data type without passing through a converter may result in inserting null values in the database (as Lukas Eder shared: "This null behavior is an old design flaw. A long time ago, I've not followed a fail-early strategy throwing exceptions"), while trying to fetch data of custom data type without a converter may lead to org.jooq.exception.DataTypeException, no converter found for types Foo and Buzz.
Writing an org.jooq.Converter interface
org.jooq.Converter is an interface that represents a conversion between two types that are generically denoted as <T> and <U>. By <T>, we represent the database type, and by <U>, we represent the User-Defined Type (UDT) or the type used in the application. Converting from <T> to <U> is accomplished in a method named U from(T), and converting from <U> to <T> is accomplished in a method named T to(U).
If you find it hard to remember which direction is "from()" and which direction is "to()", then think that the former can be read as "FROM the database to the client" and the latter as "from the client TO the database". Also, pay attention to not confuse T and U because you risk spending hours staring at compilation errors in generated code.
In other words, via U from(T), we convert from a database type to a UDT (for example, this is useful in SELECT statements), and via T to(U), we convert from a UDT to a database type (for example, this is useful in INSERT, UPDATE, and DELETE statements). Moreover, a T to(U) direction is used wherever bind variables are used, so also in SELECT when writing predicates—for instance, T.CONVERTED.eq(u). The stub of org.jooq.Converter is listed here:
public interface Converter<T, U> {U from(T databaseObject); // convert to user-defined type
T to(U userDefinedObject); // convert to database type
// Class instances for each type
Class<T> fromType();
Class<U> toType();
}
jOOQ comes with an abstract implementation of this interface (AbstractConverter) and a few concrete extensions (converters) of this abstraction that you can explore here: https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/impl/AbstractConverter.html. But as you'll see next, we can write our own converters.
If, for instance, you want to use Java 8's java.time.YearMonth type in the application but store it as an SQL INTEGER type in the database, you write a converter like this:
public class YearMonthConverter
implements Converter<Integer, YearMonth> {@Override
public YearMonth from(Integer t) { if (t != null) {return YearMonth.of(1970, 1)
.with(ChronoField.PROLEPTIC_MONTH, t);
}
return null;
}
@Override
public Integer to(YearMonth u) { if (u != null) {return (int) u.getLong(ChronoField.PROLEPTIC_MONTH);
}
return null;
}
@Override
public Class<Integer> fromType() {return Integer.class;
}
@Override
public Class<YearMonth> toType() {return YearMonth.class;
}
}
Use this converter via new YearMonthConverter() or define a handy static type, like so:
public static final Converter<Integer, YearMonth>
INTEGER_YEARMONTH_CONVERTER = new YearMonthConverter();
Moreover, using this converter for arrays can be done via the following static type, like so:
public static final Converter<Integer[], YearMonth[]>
INTEGER_YEARMONTH_ARR_CONVERTER
= INTEGER_YEARMONTH_CONVERTER.forArrays();
Once we have a converter, we can define a new data type. More precisely, we define our own DataType type programmatically by calling asConvertedDataType(Converter) or asConvertedDataType(Binding). For example, here, we define a YEARMONTH data type that can be used as any other data type defined in SQLDataType:
public static final DataType<YearMonth> YEARMONTH
= INTEGER.asConvertedDataType(INTEGER_YEARMONTH_CONVERTER);
Here, INTEGER is the org.jooq.impl.SQLDataType.INTEGER data type.
In the CUSTOMER table, we have a field named FIRST_BUY_DATE of type INT. When a customer makes their first purchase, we store the date (year-month) as an integer. For example, the date 2020-10 is stored as 24249 (we manually applied the Integer to(YearMonth u) method). Without a converter, we have to insert 24249 explicitly; otherwise, the code will not compile (for example, a type-safe INSERT statement will not compile) or we'll get an invalid insert (for example, a non-type-safe INSERT statement may store null). Relying on our converter, we can write the following type-safe INSERT statement:
ctx.insertInto(CUSTOMER, CUSTOMER.CUSTOMER_NAME, ... ,
CUSTOMER.FIRST_BUY_DATE)
.values("Atelier One", ..., INTEGER_YEARMONTH_CONVERTER.to(YearMonth.of(2020, 10)))
.execute();
Next, fetching all the FIRST_BUY_DATE values of Atelier One without using the converter will result in an array or list of integers. To fetch an array/list of YearMonth, we can use the converter, as follows:
List<YearMonth> resultListYM
= ctx.select(CUSTOMER.FIRST_BUY_DATE).from(CUSTOMER)
.where(CUSTOMER.CUSTOMER_NAME.eq("Atelier One")).fetch(CUSTOMER.FIRST_BUY_DATE,
INTEGER_YEARMONTH_CONVERTER);
In the bundled code (YearMonthConverter, available for MySQL and PostgreSQL), you can see more examples, including the usage of the YEARMONTH data type for coercing and casting operations.
Writing a converter having its own class is useful when the converter is used sporadically across different places/classes. If you know that the converter is used only in a single class, then you can define it locally in that class via Converter.of()/ofNullable(), as follows (the difference between them consists of the fact that Converter.ofNullable() always returns null for null inputs):
Converter<Integer, YearMonth> converter =
Converter.ofNullable(Integer.class, YearMonth.class,
(Integer t) -> {return YearMonth.of(1970, 1)
.with(ChronoField.PROLEPTIC_MONTH, t);
},
(YearMonth u) -> {return (int) u.getLong(ChronoField.PROLEPTIC_MONTH);
}
);
Moreover, starting with jOOQ 3.15+, we can use a so-called ad hoc converter. This type of converter is very handy for attaching a converter to a certain column just for one query or a few local queries. For instance, having a converter (INTEGER_YEARMONTH_CONVERTER), we can use it for a single column, as follows:
ctx.insertInto(CUSTOMER, CUSTOMER.CUSTOMER_NAME, ...,
CUSTOMER.FIRST_BUY_DATE.convert(INTEGER_YEARMONTH_CONVERTER))
.values("Atelier One", ..., YearMonth.of(2020, 10)).execute();
For convenience, jOOQ provides—next to the ad hoc convert() function (allows you to turn a Field<T> type into a Field<U> type and vice versa)—convertTo()(allows you to turn a Field<U> type into a Field<T> type) and convertFrom() (allows you to turn a Field<T> type into a Field<U> type) ad hoc flavors. Since our INSERT statement cannot take advantage of both directions of the converter, we can revert to convertTo(), as follows:
ctx.insertInto(CUSTOMER, CUSTOMER.CUSTOMER_NAME, ...,
CUSTOMER.FIRST_BUY_DATE.convertTo(YearMonth.class,
u -> INTEGER_YEARMONTH_CONVERTER.to(u)))
.values("Atelier One", ..., YearMonth.of(2020, 10)).execute();
Or, in the case of a SELECT statement, you may wish to use converterFrom(), as follows:
List<YearMonth> result = ctx.select(
CUSTOMER.FIRST_BUY_DATE.convertFrom(
t -> INTEGER_YEARMONTH_CONVERTER.from(t)))
.from(CUSTOMER)
.where(CUSTOMER.CUSTOMER_NAME.eq("Atelier One")).fetchInto(YearMonth.class);
Of course, you don't even need to define the converter's workload in a separate class. You can simply inline it, as we've done here:
ctx.insertInto(CUSTOMER, ...,
CUSTOMER.FIRST_BUY_DATE.convertTo(YearMonth.class,
u -> (int) u.getLong(ChronoField.PROLEPTIC_MONTH)))
.values(..., YearMonth.of(2020, 10)) ...;
List<YearMonth> result = ctx.select(
CUSTOMER.FIRST_BUY_DATE.convertFrom(
t -> YearMonth.of(1970, 1)
.with(ChronoField.PROLEPTIC_MONTH, t)))
.from(CUSTOMER) ...;
You can check the examples for ad hoc converters in YearMonthAdHocConverter for MySQL and PostgreSQL.
Going further, converters can be nested by nesting the calls of the to()/from() methods and can be chained via the <X> Converter<T,X> andThen(Converter<? super U, X> converter) method. Both nesting and chaining are exemplified in the bundled code (YearMonthConverter) by using a second converter that converts between YearMonth and Date, named YEARMONTH_DATE_CONVERTER.
Moreover, if you want to inverse a converter from <T, U> to <U, T>, then rely on the Converter.inverse() method. This can be useful when nesting/chaining converters that may require you to inverse T with U in order to obtain a proper match between data types. This is also exemplified in the bundled code.
The new data type can be defined based on converter, as follows:
DataType<YearMonth> YEARMONTH
= INTEGER.asConvertedDataType(converter);
The new data type can be defined without an explicit Converter as well. Just use the public default <U> DataType<U> asConvertedDataType(Class<U> toType, Function<? super T,? extends U> from, Function<? super U,? extends T> to) flavor, as in the bundled code, and jOOQ will use behind the scenes Converter.of(Class, Class, Function, Function).
On the other hand, if a converter is heavily used, then it is better to allow jOOQ to apply it automatically without an explicit call, as in the previous examples. To accomplish this, we need to perform the proper configurations of the jOOQ Code Generator.
Hooking forced types for converters
By using so-called forced types (<forcedTypes/>), we can instruct the jOOQ Code Generator to override the column data type. One way to accomplish this consists of mapping the column data type to a user-defined data type via org.jooq.Converter.
This configuration step relies on using the <forcedTypes/> tag, which is a child of the <database/> tag. Under the <forcedTypes/> tag, we can have one or multiple <forcedType/> tags, and each of these tags wraps a specific case of overriding the column's data types. Each such case is defined via several tags. First, we have the <userType/> and <converter/> tags, used to link the UDT and the proper Converter. Second, we have several tags used for identifying a certain column (or multiple columns) by name and/or type. While you can find all these tags described in the jOOQ manual (https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/), let's mention here two of the most used: <includeExpression/> and <includeTypes/>. <includeExpression/> contains a Java regular expression (regex) matching the fully qualified columns (or attributes/parameters), while <includeTypes/> contains a Java regex matching the data types that should be forced to have this type (the <userType/> type). In case of multiple regexes, use the pipe operator (|) to separate them, and if <includeExpression/> and <includeTypes/> are present in the same <forcedType/> tag, then keep in mind that they must match.
For instance, the <forcedType/> type for YearMonthConverter looks like this:
<forcedTypes>
<forcedType>
<!-- The Java type of the custom data type.
This corresponds to the Converter's <U> type. -->
<userType>java.time.YearMonth</userType>
<!-- Associate that custom type with our converter. -->
<converter>
com.classicmodels.converter.YearMonthConverter
</converter>
<!-- Match the fully-qualified column. -->
<includeExpression>
classicmodels.customer.first_buy_date
</includeExpression>
<!-- Match the data type to be forced. -->
<includeTypes>INT</includeTypes>
</forcedType>
</forcedTypes>
Notice how we identified the first_buy_date column via an expression containing the schema, table, and column name. In other cases, you may wish to use less restrictive expressions; therefore, here are some popular examples:
<!-- All 'first_buy_date' fields in any 'customer' table,
no matter the schema -->
.*.customer.first_buy_date
<!-- All 'first_buy_date' fields,
no matter the schema and the table -->
.*.first_buy_date
<!-- All fields containing 'first_buy_' -->
.*.first_buy_.*
<!-- Case-insensitive expressions -->
(?i:.*.customer.first_buy_date)
(?i:classicmodels.customer.first_buy_date)
Important Note
Notice that all regexes in the jOOQ Code Generator match any of the following:
1) Fully qualified object names (FQONs)
2) Partially qualified object names
3) Unqualified object names
So, instead of .*.customer.first_buy_date, you can also just write customer.first_buy_date.
Moreover, keep in mind that, by default, regexes are case-sensitive. This is important when you're using more than one dialect (for instance, Oracle identifiers (IDs) are UPPER_CASE, in PostgreSQL, they are lower_case, and in SQL Server, they are PascalCase).
Furthermore, matching any type is done via <includeTypes>.*</includeTypes>, while matching a certain type such as NVARCHAR(4000) is done via NVARCHAR(4000), and a type such as NUMBER(1, 0) via NUMBER(1,s*0). A more verbose version of this example with detailed comments is available in the bundled code.
This time, the FIRST_BUY_DATE field is not mapped to java.lang.Integer. If we check the generated table class that mirrors the CUSTOMER table (jooq.generated.tables.Customer), then we see the following declaration:
public final TableField<CustomerRecord, YearMonth>
FIRST_BUY_DATE = createField(DSL.name("first_buy_date"), SQLDataType.INTEGER, this, "", new YearMonthConverter());
So, FIRST_BUY_DATE is mapped to YearMonth, therefore our previous INSERT and SELECT statements will now look like this:
ctx.insertInto(CUSTOMER, CUSTOMER.CUSTOMER_NAME, ...,
CUSTOMER.FIRST_BUY_DATE)
.values("Atelier One", ..., YearMonth.of(2020, 10)).execute();
And the SELECT statement will then look like this:
List<YearMonth> ymList = ctx.select(CUSTOMER.FIRST_BUY_DATE)
.from(CUSTOMER)
.where(CUSTOMER.CUSTOMER_NAME.eq("Atelier One")).fetch(CUSTOMER.FIRST_BUY_DATE);
jOOQ applies our converter automatically, so there's no need to call it explicitly. It even works when we perform a coercing operation of ResultQuery<R1> to ResultQuery<R2>, like so:
Result<Record2<String, YearMonth>> result = ctx.resultQuery(
"SELECT customer_name, first_buy_date FROM customer")
.coerce(CUSTOMER.CUSTOMER_NAME, CUSTOMER.FIRST_BUY_DATE)
.fetch();
In other words, jOOQ uses our converter automatically for binding variables and for fetching data from java.util.ResultSet. In queries, we just treat FIRST_BUY_DATE as of type YEARMONTH. The code is named YearMonthConverterForcedTypes and is available for MySQL.
Defining an inline converter via Converter.of() or Converter.ofNullable()
In the previous section, our converter was written as a Java class, and we referenced that class in the configuration of the jOOQ Code Generator. But instead of writing this class, we can associate the custom data type with an inline converter, which is a converter written directly into the configuration. For this, we use the <converter/> tag, as follows:
<forcedTypes>
<forcedType>
<userType>java.time.YearMonth</userType>
...
<converter>
<![CDATA[
org.jooq.Converter.ofNullable(
Integer.class, YearMonth.class,
(Integer t) -> { return YearMonth.of(1970, 1).with(java.time.temporal.ChronoField.PROLEPTIC_MONTH, t); },
(YearMonth u) -> { return (int) u.getLong(java.time.temporal.ChronoField.PROLEPTIC_MONTH); }
)
]]>
</converter>
...
</forcedType>
</forcedTypes>
The usage part of this converter remains unchanged. The complete code is named InlineYearMonthConverter, and the programmatic version is named ProgrammaticInlineYearMonthConverter. Both applications are available for MySQL.
Defining an inline converter via lambda expressions
A more concise inline converter can be written via <lambdaExpression/>. This tag saves us from the explicit usage of Converter.of()/Converter.ofNullable() and allows us to simply specify a lambda expression that converts from the database type via the <from/> tag, and a lambda expression that converts to the database type via the <to/> tag. Let's exemplify this in our converter, as follows:
<forcedTypes>
<forcedType>
<userType>java.time.YearMonth</userType>
...
<lambdaConverter>
<from>
<![CDATA[(Integer t) -> { return YearMonth.of(1970, 1).with(java.time.temporal.ChronoField.PROLEPTIC_MONTH, t);
}]]>
</from>
<to>
<![CDATA[(YearMonth u) -> { return (int) u.getLong(java.time.temporal.ChronoField.PROLEPTIC_MONTH);
}]]>
</to>
</lambdaConverter>
...
</forcedType>
</forcedTypes>
Again, the usage part of this converter remains unchanged. The complete code is named LambdaYearMonthConverter, and the programmatic version is named ProgrammaticLambdaYearMonthConverter. Both applications are available for MySQL.
Matching forced types via SQL
In the previous sections, we matched the column names by using regexes in <includeExpression/> and <includeTypes/> tags. Whenever we need more complex criteria for matching column names, we can rely on the <sql/> tag. The body of this tag is an SQL query that executes against the dictionary views of our database. For instance, matching all columns of type TIMESTAMP from our MySQL classicmodels database can be achieved like so:
<sql>
SELECT concat('classicmodels.', TABLE_NAME, '.', COLUMN_NAME)FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'classicmodels'
AND TABLE_NAME != 'flyway_schema_history'
AND DATA_TYPE = 'timestamp'
</sql>
This should return several columns, among them being two from the PAYMENT table and one from the BANK_TRANSACTION table: PAYMENT.PAYMENT_DATE, PAYMENT.CACHING_DATE, and BANK_TRANSACTION.CACHING_DATE. For these columns, jOOQ will apply Converter<LocalDateTime, JsonNode> developed in the bundled code. But these are not the only columns returned by our query, and jOOQ will apply this converter to PAYMENT.MODIFIED and TOKEN.UPDATED_ON, which are also of type TIMESTAMP. Now, we have two options to avoid this—we can tune our query predicate accordingly or we can quickly add <excludeExpression/>, as follows:
<excludeExpression>
classicmodels.payment.modified
| classicmodels.token.updated_on
</excludeExpression>
You can find the example for MySQL under the name SqlMatchForcedTypes.
I'm pretty sure that you got the idea, and you know how to write such queries for your favorite database.
JSON converters
Whenever jOOQ detects that the database uses JavaScript Object Notation (JSON) data (for instance, a MySQL/PostgreSQL JSON type), it maps the database type to the org.jooq.JSON class. This is a very handy class that represents a neat JSON wrapper type for JSON data fetched from the database. Its API consists of the JSON.data() method that returns a String representation of org.jooq.JSON and a JSON.valueOf(String data) method that returns org.jooq.JSON from the String representation. Typically, org.jooq.JSON is all you need, but if you want to manipulate the fetched JSON via dedicated APIs (Jackson, Gson, JSON Binary (JSONB), and so on), then you need a converter.
So, in order to practice more examples, the bundled code with this book comes with a JSON converter (JsonConverter), as explained in more detail next.
For MySQL and PostgreSQL, which have the JSON data type. The converter converts between org.jooq.JSON and com.fasterxml.jackson.databind.JsonNode, therefore it implements Converter<JSON, JsonNode>. Of course, you can use this as an example, and replace Jackson's JsonNode with com.google.gson.Gson, javax/jakarta.json.bind.Jsonb, and so on. The code available for MySQL and PostgreSQL is named JsonConverterForcedTypes. A programmatic version of this application is available only for MySQL (but you can easily adapt it for any other dialect) and is named ProgrammaticJsonConverter.
For Oracle 18c, which doesn't have a dedicated JSON type (however, this type is available starting with Oracle 21c; see https://oracle-base.com/articles/21c/json-data-type-21c), it's common to use VARCHAR2(4000) for relatively small JSON data and BLOB for large JSON data. In both cases, we can add a CHECK ISJSON() constraint to ensure the JSON data validity. Astonishingly, jOOQ detects that JSON data is present, and it maps such columns to the org.jooq.JSON type. Our converter converts between org.jooq.JSON and com.fasterxml.jackson.databind.JsonNode. Consider the applications named ConverterJSONToJsonNodeForcedTypes.
For SQL Server, which doesn't have a dedicated JSON type, it's common to use NVARCHAR with a CHECK ISJSON() constraint. jOOQ doesn't have support to detect the usage of JSON data (as in the case of Oracle) and maps this type to String. In this context, we have a converter in JsonConverterVarcharToJSONForcedTypes that converts between NVARCHAR and org.jooq.JSON, and one between NVARCHAR and JsonNode in JsonConverterVarcharToJsonNodeForcedTypes.
Take your time and practice these examples in order to get familiar with jOOQ converters. Next, let's tackle UDT converters.
UDT converters
As you know from Chapter 3, jOOQ Core Concepts, Oracle and PostgreSQL support UDTs, and we have a UDT in our schema named EVALUATION_CRITERIA. This UDT is the data type of the MANAGER.MANAGER_EVALUATION field, and in Oracle, it looks like this:
CREATE OR REPLACE TYPE "EVALUATION_CRITERIA" AS OBJECT (
"communication_ability" NUMBER(7),
"ethics" NUMBER(7),
"performance" NUMBER(7),
"employee_input" NUMBER(7),
// the irrelevant part was skipped
);
We already know that the jOOQ Code Generator automatically maps the fields of the evaluation_criteria UDT via jooq.generated.udt.EvaluationCriteria, and the jooq...pojos.EvaluationCriteria Plain Old Java Object (POJO), respectively, maps the jooq...udt.EvaluationCriteria.EvaluationCriteriaRecord record.
But if we assume that our application needs to manipulate this type as JSON, then we need a converter that converts between EvaluationCriteriaRecord and JSON types (for instance, Jackson JsonNode). The JsonConverter stub looks like this:
public class JsonConverter implements
Converter<EvaluationCriteriaRecord, JsonNode> {@Override
public JsonNode from(EvaluationCriteriaRecord t) { ... }@Override
public EvaluationCriteriaRecord to(JsonNode u) { ... }...
}
Next, we configure this converter, as follows:
<forcedTypes>
<forcedType>
<userType>com.fasterxml.jackson.databind.JsonNode</userType>
<converter>com...converter.JsonConverter</converter>
<includeExpression>
CLASSICMODELS.MANAGER.MANAGER_EVALUATION
</includeExpression>
<includeTypes>EVALUATION_CRITERIA</includeTypes>
</forcedType>
</forcedTypes>
Having this set, we can express an INSERT statement, as follows:
JsonNode managerEvaluation = "{...}";ctx.insertInto(MANAGER,
MANAGER.MANAGER_NAME, MANAGER.MANAGER_EVALUATION)
.values("Mark Joy", managerEvaluation).execute();
And we can express a SELECT statement, as follows:
List<JsonNode> managerEvaluation = ctx.select(
MANAGER.MANAGER_EVALUATION)
.from(MANAGER)
.fetch(MANAGER.MANAGER_EVALUATION);
The bundled code is named ConverterUDTToJsonNodeForcedTypes and is available for Oracle and PostgreSQL.
Custom data types and type binding
Roughly, when we want to map a type onto a non-standard JDBC type (a type that is not in org.jooq.impl.SQLDataType), we need to focus on the org.jooq.Binding API, as illustrated in the following code snippet:
public interface Binding<T, U> extends Serializable { ... }For instance, binding the non-standard vendor-specific PostgreSQL HSTORE data type to some Java data type (for instance, HSTORE can be mapped quite conveniently to Java Map<String, String>) needs to take advantage of the Binding API, which contains the following methods (please read the comments):
// A converter that does the conversion between
// the database type T and the user type U
Converter<T, U> converter();
// A callback that generates the SQL string for bind values of // this binding type. Typically, just ?, but also ?::json, ...
void sql(BindingSQLContext<U> ctx) throws SQLException;
// Register a type for JDBC CallableStatement OUT parameters
ResultSet void register(BindingRegisterContext<U> ctx)
throws SQLException;
// Convert U to a type and set in on a JDBC PreparedStatement
void set(BindingSetStatementContext<U> ctx)
throws SQLException;
// Get a type from JDBC ResultSet and convert it to U
void get(BindingGetResultSetContext<U> ctx)
throws SQLException;
// Get a type from JDBC CallableStatement and convert it to U
void get(BindingGetStatementContext<U> ctx)
throws SQLException;
// Get a value from JDBC SQLInput (useful for Oracle OBJECT)
void get(BindingGetSQLInputContext<U> ctx)
throws SQLException;
// Get a value from JDBC SQLOutput (useful for Oracle OBJECT)
void set(BindingSetSQLOutputContext<U> ctx)
throws SQLException;
For instance, let's consider that we already have an org.jooq.Converter implementation between Map<String, String> and HSTORE named HstoreConverter, and we continue by adding an org.jooq.Binding implementation named HstoreBinding that starts like this:
public class HstoreBinding implements
Binding<Object, Map<String, String>> {private final HstoreConverter converter
= new HstoreConverter();
@Override
public final Converter<Object, Map<String, String>>
converter() {return converter;
}
...
}
On the other hand, for a MySQL vendor-specific POINT type, we may have a converter named PointConverter, and we need a PointBinding class as follows—the POINT type maps well to the Java Point2D.Double type:
public class PointBinding implements Binding<Object,Point2D> {private final PointConverter converter
= new PointConverter();
@Override
public final Converter<Object, Point2D> converter() {return converter;
}
...
}
Next, we focus on implementing the Binding SPI for PostgreSQL HSTORE and MySQL POINT. An important aspect of this is rendering a bind variable for the binding context's value and casting it to the HSTORE type. This is done in the sql() method, as follows:
@Override
public void sql(BindingSQLContext<Map<String, String>> ctx)
throws SQLException { if (ctx.render().paramType() == ParamType.INLINED) {ctx.render().visit(inline(
ctx.convert(converter()).value())).sql("::hstore"); } else { ctx.render().sql("?::hstore");}
}
Notice that for the jOOQ inlined parameters (for details, check Chapter 3, jOOQ Core Concepts), we don't need to render a placeholder (?); therefore, we render only the PostgreSQL specific syntax, ::hstore. Depending on the database-specific syntax, you have to render the expected SQL. For instance, for the PostgreSQL INET data type, you'll render ?::inet (or, ::inet), while for the MySQL POINT type, you'll render ST_PointFromText(?) as follows (Point2D is java.awt.geom.Point2D):
@Override
public void sql(BindingSQLContext<Point2D> ctx)
throws SQLException { if (ctx.render().paramType() == ParamType.INLINED) { ctx.render().sql("ST_PointFromText(").visit(inline(ctx.convert(converter()).value()))
.sql(")"); } else { ctx.render().sql("ST_PointFromText(?)");}
}
Next, we focus on registering a compatible/proper type for JDBC CallableStatement OUT parameters. Usually, VARCHAR is a proper choice (for instance, VARCHAR is a good choice for HSTORE, INET, or JSON types). The code is illustrated in the following snippet:
@Override
public void register(BindingRegisterContext
<Map<String, String>> ctx) throws SQLException {ctx.statement().registerOutParameter(
ctx.index(), Types.VARCHAR);
}
But since by default MySQL returns a POINT as binary data (as long as we don't use any MySQL function such as ST_AsText(g) or ST_AsWKT(g) for converting geometry values from an internal geometry format to a Well-Known Text (WKT) format), we can use java.sql.Blob, as illustrated in the following code snippet:
@Override
public void register(BindingRegisterContext<Point2D> ctx)
throws SQLException {ctx.statement().registerOutParameter(
ctx.index(), Types.BLOB);
}
Next, we convert Map<String, String> to a String value and set it on a JDBC PreparedStatement (for the MySQL POINT type, we convert Point2D to String), like so:
@Override
public void set(BindingSetStatementContext
<Map<String, String>> ctx) throws SQLException {ctx.statement().setString(ctx.index(), Objects.toString(
ctx.convert(converter()).value(), null));
}
Further, for PostgreSQL HSTORE, we get a String value from JDBC ResultSet and convert it to Map<String, String>, like so:
@Override
public void get(BindingGetResultSetContext
<Map<String, String>> ctx) throws SQLException {ctx.convert(converter()).value(
ctx.resultSet().getString(ctx.index()));
}
While for MySQL POINT, we get a Blob (or an InputStream) from JDBC ResultSet and convert it to Point2D, like so:
@Override
public void get(BindingGetResultSetContext<Point2D> ctx)
throws SQLException {ctx.convert(converter()).value(ctx.resultSet()
.getBlob(ctx.index())); // or, getBinaryStream()
}
Next, we do the same thing for JDBC CallableStatement. For the HSTORE type, we have the following:
@Override
public void get(BindingGetStatementContext
<Map<String, String>> ctx) throws SQLException {ctx.convert(converter()).value(
ctx.statement().getString(ctx.index()));
}
And for the POINT type, we have this:
@Override
public void get(BindingGetStatementContext<Point2D> ctx)
throws SQLException {ctx.convert(converter()).value(
ctx.statement().getBlob(ctx.index()));
}
Finally, we override the get(BindingGetSQLInputContext<?> bgsqlc) and set(BindingSetSQLOutputContext<?> bsqlc) methods. Since, we don't need them for HSTORE/POINT, we just throw an SQLFeatureNotSupportedException exception. For brevity, we skipped this code.
Once the Binding is ready, we have to configure it in the jOOQ Code Generator. This is quite similar to the configuration of a Converter only that, instead of using the <converter/> tag, we use the <binding/> tag as follows—here, we configure HstoreBinding (the configuration of PointBinding is available in the bundled code):
<forcedTypes>
<forcedType>
<userType>java.util.Map<String, String></userType>
<binding>com.classicmodels.binding.HstoreBinding</binding>
<includeExpression>
public.product.specs
</includeExpression>
<includeTypes>HSTORE</includeTypes>
</forcedType>
</forcedTypes>
Now, we can test HstoreBinding. For instance, the PRODUCT table has a field named SPECS of type HSTORE. The following code inserts a new product with some specifications:
ctx.insertInto(PRODUCT, PRODUCT.PRODUCT_NAME,
PRODUCT.PRODUCT_LINE, PRODUCT.SPECS)
.values("2002 Masserati Levante", "Classic Cars", Map.of("Length (in)", "197", "Width (in)", "77.5", "Height (in)", "66.1", "Engine", "Twin Turbo
Premium Unleaded V-6"))
.execute();
Here's what the rendered SQL looks like:
INSERT INTO "public"."product" (
"product_name", "product_line", "specs")
VALUES (?, ?, ?::hstore)
After resolving the ? placeholders, the SQL looks like this:
INSERT INTO "public"."product" (
"product_name", "product_line", "specs")
VALUES ('2002 Masserati Levante', 'Classic Cars', '"Width (in)"=>"77.5", "Length (in)"=>"197",
"Height (in)"=>"66.1",
"Engine"=>"Twin Turbo Premium Unleaded V-6"'::hstore)
At INSERT (UPDATE, DELETE, and so on), HstoreConverter converts from Java Map<String, String> to an HSTORE type. At SELECT, the same converter converts HSTORE to Map<String, String>. So, our SELECT statement could look like this:
List<Map<String, String>> specs = ctx.select(PRODUCT.SPECS)
.from(PRODUCT)
.where(PRODUCT.PRODUCT_NAME.eq("2002 Masserati Levante")).fetch(PRODUCT.SPECS);
Notice that we don't use explicitly any Binding or Converter and we don't touch the HSTORE type. For us, in the application, SPECS is of the type Map<String, String>.
Notice that, starting with jOOQ 3.15, we have access to the jOOQ-postgres-extensions module (https://github.com/jOOQ/jOOQ/issues/5507), which supports HSTORE as well.
Bindings and converters can be used to write different helper methods. For instance, the following method can be used to convert any Param to its database data type:
static <T> Object convertToDatabaseType(Param<T> param) {return param.getBinding().converter().to(param.getValue());
}
But what's happening without Binding? Is everything lost?
Understanding what's happening without Binding
When jOOQ detects a non-standard JDBC type that doesn't have an associated Binding, it will mark the corresponding field with @deprecated Unknown data type, and with the message, Please define an explicit {@link org.jooq.Binding} to specify how this type should be handled. Deprecation can be turned off using {@literal <deprecationOnUnknownTypes/>} in your Code Generator configuration.
As a rule of thumb, relying on Bindings is the way to go, but as a workaround, we can also use explicit mapping for SELECT statements and public static <T> Field<T> field(String sql, Class<T> type, Object... bindings), or another field() flavor that fits better, for INSERT, UPDATE, and so on.
However, using a non-standard JDBC type in INSERT statements (UPDATE statements, and so on) just like that leads to jOOQ's SQLDialectNotSupportedException exception, Type Foo is not supported in dialect Buzz, and in SELECT statements, to jOOQ's DataTypeException, No Converter found for types Foo and Buzz.
You can check the HSTORE examples from this section in the application named HstoreBinding, and the POINT examples in the application named PointGeometryBinding.
In addition, the bundled code contains InetBinding for the PostgreSQL INET type, JsonBinding for the PostgreSQL JSON type, and ProgrammaticInetBinding representing the programmatic configuration of Binding for the PostgreSQL INET type. Next, let's discuss enums and how to convert these.
Manipulating enums
jOOQ represents an SQL enum type (for example, the MySQL enum or PostgreSQL enum data type created via CREATE TYPE) via an interface named org.jooq.EnumType. Whenever the jOOQ Java Code Generator detects the usage of an SQL enum type, it automatically generates a Java enum that implements EnumType. For instance, the MySQL schema of the SALE table contains the following enum data type:
'vat' ENUM ('NONE', 'MIN', 'MAX') DEFAULT NULLFor vat, the jOOQ generator renders the jooq.generated.enums.VatType enum, as follows:
public enum VatType implements EnumType { NONE("NONE"), MIN("MIN"), MAX("MAX");private final String literal;
private VatType(String literal) {this.literal = literal;
}
@Override
public Catalog getCatalog() {return null;
}
@Override
public Schema getSchema() {return null;
}
@Override
public String getName() {return "sale_vat";
}
@Override
public String getLiteral() {return literal;
}
public static VatType lookupLiteral(String literal) {return EnumType.lookupLiteral(VatType.class, literal);
}
}
By default, the name of such a class is composed of the table name and the column name in PascalCase, which means that the name of the preceding class should be SaleVat. But whenever we want to modify the default name, we can rely on jOOQ generator strategies and regexes, as we did in Chapter 2, Customizing the jOOQ Level of Involvement. For instance, we've customized the preceding class name as VatType via the following strategy:
<strategy>
<matchers>
<enums>
<enum>
<expression>sale_vat</expression>
<enumClass>
<expression>VatType</expression>
<transform>AS_IS</transform>
</enumClass>
</enum>
</enums>
</matchers>
</strategy>
Having these pieces of knowledge is enough to start writing queries based on jOOQ-generated enums—for instance, an INSERT statement into the SALE table and a SELECT statement from it, as illustrated in the following code snippet:
import jooq.generated.enums.VatType;
...
ctx.insertInto(SALE, SALE.FISCAL_YEAR, ..., SALE.VAT)
.values(2005, ..., VatType.MAX)
.execute();
List<VatType> vats = ctx.select(SALE.VAT).from(SALE)
.where(SALE.VAT.isNotNull())
.fetch(SALE.VAT);
Of course, the Sale-generated POJO (or user-defined POJOs) and SaleRecord take advantage of VatType, as with any other type.
Writing enum converters
Whenever jOOQ-generated jOOQ enums are not enough, we focus on enum converters. Here is a non-exhaustive list of scenarios that may require some kind of enum converting to be done:
- Using your own Java enum for a database enum type
- Using your own Java enum for a database non-enum type (or enum-like type)
- Using a Java non-enum type for a database enum
- Always converting to a Java enum and occasionally to another Java enum
To simplify enum conversion tasks, jOOQ provides a built-in default converter named org.jooq.impl.EnumConverter. This converter can convert VARCHAR values to enum literals (and vice versa), or NUMBER values to enum ordinals (and vice versa). You can also instantiate it explicitly, as has been done here:
enum Size { S, M, XL, XXL; }Converter<String, Size> converter
= new EnumConverter<>(String.class, Size.class);
Next, let's tackle the previous list of enum scenarios.
Using your own Java enum for a database enum type
Of our four databases, only MySQL and PostgreSQL have dedicated types for enums. MySQL has the enum type and PostgreSQL has the CREATE TYPE foo AS enum( ...) syntax. In both cases, jOOQ generates enum classes on our behalf, but let's suppose that we'd prefer to use our own Java enums. For instance, let's focus on the MySQL schema of the SALE table, which contains these two enums:
`rate` ENUM ('SILVER', 'GOLD', 'PLATINUM') DEFAULT NULL`vat` ENUM ('NONE', 'MIN', 'MAX') DEFAULT NULLThe same enums in PostgreSQL are declared like this:
CREATE TYPE rate_type AS enum('SILVER', 'GOLD', 'PLATINUM');CREATE TYPE vat_type AS enum('NONE', 'MIN', 'MAX');…
rate rate_type DEFAULT NULL,
vat vat_type DEFAULT NULL,
And let's assume that for vat, we still rely on a jOOQ-generated Java enum-class (as in the previous section, VatType), while for rate, we have written the following Java enum:
public enum RateType { SILVER, GOLD, PLATINUM }In order to automatically map the rate column to the RateType enum, we rely on the <forcedType/> and <enumConverter/> flag tags, as illustrated here:
<forcedTypes>
<forcedType>
<userType>com.classicmodels.enums.RateType</userType>
<enumConverter>true</enumConverter>
<includeExpression>
classicmodels.sale.rate # MySQL
public.sale.rate # PostgreSQL
</includeExpression>
<includeTypes>
ENUM # MySQL
rate_type # PostgreSQL
</includeTypes>
</forcedType>
</forcedTypes>
By enabling <enumConverter/>, we instruct jOOQ to automatically apply the built-in org.jooq.impl.EnumConverter converter whenever SALE.RATE is used. Done! From this point forward, we can treat the SALE.RATE field as of type RateType, and jOOQ will handle the conversion aspects of the mapped field (listed here for MySQL), as follows:
public final TableField<SaleRecord, RateType> RATE
= createField(DSL.name("rate"), SQLDataType.VARCHAR(8), this, "", new EnumConverter<String, RateType>
(String.class, RateType.class));
The application named SimpleBuiltInEnumConverter contains the complete example for MySQL and PostgreSQL.
This is a very convenient approach and works the same in MySQL and PostgreSQL, but if we don't employ this automatic conversion, we still can use our RateType Java enum manually or explicitly. Let's see how!
First, we configure the jOOQ Code Generator to exclude enum generation for the sale_rate (MySQL)/rate_type (PostgreSQL) types; otherwise, the SALE.RATE field will be automatically mapped to the generated Java enum. The code is illustrated in the following snippet:
<database>
<excludes>
sale_rate (MySQL) / rate_type (PostgreSQL)
</excludes>
</database>
In this context, jOOQ maps SALE.RATE to String in MySQL, and to Object in PostgreSQL. In PostgreSQL, the field is annotated as @deprecated Unknown data type, but we turn off this deprecation via the <deprecationOnUnknownTypes/> configuration, as follows:
<generate>
<deprecationOnUnknownTypes>false</deprecationOnUnknownTypes>
</generate>
Next, in MySQL, we can write an INSERT statement, as follows (SALE.RATE is of type String):
ctx.insertInto(SALE, SALE.FISCAL_YEAR, ..., SALE.RATE)
.values(2005, ..., RateType.PLATINUM.name())
.execute();
And we can write a SELECT statement, as follows:
List<RateType> rates = ctx.select(SALE.RATE)
.from(SALE)
.where(SALE.RATE.isNotNull())
.fetch(SALE.RATE, RateType.class);
While for MySQL this is quite smooth, for PostgreSQL it's a little bit tricky. The PostgreSQL syntax requires us to render at INSERT something like ?::"public"."rate_type", as illustrated in the following code snippet:
ctx.insertInto(SALE, SALE.FISCAL_YEAR, ..., SALE.RATE)
.values(2005, ..., field("?::"public"."rate_type"", RateType.PLATINUM.name()))
.execute();
And at SELECT, we need an explicit coercing of Object to String, as illustrated in the following code snippet:
List<RateType> rates = ctx.select(SALE.RATE)
.from(SALE)
.where(SALE.RATE.isNotNull())
.fetch(SALE.RATE.coerce(String.class), RateType.class);
The application named MyEnumBuiltInEnumConverter contains complete examples for MySQL and PostgreSQL. If we don't suppress the jOOQ enum generation, then another approach consists of writing an explicit converter (by extending the jOOQ built-in org.jooq.impl.EnumConverter converter) between the jOOQ generated enum and our enum. Of course, this converter must be called explicitly in your queries. You can find such an example for the vat enum in the application mentioned earlier.
Using your own Java enum for a database non-enum type (or enum-like type)
Let's consider a legacy database containing a column that takes only certain values but was declared as VARCHAR (or NUMBER)—for instance, the SALE table has a TREND field of type VARCHAR that takes only the values UP, DOWN, and CONSTANT. In this context, it would be more practical to enforce the usage of this field via an enum, as shown here:
public enum TrendType { UP, DOWN, CONSTANT }But now, we have to handle the conversion between TrendType and VARCHAR. This can be done automatically by jOOQ if we add the following <forcedType/> tag (here, for Oracle):
<forcedType>
<userType>com.classicmodels.enums.TrendType</userType>
<enumConverter>true</enumConverter>
<includeExpression>
CLASSICMODELS.SALE.TREND
</includeExpression>
<includeTypes>VARCHAR2(10)</includeTypes>
</forcedType>
In the SimpleBuiltInEnumConverter application, you can see a complete example next to other examples for all four databases.
Since SQL Server and Oracle don't have an enum type, we have used an alternative. Among others, a common alternative relies on a CHECK constraint to obtain an enum-like behavior. These enum-like types can take advantage of <enumConverter/> exactly as shown previously. Here, it is the SALE.VAT field in Oracle:
vat VARCHAR2(10) DEFAULT NULL
CHECK (vat IN('NONE', 'MIN', 'MAX'))And here, it is the <forcedType/> tag:
<forcedType>
<userType>com.classicmodels.enums.VatType</userType>
<enumConverter>true</enumConverter>
<includeExpression>
CLASSICMODELS.SALE.VAT
</includeExpression>
<includeTypes>VARCHAR2(10)</includeTypes>
</forcedType>
If we don't want to rely on automatic conversion, then we can use an explicit converter, as follows:
public class SaleStrTrendConverter
extends EnumConverter<String, TrendType> { public SaleStrTrendConverter() {super(String.class, TrendType.class);
}
}
In the BuiltInEnumConverter application, you can find a complete example next to other examples for all four databases.
Using a Java non-enum type for a database enum
Sometimes, we need a non-enum type for a database enum. For instance, let's assume that we want to use some integers in place of the VatType enum (0 for NONE, 5 for MIN, and 19 for MAX) because we might need these integers in different computations. Maybe the best idea is to write a converter that starts like this:
public class SaleVatIntConverter
extends EnumConverter<VatType, Integer> { … }But this doesn't work, because the EnumConverter signature is actually of type EnumConverter<T,U extends Enum<U>>. Obviously, Integer doesn't pass this signature since it doesn't extend java.lang.Enum, hence we can rely on a regular converter (as you saw in the previous section), as illustrated here:
public class SaleVatIntConverter
implements Converter<VatType, Integer> { … }The BuiltInEnumConverter application contains this example next to other examples. Of course, you can try to write this converter as an inline converter via Converter.of()/ofNullable() or lambda expressions as well.
Always converting to a Java enum and occasionally to another Java enum
Always converting to a Java enum and occasionally to another Java enum is most probably not such a popular task, but let's use it as a pretext to condense what we've learned so far about enum conversions.
Let's consider the well-known SALE.RATE enum field in MySQL. First, we want to always/automatically convert SALE.RATE to our RateType Java enum, shown here:
public enum RateType { SILVER, GOLD, PLATINUM }For this, we write the following <forcedType/> tag:
<forcedType>
<userType>com.classicmodels.enums.RateType</userType>
<enumConverter>true</enumConverter>
<includeExpression>
classicmodels.sale.rate
</includeExpression>
<includeTypes>ENUM</includeTypes>
</forcedType>
So far, we can refer in queries to SALE.RATE as a RateType enum, but let's assume that we also have the following StarType enum:
public enum StarType { THREE_STARS, FOUR_STARS, FIVE_STARS }Basically, StarType is an alternative to RateType (THREE_STARS corresponds to SILVER, FOUR_STARS to GOLD, and FIVE_STARS to PLATINUM). Now, we may occasionally want to use StarType in queries instead of RateType, therefore we need a converter, as follows:
public class SaleRateStarConverter extends
EnumConverter<RateType, StarType> {public final static SaleRateStarConverter
SALE_RATE_STAR_CONVERTER = new SaleRateStarConverter();
public SaleRateStarConverter() {super(RateType.class, StarType.class);
}
@Override
public RateType to(StarType u) { if (u != null) { return switch (u) {case THREE_STARS -> RateType.SILVER;
case FOUR_STARS -> RateType.GOLD;
case FIVE_STARS -> RateType.PLATINUM;
};
}
return null;
}
}
Since RateType and StarType don't contain the same literals, we have to override the to() method and define the expected matches. Done!
Expressing an INSERT statement that uses RateType looks like this:
// rely on <forcedType/>
ctx.insertInto(SALE, SALE.FISCAL_YEAR, ,..., SALE.RATE)
.values(2005, ..., RateType.PLATINUM)
.execute();
And whenever we want to use StarType instead of RateType, we rely on the static SALE_RATE_STAR_CONVERTER converter, as shown here:
// rely on SALE_RATE_STAR_CONVERTER
ctx.insertInto(SALE, SALE.FISCAL_YEAR, ..., SALE.RATE)
.values(2005, ...,
SALE_RATE_STAR_CONVERTER.to(StarType.FIVE_STARS))
.execute();
The BuiltInEnumConverter application contains this example, along with other examples.
Via classicmodels.sale.rate, we nominated a certain column (CLASSICMODELS.SALE.RATE), but we may want to pick up all columns of this enum type. In such cases, an SQL query is more proper than a regex. Here is such a query for Oracle:
SELECT 'CLASSICMODELS.' || tab.table_name || '.'
|| cols.column_name
FROM sys.all_tables tab
JOIN sys.all_constraints con ON tab.owner = con.owner
AND tab.table_name = con.table_name
JOIN sys.all_cons_columns cols ON cols.owner = con.owner
AND cols.constraint_name = con.constraint_name
AND cols.table_name = con.table_name
WHERE constraint_type = 'C'
AND tab.owner in ('CLASSICMODELS')AND search_condition_vc
= q'[rate IN('SILVER', 'GOLD', 'PLATINUM')]'You can find this example for MySQL and Oracle as BuiltInEnumSqlConverter.
In the bundled code, there are more applications, such as EnumConverter, which has examples of plain org.jooq.Converter types for enums; EnumConverterForceTypes, which has <forcedType/> and enum examples; and InsertEnumPlainSql, which has INSERT and enum examples when the jOOQ Code Generator is not used.
Retrieving the DataType<T> tag for a given enum data type
Retrieving the DataType<T> tag for a given enum data type can be done as in the following three examples that speak for themselves:
DataType<RateType> RATETYPE = SALE.RATE.getDataType();
DataType<VatType> VATTYPE
= VARCHAR.asEnumDataType(VatType.class);
DataType<com.classicmodels.enums.VatType> VATTYPE
= VARCHAR.asEnumDataType(jooq.generated.enums.VatType.class)
.asConvertedDataType(VAT_CONVERTER);
Now, you can use this data type as any other data type. Next, let's tackle the topic of data type rewrites.
Data type rewrites
Another utility of <forcedTypes/> is data type rewrites. This allows us to explicitly choose the SQL data type (supported by the database, or unsupported but present in org.jooq.impl.SQLDataType) that should be used in Java.
For instance, in Oracle, a common use case is to map the missing BOOLEAN type as NUMBER(1,0) or CHAR(1), as follows:
CREATE TABLE sale (
...
hot NUMBER(1,0) DEFAULT 0
hot CHAR(1) DEFAULT '1' CHECK (hot IN('1', '0'))...
}
But this means that the jOOQ Code Generator will map fields of type NUMBER(1, 0) to the SQLDataType.TINYINT SQL data type and the java.lang.Byte type and, respectively, the fields of type CHAR(1) to the SQLDataType.CHAR SQL data type and the String Java type.
But the Java String type is commonly associated with text data manipulation, while the Byte type is commonly associated with binary data manipulations (for example, reading/writing a binary file) and the Java Boolean type clearly communicates the intention of using flag-type data. Moreover, the Java Boolean type has an SQL type (standard JDBC type) homologous to SQLDataType.BOOLEAN.
jOOQ allows us to force the type of columns, therefore we can force the type of SALE.HOT to BOOLEAN, as follows:
<forcedType>
<name>BOOLEAN</name>
<includeExpression>CLASSICMODELS.SALE.HOT</includeExpression>
<includeTypes>NUMBER(1,s*0)</includeTypes>
<includeTypes>CHAR(1)</includeTypes>
</forcedType>
Done! Now, we can treat SALE.HOT as a Java Boolean type. Here is an INSERT example:
ctx.insertInto(SALE, ..., SALE.HOT)
.values(2005,..., Boolean.FALSE)
.execute();
Depending on NUMBER precision, jOOQ will map this data type to BigInteger, Short, or even Byte (as you just saw). If you find it cumbersome to use such Java types and you know that your data fits better for Long or Integer types, then you have two options: adjust the NUMBER precision accordingly, or rely on jOOQ type rewriting. Of course, you can apply this technique to any other type and dialect.
A complete example can be found in DataTypeRewriting. The programmatic version of this example is called ProgrammaticDataTypeRewriting. Next, let's understand how you can handle jOOQ embeddable types.
Handling embeddable types
Embeddable types represent a powerful feature introduced in jOOQ 3.14. Roughly, this feature gets materialized in synthetic UDTs that can be used with all databases supported by jOOQ. While PostgreSQL and Oracle support UDTs (we can use UDTs directly in Data Definition Language (DDL)), other databases including MySQL and SQL Server don't support UDTs. But via jOOQ embeddable types, we can work at the application level with synthetic UDTs for any database, and jOOQ will take care of the underlying aspects of mapping these types to the database.
An embeddable type mimics a UDT by synthetically wrapping one (usually more) database column in a generated org.jooq.EmbeddableRecord. For instance, we can wrap OFFICE.CITY, OFFICE.STATE, OFFICE.COUNTRY, OFFICE.TERRITORY, and OFFICE.ADDRESS_LINE_FIRST under an embeddable type named OFFICE_FULL_ADDRESS via the following configuration in the jOOQ Code Generator (here, for MySQL):
<embeddable>
<!-- The optional catalog of the embeddable type -->
<catalog/>
<!-- The optional schema of the embeddable type -->
<schema>classicmodels</schema>
<!-- The name of the embeddable type -->
<name>OFFICE_FULL_ADDRESS</name>
<!-- An optional, defining comment of an embeddable -->
<comment>The full address of an office</comment>
<!-- The name of the reference to the embeddable type -->
<referencingName/>
<!-- An optional, referencing comment of an embeddable -->
<referencingComment/>
And we continue with the settings for matching tables and fields, as follows:
<!-- A regular expression matching qualified/unqualified
table names to which to apply this embeddable. If left
blank, this will apply to all tables -->
<tables>.*.office</tables>
<!-- A list of fields to match to an embeddable. Each field
must match exactly one column in each matched table. A
mandatory regular expression matches field names, and
an optional name can be provided to define the
embeddable attribute name. If no name is provided, then
the first matched field's name will be taken -->
<fields>
<field><expression>CITY</expression></field>
<field><expression>ADDRESS_LINE_FIRST</expression></field>
<field><expression>STATE</expression></field>
<field><expression>COUNTRY</expression></field>
<field><expression>TERRITORY</expression></field>
</fields> </embeddable>
Next, jOOQ generates jooq...records.OfficeFullAddressRecord, which extends EmbeddableRecordImpl and jooq...pojos.OfficeFullAddress. Moreover, in the generated Office table, we observe a new OFFICE_FULL_ADDRESS field that can be used as in the following INSERT statement:
ctx.insertInto(OFFICE, ... ,
OFFICE.ADDRESS_LINE_SECOND, ...)
.values(...,
new OfficeFullAddressRecord("Naples", "Giuseppe Mazzini", "Campania", "Italy", "N/A"),
...)
.execute();
Obviously, the OFFICE_FULL_ADDRESS column can be used in all types of statements, including INSERT, UPDATE, DELETE, and SELECT. Here, it is used in a SELECT statement:
Result<Record1<OfficeFullAddressRecord>> result
= ctx.select(OFFICE.OFFICE_FULL_ADDRESS).from(OFFICE)
.fetch();
Or it can be fetched into the OfficeFullAddress POJO, like this:
List<OfficeFullAddress> result
= ctx.select(OFFICE.OFFICE_FULL_ADDRESS).from(OFFICE)
.fetchInto(OfficeFullAddress.class);
In the bundled code, for MySQL, we have EmbeddableType, which contains the previous example, and for PostgreSQL, we have ProgrammaticEmbeddableType, which is the programmatic version of the previous example.
Replacing fields
At this point, we have access to (we can use) the embeddable type, but we still have direct access to fields wrapped in this embeddable type. For instance, these fields can be used in INSERT statements, SELECT statements, and so on, and they appear in the Integrated Development Environment's (IDE's) autocompletion list.
The replacing fields feature means to signal to jOOQ to disallow direct access to fields that are part of an embeddable type. These fields will not appear in the IDE's autocompletion list anymore, and the result set of SELECT statements will not contain these fields. Enabling this feature can be done via the <replacesFields/> flag, as follows:
<embeddable>
...
<replacesFields>true</replacesFields>
</embeddable>
The EmbeddableTypeReplaceFields application contains this example for Oracle, while ProgrammaticEmbeddableTypeReplaceFields contains a programmatic version of this example for SQL Server.
Converting embeddable types
Converting an embeddable type can be done via org.jooq.Converter, as for any other type. For example, converting between JsonNode and OFFICE_FULL_ADDRESS can be done via a Converter that starts like this:
public class JsonConverter implements
Converter<OfficeFullAddressRecord, JsonNode> {public static final JsonConverter JSON_CONVERTER
= new JsonConverter();
@Override
public JsonNode from(OfficeFullAddressRecord t) { ... }@Override
public OfficeFullAddressRecord to(JsonNode u) { ... }...
}
And here, it is a SELECT statement that fetches OFFICE.OFFICE_FULL_ADDRESS as JsonNode via JSON_CONVERTER:
List<JsonNode> result = ctx.select(OFFICE.OFFICE_FULL_ADDRESS)
.from(OFFICE)
.fetch(OFFICE.OFFICE_FULL_ADDRESS, JSON_CONVERTER);
The ConvertEmbeddableType application for MySQL contains this example.
Embedded domains
Quite popular in PostgreSQL, domain types represent UDTs built on top of other types and containing optional constraints. For instance, in our PostgreSQL schema, we have the following domain:
CREATE DOMAIN postal_code AS varchar(15)
CHECK(
VALUE ~ '^d{5}$'OR VALUE ~ '^[A-Z]{2}[0-9]{3}[A-Z]{2}$');
And it is used in the office table, as shown here:
CREATE TABLE office (
...
"postal_code" postal_code NOT NULL,
...
);
jOOQ can generate a Java type for each domain type if we turn on this feature, as has been done here:
// Maven and standalone
<database>
...
<embeddableDomains>.*</embeddableDomains>
</database>
// Gradle
database {embeddableDomains = '.*'
}
// programmatic
withEmbeddableDomains(".*")While .* matches all domain types, you can use more restrictive regexes to match exactly the domains that will be replaced by embeddable types.
The jOOQ Code Generator generates an embeddable type named (by default) PostalCodeRecord (in jooq.generated.embeddables.records). We can use it for creating semantically type-safe queries, as in these examples:
ctx.select(OFFICE.CITY, OFFICE.COUNTRY)
.from(OFFICE)
.where(OFFICE.POSTAL_CODE.in(
new PostalCodeRecord("AZ934VB"), new PostalCodeRecord("DT975HH"))).fetch();
ctx.insertInto(OFFICE, ..., OFFICE.POSTAL_CODE, ...)
.values(..., new PostalCodeRecord("OP909DD"), ...).execute();
The complete code for PostgreSQL is named Domain.
Well, we've reached the end of this section and the end of this chapter. Notice that we intentionally skipped the topic of embeddable types and embeddable keys (including composite keys) since this topic is covered later in Chapter 11, jOOQ Keys.
Summary
This chapter is a must-have in your jOOQ arsenal. Mastering the topics covered here—such as custom data types, converters, bindings, database vendor-specific data types, enums, embeddable types, and so on—will help you to shape the interaction between Java and database data types to fit your non-trivial scenarios. In the next chapter, we cover the topics of fetching and mapping.