wood burning stoves*
The moose likes JDBC and the fly likes closing Statement vs re-using it Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "closing Statement vs re-using it" Watch "closing Statement vs re-using it" New topic
Author

closing Statement vs re-using it

nikil shar
Ranch Hand

Joined: May 25, 2008
Posts: 116
hi all,
was wondering if i dont close a Statement and re-use it to do another select from db would it hold the previous records as well as the current one ??

e.g




if i dont close the stmt and rs in the finally clause would that affect anything ??


thanks.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

There is practically no gain from reusing a Statement, as far as I know.

Where the caching certainly makes sense is PreparedStatement. However, the PreparedStatement must be reused with the connection for which it was created. Most connection pools therefore cache PreparedStatements (and CallableStatements) and reuse them when you create identil version again. This is usually configurable. It will still save some minuscule time if you keep your version for repeated execution (eg. lots of INSERT statements executed at once: keep the instance of PrepraedStatement and just parametrize it for every execution as needed, then your process is as efficient as it gets).

If you don't use connection pool, then it makes sense to keep the statements for the life of your connection. Some JDBC drivers (Oracle's, for example) implement statement caching on the plain connection too - see the documentation of your JDBC driver in that case.

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
nikil shar wrote:was wondering if i dont close a Statement and re-use it to do another select from db would it hold the previous records as well as the current one ?

No. Executing a new query returns a new ResultSet object.


Ping & DNS - my free Android networking tools app
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Ah - sorry. Ulf is right. I've probably misunderstood your question.
nikil shar
Ranch Hand

Joined: May 25, 2008
Posts: 116
Ulf Dittmer wrote:
nikil shar wrote:was wondering if i dont close a Statement and re-use it to do another select from db would it hold the previous records as well as the current one ?

No. Executing a new query returns a new ResultSet object.


thanks for the reply. So if the the code has finished executing the first try block and a GC cycle kicks off would it close the "Stmt" object since there are no references to it ??

i notice i sometimes get null pointers and it sometimes works fine. i can get around the issue by checking for null but was just curious as to how the 'Stmt' object became null in the first place?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
So if the the code has finished executing the first try block and a GC cycle kicks off would it close the "Stmt" object since there are no references to it ?

It better not, because "Stmt" does hold a reference to the object (assuming that "Stmt" is an instance field).

By the way, you should always close resources when you're done with them - ResultSets, Statements and Connections. And it really goes without saying that you should not have an empty catch block without doing something about the exception (at least log it to a file or console where you'll see it).

i notice i sometimes get null pointers and it sometimes works fine. i can get around the issue by checking for null but was just curious as to how the 'Stmt' object became null in the first place?

Hard to say. Is that the entire code?
nikil shar
Ranch Hand

Joined: May 25, 2008
Posts: 116
Ulf Dittmer wrote:
So if the the code has finished executing the first try block and a GC cycle kicks off would it close the "Stmt" object since there are no references to it ?

It better not, because "Stmt" does hold a reference to the object (assuming that "Stmt" is an instance field).

By the way, you should always close resources when you're done with them - ResultSets, Statements and Connections. And it really goes without saying that you should not have an empty catch block without doing something about the exception (at least log it to a file or console where you'll see it).

i notice i sometimes get null pointers and it sometimes works fine. i can get around the issue by checking for null but was just curious as to how the 'Stmt' object became null in the first place?

Hard to say. Is that the entire code?[/quo
te]

thanks for the reply. thats not the entire code but this is the only chunk of code where i can see null pointer being thrown.
 
Don't get me started about those stupid light bulbs.
 
subject: closing Statement vs re-using it