JOOQ how to self join

Question

Now my code looks like this:

EmployeeTable employee = EMPLOYEE.as("employee");
EmployeeTable boss = EMPLOYEE.as("boss");

Map<Employee, Employee> result = dslContext.select(employee.fields())
        .from(employee)
        .join(boss)
        .on(employee.BOSS_ID.eq(boss.ID))
        .fetch()
        .intoMap(Employee.class, Employee.class);

I need to get a Map of the form <Employee(employee), Employee(boss)>. In other words, the key is an employee, and the value is his boss. But as a result of executing the code, it turns out that a Map is returned, in which both the key and the value are the boss. How can I solve the problem?

Answer 1

You have to fix two things:

1. Project all the columns, including the "boss" table's, not just the "employee" table's
2. Use the correct intoMap(Table, Table) overload

I.e.:

Map<Employee, Employee> result = 
dslContext.select() // Just leave this empty, or project everything explicitly
          .from(employee)
          .join(boss)
          .on(employee.BOSS_ID.eq(boss.ID))
          .fetch()
          .intoMap(employee, boss);