NewInstance

Using plain JDBC to access databases involves in a lot repetitive code. On serveral project I worked on I’ve seen some “frameworks” that often required more code than using jdbc direclty. I’ve always thought that a good framework/library is that one that requires to you to write the less code as possible: only the necessary code.
Example, to execute an sql select statement you should specify

1. a sql statement
2. optionally, the parameters for the query.
   example: “select * from mytable where id = ?”
3. an algorithm to translate resulting records to object(s)

Nothing more.
Instead for insert/update/delete you don’t need to specify the algorithm to read the results, as you want just to know how many records have been affected by the sql statement.
So a convenient interface for a library that access the db would be something like

// for insert/update/delete sql statements
protected int update(String statement, Object[] params)
    throws SQLException;

// for select sql statements
protected Object select(String statement, Object[] params,
    ResultSetTranslator translator) throws SQLException;

You may want to have also some mechanism to handle transaction. I have mine, but several solutions could be implemented; I leave it to your fantasy.

The resultset translator should have an interface method that takes a java.sql.ResultSet and translates it to a business object or a collection of them:

public interface ResultSetTranslator {
    public Object translate(ResultSet rs)
        throws SQLException;
}

So, a query that extracts cats by name from a db will look like:

public List findCatByName(String name) throws SQLException {
    String sql = "select * from CATS " +
                 "where NAME like ? order by NAME";
    Object[] params = new Object[] { "%" + name + "%" };
    ResultSetTranslator catsTrl = new ResultSetTranslator() {
        public Object translate(ResultSet rs)
            throws SQLException {
            List results = new ArrayList();
            while (rs.next()) {
                Cat cat = new Cat();
                cat.setName(rs.getString("NAME"));
                cat.setColor(rs.getString("COLOR"));
                cat.setBirthDate(rs.getDate("BIRTH_DATE"));
                results.add(cat);
            }
            return results;
        }
    };
    return (List) select(sql, params, catsTrl);
}

You see that the most of the code is written to translate the ResultSet to the desired object(s), in this case a List. But a nice thing is that translator objects can be reused quite frequently. Could be a good idea to declare them as instance variable in data access classes. I bet that someone is already thinking about writing a dynamic translator from xml configuration file (lol), in this case take a look at hibernate. Also the logic to iterate over the ResultSet can be abstracted in an AbstractLoopingTranslator or creating a Loop decorator.
But let’s continue seeing how an update could look like:

public void updateCat(Cat cat) throws SQLException {
    String sql = "update CATS " +
            "set COLOR = ?, BIRTH_DATE = ? where NAME = ?";
    int updated = update(sql, new Object[] { cat.getColor(),
                                             cat.getBirthDate(),
                                             cat.getName() });
}

All the code that handles jdbc classes disappeared: in the previous example you don’s see code for opening and closing Connections, Resultsets, and PreparedStatements; no code that sets parameters into the query, etc. At least the 50% of the code is spared, I believe, and the code written (resultset translator) could be probably be reused.

Ok, seems nice. Where’s the code that implements select() and update methods()? I use a base class for data access classes. Here you find an outline version (you can download the complete source here with explaining javadocs):

protected static interface ResultSetTranslator {
    public Object translate(ResultSet rs) throws SQLException;
}   

protected Object select(String statement, Object[] params,
        ResultSetTranslator translator) throws SQLException {
    if (translator == null) {
        translator = new ResultSetTranslator() {
            public Object translate(ResultSet rs)
                    throws SQLException {
                return rs.next() ? Boolean.TRUE : Boolean.FALSE;
            }
        };
    }
    return execute(statement, params, translator);
}

protected int update(String statement, Object[] params)
        throws SQLException {
    Integer count = (Integer)execute(statement, params, null);
    return count.intValue();
}    

private Object execute(
        String statement, Object[] params,
        ResultSetTranslator translator) throws SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        log(statement, params);
        conn = open();
        pstmt = conn.prepareStatement(statement);
        if (params != null)
            for (int i = 0; i < params.length; i++)
                if(params[i] == null) // fix oracle bug with nulls
                    pstmt.setNull(i+1, Types.VARCHAR);
                else
                    pstmt.setObject(i+1, params[i]);
        if (translator != null) {
            rs = pstmt.executeQuery();
            return translator.translate(rs);
        } else {
            int updatedCount = pstmt.executeUpdate();
            return new Integer(updatedCount);
        }
    } finally {
        close(conn, pstmt, rs);
    }
}

// logs the query with exploded params
private void log(String statement, Object[] params) {
    if (logger.isDebugEnabled()) {
        if (params != null && params.length > 0) {
            int estimatedLength = statement.length() + (statement.length() >> 2);
            StringBuffer buffer = new StringBuffer(estimatedLength);
            int currentParam = 0;
            for (int i = 0; i < statement.length(); i++) {
                char currentChar = statement.charAt(i);
                if (currentChar != '?'||
                        currentParam >= params.length)
                    buffer.append(currentChar);
                else {
                    Object param = params[currentParam++];
                    if (param == null )
                        buffer.append("null");
                    else if (param instanceof Number)
                        buffer.append(param);
                    else
                        buffer.append("'" + param + "'");
                }
            }
            statement = buffer.toString();
        }
        logger.debug("executing sql statement: " + statement);
    }
}

In the source, you find that select() and update() are front interface for the execute() that actually does the job.
I leaved the log method to show that having a central point for query execution could have also some benefit; logging in this case. There are also other utility methods I made using this approach, one of those is for supporting paginated queries. In this case I needed a way to decorate my queries to support server side pagination for my application (offset, fetch size and totalcount)

protected List fetchList(
        String statement, Object[] params,
        ResultSetTranslator translator,
        Integer offset, Integer fetchSize) throws SQLException {
    boolean paged = offset != null && fetchSize != null;
    if (! paged) return (List) execute(statement, params, translator);
    int totalSize =  count(statement, params);
    String newStatement =
        "select * from (" +
            "select rownum x_rownum, x_query.* " +
            "from (" + statement + ") x_query) " +
        "where " +
            "x_rownum between ? and ?";
    Object[] newParams = new Object[params.length + 2];
    System.arraycopy(params, 0, newParams, 0, params.length);
    Integer newOffset = new Integer(offset.intValue() + 1); // Oracle rownum is 1 based
    newParams[params.length] = newOffset;
    newParams[params.length + 1] = new Integer(offset.intValue() + fetchSize.intValue());
    List results = (List) execute(newStatement, newParams, translator);
    return PagedList.Instance.create(results, totalSize);
}

protected int count(String statement, Object[] params) throws SQLException {
    statement = "select count(*) from (" + statement + ")";
    BigDecimal count = (BigDecimal) select(statement, params, new ResultSetTranslator() {
        public Object translate(ResultSet rs) throws SQLException {
            rs.next();
            return rs.getBigDecimal(1);
        }
    });
    return count.intValue();
}

It works wrapping and decorating sql statement.