• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

closing Statement vs re-using it

 
nikil shar
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah - sorry. Ulf is right. I've probably misunderstood your question.
 
nikil shar
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic