Trimming JDBC

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 ( {
                Cat cat = new 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.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 ? 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); 
                    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) 
                else {
                    Object param = params[currentParam++];
                    if (param == null )  
                    else if (param instanceof Number)
                        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 {
            return rs.getBigDecimal(1);
    return count.intValue();

It works wrapping and decorating sql statement.

8 Responses to “Trimming JDBC”  

  1. 1 john smith

    What does your new JDBC framework provide that the Spring JDBC DAO framework doesn’t? We use it very successfully. …and it handles transactions transparently using AOP.

  2. 2 Brian McCallister

    Okay, it is a project plug, but… You might want to look at jDBI. It does the result set mapping, has decent tx conveniences, and has upcoming support for some serious convenience magic :-)


  3. 3 Luigi

    > What does your new JDBC framework provide
    > that the Spring JDBC DAO framework doesn’t?

    I’ll give a look at Spring JDBC DAO, thankyou for your hint

    This codes are just some utility methods I wrote for current project: I would have choosen hibernate if I could, but the buyer asked for plain jdbc access for this app and I wrote this.

    I’m working in a context where recent technologies are refused for several (ridiculous) reasons. This is the music here, let’s dance.

  4. 4 Luigi

    Thanks Brian, jDBI sounds good!
    Unluckily in current project buyer mandates that we must use jdk 1.3 :-(
    (definitely: I’m not working in a vanguard company)

    How many higher level APIs around JDBC! How time wasting JDBC API is! ;-)

  5. 5 Carfield Yim

    Have a feeling that spring jdbctemplate is what you looking for, may be you can take a look?

  6. 6 Jari

    Have a look at DBUtils from Apache Commons.


  7. 7 Hammer

    Take a look at O/R Broker.

  8. 8 niewaznejak

    Fantastic! Thank you run this page!

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


October 2006
« Sep   Nov »

Follow me

twitter flickr LinkedIn feed

Subscribe by email

Enter your email address:



Tag Cloud