File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "When to close a prepared statement?" Watch "When to close a prepared statement?" New topic

When to close a prepared statement?

Joerg Wesely

Joined: Jan 15, 2010
Posts: 3

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


Rob Spoor

Joined: Oct 27, 2005
Posts: 20271

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.

How To Ask Questions How To Answer Questions
Joerg Wesely

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:
subject: When to close a prepared statement?
It's not a secret anymore!