如何使用Java有效地对SQL查询输出进行分组? [英] How to efficiently group SQL query output using Java?

问题描述

我正在使用LEFT联接在三个不同的表上使用核心Java进行SQL查询，并获得以下输出:

I am doing SQL query using core Java on three different tables with LEFT join and getting following output:

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

现在，我要将其分组如下:

Now I want to group this as following:

[{
    "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代码中执行以下过程:

To group this as above mentioned output, I am doing following process in my Java code:

1)迭代所有pId

1) Iterate all pId

2)迭代pId中的所有sId

2) Iterate all sId in pId

3)迭代sId中的所有gId

3) Iterate all gId in sId

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

This takes lot of time to execute and get desired output.

有什么方法可以以最少的迭代更快地完成它?

Is there any way to get it done in faster way with minimum iterations?

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

Any help/workaround will be greatly appreciated.

我已经尝试过通过pId进行哈希映射，但仍然找不到解决方案

推荐答案

扩展为由麻烦者回答

伪代码:

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

这篇关于如何使用Java有效地对SQL查询输出进行分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！