java怎么分组查询,如何使用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"

}]

}]

}]

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