Will the connection.close() and statement.close() ever be called???

[Stephen Douglas]

I have a code and question on it..

import java.sql.*;

public class ResultSet_lastrow {

    public static int last_row() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Oracle9i", "rms", "stephen00.rms");
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            resultSet = statement.executeQuery("select * from rms_accountstore");
            resultSet.last();
            [COLOR="Red"]return resultSet.getRow();[/COLOR]

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (Exception exception) {
                exception.printStackTrace();
                System.exit(1);
            }
        }
        return 0;
    }

    public static void main(String[] args) {
        ResultSet_lastrow r = new ResultSet_lastrow();
        System.out.println(ResultSet_lastrow.last_row());
    }
}

The colored portion of the snippet returns a value.. I have a question that whether the connection.close() and statement.close() methods will ever be called???

i mean the return statements pass the control to the calling methods then in that cases what will be the exact behavior of this program. please suggest

[Stephen Douglas]

OR this code will be better !!

import java.sql.*;

public class ResultSet_lastrow {

    public static int last_row() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Oracle9i", "rms", "stephen00.rms");
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            resultSet = statement.executeQuery("select * from rms_accountstore");
            resultSet.last();

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (Exception exception) {
                exception.printStackTrace();
                System.exit(1);
            }
        }
        try {
            [COLOR="Red"]return resultSet.getRow();[/COLOR]
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return 0;
    }

    public static void main(String[] args) {
        ResultSet_lastrow r = new ResultSet_lastrow();
        System.out.println(ResultSet_lastrow.last_row());
    }
}

:rolleyes:

[porchrat]

I believe it will close automatically with the return but surely this depends on the nature of the variables and objects used and won't always be the case (global vs local).

If you are going to return first then the code beneath your return won't execute. (this is the case with the code in your first post).

As a habit I always close before returning a value though just to be safe as there really is no harm in it.

[Stephen Douglas]

As a habit I always close before returning a value though just to be safe as there really is no harm in it.

SO you go by the code i gave in my reply #2.

but surely this depends on the nature of the variables and objects used and won't always be the case (global vs local)

I didn't got this part. plz elaborate.thnx

[porchrat]

SO you go by the code i gave in my reply #2.

Well yeah it is far better the code in post 1 will still close it because of the return, but the actual close() method is not going to be executed. It is just that when the method is returned from that particular local variable/object is removed from memory which is essentially "closed".

I didn't got this part. plz elaborate.thnx

Well say for example your Statement object were global (it isn't in your example, but it could be for some reason), at that point unless you actually closed it at some point then even after your method returned a result it would remain open.

For this reason and to avoid the potential confusion I close it as soon as I am done getting the information required from the ResultSet.

Also I often write code that requires the use of the information from one query in order to run another query. So I can have multiple Statement objects in the same method. To avoid trying to use a Statement before it is closed elsewhere I close it as soon as possible.

[Stephen Douglas]

Is there any way we can detect that the connection is still open?? :D

[Stephen Douglas]

Well yeah it is far better the code in post 1 will still close it because of the return, but the actual close() method is not going to be executed. It is just that when the method is returned from that particular local variable/object is removed from memory which is essentially "closed".

Well say for example your Statement object were global (it isn't in your example, but it could be for some reason), at that point unless you actually closed it at some point then even after your method returned a result it would remain open.

Have I got you correct?? If the statement and connection variables are local then although the actual close() method will never be called.. but due to the return statement these variables will be completely removed from the memory and consequently the connection will be closed..

And that in the case when these variables are global... the connection will not be closed !!

[porchrat]

Is there any way we can detect that the connection is still open?? :D

Statement has an isClosed() method.

Will return true if Statement object is closed and false if it is still open.

[porchrat]

Have I got you correct?? If the statement and connection variables are local then although the actual close() method will never be called.. but due to the return statement these variables will be completely removed from the memory and consequently the connection will be closed..

And that in the case when these variables are global... the connection will not be closed !!

Yea that is kind of the nature of java.

Local is only valid within the context of the method that creates it and only within that particular invocation of the method. All local variables, objects etc. created during the execution of the method will be freed when the method returns. This includes the Statement object.

Not so with global variables and objects. Once a global variable or object is influenced by a method the return of that method won't all of a sudden cause the global variable or object to be freed. So a Statement object created outside the method shouldn't close automatically just because that method returns.

[Tolls]

Have I got you correct?? If the statement and connection variables are local then although the actual close() method will never be called.. but due to the return statement these variables will be completely removed from the memory and consequently the connection will be closed..

And that in the case when these variables are global... the connection will not be closed !!

In your example they will be closed because you have them in a finally block, which is always called when execution leaves a try{} block.

However, you cannot say that the connection or statement will be closed without these. That is a false assumption. Yes, there maybe some something in a finaliser to close them off, but there is no guarantee when that will be run, so you will be leaking resources.

The short and simple rule is your JDBC code should have the format:

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    // Open stuff, get results, turn those results into something useful.
} catch (...whatever exceptions you want to catch...) {
} finally {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException ex) { ...possibly log... }
    }
    if (ps != null) {
        try {
            ps.close();
        } catch (SQLException ex) { ...possibly log... }
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException ex) { ...possibly log... }
    }
}

The stuff in finally will possibly be a method in a utility class, since it's quite common code.

Why all the try/catch in there? Because, if the ps.close() throws an exception you still want to be able to close the conn.

Why close the resultset there? Because you should close all resources, and not rely on the implementation to do it for you.

And finally, since you seem to be getting the number of rows in this case, might I suggest a simple "SELECT count(*) FROM rms_accountstore"? It will be significantly faster, and gets you the data you want.

[Stephen Douglas]

which is always called when execution leaves a try{} block.

Which means the concept which i adopted last night is actually wrong !! is it???????
thnx for clarification.

I am sorry i didn't get this part..

but there is no guarantee when that will be run, so you will be leaking resources.

please elaborate. thnx

[JosAH]

Which means the concept which i adopted last night is actually wrong !! is it???????
thnx for clarification.

I am sorry i didn't get this part.. please elaborate. thnx

You can't just leave a ResultSet, nor a Statement, nor a Connection be and forget all about it; if the garbage collector runs (if it runs) the stuff will be closed eventually but there is no guarantee that the garbage collector ever runs. You should close those things if you don't need them anymore; first, before you close them you should get the data you want and after that close them (e.g. see the API documentation for the ResultSet: you can't get any data from it after it has been closed).

kind regards,

Jos

[Tolls]

Indeed, there's actually no guarantee that even after garbage collection has collected a connection or whatever that that will actually close the physical resource in the db. I wouldn't be surprised to find that some of the drivers out there do not have a finaliser for their resources.

You should never expect that sort of behaviour...always close what you open, and always do it in a finally block. There may be the odd exception to this, but it's a good start point.

[Stephen Douglas]

Got it ! :) thnx