My name
is
Jon Skeet

SQL Statement Presumably runs, no errors, but assertion fails

So I got this issue where I have to try and book all seats in a flight.

I've made a Helper class with static methods, which is used for Unit Testing purposes. When I run the test though, my bookAll() method runs as so does the "isAllBooked()" method. But the isAllBooked method returns false.

After investigating, it turns out the database was not touched by the initial bookAll() statement at all. But there are no errors. What could be the cause of this?

HelperClass.java

public static void bookAll(String plane_no) {
    conn = getConnection("", "");
    try {
        conn.prepareStatement(""
                + "UPDATE SEAT"
                + "SET reserved='1337',booked='1337',booking_time='1337'"
                + "WHERE plane_no='" + plane_no + "';"
        );
        conn.commit();
    } catch (SQLException ex) {
        System.out.println("[HELPER CLASS] SQL ERROR: " + ex.getMessage());
    }
    closeConnection(conn);
}

public static boolean isAllBooked(String plane_no) {
    conn = getConnection("", "");
    try {
        ResultSet rs =
                conn.prepareStatement(
                        "SELECT booked FROM SEAT WHERE plane_no='"
                                + plane_no + "'")
                        .executeQuery();
        while(rs.next()) {
            int i = rs.getInt("BOOKED");
            if(rs.wasNull()) {
                closeConnection(conn);
                return false;
            }
        }
    } catch (SQLException ex) {
        System.out.println("[HELPER CLASS] SQL ERROR: " + ex.getMessage());
    }
    closeConnection(conn);
    return true;
}

HelperClassTest.java

@Test
public void testBookAll() {
    System.out.println("bookAll");
    String plane_no = "CR9";
    HelperClass.bookAll(plane_no);
    boolean expResult = true;
    boolean result = HelperClass.isAllBooked(plane_no);
    assertEquals(expResult,result);
}

You're never executing the statement:

conn.prepareStatement(""
        + "UPDATE SEAT"
        + "SET reserved='1337',booked='1337',booking_time='1337'"
        + "WHERE plane_no='" + plane_no + "';"
);
conn.commit();

You're ignoring the return value of prepareStatement, so you're not actually executing anything against the database.

You should be creating a PreparedStatement using fixed SQL with parameters, specifying parameter values against that PreparedStatement, and then call PreparedStatement.executeUpdate().

So something like:

public static void bookAll(String planeNo) {
    String sql = "UPDATE SEAT "
               + "SET (reserved, booked, booking_time) "
               + "VALUES ('1337', '1337', '1337') "
               + "WHERE plane_no=?";
    try (Connection conn = getConnection("", "");
         PreparedStatement st = conn.prepareStatement(sql)) {
        st.setString(1, planeNo);
        st.executeUpdate();
        conn.commit();
    } catch (SQLException ex) {
        System.out.println("[HELPER CLASS] SQL ERROR: " + ex.getMessage());
    }
}

Note that this also:

Uses a local variable for the connection, instead of an instance variable. You should use a separate Connection for each operation, even if it's actually pooled
Uses variable names which comply with Java coding conventions
Uses a try-with-resources statement to make sure everything is closed even on error

Personally I wouldn't use that style of exception handling - I'd let the exception propagate up, possibly rethrowing it as an unchecked exception - but that's a different matter.

See more on this question at Stackoverflow