Is there a cleaner way to use try-with-resource and PreparedStatement?

Question

Here is the Main.java:

package foo.sandbox.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        final String SQL = "select * from NVPAIR where name=?";
        try (
                Connection connection = DatabaseManager.getConnection();
                PreparedStatement stmt = connection.prepareStatement(SQL);
                DatabaseManager.PreparedStatementSetter<PreparedStatement> ignored = new DatabaseManager.PreparedStatementSetter<PreparedStatement>(stmt) {
                    @Override
                    public void init(PreparedStatement ps) throws SQLException {
                        ps.setString(1, "foo");
                    }
                };
                ResultSet rs = stmt.executeQuery()
        ) {
            while (rs.next()) {
                System.out.println(rs.getString("name") + "=" + rs.getString("value"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

And here is DatabaseManager.java

package foo.sandbox.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Initialize script
 * -----
 * CREATE TABLE NVPAIR;
 * ALTER TABLE PUBLIC.NVPAIR ADD value VARCHAR2 NULL;
 * ALTER TABLE PUBLIC.NVPAIR ADD id int NOT NULL AUTO_INCREMENT;
 * CREATE UNIQUE INDEX NVPAIR_id_uindex ON PUBLIC.NVPAIR (id);
 * ALTER TABLE PUBLIC.NVPAIR ADD name VARCHAR2 NOT NULL;
 * ALTER TABLE PUBLIC.NVPAIR ADD CONSTRAINT NVPAIR_name_pk PRIMARY KEY (name);
 *
 * INSERT INTO NVPAIR(name, value) VALUES('foo', 'foo-value');
 * INSERT INTO NVPAIR(name, value) VALUES('bar', 'bar-value');
 */
public class DatabaseManager {
    /**
     * Class to allow PreparedStatement to initialize parmaters inside try-with-resource
     * @param <T> extends Statement
     */
    public static abstract class PreparedStatementSetter<T extends Statement> implements AutoCloseable {
        public PreparedStatementSetter(PreparedStatement pstmt) throws SQLException {
            init(pstmt);
        }

        @Override
        public void close() throws Exception {
        }

        public abstract void init(PreparedStatement pstmt) throws SQLException;
    }

    /* Use local file for database */
    private static final String JDBC_CONNECTION = "jdbc:h2:file:./db/sandbox_h2.db;MODE=PostgreSQL";

    static {
        try {
            Class.forName("org.h2.Driver");  // Init H2 DB driver
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @return Database connection
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_CONNECTION, "su", "");
    }
}

I am using H2 database for simplicity since it's a file based one that is easy to create and test on.

So everything works and resources get cleaned up as expected, however I just feel there may be a cleaner way to set the PreparedStatement parameters from inside the try-with-resources block (and I don't want to use nested try/catch blocks as those look 'awkward'). Maybe there already exists a helper class in JDBC that does just this, but I have not been able to find one.

Preferably with a lambda function to initialize the PreparedStatement but it would still require allocating an AutoCloseable object so it can be inside the try-with-resources.

Answer 1

First off, your PreparedStatementSetter class is awkward:

• it is a typed class but the type is not used.
• the constructor is explicitly calling an overridable method, which is a bad practice.

Consider the following interface instead (inspired from the Spring interface of the same name).

public interface PreparedStatementSetter {
    void setValues(PreparedStatement ps) throws SQLException;
}

This interface defines a contract of what a PreparedStatementSetter is supposed to do: set values of a PreparedStatement, nothing more.

Then, it would be better to do the creation and initialization of the PreparedStatement inside a single method. Consider this addition inside your DatabaseManager class:

public static PreparedStatement prepareStatement(Connection connection, String sql, PreparedStatementSetter setter) throws SQLException {
    PreparedStatement ps = connection.prepareStatement(sql);
    setter.setValues(ps);
    return ps;
}

With this static method, you can then write:

try (
    Connection connection = DatabaseManager.getConnection();
    PreparedStatement stmt = DatabaseManager.prepareStatement(connection, SQL, ps -> ps.setString(1, "foo"));
    ResultSet rs = stmt.executeQuery()
) {
    // rest of code
}

Notice how the PreparedStatementSetter was written here with a lambda expression. That's one of the advantage of using an interface instead of an abstract class: it actually is a functional interface in this case (because there is a single abstract method) and so can be written as a lambda.

Answer 2

Extending from @Tunaki's answer, it's also possible to factor-in the try-with-resources and rs.executeQuery() such that the DatabaseManager handles all of this for you and only asks for the SQL, a PreparedStatementSetter and a ResultSet handler.

This would avoid repeating this everywhere you make a query. Actual API will depend on your usage however – e.g. will you make several queries with the same connection?

Supposing you will, I propose the following:

public class DatabaseManager implements AutoCloseable {

    /* Use local file for database */
    private static final String JDBC_CONNECTION = "jdbc:h2:file:./db/sandbox_h2.db;MODE=PostgreSQL";

    static {
        try {
            Class.forName("org.h2.Driver");  // Init H2 DB driver
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private final Connection connection;

    private DatabaseManager() throws SQLException {
        this.connection = getConnection();
    }

    @Override
    public void close() throws SQLException {
        connection.close();
    }

    public interface PreparedStatementSetter {
        void setValues(PreparedStatement ps) throws SQLException;
    }

    public interface Work {
        void doWork(DatabaseManager manager) throws SQLException;
    }

    public interface ResultSetHandler {
        void process(ResultSet resultSet) throws SQLException;
    }

    /**
     * @return Database connection
     * @throws SQLException
     */
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_CONNECTION, "su", "");
    }

    private PreparedStatement prepareStatement(String sql, PreparedStatementSetter setter) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(sql);
        setter.setValues(ps);
        return ps;
    }

    public static void executeWork(Work work) throws SQLException {
        try (DatabaseManager dm = new DatabaseManager()) {
            work.doWork(dm);
        }
    }

    public void executeQuery(String sql, PreparedStatementSetter setter, ResultSetHandler handler) throws SQLException {
        try (PreparedStatement ps = prepareStatement(sql, setter);
            ResultSet rs = ps.executeQuery()) {
            handler.process(rs);
        }
    }
}

It wraps the connection as an instance field of DatabaseManager, which will handle the life-cycle of the connection, thanks to its implementation of AutoCloseable.

It also defines 2 new functional interfaces (additionally to @Tunaki's PreparedStatementSetter) :

• Work defines some work to do with a DatabaseManager via the executeWork static method
• ResultSetHandler defines how the ResultSet must be handled when executing a query via the new executeQuery instance method.

It can be used as follows:

    final String SQL = "select * from NVPAIR where name=?";
    try {
        DatabaseManager.executeWork(dm -> {
            dm.executeQuery(SQL, ps -> ps.setString(1, "foo"), rs -> {
                while (rs.next()) {
                    System.out.println(rs.getString("name") + "=" + rs.getString("value"));
                }
            });
            // other queries are possible here
        });
    } catch (Exception e) {
        e.printStackTrace();
    }

As you see, you don't have to worry about resource handling any more.

I left SQLException handling outside the api since you might want to let it propagate.

_{This solution was inspired by Design Patterns in the Light of Lambda Expressions by Subramaniam.}