How to retrieve items from database using JSONArray

Question

I'm trying to retrieve the data from the database by using JSONArray. My code works well but it only displays the last item in the database as number of items in the database.

My code is :

    Gson gson = new Gson();
    JsonObject myObj = new JsonObject();
    JSONObject Obj = new JSONObject();
    Connection conn = null; 
    try {
        conn=prepareConnection();
    } catch (ClassNotFoundException e1) {
        System.out.println( "Error --> " + displayErrorForWeb(e1));
        e1.printStackTrace();
    } catch (SQLException e1) {
        System.out.println( "Error --> " + displayErrorForWeb(e1));
        e1.printStackTrace();
    }

    try {
        JsonElement commentObj;

        StringBuilder sb1=new StringBuilder(1024);
        sb1.append("insert into ").append(uname.trim()).append("vcomments values(?,?,?,?)");
        String sql1=sb1.toString();
        PreparedStatement stmt1 = conn.prepareStatement(sql1);
         stmt1.setString(1,uname);
         stmt1.setString(2,message);
         stmt1.setInt(3,itemId);
         stmt1.setInt(4,albumId);
         int i=stmt1.executeUpdate();

        if(i!=1){
        myObj.addProperty("success", false);
    }
    else {
        myObj.addProperty("success", true);
    }
    PreparedStatement stmt = null;    
    String sql = null;

     try {     

        StringBuilder sb=new StringBuilder(1024);
        sb.append("select * from ").append(uname.trim()).append("vcomments").append(" where itemid=").append(itemId).append(" and albumid=").append(albumId);
        sql=sb.toString();
        stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        ArrayList<JSONObject> CommArray=new ArrayList<JSONObject>();

         while(rs.next()){
            Obj.put("uname",rs.getString("uname").trim());
            Obj.put("comment",rs.getString("comments").trim());
            CommArray.add(Obj);

            }    
         JSONArray arrayObj=JSONArray.fromObject(CommArray);
         commentObj=gson.toJsonTree(arrayObj);
         myObj.add("commentInfo", commentObj);
         out.println(myObj.toString());
         rs.close();                                                              
         stmt.close();                                                            
         stmt = null;                                                             


         conn.close();                                                            
         conn = null;                                                  

     }                                                              
     catch(Exception e){System.out.println( "Error --> " + displayErrorForWeb(e));}

The response received :

 {"success":true,"commentInfo":[{"uname":"shyam","comment":"erxdg"},{"uname":"shyam","comment":"erxdg"},{"uname":"shyam","comment":"erxdg"},{"uname":"shyam","comment":"erxdg"}]}

The database contains two items. And the last item is erxdg. Servlet sent the response back only last item.

Please anyone help me to fix it..... Thanks...........

something may be wrong with your query. Can you verify that your resultSet contains different values than erxdh? It would appear it is returning the same element over and over — 75inchpianist
– 75inchpianist, Commented Feb 21, 2013 at 18:27
@75inchpianist No, My Resultset returning the all values in the database — user2024438
– user2024438, Commented Feb 21, 2013 at 18:32

Gijs · Accepted Answer · 2013-02-21 18:30:30Z

1

You have to create a new JSONObject for each resultset. Create one in the loop, and add it to the array. Otherwise, you're just replacing the fields for the old object, and adding another reference to it to the results.

while(rs.next()){
    //new line:
    JSONObject Obj = new JSONObject();
    // original part looks fine:
    Obj.put("uname",rs.getString("uname").trim());
    Obj.put("comment",rs.getString("comments").trim());
    CommArray.add(Obj);
}

answered Feb 21, 2013 at 18:30

Gijs

11k5 gold badges29 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

How to retrieve items from database using JSONArray

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related