Ad-hoc Data Type Conversion with jOOQ 3.15

jOOQ 3.15 shipped with a ton of new features, the most important ones being:

• MULTISET support (type safe, nested collections)
• Reactive SQL support via R2DBC

A very useful, lesser known new feature is “ad-hoc data type conversion”. Data type converters and bindings have been around in jOOQ for a long time. Their goal is to allow for using custom data types for common JDBC types like String or Integer. So, if you have a table like this:

CREATE TABLE furniture (
  id INT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  length NUMERIC,
  width NUMERIC,
  height NUMERIC
);

Instead of using BigDecimal for those dimensions, you may have preferred a custom, more semantic wrapper type for numbers, such as:

record Dimension(BigDecimal value) {}

And your Java representation of Furniture would be:

record Furniture(
  Integer id,
  String name,
  Dimension length,
  Dimension width,
  Dimension height
) {}

You’d go and attach a converter to your code generator, e.g.

<configuration>
  <generator>                            
    <database>
      <forcedTypes>
        <forcedType>
          <userType>com.example.Dimension</userType>
          <converter><![CDATA[
          org.jooq.Converter.ofNullable(
            BigDecimal.class,
            Dimension.class,
            Dimension::new,
            Dimension::value
          )
          ]]></converter>
          <includeExpression>LENGTH|WIDTH|HEIGHT</includeExpression>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>

That would allow you to query your database like this:

Result<Record3<Dimension, Dimension, Dimension>> result =
ctx.select(FURNITURE.LENGTH, FURNITURE.WIDTH, FURNITURE.HEIGHT)
   .from(FURNITURE)
   .fetch();

But sometimes, you can’t leverage code generation:

• You can’t access the code generator configuration for some reason
• You don’t want to attach a converter to your columns for every query
• You’re not using the code generator because you have a dynamic schema known only at runtime

Enter Ad-hoc Converters

Starting from jOOQ 3.15, we support various ways of registering a convenient ad-hoc converter to your Field<T> expression. This feature was mainly introduced to allow for mapping MULTISET nested collections to lists of a custom data type (a feature we urge you to try out, you won’t look back!)

But you can use the feature also for any other Field expression. Assuming you can’t use code generation for the above query (the main reason, again, being your schema being dynamic). You would probably write something like this:

Table<?> furniture = table(name("furniture"));
Field<BigDecimal> length = field(name("furniture", "length"), NUMERIC);
Field<BigDecimal> width  = field(name("furniture", "width"), NUMERIC);
Field<BigDecimal> height = field(name("furniture", "height"), NUMERIC);

Result<Record3<BigDecimal, BigDecimal, BigDecimal>> result =
ctx.select(length, width, height)
   .from(furniture)
   .fetch();

As always, the usual static imports are implied:

import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;

But code generation is ultimately just convenience. You can always achieve everything you can with jOOQ’s code generator also without it (though I do recommend you use code generation if possible!). So, in order to re-use our Dimension data type, historically, you could do this:

DataType<Dimension> type = NUMERIC.asConvertedDataType(
    Converter.ofNullable(
        BigDecimal.class,
        Dimension.class,
        Dimension::new,
        Dimension::value
    )    
);

Table<?> furniture = table(name("furniture"));
Field<Dimension> length = field(name("furniture", "length"), type);
Field<Dimension> width  = field(name("furniture", "width"), type);
Field<Dimension> height = field(name("furniture", "height"), type);

Result<Record3<Dimension, Dimension, Dimension>> result =
ctx.select(length, width, height)
   .from(furniture)
   .fetch();

That’s already very neat. But again, you’re going to create a Field reference that always uses this converter. Maybe, you wanted conversion to apply just for this one query? No problem with ad-hoc converters! Write this:

Table<?> furniture = table(name("furniture"));
Field<BigDecimal> length = field(name("furniture", "length"), NUMERIC);
Field<BigDecimal> width  = field(name("furniture", "width"), NUMERIC);
Field<BigDecimal> height = field(name("furniture", "height"), NUMERIC);

Result<Record3<BigDecimal, BigDecimal, Dimension>> result =
ctx.select(length, width, height.convertFrom(Dimension::new))
   // ad-hoc conversion here:    ^^^^^^^^^^^^^^^^^^^^^^^^^^^
   .from(furniture)
   .fetch();

There are various overloads of Field.convert(), the most powerful one being the ones that accept a complete Binding or Converter reference. The above one is very convenient, as it allows you to provide only the “from” Function<T, U> of a converter, omitting the Class<T>, Class<U>, and “to” Function<U, T>.

What is a Converter?

What is a Converter after all? It is an implementation for this:

public interface Converter<T, U> {
    U from(T databaseObject);
    T to(U userObject);
    Class<T> fromType();
    Class<U> toType();
}

Where:

• T is the “JDBC type”, i.e. a technical type understood by the JDBC API, such as String or BigDecimal
• U is the “user type”, i.e. a semantic type that you choose to represent data in your client application
• Class<T> is a class literal for T, required for reflection purposes, e.g. to create an array T[] at runtime
• Class<U> is a class literal for U, required for reflection purposes, e.g. to create an array U[] at runtime

When attaching a Converter to the code generator, it is always good to provide all of the above. The two conversion functions converting between T and U, as well as the class literals. You never know if jOOQ needs them for some specific operation.

But in the case of ad-hoc conversion, you usually only need one of the from (read) or to (write) functions. Why repeat all of the rest? Hence, these options:

// A "read-only" field converting from BigDecimal to Dimension
height.convertFrom(Dimension::new);

// Like above, but with an explicit class literal, if needed
height.convertFrom(Dimension.class, Dimension::new);

// A "write-only" field converting from Dimension to BigDecimal
height.convertTo(Dimension::value);

// Like above, but with an explicit class literal, if needed
height.convertTo(Dimension.class, Dimension::value);

// Full read/write converter support
height.convert(Dimension.class, Dimension::new, Dimension::value);
height.convert(Converter.ofNullable(
    BigDecimal.class,
    Dimension.class, 
    Dimension::new, 
    Dimension::value
));

What’s the difference between “read-only” and “write-only” conversions? Simple. Look at these queries:

Result<Record1<Dimension>> result =
ctx.select(height.convertFrom(Dimension::new))
   .from(furniture)
   .fetch();

ctx.insertInto(furniture)
   .columns(height.convertTo(Dimension::value))
   .values(new Dimension(BigDecimal.ONE))
   .execute();

So, in summary:

• The read-only ad-hoc converter is useful in projections (SELECT)
• The write-only ad-hoc converter is useful in predicates (WHERE), or DML

from Java, SQL and jOOQ. https://ift.tt/3hN5AoC
via IFTTT