Best way to convert DB resultset into JSON string?

Amos_Mike
Amos_Mike used Ask the Experts™
on
I'm currently trying to populate a jQGrid with the results from a database query. jQgrid accepts either XML or JSON in order to populate the grid, but I'd like to use a JSON string. What is the best way to take the resultset from a database query and convert it into a JSON string? Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Can you show us an example JSON string ResultSet?

Author

Commented:
I need the string to be formatted like the attached snippet.. I've been trying to follow this guide:  http://www.greggbolinger.com/blog/2008/04/14/1208228820000.html but the only thing I'm unsure about is the method he uses to convert his results over to a json string.



{"page":"1","records":5,"rows": [ 
    {"cell":["Blue","This is blue"],"id":1}, 
    {"cell":["Green","This is green"],"id":2}, 
    {"cell":["Re","This is red"],"id":3}, 
    {"cell":["Black","This is Black"],"id":4}, 
    {"cell":["Purple","This is purple"],"id":5} 
    ],"total":"1"} 

Open in new window

Top Expert 2016

Commented:
My reading is that he uses http://flexjson.sourceforge.net/ to serialize it to a String using the code he gives
Java Developer
Top Expert 2010
Commented:
Just store your result set in a List and then use the code at the link to generate the json

while (rs.next()) {
     JQGridRow row = new JQGridRow();
     row.setId(rs.getColumn("id"));
     List cells = new ArrayList();
     cell.add(rs.getColumn("abc"));
     cell.add(rs.getColumn("xyz"));
     row.setCells(cells);
     rows.add(row);
}

json.setRows(rows);

// now serialize with whatever serializer you use, eg.

JSONSerializer serializer = new JSONSerializer();
String jsonResult = serializer.exclude("*.class").deepSerialize(json);

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial