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?
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.
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.
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.