Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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

    ArrayList<Employee> employees = new ArrayList<Employee>();

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

            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
share|improve this question
2  
Can you please give us the DESCRIBE of employees and projects table? –  tintinmj Feb 19 at 19:22
1  
@tintinmj My database doesn't support the DESCRIBE statement, but I've done the best I can with the edit above. –  Bri Feb 19 at 19:42
    
Your problem is not so much that the code is messy, but that it is slow. Any time you execute a query repeatedly in a loop, you're likely to get a performance problem. –  200_success Feb 20 at 5:51

1 Answer 1

up vote 10 down vote accepted

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<Integer, List<Project>> employeeProjects = new HashMap<>();
Map<Integer, Employee> 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<Project>());
    }

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

    if (!rs.wasNull()) {
        List<Project> 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<Employee> result = new ArrayList<>();
for (Employee emp : employees.values()) {
    emp.setProjects(employeeProjects.get(emp.getEmployeeID());
    result.add(emp);
}
return result;
share|improve this answer
    
That's an amazing answer. I never would have thought of that on my own! –  Bri Feb 19 at 19:56
    
@Bri I just fixed it to do an actual outer-join .... sorry about that ;-) –  rolfl Feb 19 at 19:57
    
Okay, thanks very much. I hadn't even noticed because I was focusing on the other code. –  Bri Feb 19 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.