如何使用Java有效地分组SQL查询输出

Question

我正在使用三个不同表上的core java进行SQL查询，左JOIN并获得以下输出：

：

pId  pName  sId  sName  gId  gName
1    p1     11   s1     111  g1
1    p1     11   s1     112  g2
2    p2     12   s2     null null
3    p3     13   s3     113  g3

现在我想将其分组为以下：

[{
    "pId": 1,
    "pname": "p1",
    "sub": [{
        "sId": 11,
        "sName": "s1",
        "grades": [{
            "gId": 111,
            "gName": "g1"
        }, {
            "gId": 112,
            "gName": "g2"
        }]
    }]
}, {
    "pId": 2,
    "pname": "p2",
    "sub": [{
        "sId": 12,
        "sName": "s2",
        "grades": []
    }]
}, {
    "pId": 3,
    "pname": "p3",
    "sub": [{
        "sId": 13,
        "sName": "s3",
        "grades": [{
            "gId": 113,
            "gName": "g3"
        }]
    }]
}]

要按上述输出进行分组，我在Java代码中进行以下过程：

1）迭代所有pid

2）迭代pid中的所有sid

3）迭代SID中的所有GID

这需要大量时间来执行并获得所需的输出。

有什么方法可以使用最小的迭代速度更快地完成操作？

任何帮助/解决方法都将不胜感激。

我尝试了PID上的哈希姆普，但仍然找不到解决方案

Answer 1

创建一个对象，如下所示

 public class Process{
        // getters and setters
        private int pid;
        // getters and setters
        private String pName;
        // getters and setters
        private List<SubProcess> subList;
        // override equals and hashcode basd on process name and id 
    }
    public class SubProcess{
       // getters and setters
       private int subProcessId;
        // getters and setters
        private String subProcessName;
        // getters and setters
        private List<Grade> gradeList;
        // override equals and hashcode basd on sub process name and id 

    }
    public class Grade{
      // getters and setters
      private int gradeId;
      // getters and setters
      private String gradeName;
    }

现在遍历结果集，并填充这些对象，创建一个过程列表，同时添加新过程是否已经存在。...如果是这样。新。

Answer 2

prashant

在答案上扩展

伪代码：

String sql = "SELECT pId, pName, sId, sName, gId, gName" +
              " FROM ..." +
              " LEFT JOIN ..." +
             " WHERE ..." +
             " ORDER BY pId, sId, gId"; // <-- IMPORTANT !!!
rs = stmt.executeQuery(sql)
List<Process> processes = new ArrayList<>();
Process p = null;
SubProcess s = null;
while (rs.next()) {
    if (p == null || p.getId() != rs.getInt("pId")) {
        p = new Process(rs.getInt("pId"), rs.getString("pName"));
        processes.add(p);
        s = null; // force new SubProcess
    }
    if (rs.getInt("sId") == 0/*null*/)
        continue; // skip, no SubProcess found for Process
    if (s == null || s.getId() != rs.getInt("sId")) {
        s = new SubProcess(rs.getInt("sId"), rs.getString("sName"));
        p.addSubProcess(s);
    }
    if (rs.getInt("gId") == 0/*null*/)
        continue; // skip, no Grade found for SubProcess
    Grade g = new Grade(rs.getInt("gId"), rs.getString("gName"));
    s.addGrade(g);
}
// Now generate JSON from 'processes' list