Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14

ConverterProvider

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ supports some useful default data type conversion between common JDBC data types in org.jooq.tools.Convert. These conversions include, for example:

int i = Convert.convert("1", int.class); // Yields 1
Date d = Convert.convert("2000-01-01", Date.class); // Yields Date.valueOf("2000-01-01")

These auto-conversions are made available throughout the jOOQ API, for example when writing

Record record = create.fetchSingle(field("current_date"));
LocalDate d1 = record.get(0, LocalDate.class);
LocalDate d2 = create.fetchSingle(field("current_date"), LocalDate.class);

These auto-conversions are also applied implicitly when mapping POJOs as the previous sections have shown:

class POJO {
    LocalDate date;
}

POJO pojo = create.fetchSingle(field("current_date").as("date")).into(POJO.class);

Overriding the DefaultConverterProvider

Sometimes, it may be desireable to override the default behaviour provided by the org.jooq.impl.DefaultConverterProvider via a custom org.jooq.ConverterProvider. For example, assume you have an object like this:

public class Name {
    public String firstName;
    public String lastName;
}

public class Book {
    public String title;
}

public class Author {
    public Name name;
    public List<Book> books;
}

Now, imagine projecting some JSON functions or XML functions. You would probably want them to be mapped hierarchically to your above data structure:

List<Author> authors = create()
  .select(jsonObject(
     key("name").value(jsonObject(
       key("firstName").value(AUTHOR.FIRST_NAME),
       key("lastName").value(AUTHOR.LAST_NAME)
     )),
     key("books").value(jsonArrayAgg(
       jsonObject("title", BOOK.TITLE)
     ))
  ))
  .from(AUTHOR)
  .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
  .groupBy(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
  .orderBy(AUTHOR.ID)
  .fetchInto(Author.class);

If jOOQ finds Jackson or Gson on your classpath, the above works out of the box. If you want to override jOOQ's out of the box binding, you can easily provide your own by implementing a org.jooq.ConverterProvider as follows, e.g. using the Jackson library:

class JSONConverterProvider implements ConverterProvider {
    final ConverterProvider delegate = new DefaultConverterProvider();
    final ObjectMapper mapper = new ObjectMapper();

    @Override
    public <T, U> Converter<T, U> provide(Class<T> tType, Class<U> uType) {

        // Our specialised implementation can convert from JSON (optionally, add JSONB, too)
        if (tType == JSON.class) {
            return Converter.ofNullable(tType, uType,
                t -> {
                    try {
                        return mapper.readValue(((JSON) t).data(), uType);
                    }
                    catch (Exception e) {
                        throw new DataTypeException("JSON mapping error", e);
                    }
                },
                u -> {
                    try {
                        return (T) JSON.valueOf(mapper.writeValueAsString(u));
                    }
                    catch (Exception e) {
                        throw new DataTypeException("JSON mapping error", e);
                    }
                }
            );
        }

        // Delegate all other type pairs to jOOQ's default
        else
            return delegate.provide(tType, uType);
    }
}

Note: For best results with Jackson and kotlin, please also put the jackson-module-kotlin on the classpath.

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo