How to efficiently group SQL query output using Java?

Question

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

Just order by pId, pname, sId, sName in your SQL. Then you can build the JSON pretty easily with one pass through the data. — user554546
– user554546, Commented Dec 30, 2016 at 18:08
@JackManey Exactly did that but still I need to iterate through all rows that I get as a result of query. — Vishwas
– Vishwas, Commented Dec 30, 2016 at 18:13
Yep. Iterate through and build your hash maps, then output the JSON. — user554546
– user554546, Commented Dec 30, 2016 at 18:21
How did you expect to process all the data without iterating through all rows at least once? --- Sure, for good performance, you should also iterating through all rows at most once, but you have to do it at least once. — Andreas
– Andreas, Commented Dec 31, 2016 at 4:04

prashant · Accepted Answer · 2016-12-30 18:35:21Z

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.

Community · Accepted Answer · 2017-05-23 11:53:41Z

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

Collectives™ on Stack Overflow

How to efficiently group SQL query output using Java?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related