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