Teddybugs Teddybugs - 1 year ago 32
Java Question

java simple json fetch data from database and display

i would like to ask on how to produce the desire json output like below:

{
"Result":"OK",
"Records":[
{"PersonId":1,"Name":"Benjamin Button","Age":17,"RecordDate":"\/Date(1320259705710)\/"},
{"PersonId":2,"Name":"Douglas Adams","Age":42,"RecordDate":"\/Date(1320259705710)\/"},
{"PersonId":3,"Name":"Isaac Asimov","Age":26,"RecordDate":"\/Date(1320259705710)\/"},
{"PersonId":4,"Name":"Thomas More","Age":65,"RecordDate":"\/Date(1320259705710)\/"}
]
}


my jsp code are look like below:

<%@page language="java" import="java.sql.*"%>
<%@page import="java.util.*" %>
<%@page contentType="text/html; charset=UTF-8"%>
<%@page import="org.json.simple.JSONArray"%>
<%@page import="org.json.simple.JSONObject"%>
<%@page import="org.json.simple.parser.JSONParser"%>
<%@page import="org.json.simple.parser.ParseException"%>
<%
String dept = (String)request.getParameter("dept");
String sql = "SELECT * FROM employees WHERE department='"+dept+"'";

try
{
Class.forName("com.mysql.jdbc.Driver");
Connection conn=null;
conn=DriverManager.getConnection("jdbc:mysql://localhost/jspjsons","root","123456");
ResultSet rs=null;
Statement stm1=conn.createStatement();

JSONArray list = new JSONArray();
rs=stm1.executeQuery(sql);
while(rs.next())
{
JSONObject obj=new JSONObject();
obj.put("PersonId", rs.getString("id"));
obj.put("Name", rs.getString("name"));
obj.put("Age", rs.getString("age"));
obj.put("RecordDate", rs.getString("date"));

list.add(obj);
}

out.print(list);
}
catch(Exception ex)
{
out.println("<h1>"+ex+"</g1>");
}
%>


also upon display the output there is always the front and back bracket like this [], how do i get rid of it? need it to start and end with {} not []

Answer Source

You are creating the Records array correctly (which you have stored in the list variable), all you would need to do is add that to a new JSONObject along with the Result.

Keep in mind that { ... } indicates a JSONObject, and [ ... ] indicates a JSONArray.

while(rs.next())
{
    JSONObject obj=new JSONObject();
    obj.put("PersonId", rs.getString("id"));
    obj.put("Name", rs.getString("name"));
    obj.put("Age", rs.getString("age"));
    obj.put("RecordDate", rs.getString("date"));

    list.add(obj);
}

//Include this code beneath to create the JSON you require (mainObject).
JSONObject mainObject = new JSONObject();
mainObject.put("Result", "OK");
mainObject.put("Records", list);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download