This week's book giveaway is in the Java in General forum.
We're giving away four copies of Think Java: How to Think Like a Computer Scientist and have Allen B. Downey & Chris Mayfield on-line!
See this thread for details.
Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General 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: 34378
346
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: 4014
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