Is it bad when a function returns without closing prepared statement / resultset?

Related searches

So i stumbled upon a piece of java code, and noticed the login function returns 3 (int) if the user is banned. the closing of the resultset and the prepared statement used to login is after the return, so if the user is banned, it is never actually closed. My question is, if the function returns 3, does it reach the "finally" part where it closes everything?

I created a boolean and if the player is banned, the boolean is set to true. Then, at the end of the function where it returns "loginok" i added:

if(Banned)
    return 3;
else
    return loginok;

This should work but i was wondering if the previous method was bad or not.

The code looks somewhat like this:

    public int login(String login, String pwd) {

        Connection con = DatabaseConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement("SELECT id, password, banned WHERE name = ?");
            ps.setString(1, login);
            rs = ps.executeQuery();
            if (rs.next()) {
                int banned = rs.getInt("banned");
                if (rs.getByte("banned") == 1) { 
                    return 3;
                }
                accId = rs.getInt("id");
                String passhash = rs.getString("password");
                ps.close();
                rs.close();
                if (getLoginState() > LOGIN_NOTLOGGEDIN) {
                    loggedIn = false;
                    loginok = 7;
                } else if (pwd.equals(passhash)) {
                    loginok = 0;
                } else {
                    loggedIn = false;
                    loginok = 4;
                }
            }
        } catch (SQLException e) {
            log.error("ERROR", e);
        } finally { //Or maybe its fine because of this?
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
            }
        }
        return loginok;
    }

The previous method was ok. the finally clause will be executed. In case like that: The return statement is executed, finally block runs, the method actually returns.

closing Connection before returning ResultSet (JDBC and , Reconnecting every time you need to interact is likely to be slow. Also, a ResultSet becomes unusable when the statement that generated it is closed, and a statement becomes unusable when the connection is closed. You have mentioned that if we are calling a function from Java that return a ref cursor, then that cursor can be closed using resultset.close(); Do this way we close the resulset of Java only? Or that closes the actual database Cursors as well. I think both will take separate memory area. Thanks in advance.

Strongly recommend you refactor that code to use try-with-resources.

public int login(String login, String pwd) {
    Connection con = DatabaseConnection.getConnection();
    try (PreparedStatement ps = con.prepareStatement("SELECT id, password, banned WHERE name = ?")) {
        ps.setString(1, login);
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                if (rs.getByte("banned") == 1) { 
                    return 3;
                }
                accId = rs.getInt("id");
                String passhash = rs.getString("password");
                if (getLoginState() > LOGIN_NOTLOGGEDIN) {
                    loggedIn = false;
                    loginok = 7;
                } else if (pwd.equals(passhash)) {
                    loginok = 0;
                } else {
                    loggedIn = false;
                    loginok = 4;
                }
            }
        }
    } catch (SQLException e) {
        log.error("ERROR", e);
    }
    return loginok;
}

Now the question is a moot point, because they will always be closed.

Closing PreparedStatement (JDBC and Relational Databases forum , I have a private class with static public methods that return ResultSet objects such as : public static The connection object is for sure closed by the caller function but since. I am using a that nothings bad will happen but thanks again : ) I didn't notice I am not closing the PreparedStatement object. JDBC Introduction JDBC Driver DB Connectivity Steps Connectivity with Oracle Connectivity with MySQL Access without DSN DriverManager Connection Statement ResultSet PreparedStatement ResultSetMetaData DatabaseMetaData Store image Retrieve image Store file Retrieve file CallableStatement Transaction Management Batch Processing RowSet Interface

You should use ARM with Java 7. That way, you don't have to worry about closing your resources in the finally block, but you do need to catch exceptions. There is a slight flaw in your code though. You should not be using a if block to iterate through the result set. You would only pick up the first row of the result set, not iterate through the rest.

Your code can be something like this

try(Connection con = DatabaseConnection.getConnection();
    ps = con.prepareStatement("SELECT id, password, banned WHERE name = ?");) {
   ps.setString(1, login);
   try(ResultSet rs = ps.executeQuery();) {
       while (rs.next()) {
            ... You calculations here ...
       }
   }

}

You won't need to close any resources.

PreparedStatement (Java Platform SE 8 ), A SQL statement is precompiled and stored in a PreparedStatement object. If arbitrary parameter type conversions are required, the method setObject should be used java.sql.Statement � addBatch, cancel, clearBatch, clearWarnings, close, PreparedStatement or the SQL statement does not return a ResultSet object� The SELECT statement is the standard way to select rows from a database and view them in a result set. The java.sql.ResultSet interface represents the result set of a database query. A ResultSet object maintains a cursor that points to the current row in the result set.

To your question:

if the function returns 3, does it reach the "finally" part where it closes everything?

Yes, it always reaches the finally block. That's what the finally block is for.

Also, you should remember to always free the resources you are allocating. As other answers point out, since Java 7 you can use the "try with resources", that it's easier to use, since the finally block is implicit.

Closing resources is specially critical in applications that run non-stop for a long time, such as web applications. Otherwise, you'll start losing resources pretty fast, and the server would need to be restarted very often.

How to use isClosed method in java.sql.ResultSet, protected void clearResultSet() { if (resultSetTrace == null) { return; } for ( ResultSet rs assert rs2.isClosed() : "ResultSet must be explicitly closed after close statement"; } isClosed()); stmt.close(); stmt.close(); // Closing closed is ok assertTrue(stmt. isClosed() : "Statement must not be closed"; rs1.close(); assert !stmt. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically. A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends.

Class JdbcPreparedStatement | Apps Script, For documentation of this method, see java.sql.PreparedStatement#execute() . Return. Boolean — true if the first result is a result set; false if the� Canceling statement processing: SQLCancel Function: Returning information about a column in a result set: SQLDescribeCol Function: Executing an SQL statement: SQLExecDirect Function: Executing a prepared SQL statement: SQLExecute Function: Fetching a block of data or scrolling through a result set: SQLFetchScroll Function: Closing the cursor on

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. Some prepared statements return multiple results; the execute method handles these complex statements as well as the simpler form of statements handled by the methods executeQuery and executeUpdate.

As with Statement objects, to execute a PreparedStatement object, call an execute statement: executeQuery if the query returns only one ResultSet (such as a SELECT SQL statement), executeUpdate if the query does not return a ResultSet (such as an UPDATE SQL statement), or execute if the query might return more than one ResultSet object.