File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "Statement.close() - when to use it?" Watch "Statement.close() - when to use it?" New topic

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 = conn.createStatement();

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
author & internet detective

Joined: May 26, 2003
Posts: 33124

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

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.

[OCA 8 Book] [Blog]
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?

[ 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:
subject: Statement.close() - when to use it?
It's not a secret anymore!