How to do Looping with ResultSet?

Question

So, I want to loop this ResultSet in order to update the table one by one, but the method while(rsl.next()) can't help me do the looping. It's just work once, and then the others are skipped. Can someone help me fix this problem? Thanks in advance

try {
    String url = "jdbc:mysql://localhost/minimarket";
    String user = "root";
    String pass = "";
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection(url, user, pass);
    Statement stmt = conn.createStatement();

    ResultSet rsl = stmt.executeQuery("SELECT * FROM keranjang WHERE pemesan='"+login.userid+"'");

    while (rsl.next()) {
        String nb = rsl.getString("nama_barang");
        String dtl = rsl.getString("detail");
        String beratt = rsl.getString("berat");
        String hrga = rsl.getString("harga");
        String jmlh = rsl.getString("jumlah");
        stmt.executeUpdate("UPDATE barang SET stok=stok+'"+jmlh+"' WHERE nama_barang='"+nb+"' AND detail='"+dtl+"' AND berat='"+beratt+"'");
        stmt.executeUpdate("DELETE FROM keranjang WHERE pemesan ='"+login.userid+"' AND nama_barang='"+nb+"'");
    }

    conn.close();
} catch (Exception error) {

}

System.exit(0);

Answer 1

When you execute an executeUpdate on your statement an int is returned and most importantly your result set object rs1 from your query gets closed and can't be accessed anymore since the Statement class only handles one query/result set. I haven't tested this myself but I am pretty sure this is the reason.

The solution is to have a separate Statement object for the update/delete so that the original ResultSet is not affected. Something like below

Statement stmt = conn.createStatement();
Statement updStmt = conn.createStatement();
ResultSet rsl = stmt.executeQuery("SELECT * FROM keranjang WHERE pemesan='"+login.userid+"'");

while (rsl.next()) {
    String nb = rsl.getString("nama_barang");
    String dtl = rsl.getString("detail");
    String beratt = rsl.getString("berat");
    String hrga = rsl.getString("harga");
    String jmlh = rsl.getString("jumlah");

    updStmt.executeUpdate("UPDATE barang SET stok=stok+'"+jmlh+"' WHERE nama_barang='"+nb+"' AND detail='"+dtl+"' AND berat='"+beratt+"'");
    updStmt.executeUpdate("DELETE FROM keranjang WHERE pemesan ='"+login.userid+"' AND nama_barang='"+nb+"'");
}