How should I use try-with-resources with JDBC?

java

try-with-resources

jdbc

best-practices

byNikita Barsukov·Sep 22, 2024

Leverage try-with-resources to automatically close JDBC classes like Connection, PreparedStatement, and ResultSet. This technique boils down boilerplate code and mitigates resource leaks. Here's how you do it:

try (Connection conn = DriverManager.getConnection("url", "user", "pass");
     PreparedStatement ps = conn.prepareStatement("SQL statement here, not exactly a haiku");
     ResultSet rs = ps.executeQuery()) {
    while (rs.next()) {
        //Can't touch this, until rs.next() 
    }
} //Abracadabra! Resources are auto-closed, no need for cleanup in finally block.

The try block ensures that each declared resource is closed at the end. Encase your JDBC dealings in this setup for safer and sleeker code.

Advanced try-with-resources tactics

Try-with-resources Java 9+ enhancements

In Java 9 and onwards, you can improve readability by declaring the resources outside the try-with-resources statement, especially when they need to be final or effectively final:

Connection conn = DriverManager.getConnection("url", "user", "pass");
PreparedStatement ps = conn.prepareStatement("SELECT * FROM table");
try (conn; ps; ResultSet rs = ps.executeQuery()) {
    //Snap, Crackle, Pop goes the ResultSet
}

Magic safety wrapper class for JDBC

As Harry Potter fans, we can demonstrate our love for magic by adding it to our code. We can create wrapper class around JDBC operations. This SafeConnection class would implement AutoCloseable and override prepareStatement method:

public class SafeConnection implements AutoCloseable {
    private final Connection connection;

    public SafeConnection(Supplier<Connection> connectionSupplier) {
        this.connection = connectionSupplier.get();
    }

    // Override AutoCloseable close method
    @Override
    public void close() throws SQLException {
        if (connection != null) {
            //Hasta la vista, connection
            connection.close();
        }
    }

    // Prep, cook and serve the statement
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        //Add any secret sauce before serving here
        return connection.prepareStatement(sql);
    }
}

And here's how you cook try-with-resources with SafeConnection:

try (SafeConnection conn = new SafeConnection(() -> DriverManager.getConnection("url", "user", "pass"));
    PreparedStatement ps = conn.prepareStatement("SELECT * FROM table");
    ResultSet rs = ps.executeQuery()) {
    //Magic happens here 
}

This gives a sheen to your code and makes it self-documenting, so you don't end up with mystery meat in your project.

Taming the wild SQLException

To be a Jedi master of exceptions, tame the SQLException within your SafeConnection class’s close() method:

@Override
public void close() {
    try {
        connection.close();
    } catch (SQLException e) {
        //Swallow or spit out the exception, your heart’s choice
    }
}

This approach ensures that even if exception breaks storm, it doesn’t rain on your parade.

Galactic database configurations

To make yourself a JNDI or external configuration guru, manage them effectively. It’s like taking the magic wand away from Voldemort, so your application doesn’t go Avada Kedavra on users.