Document using jOOQ with Spring for transaction support #1836

Closed
lukaseder opened this Issue Sep 19, 2012 · 13 comments

3 participants

@azell

I don't think that most of the glue code in the original gist is required:

1) Closing the statement and connection in the exception handler is redundant, as I believe that this will occur automatically at the end of execution by jOOQ. Also releasing the connection in the exception handler caused a NullPointerException when used with a DataSource and c3p0 ("You can't operate on a closed Connection...").
2) The synchronized modifier is not necessary, as the code is not protecting a shared resource as in the original Spring code. The exception types appear to be cheap to construct, as they rely on a singleton for most of their mapping logic.

See https://gist.github.com/3903290 for minimal code sample.

@lukaseder
Java Object Oriented Querying member

@azell : Thank you for your contribution. I will have a closer look at it shortly

@azell

After digging into the Spring JDBC exception code a little further, it appears that SQLErrorCodesFactory will ask the DataSource for a Connection in order to initialize its error codes. In theory, this could cause the connection pool to block if the pool is at its limit. Releasing the connection which caused the exception could prevent this problem.

Releasing the connection manually ends up closing the connection due to Connection.equals not being implemented by the DataSource provider (in this case c3p0). Will debug DataSourceUtils.connectionEquals a bit more.

Follow-up: what c3p0 wants passed to DataSourceUtils.releaseConnection() is the underlying delegate (e.g. ctx.getConnection().getDelegate()). However because the implementation class is package-private, the only way to do that from outside jOOQ is through reflection.

But once the delegate is passed through, then the underlying connection is not closed.

@lukaseder lukaseder was assigned Oct 25, 2012
@ben-manes

fyi, #2373 provides an alternative integration example.

@azell

The original gist breaks in 3.0.0 due to the removal of ExecuteContext#getDataSource. Is there a recommended work around?

@ben-manes

Perhaps implement a custom ConnectionProvider that you can downcast to fetch the underlying DataSource?

@lukaseder
Java Object Oriented Querying member

Hmm, yes, I guess the new DataSourceConnectionProvider could expose its underlying DataSource. I'll fix that in the next release (#2440). In the mean time, you could proceed as @ben-manes indicated, extending the DataSourceConnectionProvider and adding a getter to it.

@azell

I have worked around one issue by using ctx.configuration().dialect().name() to initialize SQLErrorCodeSQLExceptionTranslator instead of passing it a DataSource.

However, ExecuteContext.setConnection has disappeared. Is there a simple way to set the current connection to DataSourceUtils#getConnection?

Guessing I will need to create a ConnectionProvider along with the existing ExecuteListener. http://stackoverflow.com/a/14243136 appears simpler, but does not map exceptions to Spring exceptions.

Latest attempt at https://gist.github.com/azell/5655888.

@azell

If the gist above looks correct, perhaps we can add it to the jOOQ 3.0 manual.

@lukaseder
Java Object Oriented Querying member

I have added some examples to the manual's "Using jOOQ with Spring and DBCP" tutorial section. It includes a Spring configuration similar to Sergey's:
https://groups.google.com/forum/?fromgroups#!topic/jooq-user/XTDG9VVP-3I

This configuration operates on TransactionAwareDataSourceProxy, which seems to render most implementations involving ExecuteListener unnecessary (unless I'm missing something?). I think that Spring JDBC has already solved transaction management quite well, so I'd like to keep examples in the manual simple. The examples offered using ExecuteListener might seem a bit scary to first-time users :-)

Any further feedback is very welcome, of course!

@lukaseder lukaseder closed this Jun 14, 2013
@ben-manes

How does the current example handle tearing when outside of a transactional context? By using a connection pool and making complex operations, jooq returning and checking out the connection will result in race condition bugs.

Because I couldn't ensure that all developers used transactions and didn't want to use one per request, I had to write an ExecutionListener that is transaction aware. This built on a similar configuration as the accepted answer, after I verified the problem in a test. Alternatively a listener could have failed fast by asserting a transactional context. Either way, a naive developer won't accidentally check in code that breaks under a real workload.

@azell

Couple of notes:

The TransactionAwareDataSourceProxy Javadoc is ambivalent in promoting usage of the class:

This proxy allows data access code to work with the plain JDBC API and still participate in Spring-managed transactions, similar to JDBC code in a J2EE/JTA environment. However, if possible, use Spring's DataSourceUtils, JdbcTemplate or JDBC operation objects to get transaction participation even without a proxy for the target DataSource, avoiding the need to define such a proxy in the first place.

That may be due to the heavy use of reflection in the implementation, which may or may not cause a performance degradation.

Also, it is not clear to me that using TransactionAwareDataSourceProxy helps with any unchecked exceptions thrown by jOOQ:

Exception handling: While SQL uses the checked java.sql.SQLException, jOOQ wraps all exceptions in an unchecked org.jooq.exception.DataAccessException

If this is true, then integration will still require a exception mapping from jOOQ DataAccessException to SQLException to Spring DataAccessException.

@lukaseder
Java Object Oriented Querying member

By using a connection pool and making complex operations, jooq returning and checking out the connection will result in race condition bugs.

@ben-manes: True

Alternatively a listener could have failed fast by asserting a transactional context

@ben-manes: That would be a useful addition to Sergey's solution.

The TransactionAwareDataSourceProxy Javadoc is ambivalent in promoting usage of the class [...]

@azell: That slipped by me. Funny, why would such a general disclaimer be there? I understand the reflection argument, but still, given that this proxy was introduced mainly to to be "Similar to a transactional JNDI DataSource as provided by a J2EE server.". Being used to J2EE, I find the idea compelling of being able to access a contextual data source that "knows" I'm in a transaction by accessing some context.

If this is true, then integration will still require a exception mapping from jOOQ DataAccessException to SQLException to Spring DataAccessException.

@azell: I don't know Spring well enough, but I would imagine that you could still correctly handle failures (and thus, rollbacks) in your transaction management code, even if you're confronted with a jOOQ DataAccessException?

Anyway, you guys have more experience with Spring. I think I will create another example in the manual, more along the lines of what you have been doing.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment