Naveen Naveen - 2 months ago 10
Java Question

Need to form a JSON response in the following way from the following resultset

This could be simple but I am not able to figure it out.

This is the ResultSet I am receiving from the database.

Name Method Value
Website Online 450
Website Offline 500
Mobile Online 100
Mobile Offline 600


I need to send the response to the front end in the following JSON Format.

response: [
{
"Name" : "Website",
"Online" : 450,
"Offline" : 500
},
{
"Name" : "Mobile",
"Online" : 100,
"Offline" : 600
}
]


I need to form that JSON in a single loop, preferably while reading the ResultSet itself. What is the best way to achieve that.?
Thanks in Advance.!

P.S : The query is already a complex one, so cannot pivot the table - performance issues. The above ResultSet is a sample, there may be hundreds of records in the ResultSet

Update:
I am not sure how efficient the following solution is, but it works.!

Map<String, Map<String, Object>> objectMap = new LinkedHashMap<String,Map<String, Object>>();
Map<String, Object> map;
while(rs.next()){
if(objectMap.containsKey(rs.getString("Name"))){
map = objectMap.get(rs.getString("Name"));
map.put(rs.getString("Method"), rs.getInt("Value"));
}
else {
map = new HashMap<String, Object>();
map.put("Tag", 0);
map.put("Plate", 0);
map.put("Name", rs.getString("Name"));
map.put(rs.getString("Method"), rs.getInt("Value"));
}
objectMap.put(rs.getString("Name"), map);
}

return objectMap.values();

Answer
Map<String, Map<String, Object>> objectMap = new LinkedHashMap<String,Map<String, Object>>();
Map<String, Object> map;
 while(rs.next()){
     if(objectMap.containsKey(rs.getString("Name"))){
        map = objectMap.get(rs.getString("Name"));
        map.put(rs.getString("Method"), rs.getInt("Value"));
    }
    else {
      map = new HashMap<String, Object>();
      map.put("Tag", 0);
      map.put("Plate", 0);
      map.put("Name", rs.getString("Name"));
      map.put(rs.getString("Method"), rs.getInt("Value"));
    }
    objectMap.put(rs.getString("Name"), map);
 }

return objectMap.values();
Comments