Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Statement.close() - when to use it?

 
Brian Percival
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34672
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4015
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Brian Percival
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 893
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic