java simple json fetch data from database and display

Question

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 []

Your code means you want array of objects, Because of that it must start with []. — Fady Saad
– Fady Saad, Commented May 23, 2017 at 3:20
how to get it start and end with {} ? like the desired output above — Teddybugs
– Teddybugs, Commented May 23, 2017 at 3:26
Then you don't need JSONArray list = new JSONArray(); directly Instead you need JSONObject inside it you can define JSONArray property and fil l it. — Fady Saad
– Fady Saad, Commented May 23, 2017 at 3:29

dat3450 · Accepted Answer · 2017-05-23 03:25:32Z

3

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);

answered May 23, 2017 at 3:25

dat3450

9543 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Teddybugs Over a year ago

you mean to use JSONObject instead of JSONArray like out.print(mainObject); correct?

dat3450 Over a year ago

Yes, mainObject should look like the desired JSON you have included at the top of your question.

Collectives™ on Stack Overflow

java simple json fetch data from database and display

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related