Use JOOQ Multiset with custom RecordMapper - How to create Field<List<String>>?

Question

Suppose I have two tables USER_GROUP and USER_GROUP_DATASOURCE. I have a classic relation where one userGroup can have multiple dataSources and one DataSource simply is a String.

Due to some reasons, I have a custom RecordMapper creating a Java UserGroup POJO. (Mainly compatibility with the other code in the codebase, always being explicit on whats happening). This mapper sometimes creates simply POJOs containing data only from the USER_GROUP table, sometimes also the left joined dataSources.

Currently, I am trying to write the Multiset query along with the custom record mapper. My query thus far looks like this:

List<UserGroup> = ctx
                .select(
                        asterisk(),
                        multiset(select(USER_GROUP_DATASOURCE.DATASOURCE_ID)
                                .from(USER_GROUP_DATASOURCE)
                                .where(USER_GROUP.ID.eq(USER_GROUP_DATASOURCE.USER_GROUP_ID))
                        ).as("datasources").convertFrom(r -> r.map(Record1::value1))
                )
                .from(USER_GROUP)
                .where(condition)
                .fetch(new UserGroupMapper()))

Now my question is: How to create the UserGroupMapper? I am stuck right here:

    public class UserGroupMapper implements RecordMapper<Record, UserGroup> {
        @Override
        public UserGroup map(Record rec) {
            UserGroup grp = new UserGroup(rec.getValue(USER_GROUP.ID),
                    rec.getValue(USER_GROUP.NAME),
                    rec.getValue(USER_GROUP.DESCRIPTION)
                    javaParseTags(USER_GROUP.TAGS)
            );

            // Convention: if we have an additional field "datasources", we assume it to be a list of dataSources to be filled in
            if (rec.indexOf("datasources") >= 0) {
                // How to make `rec.getValue` return my List<String>???? 
                List<String> dataSources = ?????
                grp.dataSources.addAll(dataSources);
            }
        }

My guess is to have something like List<String> dataSources = rec.getValue(..) where I pass in a Field<List<String>> but I have no clue how I could create such Field<List<String>> with something like DSL.field().

Answer 1

How to get a type safe reference to your field from your RecordMapper

There are mostly two ways to do this:

1. Keep a reference to your multiset() field definition somewhere, and reuse that. Keep in mind that every jOOQ query is a dynamic SQL query, so you can use this feature of jOOQ to assign arbitrary query fragments to local variables (or return them from methods), in order to improve code reuse
2. You can just raw type cast the value, and not care about type safety. It's always an option, evne if not the cleanest one.

How to improve your query

Unless you're re-using that RecordMapper several times for different types of queries, why not do use Java's type inference instead? The main reason why you're not getting type information in your output is because of your asterisk() usage. But what if you did this instead:

List<UserGroup> = ctx
    .select(
        USER_GROUP, // Instead of asterisk()
        multiset(
            select(USER_GROUP_DATASOURCE.DATASOURCE_ID)
            .from(USER_GROUP_DATASOURCE)
            .where(USER_GROUP.ID.eq(USER_GROUP_DATASOURCE.USER_GROUP_ID))
        ).as("datasources").convertFrom(r -> r.map(Record1::value1))
    )
    .from(USER_GROUP)
    .where(condition)
    .fetch(r -> {
         UserGroupRecord ug = r.value1();
         List<String> list = r.value2(); // Type information available now

         // ...
    })

There are other ways than the above, which is using jOOQ 3.17+'s support for Table as SelectField. E.g. in jOOQ 3.16+, you can use row(USER_GROUP.fields()).

The important part is that you avoid the asterisk() expression, which removes type safety. You could even convert the USER_GROUP to your UserGroup type using USER_GROUP.convertFrom(r -> ...) when you project it:

List<UserGroup> = ctx
    .select(
        USER_GROUP.convertFrom(r -> ...),
        // ...