Custom converter for Jooq Result

Question

Actually we are using fetchInto() to convert the results to list of object.

For example:

Employee pojo matching database table is employee.

List<Employee> employeeList = sql.select(Tables.Employee)
                                 .from(Tables.EMPLOYEE).fetchInto(Employee.class);

similarly, how could we convert the records we are fetching using joins?

For example:

Customer pojo matching database table is customer.

Employee pojo matching database table is employee.

sql.select(<<IWantAllFields>>).from(Tables.CUSTOMER)
                              .join(Tables.EMPLOYEE)
                              .on(Tables.EMPLOYEE.ID.equal(Tables.CUSTOMER.EMPLOYEE_ID))
                              .fetchInto(?);

Answer 1

To select all fields from a joined table source, simply select "none":

Result<Record> result =
sql.select().from(Tables.CUSTOMER)
            .join(Tables.EMPLOYEE)
            .on(...)
            .fetch();

jOOQ will then introspect the known table source and generate all column references for you. One way to create a POJO relationship is to use one of the various Result.intoGroups() methods. E.g.:

Map<Integer, List<Customer>> map =
result.intoGroups(CUSTOMER.EMPLOYEE_ID, Customer.class);

This will produce a map of List<Customer> pojos per EMPLOYEE_ID value.

On a side-note: As with any mapping operation that calls upon the DefaultRecordMapper, mapping might not work as expected when your JOIN operation produces two times the same column name (e.g. CUSTOMER.ID and EMPLOYEE.ID) - as the DefaultRecordMapper doesn't know what table a particular column originates from.

For more sophisticated mapping, you should probably implement your own RecordMapperProvider