SQLException: query does not return results

Question

I am doing a project in Java Swing and using SQLite as my database. This is the function I wrote for deleting a record from the Room table in my database.

public void Delete() {
        String room_code = jTextField5.getText();
        String sql = "DELETE FROM Room WHERE room_code = '" + room_code + "'";
        try {
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            if (rs.next()) {
                JOptionPane.showMessageDialog(null, "Room Deleted Successfully");
            }
            else {
                JOptionPane.showMessageDialog(null, "Invalid Room Code");
            }
        } 
        catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

However, I am met with the following Exception: SQLException: query does not return results. I have tried to use pst.executeUpdate() as suggested in other answers but it says "int cannot be converted into resultset".

Answer 1

A DELETE statement does not return a result set. You should call method executeUpdate rather than method executeQuery.

Also, you can use place holders with a PreparedStatement.

Also you should use try-with-resources

Consider the following code.

public void Delete() {
    String room_code = jTextField5.getText();
    String sql = "DELETE FROM Room WHERE room_code = ?";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(room_code);
        int count = ps.executeUpdate();
        if (count > 0) {
            JOptionPane.showMessageDialog(null, "Room Deleted Successfully");
        }
        else {
            JOptionPane.showMessageDialog(null, "Invalid Room Code");
        }
    } 
    catch (Exception ex) {
        JOptionPane.showMessageDialog(null, ex);
    }
}