Mapping derived columns to POJOs with RecordMapper in JOOQ

Question

i have a table tickets where i insert ticket and have a field createdBy which stores the UserId Integer of the creator of that record. During fetching I join with users table and concat firstname and last name and my DTO has field createdBy of the concatenated name of creator. How can i map the derived field? this is my reference https://www.jooq.org/doc/3.13/manual/sql-execution/fetching/pojos/ and I cant seem to find such a scenario provided

the issue is not the join. the issue is mapping the string createdBy derived after the join whereas in the record class generated by jooq is an Integer because in the database table i store the userId.

    List<MyTickets> mytickets = create.select(....FIELDS).from(TICKETS_).fetch().into(MyTickets.class);
    @Override
public Field<Integer> field9() {
    return Tickets.TICKETS_.CREATEDBY;
}

Answer 1

In my answer, I will assume that your computed column is going to be called CREATED_BY_NAME, not CREATED_BY, which is a name that's already taken, and to avoid confusion.

If this is something you do frequently, you have a few options that could be interesting to you:

• Use views to generate this alternative CREATED_BY_NAME column. A lot of databases can insert into / update views as well, so you won't have a big penalty in using views to replace your tables. To your client logic, the origin of this column will be transparent. If you want to work with UpdatableRecord, you will have to tell jOOQ's code generator what the view's underlying primary key is using the synthetic primary key flag.
• Similar to the above views, you could use computed columns on your tables, using the GENERATED ALWAYS AS ... syntax (or whatever your dialect uses for the syntax). Not all dialects support this, but it is a nice feature that turns tables into views without the extra view object.
• If you want to keep computing this column manually in your jOOQ code, you could either write your own DTO / POJO objects, or extend the code generator with a custom code section, where you generate the relevant attribute / getter / setter. This approach only works for mutable POJOs, as you cannot modify the constructor of an immutable POJO.
• You can also specify a base class for all of your affected POJOs and inject that base class using a generator strategy (programmatic or configurative). The base class could then implement all the getters / setters for columns like CREATED_BY_NAME.
• You can also use structural typing instead. You don't have to map all the columns into your POJO. You could also map some columns into your generated POJO (excluding CREATED_BY_NAME) and map the CREATED_BY_NAME column separately. Just keep a reference to your jOOQ Result and/or Record, and perform several map / intoXYZ() calls on it.