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"
}]
}]
}]
To group this as above mentioned output, I am doing following process in my Java code:
1) Iterate all pId
2) Iterate all sId in pId
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.
I have tried hashmap over pId but still could not find a solution
解决方案
Pseudo-code:
String sql = "SELECT pId, pName, sId, sName, gId, gName" +
" FROM ..." +
" LEFT JOIN ..." +
" WHERE ..." +
" ORDER BY pId, sId, gId"; //
rs = stmt.executeQuery(sql)
List 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