How do I insert MySQL Table data into an JSONArray?

Question

I'm trying to get some data from a MySQL table and insert this data into an JSONArray.

Below my Code:

public static JSONArray get_inactive_bookings(String username) {
        JSONArray array = new JSONArray();
        JSONObject obj = new JSONObject();
        Connection conn = null;
        ResultSet rs = null;
        int index = 0;
        int user_id = Users.get_user_id(username);
        String sql = "select * from bookings where user_id =" + user_id + 
                " and Effettiva_Restituzione is not null";
        try {
            conn = Utilities.connect();
            Statement st = conn.createStatement();
            rs = st.executeQuery(sql);
            //This is where I think problem shows up
            while (rs.next()) {

                obj.put("Booking_ID", rs.getInt("booking_id"));
                obj.put("Book_Title", Books.get_title(rs.getInt("book_id")));
                obj.put("Booking_Date", rs.getString("Data_Prenotazione"));
                obj.put("Return_Date", rs.getString("Data_Restituzione"));
                array.add(index, obj);
                index++;
            }
        } catch (SQLException ecc) {
            System.out.println("ERROR: " + ecc.getMessage());
        } finally {
            Utilities.disconnect(conn);
        }
        return array;
    }

In my table I have two occurrences once sql query is executed but in the resulting array I get two times the last occurrence in the Table. I guess it's while's fault. Do you guys know how to get both occurrences in the JSONArray? Thank you

peja · Accepted Answer · 2018-01-02 19:22:23Z

In the first iteration, the key-values are added firstly. But after the first iteration, you are overriding the mapped values for each keys in the object. Because, you add the same object multiple times overriding its entries. You must create a new jsonObject with each iteration. So the

JSONObject obj = new JSONObject();

must be in the while loop. like

public static JSONArray get_inactive_bookings(String username) {
    JSONArray array = new JSONArray();
    //JSONObject obj = new JSONObject(); -> this line must be in while loop
    Connection conn = null;
    ResultSet rs = null;
    int index = 0;
    int user_id = Users.get_user_id(username);
    String sql = "select * from bookings where user_id =" + user_id + 
            " and Effettiva_Restituzione is not null";
    try {
        conn = Utilities.connect();
        Statement st = conn.createStatement();
        rs = st.executeQuery(sql);
        //This is where I think problem shows up
        while (rs.next()) {
            JSONObject obj = new JSONObject(); // moved to here
            obj.put("Booking_ID", rs.getInt("booking_id"));
            obj.put("Book_Title", Books.get_title(rs.getInt("book_id")));
            obj.put("Booking_Date", rs.getString("Data_Prenotazione"));
            obj.put("Return_Date", rs.getString("Data_Restituzione"));
            array.add(index, obj);
            index++;
        }
    } catch (SQLException ecc) {
        System.out.println("ERROR: " + ecc.getMessage());
    } finally {
        Utilities.disconnect(conn);
    }
    return array;
}

Moreover, you don't need to keep 'index' separately to specify place of the element to be added. It is already added by default to the end of the array. So, it is better to add element like: array.add(obj); if the new element will be added to the end of the array.

Stefan H · Accepted Answer · 2018-01-02 18:59:33Z

You need to move your object instantiation into the loop, like this:

public static JSONArray get_inactive_bookings(String username) {
        JSONArray array = new JSONArray();
        Connection conn = null;
        ResultSet rs = null;
        int index = 0;
        int user_id = Users.get_user_id(username);
        String sql = "select * from bookings where user_id =" + user_id + 
                " and Effettiva_Restituzione is not null";
        try {
            conn = Utilities.connect();
            Statement st = conn.createStatement();
            rs = st.executeQuery(sql);
            //This is where I think problem shows up
            while (rs.next()) {
                JSONObject obj = new JSONObject();

                obj.put("Booking_ID", rs.getInt("booking_id"));
                obj.put("Book_Title", Books.get_title(rs.getInt("book_id")));
                obj.put("Booking_Date", rs.getString("Data_Prenotazione"));
                obj.put("Return_Date", rs.getString("Data_Restituzione"));
                array.add(index, obj);
                index++;
            }
        } catch (SQLException ecc) {
            System.out.println("ERROR: " + ecc.getMessage());
        } finally {
            Utilities.disconnect(conn);
        }
        return array;
    }

rimonmostafiz · Accepted Answer · 2018-01-02 19:07:30Z

1

You are using the same JSONObject(the same reference) for every element that you are trying to put into the array, so you just override its values in a cycle.

JSONObject obj = new JSONObject(); //put this inside your while loop

answered Jan 2, 2018 at 19:07

rimonmostafiz

1,4611 gold badge17 silver badges33 bronze badges

Comments

user9084493 · Accepted Answer · 2018-01-02 19:10:52Z

1

You need create a new object in each iteration, because you are overwriting that object-

while (rs.next()) {
    JSONObject obj = new JSONObject();
    obj.put("Booking_ID", rs.getInt("booking_id"));
    obj.put("Book_Title", Books.get_title(rs.getInt("book_id")));
    obj.put("Booking_Date", rs.getString("Data_Prenotazione"));
    obj.put("Return_Date", rs.getString("Data_Restituzione"));
    array.add(index, obj);
    index++;
}

answered Jan 2, 2018 at 19:10

user9084493

111 bronze badge

Collectives™ on Stack Overflow

How do I insert MySQL Table data into an JSONArray?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related