CODEWITHSUNDEEP
javasqldatabase
Dylan
Dylan

Reputation: 158

Java - unreachable statement when i try to close database resources

Whenever I try to close the resources rs.close() or stmt.close() or even conn.close() I get an error saying "unreachable statement". Strange thing is that it works in other methods. Maybe I forgot something?

   public static boolean exists(int av) {

    try {

        Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
        Statement stmt = conn.createStatement();
        String query = "SELECT id FROM audiovisuals WHERE id='" + av + "' LIMIT 1";
        ResultSet rs = stmt.executeQuery(query);

        if(!rs.isBeforeFirst()) {
           return false;
        }
        else {
            return true;
        }

        rs.close();
        stmt.close();
        conn.close();

    } catch (SQLException e) {

        String error = "SQLException: " + e.getMessage() + "\nSQLState: " + e.getSQLState() + "\nVendorError: " + e.getErrorCode();
        return false;

    }

}

Upvotes: 0

Views: 1353

Answers (3)

fabian
fabian

Reputation: 82461

Your close statements can never be reached, since you return before in both if cases.

You can use try-with-resources to close AutoCloseables automatically (everything you try to close is autocloseable):

try {

   String query = "SELECT id FROM audiovisuals WHERE id='" + av + "' LIMIT 1";
   try (Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(query)) {

        if(!rs.isBeforeFirst()) {
           return false;
        }
        else {
            return true;
        }

    } // resources are automatically closed here
} catch (SQLException e) {
    String error = "SQLException: " + e.getMessage() + "\nSQLState: " + e.getSQLState() + "\nVendorError: " + e.getErrorCode();
}
return false;

Try-with-resources closes all resources (conn, stmt amd rs) even if an exception is thrown for one of the close() method calls.

Upvotes: 1

resueman
resueman

Reputation: 10613

This looks like a good place to use try-with-resources. With you code, it would look like this

public static boolean exists(int av) {

    try(Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT id FROM audiovisuals WHERE id='" + av + "' LIMIT 1")){

        if(!rs.isBeforeFirst()) {
           return false;
        }
        else {
            return true;
        }
    } catch (SQLException e) {

        String error = "SQLException: " + e.getMessage() + "\nSQLState: " + e.getSQLState() + "\nVendorError: " + e.getErrorCode();
        return false;

    }

}

Upvotes: 1

mikea
mikea

Reputation: 6667

I'm assuming here that you are getting unreachable statement because you are putting the calls after a return statement. You need to put the close calls in a finally block. So your method would look something like this:

 Connection conn = null;
 Statement stmt = null;
 ResultSet rs = null;
 try {

    conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
    stmt = conn.createStatement();
    String query = "SELECT id FROM audiovisuals WHERE id='" + av + "' LIMIT 1";
    rs = stmt.executeQuery(query);

    if(!rs.isBeforeFirst()) {
       return false;
    }
    else {
        return true;
    }

} catch (SQLException e) {

    String error = "SQLException: " + e.getMessage() + "\nSQLState: " + e.getSQLState() + "\nVendorError: " + e.getErrorCode();
    return false;

} finally {
    if (rs != null)
        rs.close();
    if (stmt != null)
        stmt.close();
    if (conn != null)
        conn.close();
}

Upvotes: 2

Related Questions