Designing a generic DB utility class

Question

Time and again I find myself creating a database utility class which has multiple functions which all do almost the same thing but treat the result set slightly differently.

For example, consider a Java class which has many functions which all look like this:

public void doSomeDatabaseOperation() {
    Connection con = DriverManager.getConnection("jdbc:mydriver", "user", "pass");
    try {
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT whatever FROM table"); // query will be different each time
        while (rs.next()) {
            // handle result set - differently each time
        }
    } catch (Exception e) {
        // handle
    } finally {
        con.close();
    }
}

Now imagine a class with 20 of these functions.

As you can see, tons of boilerplate (opening a connection, try-finally block), and the only thing that changes would be the query and the way you handle the result set. This type of code occurs in many languages (considering you're not using an ORM).

How do you manage your DB utility classes so as to reduce code duplication? What does a typical DB utility class look like in your language/framework?

Answer 1

When using .net, the Data Access Application Block is in fairly widespread use to provide support for the following:

The [data access] application block was designed to achieve the following goals:

Encapsulate the logic used to perform the most common data access tasks.

Eliminate common coding errors, such as failing to close connections.

Relieve developers of the need to write duplicated code for common data access tasks.

Reduce the need for custom code.

Incorporate best practices for data access, as described in the .NET Data Access Architecture Guide.

Ensure that, as far as possible, the application block functions work with different types of databases.

Ensure that applications written for one type of database are, in terms of data access, the same as applications written for another type of database.

There are plenty of examples and tutorials of usage too: a google search will find msdn.microsoft, 4guysfromrolla.com, codersource.com and others.

Answer 2

Sounds like you could make use of a Template Method pattern here. That would allow you to define the common steps (and default implementations of them, where applicable) that all subclasses will take to perform the action. Then subclasses need only override the steps which differ: SQL query, DB-field-to-object-field mapping, etc.

Answer 3

The way I have done in one of my project is that I followed what Spring does with JDBC template and came up with a Query framework. Basically create a common class which can take select statement or pl/sql calls and bind parameters. If the query returns resultset, also pass the Rowmapper. This rowmapper object will be called by the framework to convert each row into an object of any kind.

Example -

Query execute = new Query("{any select or pl/sql}",
                          // Inputs and Outputs are for bind variables.
                          new SQL.Inputs(Integer.class, ...),
                          // Outputs is only meaningful for PL/SQL since the
                          // ResultSetMetaData should be used to obtain queried columns.
                          new SQL.Outputs(String.class));

If you want the rowmapper -

Query execute = new Query("{any select or pl/sql}",
                          // Inputs and Outputs are for bind variables.
                          new SQL.Inputs(Integer.class, ...),
                          // Outputs is only meaningful for PL/SQL since the
                          // ResultSetMetaData should be used to obtain queried columns.
                          new SQL.Outputs(String.class), new RowMapper() {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Actor actor = new Actor();
            actor.setFirstName(rs.getString("first_name"));
            actor.setSurname(rs.getString("surname"));
            return actor;
        });

Finally a Row class is the output which will have list of objects if you have passed the RowMapper -

for (Row r : execute.query(conn, id)) {
  // Handle the rows
}

You can go fancy and use Templates so that type safety is guaranteed.