jeudi 30 juillet 2015

Convert relational data into JSON Efficiently

I have following table People

enter image description here

I want to get relational records and make the JSON Array like following:

{ "Person":[
    {"ID":1, 
     "FirstName":"James", 
     "LastName":"Donovan",
     "Child":[
         {"ID":6, "FirstName":"Nikolai", "LastName":"Donovan"}
     ]
    },
    {"ID":2, 
     "FirstName":"Jeffrey", 
     "LastName":"Williams",
     "Child":[
         {"ID":4, "FirstName":"Carol", "LastName":"Williams"},
         {"ID":5, "FirstName":"Sarah", "LastName":"Williams"}
     ]
    },
    .... and so on
  ]
}

What I use following approach; short summary of below code: I perform db operation twice in loop and put node into JSON Object is match certain conditions.

preparedStatement = con.prepareStatement("SELECT * FROM PEOPLE");
rs = preparedStatement.executeQuery(); // ResultSet Object

if (rs != null && rs.next()) {
    Roles = new JSONObject();
    parentNode = new JSONObject();
    childNode = new JSONObject();

    while(rs.next()) {
        parentNode.put("ID", rs.getInt("Id"));
        parentNode.put("FirstName", rs.getString("firstname"));
        parentNode.put("LastName", rs.getString("lastname"));

        if(rs.getInt("parent") > 0) { // Equal 0 Mean it has not child
            // Perform again database operation and get record related to parent id
            preparedStatement = con.prepareStatement("SELECT * FROM PEOPLE WHERE parent=?");
            preparedStatement.setString(1, rs.getInt("id");
            resultSet2 = preparedStatement.executeQuery();

            while(resultSet2.next()) {
                childNode.put("ID", rs.getInt("Id"));
                childNode.put("FirstName", rs.getString("firstname"));
                childNode.put("LastName", rs.getString("lastname"));

                parentNode.put("Child":childRole); // put child into parent node
            }
       }
    }
}

What I want? Performing db select query in loop is too much expensive for server side.

Is there any better solution which generate desired JSON of relational data and save me from additional SELECT operations!

I am thankful for your attention!

Aucun commentaire:

Enregistrer un commentaire