Re: jOOQ 3.0 and Spring

567 views
Skip to first unread message

Sergey Epik

unread,
Jun 12, 2013, 10:57:16 AM6/12/13
to jooq...@googlegroups.com
Hello Martin,

You can use jOOQ without JdbcTemplate and SpringExceptionTranslationExecuteListener.

Just create DSLContextImpl in spring context and use it into your service class.
Below you and find example how to initialize DSLContext instance in spring context.

Unfortunately we found DefaultConfiguration deprecated in jOOQ 3.0.


<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
      init-method="createDataSource" destroy-method="close">
    <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
    [...]
</bean>

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>

<bean id="transactionAwareDataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    <constructor-arg ref="dataSource"/>
</bean>

<bean id="jooqContext" class="org.jooq.impl.DSLContextImpl">
    <constructor-arg name="configuration" ref="config"/>
</bean>

<bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider">
    <constructor-arg ref="transactionAwareDataSource"/>
</bean>

<bean class="org.jooq.impl.DefaultConfiguration" name="config">
    <constructor-arg name="connectionProvider" ref="connectionProvider"/>
    <constructor-arg name="dialect">
        <value type="org.jooq.SQLDialect">ORACLE</value>
    </constructor-arg>
    <constructor-arg name="listenerProviders">
        <list>
            <bean class="org.jooq.impl.DefaultExecuteListenerProvider">
                <constructor-arg name="listener">
                    <bean class="com.example.YourCustomSqlExceptionTransformer"/>
                </constructor-arg>
            </bean>
        </list>
    </constructor-arg>
    <constructor-arg name="settings">
        <bean class="org.jooq.conf.Settings">
            <property name="renderMapping">
                <bean class="org.jooq.conf.RenderMapping">
                    <property name="defaultSchema" value="${db.schema}"/>
                    <property name="schemata">
                        <list>
                            <bean class="org.jooq.conf.MappedSchema">
                                <property name="input" value="YOUR_PROJECT_SCHEMA_NAME"/>
                                <property name="output" value="${db.schema}"/>
                            </bean>
                        </list>
                    </property>
                </bean>
            </property>
        </bean>
    </constructor-arg>
    <constructor-arg name="data">
        <null/>
    </constructor-arg>
</bean>











On Wed, Jun 12, 2013 at 12:34 AM, <martin...@csgi.com> wrote:
So is there somewhere an updated version of the SpringExceptionTranslationExecuteListener and any supporting classes that was shown in the "A nice way of using jOOQ with Spring" blog entry that can be downloaded? I am just starting out experimenting with jOOQ and proper integration with Spring's transaction management is mandatory.

I found a few Gist pages by azell but could not find the complete classes.

The way things stand right now, I think I could only use jOOQ for help in construction SQL String generation that I can then use with Spring's JdbcTemplate. This is not quite optimal; but at least would be a start.

Thanks for what looks like a GREAT libary.

Martin Bosak

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Lukas Eder

unread,
Jun 12, 2013, 11:04:03 AM6/12/13
to jooq...@googlegroups.com, Sergey Epik
Hi Sergey,

Good to know that you're still around! :-)

2013/6/12 Sergey Epik <serge...@gmail.com>

Hello Martin,

You can use jOOQ without JdbcTemplate and SpringExceptionTranslationExecuteListener.

Just create DSLContextImpl in spring context and use it into your service class.
Below you and find example how to initialize DSLContext instance in spring context.

Unfortunately we found DefaultConfiguration deprecated in jOOQ 3.0.

Hmm, are you referring to the right type? It isn't deprecated:

As a matter of fact, it's there to stay... The various recent discussions about the Configuration lifecycle, etc. convinced me to open up some tools to the public for broader reuse. Your below Spring configuration file shows nicely that jOOQ 3.0 succeeded in cleaning up most 1.x and 2.x API problems.

As a matter of fact, I had been pushing the documentation of a clean Spring / jOOQ integration for too long. May I reuse your below contribution for the manual and for integration tests? I think this will be very helpful to others!

Cheers
Lukas

Lukas Eder

unread,
Jun 14, 2013, 5:31:21 AM6/14/13
to jooq...@googlegroups.com
Sergey agreed to me in a PM that I can reuse his example. So I will add this to the manual, finally documenting #1836:

Cheers
Lukas


2013/6/12 <martin...@csgi.com>
Sergey,

THANK YOU!
That is exactly what I was looking for (and for me, it wasn't clear in the docs).
And PLEASE allow Lucas to include it in the docs.  I am sure others would appreciate it too.

Gérald Quintana

unread,
Jan 16, 2014, 4:25:47 PM1/16/14
to jooq...@googlegroups.com
 Hello,

To integrate Spring and jOOQ, I wrote a really simple class inspired by DataSourceConnectionProvider:
 public class SpringConnectionProvider implements ConnectionProvider {
private final DataSource dataSource;
public SpringConnectionProvider(DataSource dataSource) {
this.dataSource = dataSource;
}
public Connection acquire() throws DataAccessException {
return DataSourceUtils.getConnection(dataSource);
}
public void release(Connection connection) throws DataAccessException {
DataSourceUtils.releaseConnection(connection, dataSource);
}
}
It's very similar to DataSourceConnectionProvider but uses DataSourceUtils like JdbcTemplate. It avoids having 2 DataSources (main + transaction aware) in Spring's context. Yet it doesn't handle exception translation.

I seems to handle properly transactions. Did I miss something? Can you see a drawback to this approach?

Gérald

Lukas Eder

unread,
Jan 28, 2014, 10:25:39 AM1/28/14
to jooq...@googlegroups.com, Gérald Quintana
Hi Gérald,

I'm sorry this wasn't answered any earlier. My Spring expertise is not good enough to give you an authoritative answer on this matter. Intuitively, I would say that Sergey Epik's suggestion to use a TransactionAwareDataSourceProxy magically has the same effect as your SpringConnectionProvider.

Have a look at the source code below. I took spring-jdbc-3.2.6.RELEASE for this. Relevant parts are marked in yellow. I then let you assess, what the exact difference between your approach and Sergey's is :-)

private class TransactionAwareInvocationHandler implements InvocationHandler {

    private final DataSource targetDataSource;

    private Connection target;

    private boolean closed = false;

    public TransactionAwareInvocationHandler(DataSource targetDataSource) {
        this.targetDataSource = targetDataSource;
    }

    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        // Invocation on ConnectionProxy interface coming in...

        if (method.getName().equals("equals")) {
            // Only considered as equal when proxies are identical.
            return (proxy == args[0]);
        }
        else if (method.getName().equals("hashCode")) {
            // Use hashCode of Connection proxy.
            return System.identityHashCode(proxy);
        }
        else if (method.getName().equals("toString")) {
            // Allow for differentiating between the proxy and the raw Connection.
            StringBuilder sb = new StringBuilder("Transaction-aware proxy for target Connection ");
            if (this.target != null) {
                sb.append("[").append(this.target.toString()).append("]");
            }
            else {
                sb.append(" from DataSource [").append(this.targetDataSource).append("]");
            }
            return sb.toString();
        }
        else if (method.getName().equals("unwrap")) {
            if (((Class) args[0]).isInstance(proxy)) {
                return proxy;
            }
        }
        else if (method.getName().equals("isWrapperFor")) {
            if (((Class) args[0]).isInstance(proxy)) {
                return true;
            }
        }
        else if (method.getName().equals("close")) {
            // Handle close method: only close if not within a transaction.
            DataSourceUtils.doReleaseConnection(this.target, this.targetDataSource);
            this.closed = true;
            return null;
        }
        else if (method.getName().equals("isClosed")) {
            return this.closed;
        }

        if (this.target == null) {
            if (this.closed) {
                throw new SQLException("Connection handle already closed");
            }
            if (shouldObtainFixedConnection(this.targetDataSource)) {
                this.target = DataSourceUtils.doGetConnection(this.targetDataSource);
            }
        }
        Connection actualTarget = this.target;
        if (actualTarget == null) {
            actualTarget = DataSourceUtils.doGetConnection(this.targetDataSource);
        }

        if (method.getName().equals("getTargetConnection")) {
            // Handle getTargetConnection method: return underlying Connection.
            return actualTarget;
        }

        // Invoke method on target Connection.
        try {
            Object retVal = method.invoke(actualTarget, args);

            // If return value is a Statement, apply transaction timeout.
            // Applies to createStatement, prepareStatement, prepareCall.
            if (retVal instanceof Statement) {
                DataSourceUtils.applyTransactionTimeout((Statement) retVal, this.targetDataSource);
            }

            return retVal;
        }
        catch (InvocationTargetException ex) {
            throw ex.getTargetException();
        }
        finally {
            if (actualTarget != this.target) {
                DataSourceUtils.doReleaseConnection(actualTarget, this.targetDataSource);
            }
        }
    }
}


2014-01-16 Gérald Quintana <gerald....@gmail.com>

--
Reply all
Reply to author
Forward
0 new messages