How to efficiently group SQL query output using Ja

问题:

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

回答1:

Create a object as shown below

 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;

    }

Now iterate through the result set and populate these objects creating a list of processes checking while adding a new process whether a process already exists....if so then repeat the check for sub process and grade adding data only if it is new.

回答2:

Expanding on answer by prashant

Pseudo-code:

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