JdbcTemplate

A nice way of using jOOQ with Spring

This blog post is outdated. For a more up-to-date example of how to integrate jOOQ with Spring, please consider the relevant sections of the jOOQ manual!

A nice way of using jOOQ with Spring was recently discussed on Stack Overflow by Adam Gent:
http://adamgent.com/post/31128631472/getting-jooq-to-work-with-spring-correctly

The essence of it was given here in this gist:

package com.snaphop.jooq;

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

import javax.sql.DataSource;

import org.jooq.ExecuteContext;
import org.jooq.impl.DefaultExecuteListener;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;

/**
 * Example provided by Adam Gent
 */
public class SpringExceptionTranslationExecuteListener
extends DefaultExecuteListener {

    @Override
    public void start(ExecuteContext ctx) {
        DataSource dataSource = ctx.getDataSource();
        Connection c = DataSourceUtils.getConnection(dataSource);
        ctx.setConnection(c);
    }

    @Override
    public void exception(ExecuteContext ctx) {
        SQLException ex = ctx.sqlException();
        Statement stmt = ctx.statement();
        Connection con = ctx.getConnection();
        DataSource dataSource = ctx.getDataSource();
        // This note and code below comes from
        // JdbcTemplate.execute(StatementCallback)
        // Release Connection early, to avoid potential connection pool
        // deadlock in the case when the exception translator hasn't
        // been initialized yet.
        JdbcUtils.closeStatement(stmt);
        stmt = null;
        DataSourceUtils.releaseConnection(con, dataSource);
        con = null;
        ctx.exception(getExceptionTranslator(dataSource)
                        .translate("jOOQ", ctx.sql(), ex));
    }

    /**
     * Return the exception translator for this instance.
     *
Creates a default {@link SQLErrorCodeSQLExceptionTranslator}
     * for the specified DataSource if none set, or a
     * {@link SQLStateSQLExceptionTranslator} in case of no DataSource.
     * @see #getDataSource()
     */
    public synchronized SQLExceptionTranslator
    getExceptionTranslator(DataSource dataSource) {
        // This method probably does not need to be synchronized but in
        // Spring it was because of a mutable field on the JdbcTemplate.
        // Also I have no idea how expensive it is to create a translator
        // as one will get created on every exception.
        final SQLExceptionTranslator exceptionTranslator;
        if (dataSource != null) {
            exceptionTranslator =
                new SQLErrorCodeSQLExceptionTranslator(dataSource);
        }
        else {
            exceptionTranslator = new SQLStateSQLExceptionTranslator();
        }
        return exceptionTranslator;
    }
}

See the relevant Stack Overflow answer for more details:
http://stackoverflow.com/a/12326885/521799

The Ollin Framework

I have recently discovered a simple Java database framework called “Ollin“. It can be found when searching jdbc fluent api on Google. It has a couple of transaction handling and ORM features, the way many other frameworks do this. But one specific functionality I found particularly interesting:

ValuedRowVisitor<Integer> rowCounter =
dbSession.createQuery("select * from app.employee")
         .forEachRow(new ValuedRowVisitor<Integer>() {
            private int counter;

            public Integer getValue() {
              return counter;
            }

            public void visit(ResultSetRow row) throws SQLException {
              counter++;
            }
         });
System.out.println("Count of rows: " + rowCounter.getValue());

In this example, you can pass an anonymous type implementing a RowVisitor base interface to a “closure-style” foreach method. Victor Herrera, the creator of Ollin designed this specifically for a use case he had where large amounts of rows (or records) needed to be processed and memory was getting low. This use case is already covered in jOOQ when using the ResultQuery.fetchLazy() method, and then iterate over the resulting Cursor. But the idea of “injecting” a custom handler seemed appealing to me, so with Victor’s permission, I have implemented a similar construct for jOOQ 1.6.4 with #728.

In jOOQ, the magic word is “fetch”, and as discussed recently, there is now the possibility of fetching things into custom types using ResultQuery.fetchInto(Class<?>). So why not also fetch results into custom “targets” like this:

create.selectFrom(T_BOOK)
      .orderBy(TBook.ID)
      .fetchInto(new RecordTarget<TBookRecord>() {
        @Override
        public void next(TBookRecord record) {
          // Do stuff
        }
      });

I’m thrilled to find good open source ideas on the web. I’m always open to new ideas and this Ollin Framework made my day, today :-)

See the framework here: http://code.google.com/p/ollin/