aspose file tools*
The moose likes JDBC and the fly likes Statement.close() - when to use it? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Statement.close() - when to use it?" Watch "Statement.close() - when to use it?" New topic
Author

Statement.close() - when to use it?

Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
I was wondering what's the overhead of doing s.close() each time for every query
Statement s;
s = conn.createStatement();
s.execute(createString1);
s.close();
s = conn.createStatement();
s.execute(createString2);
s.close();

or should there be only one s.close() at the end of all executions? Can anyone please point me to the theory behind this?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

Brian,
You should call close each time. When you call createStatement() you get a different object so calling close on it won't close the first statement. It will be garbage collected eventually, but it's better to inform the driver that it can free any resources used by the statement.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

The only time I don't close statements immediately after using them is during mass inserts/updates, but then those only with prepared statements. For example, if you're inserting 10,000 records, you can create one PreparedStatement and just call setString()/setInt()/etc to change the parameters between calls allowing you to insert multiple records without creating new statement objects. This is a pretty special case though.


My Blog: Down Home Country Coding with Scott Selikoff
Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
Jeanne and Scotty,

Thanks for your responses.. it has been a while that I did jdbc, need a bit of reading...

So what you are saying is, the below code as the optimum (with regards to Statement usage):



So when using Statement, the above is a 'good' code?

Brian
[ August 10, 2007: Message edited by: Brian Percival ]
Remko Strating
Ranch Hand

Joined: Dec 28, 2006
Posts: 893
I would run the code to close in a finally block so you're sure that the connection is closed. I've written a utility method for that because you have to catch a SQLException when you're closing a connection.

Also just use one connection within your method, which you can close after you've done with your method.


Remko (My website)
SCJP 1.5, SCWCD 1.4, SCDJWS 1.4, SCBCD 1.5, ITIL(Manager), Prince2(Practitioner), Reading/ gaining experience for SCEA,
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Statement.close() - when to use it?