JOOQ Spring Transaction support.

SpringExceptionTranslationExecuteListener.java

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;


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 
        // org.springframework.jdbc.core.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;
    }

    
}

@agentgt: I'm looking at your implementation here in the context of jOOQ/jOOQ#3787.

I'm not so sure if I understand this correctly given that some of the API doesn't even exist (e.g. ctx.getDataSource() or ctx.setConnection()).

On the other hand, I don't think that this implementation of JdbcTemplate is sensible. It smells extremely fishy in my opinion and violates a couple of basic assumptions about JDBC contracts. I suspect that this just displays a flaw in Spring in general.

For instance: What if you want to fetch warnings from the statement? What if you want to execute the statement again?