SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 226 posts at DZone. You can read more from them at their website. View Full User Profile

A Nice Way of Using jOOQ with Spring

09.30.2012
| 3389 views |
  • submit to reddit
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<T>)
        // 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.
     * <p>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

Published at DZone with permission of Lukas Eder, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)