如何将mysql表数据插入jsonarray?

jei2mxaa  于 2021-06-21  发布在  Mysql
关注(0)|答案(4)|浏览(419)

我试图从mysql表中获取一些数据,并将这些数据插入到jsonarray中。
在我的代码下面:

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

在我的表中,一旦执行sql查询,我就有两个引用,但是在结果数组中,我得到的是表中最后一个引用的两倍。我想这是while的错。你们知道如何在jsonarray中同时出现这两种情况吗?谢谢您

f87krz0w

f87krz0w1#

您需要将对象示例化移动到循环中,如下所示:

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;
    }
apeeds0o

apeeds0o2#

对于要放入数组的每个元素,都使用相同的jsonobject(相同的引用),因此只需在一个循环中重写它的值。

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

czfnxgou3#

您需要在每次迭代中创建一个新对象,因为您正在覆盖该对象-

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++;
}
rt4zxlrg

rt4zxlrg4#

在第一次迭代中,首先添加关键值。但是在第一次迭代之后,您将覆盖对象中每个键的Map值。因为,您多次添加同一对象以覆盖其条目。每次迭代都必须创建一个新的jsonobject。所以
jsonobject obj=新建jsonobject();
必须在while循环中。喜欢

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

此外,您不需要单独保留“index”来指定要添加的元素的位置。默认情况下,它已经添加到数组的末尾。因此,最好添加如下元素: array.add(obj); 如果新元素将被添加到数组的末尾。

相关问题