java - Java 中的一对多结果集

Question

我的员工数据是:-

NAME | ID  | DEP_CODE
 AA   3679  SALES
 AA   3679  HR
 AA   3679  TECH
 AA   3679  MARKETTING
 BB   3604  HR
 BB   3604  SALES

有人可以修改它并帮助我删除这里 FOR 循环的需要并一次性获取员工列表吗？我只想进行一次数据库调用，我想根据 ID 获取 DEP_CODE。

public List<Employee> fetchDistinctEmployee() {

    List<Employee> empList = new ArrayList<Employee>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select distinct NAME, ID FROM Emp_Table");

        while (rs.next()) {
            Employee ep = new Employee();
            em.setNAME(rs.getString("NAME"));
            em.setID(rs.getInt("ID"));
            empList.add(em);
        }
        for (Employee emp : empList) {
            String sql1 = "SELECT DISTINCT DEP_CODE FROM Emp_Table WHERE id='" + emp.getID() + "'";
            ResultSet rs1 = stmt.executeQuery(sql1);
            ArrayList<String> list = new ArrayList<String>();
            while (rs1.next()) {
                list.add(rs1.getString("DEP_CODE"));
            }
            emp.setDEPCODE(list);
        }
    return empList;
}           

Answer 1

我会使用 map :

    Map<Integer,Employee> empMap = new LinkedHashMap<>();
    try (Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt.executeQuery("select distinct NAME, ID, DEP_CODE FROM Emp_Table");

        while (rs.next()) {
            int id = rs.getInt("ID");
            Employee em = empMap.get(id);
            if (em == null) {
                em = new Employee();
                em.setID(id);
                em.setNAME(rs.getString("NAME"));
                em.setDEPCODE(new ArrayList<>()); // could be done in the constructor of Employee instead
                empMap.put(id, em);
            }
            em.addDEPCODE(rs.getString("DEP_CODE"));
        }
    }
    return new ArrayList<>(empMap.values());

方法 addSEPCODE 会将一个值添加到 SEPCODE 列表中。

顺便说一句，请注意，完成语句后关闭它是一个好主意。