How to use a PreparedStatement twice with Try-with-Resources?

Question

When using PreparedStatements in a regular Java Try-Catch block, I can change the PreparedStatement to run different queries whenever I need, like so:

String sqlStatement = "update someTable set someValue = true";
try{
    PreparedStatement pstmt = con.prepareStatement(sqlStatement);
    pstmt.executeUpdate();

    /* Here I change the query */
    String anotherSqlStatement = "update aDifferentTable set something = false";
    pstmt = con.prepareStatement(anotherSqlStatement);
    pstmt.executeUpdate();
}
catch(Exception ex){
    ...
}

What is the right way to do this using Java's Try-with-Resources? This is what I've tried, but "The resource pstmt of a try-with-resources statement cannot be assigned".

try(Connection con = DriverManager.getConnection(someConnection, user, password);
    PreparedStatement pstmt = con.prepareStatement(sqlStatement)){
    ResultSet rs = pstmt.executeQuery();
    ....

    /* Here I attempt to change the query, but it breaks */
    String anotherSqlStatement = "select something from someTable";
    pstmt = con.prepareStatement(anotherSqlStatement);
}
catch(Exception ex){
    ...
}

I don't want to declare the variable again, I understand that would defeat the purpose of Try-with-Resources, I just want to assign it to something else. What is the correct way to do that?

I'm not sure that this is a duplicate; this question *is* trying to make assignments, he's just trying with more than one resource. — chrylis -cautiouslyoptimistic-, May 13 '16 at 17:25
More generally, never reuse a variable for a semantically different purpose. It's confusing and a good way to introduce bugs. Immutability is your friend. — charles-allen, Jul 18 '17 at 09:51

Nathan Hughes · Accepted Answer · 2016-05-13T18:35:15.657

8

Consider what would happen if Java let you do this. If you reassign what pstmt references, then after the first PreparedStatement executes then pstmt will refer to the second PreparedStatement. The close method only gets called on what pstmt refers to at the point that the block is finished executing, so close never gets called on the first PreparedStatement.

Instead make nested try-with-resources blocks:

try (Connection con = DriverManager.getConnection(someConnection, user, password)) {
    try (PreparedStatement pstmt = con.prepareStatement(sqlStatement)) {
        pstmt.executeUpdate();
    }

    try (PreparedStatement pstmt = con.prepareStatement(anotherSqlStatement)) {
        pstmt.executeUpdate();            
    }
}

This way there are two pstmt local variables in different scopes. The first PreparedStatement gets closed before the second one begins.

edited May 13 '16 at 18:35

answered May 13 '16 at 18:29

Nathan Hughes

94,330
19
181
276

The second example in the question uses a ResultSet... Don't forget to include that in the try-with-resources clause (it's also an AutoCloseable). @Nathan I don't know if you wanted to show that in your example? – charles-allen Jul 18 '17 at 09:48
Excellent answer @Nathan – Gaurav May 17 '18 at 09:16
For me i got an exception when i try to put a parameter in my query (someValue = ?), someone has the same issue ? – ⵔⴰⴼⵉⵇ ⴱⵓⵖⴰⵏⵉ Sep 17 '21 at 14:11
How would you rollback in case of any SQLException? The connection object is not available in catch block of parent try with resources block. – saran3h Jan 30 '23 at 19:36
@saran3h: you can do something similar to https://stackoverflow.com/a/3161654/217324 – Nathan Hughes Jan 31 '23 at 11:36

How to use a PreparedStatement twice with Try-with-Resources?

1 Answers1