mapping jooq record entity to pojo with nested objects

i have two tables d_cities, d_countries. Mapping between them is unidirectional like this:

also, i have two pojo dtos like this:

Entity classes are jooq generated.

Now I can't come up with the decision how can I retrieve response like this when i call getCityById(String cityId):

Can you please help me with this problem? I am very new to JOOQ and DSLContext methods.

There are two main ways of nesting records in jOOQ:

Directly in SQL

jOOQ supports SQL nested records just like standard SQL and some implementations (e.g. PostgreSQL). If it's not supported natively by your RDBMS, then jOOQ will emulate it. You can attach ad-hoc converters to your nested record expressions in order to map them to your custom CountryDto type as follows:

List<CityDto> result =
ctx.select(
      CITY.ID,
      CITY.NAME,
      row(CITY.country().ID, CITY.country().NAME).mapping(CountryDto::new))
   .from(CITY)
   .fetch(Records.mapping(CityDto::new));

This assumes you have appropriate constructors on your DTOs. I guess you'll have to add a lombok @AllArgsConstructor annotation for that purpose (I really recommend it, it will work very well with jOOQ's ad-hoc conversion!)

For convenience, I'm using implicit path joins, but you can obviously use explicit joins as well.

Using DefaultRecordMapper functionality

The reflection based, historic DefaultRecordMapper can nest records as well using a dot notation, if that's more your style. Just write this:

List<CityDto> result =
ctx.select(
      CITY.ID,
      CITY.NAME,
      CITY.country().ID.as("countryDto.id"), 
      CITY.country().NAME.as("countryDto.name"))
   .from(CITY)
   .fetchInto(CityDto.class);

As this is purely reflection based, there's no type safety. As soon as you rename your properties in your DTO, the query will stop working. Personally, I recommend the explicit, type safe approach from the first solution for that reason, specifically, because it can also work with nested collections.