Allow for deriving SELECT clause from mapped classes

[lukaseder]

One annoyance that is often reported by users in different ways is that often, the projection has to be explicitly specified twice - due to Java being not a very structurally typed language, and people historically preferring to map results into POJOs.

For example, there's redundancy in these two API usages:

// T.COL has to be specified twice when from a user perspective,
// a single time would suffice
List<Integer> list = ctx.select(T.COL).from(T).fetch(T.COL);

// MyPojo is a "view" of the T table, containing only a subset of columns
// Redundantly having to write the projection explicitly for this query is tedious
List<MyPojo> list1 = ctx.select(T.COL1, T.COL2).from(T).fetchInto(MyPojo.class);

// Alternatively, users may prefer to just fetch all columns instead, ignoring the
// overhead this creates, or the subtle incorrectness when using e.g. DISTINCT:
List<MyPojo> list2 = ctx.selectFrom(T).fetchInto(MyPojo.class);

It would be great if there was a syntax that allowed for projecting SQL columns directly from a pre-defined mapping that is already embedded into jOOQ's DSL API, e.g.

List<MyPojo> list3 = dsl.selectFrom(T.project(MyPojo.class)).fetch();
List<MyPojo> list4 = dsl.select(MyPojo.class).from(T).fetch(); // Probably preferrable

See also discussion here: #9005 (comment)

[lukaseder]

The probably preferrable solution:

List<MyPojo> list4 = dsl.select(MyPojo.class).from(T).fetch();

Will require relaxing the type bound on R extends Record throughout the DSL and model APIs to R (or E, for consistency?). Internally, it would be easy to register:

• A set of fields that are actually projected, by record-unmapping MyPojo.class into the T table
• A RecordMapper that maps TRecord into MyPojo

[lukaseder]

This feature corresponds to Hibernate's "projections" feature. It currently suffers from similar problems like the "projections". In the currently suggested form, there's no way to write actual expressions in the projection anymore. In jOOQ's case, the workaround is to revert to ordinary syntax.

[lukaseder]

A subtle problem when trying to derive the projection from a POJO is that the POJO might be an immutable POJO with parameter names unavailable, so we cannot derive the order of columns from the POJO via reflection.

This is almost a show stopper!

[lukaseder]

These ideas have been tempting, but a much better solution was offered in jOOQ 3.15 via the new Records.mapping() utility, which allows for this:

// Separate SQL and mapping
List<MyPojo> list1 = ctx.select(T.COL1, T.COL2).from(T).fetch(mapping(MyPojo::new));

// Embed mapping in SQL
Result<Record1<MyPojo>> list2 = ctx.select(row(T.COL1, T.COL2).mapping(MyPojo::new)).from(T).fetch();

These approaches are very powerful and composable also in case the mapping isn't trivial (nested records, nested collections), but at the same time covers the trivial cases sufficiently well.