This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes When to close a prepared statement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "When to close a prepared statement?" Watch "When to close a prepared statement?" New topic
Author

When to close a prepared statement?

Joerg Wesely
Greenhorn

Joined: Jan 15, 2010
Posts: 3
Hi,

I've created an application that periodically polls data from another sytem's database using a poller class as Runnable that does the fetching.
The DB was MS SQL in an earlier version, now it's Informix.

I ran in massive issues with the Informix database (it kind of crashed with a kind of out of memory error), what seems to be caused by code like this:

writing it like this seems to solve it:

So if I close the prepared statement in every loop cycle, everything seems to be ok.
I'm wondering if the second way is the best practice to do it or just a workaround- doesnt't it kill the performance gain of a prepared statement if I close it every time?

Did I made it wrong with the first implementation and MSSQL (using the JTDS JDBC driver) was just more fault-tolerant than Informix or is there kind of a bug in the Informix JDBC-driver raising this issue?

thank you in advance for your answer

Jörg

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

The correct way would be the first. You're closing the ResultSet objects properly (unless an error occurs; try-finally or try-with-resources would be better) and that should free up the memory used by them.
The second should actually not work. The first time, sure. But you've then closed the PreparedStatement, and the next time the loop body is executed the executeQuery call should throw an exception. The creation of the PreparedStatement should be moved inside the loop body to prevent this.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Joerg Wesely
Greenhorn

Joined: Jan 15, 2010
Posts: 3
thank you for your reply.

I tried the second variant against MSSQL and Informix, and it seems to work. I also was expecting an exception executing a closed prepared statement.

As far as I understand the JDBC javadocs, even closing the result set is optional- it should be closed as soon as the prepared statement is re-executed. Am I right here and the specification is not implemented correctly (what seems to be quite usual :-( ) or did I understand it wrong?
If I'd move the statement creation into the loop, using a prepared statement would be completely useless, right?

I've solved it now by using the Spring JDBCTemplate (I think the Spring guys have implemented this better than I ever could), but it would be good to know if the DB-server crashed due to workaround-requiring JDBC drivers or my lack of JDBC skills...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: When to close a prepared statement?