BerndGit BerndGit - 6 months ago 9
Java Question

different results if PreparedStatement is used as resource in try block

Some behaviour I don't understand.
I have a running script like:

ResultSet res = null;

String cmd = new String("SELECT value FROM " +pDS.getValueTableName() + " WHERE itemID=? and propertyID=? ORDER BY checkpointID DESC");
PreparedStatement pstmt= dbconn.prepareStatement(cmd) ;
pstmt.setLong(1,itemID);
pstmt.setLong(2,pDS.getPropertyID());


try {
res = pstmt.executeQuery();
}
catch(Exception e)
{
throw(e);
}
if (!res.next())
{
// other code
}


Here i get the expected values
res.next()=true
.
No exception is thrown.

I wanted to refactor the code, and use the Autoclose funtionalty of the try block, like:

ResultSet res = null;

String cmd = new String("SELECT value FROM " +pDS.getValueTableName() + " WHERE itemID=? and propertyID=? ORDER BY checkpointID DESC");

try (PreparedStatement pstmt= dbconn.prepareStatement(cmd) ){
pstmt.setLong(1,itemID);
pstmt.setLong(2,pDS.getPropertyID());
res = pstmt.executeQuery();
}
catch(Exception e)
{
LOGGER.error("Error at getLatestPropertyResultSet",e);
throw(e);
}
if (!res.next())
{
// other code
}


However now
res.next()=false
. The resultset itselve is intialized
res!=null
.

Why did this modification change the behaviour of the script?

Answer

Without seeing where you call res.next() I can only assume that the try-with-resource is doing exactly as advertised and closing the prepared statement once you leave the try block and thus the result is "closed" with it.

update: based on your edit my suspicions are confirmed. You need to move any work related to the resource inside the try block.