Advert-hoc Information Kind Conversion with jOOQ 3.15 – Java, SQL and jOOQ.

jOOQ 3.15 shipped with a ton of latest options, an important ones being:

A really helpful, lesser identified new characteristic is “ad-hoc information kind conversion”. Information kind converters and bindings have been round in jOOQ for a very long time. Their purpose is to permit for utilizing customized information varieties for widespread JDBC varieties like String or Integer. So, in case you have a desk like this:

CREATE TABLE furnishings (
  id INT NOT NULL PRIMARY KEY,
  identify TEXT NOT NULL,
  size NUMERIC,
  width NUMERIC,
  peak NUMERIC
);

As an alternative of utilizing BigDecimal for these dimensions, you might have most popular a customized, extra semantic wrapper kind for numbers, akin to:

file Dimension(BigDecimal worth) {}

And your Java illustration of Furnishings could be:

file Furnishings(
  Integer id,
  String identify,
  Dimension size,
  Dimension width,
  Dimension peak
) {}

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

<configuration>
  <generator>                            
    <database>
      <forcedTypes>
        <forcedType>
          <userType>com.instance.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 will can help you question your database like this:

Consequence<Record3<Dimension, Dimension, Dimension>> outcome =
ctx.choose(FURNITURE.LENGTH, FURNITURE.WIDTH, FURNITURE.HEIGHT)
   .from(FURNITURE)
   .fetch();

However typically, you’ll be able to’t leverage code technology:

• You may’t entry the code generator configuration for some cause
• You don’t wish to connect a converter to your columns for each question
• You’re not utilizing the code generator as a result of you have got a dynamic schema identified solely at runtime

Enter Advert-hoc Converters

Ranging from jOOQ 3.15, we help varied methods of registering a handy ad-hoc converter to your Area<T> expression. This characteristic was primarily launched to permit for mapping MULTISET nested collections to lists of a customized information kind (a characteristic we urge you to check out, you received’t look again!)

However you should utilize the characteristic additionally for every other Area expression. Assuming you’ll be able to’t use code technology for the above question (the primary cause, once more, being your schema being dynamic). You’ll most likely write one thing like this:

Desk<?> furnishings = desk(identify("furnishings"));
Area<BigDecimal> size = subject(identify("furnishings", "size"), NUMERIC);
Area<BigDecimal> width  = subject(identify("furnishings", "width"), NUMERIC);
Area<BigDecimal> peak = subject(identify("furnishings", "peak"), NUMERIC);

Consequence<Record3<BigDecimal, BigDecimal, BigDecimal>> outcome =
ctx.choose(size, width, peak)
   .from(furnishings)
   .fetch();

As at all times, the same old static imports are implied:

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

However code technology is in the end simply comfort. You may at all times obtain every part you’ll be able to with jOOQ’s code generator additionally with out it (although I do suggest you employ code technology if doable!). So, in an effort to re-use our Dimension information kind, traditionally, you might do that:

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

Desk<?> furnishings = desk(identify("furnishings"));
Area<Dimension> size = subject(identify("furnishings", "size"), kind);
Area<Dimension> width  = subject(identify("furnishings", "width"), kind);
Area<Dimension> peak = subject(identify("furnishings", "peak"), kind);

Consequence<Record3<Dimension, Dimension, Dimension>> outcome =
ctx.choose(size, width, peak)
   .from(furnishings)
   .fetch();

That’s already very neat. However once more, you’re going to create a Area reference that at all times makes use of this converter. Perhaps, you wished conversion to use only for this one question? No downside with ad-hoc converters! Write this:

Desk<?> furnishings = desk(identify("furnishings"));
Area<BigDecimal> size = subject(identify("furnishings", "size"), NUMERIC);
Area<BigDecimal> width  = subject(identify("furnishings", "width"), NUMERIC);
Area<BigDecimal> peak = subject(identify("furnishings", "peak"), NUMERIC);

Consequence<Record3<BigDecimal, BigDecimal, Dimension>> outcome =
ctx.choose(size, width, peak.convertFrom(Dimension::new))
   // ad-hoc conversion right here:    ^^^^^^^^^^^^^^^^^^^^^^^^^^^
   .from(furnishings)
   .fetch();

There are numerous overloads of Area.convert(), probably the most highly effective one being those that settle for an entire Binding or Converter reference. The above one may be very handy, because it means that you can present solely the “from” Perform<T, U> of a converter, omitting the Class<T>, Class<U>, and “to” Perform<U, T>.

What’s a Converter?

What’s a Converter in spite of everything? It’s an implementation for this:

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

The place:

• T is the “JDBC kind”, i.e. a technical kind understood by the JDBC API, akin to String or BigDecimal
• U is the “consumer kind”, i.e. a semantic kind that you simply select to signify information in your shopper software
• Class<T> is a category literal for T, required for reflection functions, e.g. to create an array T[] at runtime
• Class<U> is a category literal for U, required for reflection functions, e.g. to create an array U[] at runtime

When attaching a Converter to the code generator, it’s at all times good to offer the entire above. The 2 conversion features changing between T and U, in addition to the category literals. You by no means know if jOOQ wants them for some particular operation.

However within the case of ad-hoc conversion, you often solely want one of many from (learn) or to (write) features. Why repeat the entire relaxation? Therefore, these choices:

// A "read-only" subject changing from BigDecimal to Dimension
peak.convertFrom(Dimension::new);

// Like above, however with an express class literal, if wanted
peak.convertFrom(Dimension.class, Dimension::new);

// A "write-only" subject changing from Dimension to BigDecimal
peak.convertTo(Dimension::worth);

// Like above, however with an express class literal, if wanted
peak.convertTo(Dimension.class, Dimension::worth);

// Full learn/write converter help
peak.convert(Dimension.class, Dimension::new, Dimension::worth);
peak.convert(Converter.ofNullable(
    BigDecimal.class,
    Dimension.class, 
    Dimension::new, 
    Dimension::worth
));

What’s the distinction between “read-only” and “write-only” conversions? Easy. Take a look at these queries:

Consequence<Record1<Dimension>> outcome =
ctx.choose(peak.convertFrom(Dimension::new))
   .from(furnishings)
   .fetch();

ctx.insertInto(furnishings)
   .columns(peak.convertTo(Dimension::worth))
   .values(new Dimension(BigDecimal.ONE))
   .execute();

So, in abstract:

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