How to fill an ArrayList of ArrayLists with a Left Join?

I have a class Employee that contains an ArrayList of Projects.

I'm storing the Employees in one table, and the Projects in another.

I'm trying to find the best way to create an ArrayList of Employee objects based on a result set. Simply creating an ArrayList of Employees based on a result set is pretty straightforward, but I'm finding that filling an ArrayList of Employees that each contains an ArrayList of Projects isn't so simple.

Right now, my getEmployees() function is using two nested SQL queries to accomplish this, something like this:

```
public ArrayList getEmployees()
{
PreparedStatement ps1 = null;
ResultSet rs1 = null;
PreparedStatement ps2 = null;
ResultSet rs2 = null;

ArrayList employees = new ArrayList();

String query1 = "SELECT * FROM employees "
+ "ORDER BY employee_id ASC";

try
{
ps1 = conn.prepareStatement(query1);
rs1 = ps1.executeQuery();

while (rs1.next())
{
Employee employee = new Employee();

int employeeID = rs1.getInt("employee_id");
employee.setEmployeeID(employeeID);
employee.setName(rs1.getString("employee_name"));

// Get projects for this employee
ArrayList projects = new ArrayList();

String query2 = "SELECT * FROM projects "
+ "WHERE employee_id = ?";

ps2 = conn.prepareStatement(query2);
ps2.setInt(1, employeeID);
rs2 = ps2.executeQuery();

while (rs2.next())
{
Project project = new Project();

project.setProjectID(rs2.getInt("project_id"));
project.setName(rs2.getInt("project_name"));

projects.add(project);
}

employee.setProjects(projects);

employees.add(employee);
}

return employees;
}
catch (SQLException e)
{
e.

A standard way to do this is through break-processing, where you track one value, and when it changes, you do something special.... but you may find it easier to do a more unstructured system:

Map> employeeProjects = new HashMap<>();
Map employees = new TreeMap<>(); // Treeset ... sorted by employeeID

// join the tables.
String select = " select e.employee_id, e.employee_name, p.project_id, p.project_name "
              + " from employees e
                      left outer join projects p on e.employee_id = p.employee_id"
              + " order by e.employee_id, p.project_name";

// do the select.....

while (rs.next()) {
    Integer employeeID = rs.get("employee_id");
    Employee emp = employees.get(employeeID);
    if (emp == null) {
        emp = new Employee();
        emp.setName(rs.get("employee_name");
        emp.setEmployeeID(employeeID);
        employees.put(employeeID, emp);
        // create a new list for this employee
        employeeProject.put(employeeID, new ArrayList());
    }

    String projectName = rs.getString("project_name");

    if (!rs.wasNull()) {
        List projects = employeeProject.get(employeeID);
        Project proj = new Project();
        proj.setID(rs.getInt("project_id"));
        proj.setName(projectName);
        projects.add(proj);
    }

}
rs.close();

Then, once you have the data structured the way you want, you can:

List result = new ArrayList<>();
for (Employee emp : employees.values()) {
    emp.setProjects(employeeProjects.get(emp.getEmployeeID());
    result.add(emp);
}
return result;

StackExchange Code Review Q#42185, answer score: 11