I want to map one-to-one and one-to-many relationships to generated Records, preferably in a type-safe manner.

I now have this working with Java records, but I would rather use the code generated Records, so I can update them easier afterwards, though I don't need all the columns of a Record.

I also have the problem that isn't doens't map the contacts to a List but to a List while it does map the Address correctly.

record BulkContactEdit(long id, String organizationName, List < Contact > contacts, Address visitedAddress) {}

record Contact(long id) {}

record Address(long id, String address, String state, String city, String zipcode, Long country_id) {}

var bce = BULK_CONTACT_EDIT.as("bce");
var bceContacts = BULK_CONTACT_EDIT_CONTACTS.as("bceContacts");
var rel = RELATION.as("rel");

var bceAddress = ADDRESS.as("bceAddress");

var query = jooq().select(
bce.ID, bce.ORGANIZATION_NAME,
// For some reason this doesn't map.
//                DSL.multisetAgg(rel.ID)
//                    .as("contacts")
//                    .convertFrom(r -> r.map(mapping(Contact::new))),
jsonArrayAgg(jsonObject(rel.ID)).as("contacts"), jsonObject(bceAddress.fields()).as("visitedAddress"))
// Same as above, but with redundant sub-query.
//                DSL.field(
//                        jooq()
//                            .select(jsonArrayAgg(jsonObject(rel.ID)))
//                            .from(rel)
//                            .where(rel.ID.eq(bceContacts.CONTACT_ID)))
//                    .as("contacts"))
.from(
bce.join(bceContacts).on(bceContacts.BULK_CONTACT_EDIT_ID.eq(bce.ID)).join(rel).on(rel.ID.eq(bceContacts.CONTACT_ID)).leftJoin(bceAddress).on(bceAddress.ID.eq(bce.VISIT_ADDRESS_ID)).where(bce.ID.eq(bulkContactEditId)));

var bulkContactEdit = query.fetchOneInto(BulkContactEdit.class);

CodePudding user response：

Regarding type safety

I want to map one-to-one and one-to-many relationships to generated Records, preferably in a type-safe manner.

If you want type safety, you should not use 2 of the features you've used:

• Manual SQL/JSON API usage, which produces generic JSON documents that lack type safety
• Reflective mapping via into(X.class) calls, which make use of the DefaultRecordMapper's reflective mapping capabilities.

There's nothing wrong with the above approach. jOOQ 3.14 introduced SQL/JSON support as well as the capability of mapping JSON documents to Java objects using Gson or Jackson, if found on the classpath. The approach is just not type safe.

Now, let's look at your individual questions individually, you seem to be mixing concerns...

One-to-one mapping

Starting with jOOQ 3.17 (not yet released, but soon), the Table<R> type extends SelectField<R> for convenience, which means you can project any table expression as a nested record, and map it to your own custom record (or not):

// Using implicit joins, mixing scalar values and nested records
Result<Record3<Long, String, AddressRecord>> r1 =
ctx.select(bce.ID, bce.ORGANIZATION_NAME, bce.address())
   .from(bce)
   .fetch();

// Using implicit joins, projecting only nested records
Result<Record2<BulkContactEditRecord, AddressRecord>> r1 =
ctx.select(bce, bce.address())
   .from(bce)
   .fetch();

While not necessary, the above example makes use of implicit joins, which greatly simplify a query like this. Of course, you can continue using explicit joins like you did.

In jOOQ 3.16, you can do this via an explicit projection of a nested row() expression, like this. There is no type safety when mapping the nested record to a typed, generated UpdatableRecord, but I think that's acceptable, given that you're still using generated code and as such can't really have any mapping errors:

// Using implicit joins, mixing scalar values and nested records
Result<Record3<Long, String, AddressRecord>> r1 =
ctx.select(
        bce.ID, 
        bce.ORGANIZATION_NAME, 
        row(bce.address().fields()).convertFrom(r -> r.into(ADDRESS)))
   .from(bce)
   .fetch();

// Using implicit joins, projecting only nested records
Result<Record2<BulkContactEditRecord, AddressRecord>> r1 =
ctx.select(
        row(bce.fields()).convertFrom(r -> r.into(BULK_CONTACT_EDIT)), 
        row(bce.address().fields()).convertFrom(r -> r.into(ADDRESS)))
   .from(bce)
   .fetch();

One-to-many mapping

There seems to have been something going wrong in your code when you used the multisetAgg(rel.ID) approach, but I can't see what could have been the problem from your question. Also, it's not really a different question from your previous one, which already has an answer on how to do this.

The only difference between your two questions is that here, you prefer not to use a Java record, but jOOQ's generated records, in case of which you'll use this idiom:

Field<Result<RelationRecord>> f = multisetAgg(rel.ID).convertFrom(r -> r.map(RELATION));