Ad-hoc Converter
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Sometimes, you want to attach an ad-hoc converter to some column, just for a single query or a few local queries. This is possible through a variety of ways. The most convenient one is to call Field.convert() and related methods. Assuming you have a converter like this to convert your SQL VARCHAR columns to Language:
enum Language { de, en, fr, it, pt; }
Converter<String, Language> converter = new EnumConverter<>(String.class, Language.class);
Now, instead of attaching them to your generated code, you may keep your generated code as Field<String>, and convert them on the fly to Field<Language> for the purpose of a single query:
Result<Record2<Integer, Language>> result =
create.select(LANGUAGE.ID, LANGUAGE.CD.convert(converter))
.from(LANGUAGE)
.fetch();
Alternatively, if you don't even have a Converter instance ready, you can attach conversion logic to fields like this:
Result<Record2<Integer, Language>> result =
create.select(LANGUAGE.ID, LANGUAGE.CD.convert(Language.class, Language::valueOf, Language::name))
.from(LANGUAGE)
.fetch();
Or, since in this case, conversions only happen from the database type (the T type) to the user type (the U type), you can omit the inverse conversion function using Field.convertFrom() ("from" as in "reading from the database"):
Result<Record2<Integer, Language>> result =
create.select(LANGUAGE.ID, LANGUAGE.CD.convertFrom(Language.class, Language::valueOf))
.from(LANGUAGE)
.fetch();
The inverse is possible too, e.g. when you only need to convert from the user type (the U type) to the database type (the T type) using Field.convertTo() ("to" as in "writing to the database"):
Result<Record2<Integer, Language>> result =
create.insertInto(LANGUAGE)
.columns(LANGUAGE.ID, LANGUAGE.CD.convertTo(Language.class, Language::name))
.values(5, Language.it)
.execute();
Using ad hoc converters on nested collections
Ad-hoc converters are extremely powerful when used on nested collections, e.g. those constructed with the MULTISET value constructor
// Structurally typed result
Result<Record4<
String, // AUTHOR.FIRST_NAME
String, // AUTHOR.LAST_NAME
Result<Record2<
String, // LANGUAGE.CD
String // LANGUAGE.DESCRIPTION
>>, // books
Result<Record1<
String // BOOK_TO_BOOK_STORE.BOOK_STORE_NAME
>> // book_stores
>> result = create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
multiset(
selectDistinct(
BOOK.language().CD,
BOOK.language().DESCRIPTION)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
).as("books"),
multiset(
selectDistinct(BOOK_TO_BOOK_STORE.BOOK_STORE_NAME)
.from(BOOK_TO_BOOK_STORE)
.where(BOOK_TO_BOOK_STORE.tBook().AUTHOR_ID.eq(AUTHOR.ID))
).as("book_stores"))
.from(AUTHOR)
.orderBy(AUTHOR.ID)
.fetch();
For details about MULTISET, refer to the section about the MULTISET value constructor. Now, instead of the above structurally typed result, it may be desirable to map things into Java 16 record types instead, or some other form of DTO:
record Book(String cd, String description) {}
record BookStore(String name) {}
record Author(String firstName, String lastName, List<Book> books, List<BookStore> bookStores) {}
And now, using the static-import friendly org.jooq.Records utility:
// Nominally typed result, all type checked!
List<Author> result = create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
// This is now a Field<List<Book>>
multiset(
selectDistinct(
BOOK.language().CD,
BOOK.language().DESCRIPTION)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
).as("books").convertFrom(r -> r.map(Records.mapping(Book::new))),
// This is now a Field<List<BookStore>>
multiset(
selectDistinct(BOOK_TO_BOOK_STORE.BOOK_STORE_NAME)
.from(BOOK_TO_BOOK_STORE)
.where(BOOK_TO_BOOK_STORE.tBook().AUTHOR_ID.eq(AUTHOR.ID))
).as("book_stores").convertFrom(r -> r.map(Records.mapping(BookStore::new))))
.from(AUTHOR)
.orderBy(AUTHOR.ID)
.fetch(Records.mapping(Author::new));
Try adding or removing a column from the projections, or adding or removing an attribute from your records, and the query no longer type-checks!
Of course, the usual reflective RecordMapper API can still be used just the same with these ad-hoc converters.
// Nominally typed result, but not strongly type checked
List<Author> result = create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
// This is now a Field<List<Book>>
multiset(
selectDistinct(
BOOK.language().CD,
BOOK.language().DESCRIPTION)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
).as("books").convertFrom(r -> r.into(Book.class)),
// This is now a Field<List<BookStore>>
multiset(
selectDistinct(BOOK_TO_BOOK_STORE.BOOK_STORE_NAME)
.from(BOOK_TO_BOOK_STORE)
.where(BOOK_TO_BOOK_STORE.tBook().AUTHOR_ID.eq(AUTHOR.ID))
).as("book_stores").convertFrom(r -> r.into(BookStore.class)))
.from(AUTHOR)
.orderBy(AUTHOR.ID)
.fetchInto(Author.class);
Feedback
Do you have any feedback about this page? We'd love to hear it!