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&lt;Employee&gt; getEmployees()
{
    PreparedStatement ps1 = null;
    ResultSet rs1 = null;
    PreparedStatement ps2 = null;
    ResultSet rs2 = null;

    ArrayList&lt;Employee&gt; employees = new ArrayList&lt;Employee&gt;();

    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&lt;Project&gt; projects = new ArrayList&lt;Project&gt;();

            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.printStackTrace();
    }
    finally
    {
        try
        {
          // close result sets and prepared statements
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    return null;    
}


The above code works, but it seems messy to me.  

Is there a way to do this without having to nest two separate SQL queries?  I've tried using a LEFT JOIN to get a single result set back, but I was unable to figure out a way to use that single result set to fill the ArrayList of Employees, each containing an ArrayList of Projects.

EDIT: Assume the tables have the following structures:

employees table:

employee_id  int(11)  NOT NULL  PRIMARY KEY  AUTO_INCREMENT
employee_name  varchar(60)  NOT NULL  


projects table:

project_id  int(11)  NOT NULL  PRIMARY KEY  AUTO_INCREMENT
employee_id  int(11)  NOT NULL
project_name  varchar(60)  NULL
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&lt;Integer, List&lt;Project&gt;&gt; employeeProjects = new HashMap&lt;&gt;();
Map&lt;Integer, Employee&gt; employees = new TreeMap&lt;&gt;(); // 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&lt;Project&gt;());
    }

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

    if (!rs.wasNull()) {
        List&lt;Project&gt; 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&lt;Employee&gt; result = new ArrayList&lt;&gt;();
for (Employee emp : employees.values()) {
    emp.setProjects(employeeProjects.get(emp.getEmployeeID());
    result.add(emp);
}
return result;