How to map Employee's column - manager as Employee instance

I should perform cursor moving in order to get all Employee instances. Consider result set to be fully scrollable (back and forward, begin, end, etc.). If an Employee has a Manager it should contain it as Employee instance as well. This is a table Employee with some columns.

ID         INTEGER PRIMARY KEY,
FIRSTNAME  VARCHAR(10),
LASTNAME   VARCHAR(10),
MIDDLENAME VARCHAR(10),
POSITION   VARCHAR(9),
MANAGER    INTEGER,
HIREDATE   DATE,
SALARY     DOUBLE,
DEPARTMENT INTEGER,
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT (ID)

For exeample insert in table:

INSERT INTO EMPLOYEE VALUES (7654, 'JOHN', 'MARTIN', 'MARIA', 'SALESMAN', 7698, TO_DATE('28-9-1981', 'DD-MM-YYYY'), 1250, 30);


INSERT INTO EMPLOYEE VALUES (7698, 'JOHN', 'BLAKE', 'MARIA', 'MANAGER', 7839, TO_DATE('1-5-1981', 'DD-MM-YYYY'), 2850, 30);

The manager ID first employee is 7698 and in instead of ID need to print all information about manаger. In final need to be

[Employee{id=7654, fullName=FullName{firstName=JOHN, lastName=MARTIN, middleName=MARIA}, position=SALESMAN, hired=1981-09-28, salary=1250.00000, manager=Employee{id=7698, fullName=FullName{firstName=JOHN, lastName=BLAKE, middleName=MARIA}, position=MANAGER, hired=1981-05-01, salary=2850.00000, manager=null}}

I create two maps and with if add employee to map employ and manager to map manag

 Map<Integer, Employee> manag = new HashMap<>();
            Map<Integer, Employee> employ = new HashMap<>();
        
        BigInteger id = BigInteger.valueOf(resultSet.getInt("id"));
                FullName fullname = new FullName(resultSet.getString("firstName"), resultSet.getString("lastName"), resultSet.getString("middleName"));
                Position position = Position.valueOf(resultSet.getString("position"));
                LocalDate hired = resultSet.getObject("hiredate", LocalDate.class);
                BigDecimal salary = BigDecimal.valueOf(resultSet.getInt("salary"));
                Employee manager = null;//String.valueOf(resultSet.getInt("manager")) !=null? new Employee(id, fullname, position, hired, salary, null) : null;


if(resultSet.getString("position").equalsIgnoreCase("manager")) {
    Integer key = resultSet.getInt("id");
    Employee value = new Employee(id, fullname, position, hired, salary, null);
    manag.put(key, value);
} else {
    Integer key = resultSet.getInt("id");
    Employee value = new Employee(id, fullname, position, hired, salary, null);
    employ.put(key, value);

but what do I need to do next to solve the problem?

Solution

I suppose the MANAGER column is a foreign key on another employee, right?

If so, I would select all of the employee's without a manager first and add each one to a Map<Integer, Employee>. Then, when you iterate over the employee's with a manager, you can fetch the corresponding manager from this map and add it to the newly created employee instance.

This becomes more tricky if you have a hierarchy with more than 2 levels. If you can't order them by a property (e.g. department), then you have to read all of the employee's first and add their manager in a second step.

If you then need a set of employee's as a result, you'll only need to create a new Set of the maps' values.