Try with resource how to use custom logic for closing connection

Question

In the below example we are closing connection and prepared statement using try with resource. It will close connection and prepared statement.

And also we are using 2 try block as explained below

For closing connection and prepared statement
Closing result set

In future, if we have any file-related operation then we'll need to write another try with resource block

try (Connection con = ds.getConnection();
     PreparedStatement ps = con.prepareStatement(sql);) {
    try (ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            list.add(rs.getInt("id"));
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

But if we are not using try with resource then we can rewrite above login in single try catch block as below

try {
    Connection con = ds.getConnection();
    PreparedStatement ps = con.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();

    while (rs.next()) {
        list.add(rs.getInt("id"));
    }
} catch (Exception e) {
    // Closing RS
    // Closing PS
    // Closing Connection or Customized closing connection logic
}

Question: Even if we have any custom operation while closing connection, is it possible to use a try with resource block?

Also please suggest which one better solution in that case.

Why would you add another try-with-resources? Why not add it to the existing try-with-resources? — Vince, Jan 16 '20 at 08:35
I think you cannot (easily or at all) intervent the automated closing of an `AutoCloseable`... And if you want to make sure everything is closed in a `try` without resources, then close it in a `finally` block and check for `null` before. — deHaar, Jan 16 '20 at 08:39
describe "custom closing connection logic". What do you want to do exactly? Seems that you want to do something very odd (and risky?) action. You can wrap the given connection with a custom class that overrides the `close` functionality in your try-with-resource structure. — KarelG, Jan 16 '20 at 09:15

score 2 · Answer 1 · answered Jan 16 '20 at 09:03

Try with resources is a better solution because it uses your connections only inside the "try" block. BUT :

catch (SQLException e)

is not the same thing with :

 catch (Exception e)

The (Exception e) will catch SQLException and if you said that you want to do some file related operations, will catch maybe a FileNotFoundException or IOException too so this catch is not very suggestive. In my opinion you should first read the Single Responsibility Principle and then you'll see that the best option to handle your problem is to create 2 try blocks which will handle independently the operations like this:

 public static List<Integer> getAllProducts() {
    List<Integer> productIds = new ArrayList<>();
    try (Connection con = ds.getConnection(); PreparedStatement ps = con.prepareStatement(sql);) {
        try (ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                productIds.add(rs.getInt("id"));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return productIds;
}

public static void writeSomething(String fileName) {
    try (BufferedWriter writer = new BufferedWriter(new FileWriter(fileName))) {
        writer.write("StackOverflow");
    } catch (IOException e) {
        e.printStackTrace();
    }
}

score 1 · Answer 2 · answered Jan 16 '20 at 08:48

I've had a similar issue a couple of weeks ago. You don't need to have multiple try-with-resources, you can only have one see this.

But in your second sample of code, you don't want to close your connection and all in the catch block. You must use the finally block.

In short you can use only one try-with-resources for the "normal" stuff, and create another try-catch-finally inside the latter (or use call to a custom method) to handle and close your customs operations.

try (Connection conn = datasource.getConnection();
         Statement statement = conn.createStatement();
         ResultSet rs = statement.executeQuery(request); ) {

    while (rs.next()) 
    {
        // Do your stuff.                     
    }

    try
    {

        // Do your stuff.
    } catch (Whatever e) {

        // Handle.
    } finally {

        // Close your custom stuff.
    }

} catch (SQLException ex) {

    // throw something.
}

Hope this helps.

Try with resource how to use custom logic for closing connection

2 Answers2