jooq + jackson fails to convert json to pojo due to case sensitivity

Question

The following code fails:

    final var account = dc.select(ACCOUNT.asterisk())
            .from(ACCOUNT)
            .limit(1)
            .forJSON().path().withoutArrayWrapper()
            .fetchOneInto(Account.class);

The error message is like:

Caused by: com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: Unrecognized field "FIRSTNAME" ... known properties: ... "firstname" ...

Jackson seems to have problems mapping the upper case column FIRSTNAME into the pojo's lowercase field "firstname" (and similar for all other fields). I am using the generated classes. How can I fix it while still using json?

The jooq version is 3.16 and the database is oracle.

Answer 1

Fixing the Jackson mapping use-case

In your particular query, you could auto-alias all columns to their lower case equivalent, e.g.:

final var account = 
    dc.select(Stream
          .of(ACCOUNT.fields())
          .map(f -> f.as(f.getName().toLowerCase()))
          .toList())
      .from(ACCOUNT)
      .limit(1)
      .forJSON().path().withoutArrayWrapper()
      .fetchOneInto(Account.class);

Avoiding the use-case entirely

However, given your particular query, I don't really see the point of passing through the JSON serialisation only to benefit from a third party mapper. Why not use jOOQ for that as well?

final var account = 
    dc.selectFrom(ACCOUNT)
      .limit(1)
      .fetchOneInto(Account.class);

Or, if Account is an immutable class (e.g. a Java record), why not use type safe mapping like this:

final var account = 
    dc.selectFrom(ACCOUNT)
      .limit(1)
      .fetchOne(Records.mapping(Account::new));