Results 1 to 14 of 14
- 04-08-2010, 10:53 PM #1
Will the connection.close() and statement.close() ever be called???
I have a code and question on it..
Java Code: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()); } }
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 suggestThe Quieter you become the more you are able to hear !
- 04-08-2010, 10:55 PM #2
OR this code will be better !!
Java Code: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()); } }
The Quieter you become the more you are able to hear !
- 04-08-2010, 11:04 PM #3
Senior Member
- Join Date
- Mar 2009
- Posts
- 105
- Rep Power
- 0
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.Last edited by porchrat; 04-08-2010 at 11:09 PM.
- 04-08-2010, 11:12 PM #4As a habit I always close before returning a value though just to be safe as there really is no harm in it.
but surely this depends on the nature of the variables and objects used and won't always be the case (global vs local)The Quieter you become the more you are able to hear !
- 04-08-2010, 11:22 PM #5
Senior Member
- Join Date
- Mar 2009
- Posts
- 105
- Rep Power
- 0
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
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.Last edited by porchrat; 04-08-2010 at 11:26 PM.
- 04-08-2010, 11:30 PM #6
Is there any way we can detect that the connection is still open?? :D
The Quieter you become the more you are able to hear !
- 04-08-2010, 11:37 PM #7Well 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.
And that in the case when these variables are global... the connection will not be closed !!The Quieter you become the more you are able to hear !
- 04-08-2010, 11:38 PM #8
Senior Member
- Join Date
- Mar 2009
- Posts
- 105
- Rep Power
- 0
- 04-08-2010, 11:43 PM #9
Senior Member
- Join Date
- Mar 2009
- Posts
- 105
- Rep Power
- 0
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.Last edited by porchrat; 04-08-2010 at 11:48 PM.
- 04-09-2010, 10:30 AM #10
Moderator
- Join Date
- Apr 2009
- Posts
- 13,541
- Rep Power
- 27
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:
Java Code: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... } } }
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.
- 04-09-2010, 11:42 AM #11which is always called when execution leaves a try{} block.
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.The Quieter you become the more you are able to hear !
- 04-09-2010, 11:58 AM #12
- Join Date
- Sep 2008
- Location
- Voorschoten, the Netherlands
- Posts
- 14,422
- Blog Entries
- 7
- Rep Power
- 29
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
- 04-09-2010, 12:04 PM #13
Moderator
- Join Date
- Apr 2009
- Posts
- 13,541
- Rep Power
- 27
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.
- 04-09-2010, 12:15 PM #14
Similar Threads
-
Can't close JFrame
By dunafrothint in forum AWT / SwingReplies: 5Last Post: 12-16-2009, 06:00 PM -
How to Close Combobox.
By ocean in forum New To JavaReplies: 4Last Post: 11-17-2009, 05:50 PM -
close to zero
By nokicky in forum New To JavaReplies: 6Last Post: 10-26-2009, 05:30 PM -
[SOLVED] Close Exe
By smartsubroto in forum New To JavaReplies: 11Last Post: 10-14-2008, 09:04 AM -
How to close JDBC Connection
By Heather in forum JDBCReplies: 2Last Post: 07-15-2007, 02:07 PM
Bookmarks