Context: I'm building an HTTP API wrapping a normalized database schema. The database access layer is using jOOQ. For most queries, I have no issues. The problem begins with this type of read access:
GET /foo/123
GET /foo/123?include=bars&include=baz
where foo, bar and baz are resource types in the model, each represented by a table. Foo might-have Baz, and Foo has-many Bar, so this is what a Foo resource can look like when everything is included:
{"id": 123,
"fooname": "the foo",
"and more": "foo fields",
"baz_id": 456,
"baz": {"id": 456, "and more": "baz fields"},
"bars": [{"id": 789, "foo_id": 123, "and more": "bar fields"},
{...}]}
The user might also set the ?include parameter to something else or omit it entirely in which case the baz and bars fields would be omitted from the Foo resource representation. If the relationships are requested, but there are no matching rows, the expectation is that the fields are present in the response but with a null or empty array value. Wrangling Jackson so it does this correctly is a bit of a pain but doable. Think GraphQL level of control over the representation (obviously not as much), but for various reasons, not using GraphQL.
Now to build the jOOQ query:
// I will also have to build the Page version of this for returning multiple Foos,
// with the same kind of issues. note not using the generated FooRecord because we
// do not need all the fields in the response
Foo foo = this.dsl.select(
some, foo, fields,
include.contains("baz") ? BAZ_INCLUDE : null,
include.contains("bars") ? BARS_INCLUDE : null)
.from(FOOS)
.where(FOOS.ID.equal(fooId))
.fetchSingle(Records.mapping(Foo::new));
where Foo is a Java record (some, foo, fields, @Nullable Baz, @Nullable List<Bar>). It has to be that dynamic; some resource types have 5 or 6 of these optional relationships, sometimes across multi-level joins.
I have defined the Baz and Bars projections like this:
// we're actually using the Foo table's Bar and Baz relations here since they carry
// the information about the relationship, and will cause an implicit join, which
// saves some work when building the query. e.g. FOO.bar().ID
// Foo has 0 or 1 Baz
private static SelectField<Baz> BAZ_INCLUDE = row(some, baz, fields)
.mapping(Baz::new)
.as("baz");
// Foo has 0 to N Bars
private static SelectField<List<Bar>> BARS_INCLUDE = multiset(select(some, bar, fields))
.convertFrom(r -> r.map(Records.mapping(Bar::new))
.as("bars");
and miraculously, it works!... when using the full projection (include={baz,bars}). When one or more of the relationships is not followed, I get a runtime ClassCastException as jOOQ doesn't like the nulls in the select, apparently: class org.jooq.impl.RecordImpl3 cannot be cast to class org.jooq.Record5 (3 is the arity of the select when include is empty, 5 when it is full, and also of the Foo ctor).
I've also tried include.contains("baz") ? BAZ_INCLUDE : inline(null, BAZ_INCLUDE.getDataType()), but I get org.jooq.exception.DataTypeException: Conversion function not implemented. I feel like there must be a way to get over this one (mapping a NULL to a Java null should be simple?), but I'm sort of lost in the conversion features.
How do I do dynamic projections across joins, and still get the nice type safety features so that I am reasonably confident that I haven't forgotten a field in the Foo record or in the SELECT?
This is, I think, one of the last things I need to achieve feature parity with our existing Hibernate+QueryDSL+EntityGraph tooling (as well as improve some stuff over it!).
ClassCastException?The behaviour of passing null to the select() method is undocumented. It currently (jOOQ 3.20) behaves as if you hadn't passed the field, but that's inconsistent with the reported type in the compiler. The ClassCastException arises because with this:
ctx.select(T.A, null).from(T).fetch();
... jOOQ expects to get Record2<T1, T2> of some sort, but at runtime, given that the null expression is removed, a Record1<T1> type is effectively projected. This should obviously be fixed at some point, but backwards compatibility prevents a simple fix.
What you're looking for is to provide a "typed null" of some sort, similar as is being requested here:
As of jOOQ 3.20.8, that doesn't work yet, so as a workaround until #14462 is fixed you'll have to replace your MULTISET subquery by a dummy query that returns an empty result of the same type, e.g.:
BARS_INCLUDE = multiset(select(some, bar, fields))
.convertFrom(r -> r.map(Records.mapping(Bar::new))
.as("bars");
BARS_EMPTY = multiset(select(some, bar, fields).where(falseCondition()))
.convertFrom(r -> r.map(Records.mapping(Bar::new))
.as("bars");