Re: DAOs, Datasources and transaction management
658 views
Skip to first unread message
Lukas Eder
Jul 25, 2012, 3:24:03 AM7/25/12
to jooq...@googlegroups.com
Hi Stephane,
> First, to tell the truth, I have not yet used jooq. However I am wondering
> if it could replace mybatis for my next project as objectized SQL seems an
> excellent way to prevent regression (oh yes I know, I should better unit
> test my DAOs but it is often hard with deadlines approaching).
Yes, compile-time SQL syntax and schema safety is already a big step
forward, short of more sophisticated regression tests.
> From what I have learnt so far, the Factory object may be a good candidate
> for injection into DAO layers. The documentation states that new Connection
> objects are requested and closed on a per call-basis which is fine as long
> as the provided DataSource is backed by some pooling mechanism like BoneCp
> or Dbcp.
Yes, if you pass a DataSource to the Factory constructor. The intent
of this was indeed to support those connection pools that are capable
of distributed transactions along the lines of a
javax.transaction.UserTransaction. In those contexts,
Connection.close() has the semantics of freeing the connection,
locally, leaving the final decision of actually
releasing/closing/returning the connection up to the transaction
manager.
> However, what if you need to share the same connection accross
> multiple queries in order to make them part of the same transaction? Is
> there any way to prevent the connection from being released if autocommit is
> turned off?
You have several options:
1. Use a distributed UserTransaction, in the context of which a
DataSource will provide a pooled connection. Calling
Connection.close() will only truly release the connection once the
transaction has been committed or rolled back. This is typically the
case in a J2EE container like Weblogic
2. Provide jOOQ with a JDBC Connection instead, handling the
connection lifecycle yourself. jOOQ will not close those connections
3. Implement your own DataSource or use a DataSource that behaves like
the one mentioned in 1.
4. Don't "attach" your Factories to JDBC Connections or DataSources,
but provide jOOQ with a Connection through an ExecuteListener. See the
manual and Javadoc for details:
- http://www.jooq.org/manual/ADVANCED/ExecuteListener/
- http://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html
Option 2. probably being the simplest
Note, the "best" integration of DataSources in jOOQ is still an open
point. If you see room for improvement, feel free to participate in
the discussions on this list. There had been several opinions on the
topic. My current feeling is that jOOQ will keep the API as it is (for
convenience and JDBC-compliance reasons), but publish 3-4 DataSource
implementations that will fulfill most end-user needs (for additional
convenience). This will be somewhat redundant to the data sources
already provided in APIs like Spring Data.
Cheers
Lukas
> First, to tell the truth, I have not yet used jooq. However I am wondering
> if it could replace mybatis for my next project as objectized SQL seems an
> excellent way to prevent regression (oh yes I know, I should better unit
> test my DAOs but it is often hard with deadlines approaching).
Yes, compile-time SQL syntax and schema safety is already a big step
forward, short of more sophisticated regression tests.
> From what I have learnt so far, the Factory object may be a good candidate
> for injection into DAO layers. The documentation states that new Connection
> objects are requested and closed on a per call-basis which is fine as long
> as the provided DataSource is backed by some pooling mechanism like BoneCp
> or Dbcp.
Yes, if you pass a DataSource to the Factory constructor. The intent
of this was indeed to support those connection pools that are capable
of distributed transactions along the lines of a
javax.transaction.UserTransaction. In those contexts,
Connection.close() has the semantics of freeing the connection,
locally, leaving the final decision of actually
releasing/closing/returning the connection up to the transaction
manager.
> However, what if you need to share the same connection accross
> multiple queries in order to make them part of the same transaction? Is
> there any way to prevent the connection from being released if autocommit is
> turned off?
You have several options:
1. Use a distributed UserTransaction, in the context of which a
DataSource will provide a pooled connection. Calling
Connection.close() will only truly release the connection once the
transaction has been committed or rolled back. This is typically the
case in a J2EE container like Weblogic
2. Provide jOOQ with a JDBC Connection instead, handling the
connection lifecycle yourself. jOOQ will not close those connections
3. Implement your own DataSource or use a DataSource that behaves like
the one mentioned in 1.
4. Don't "attach" your Factories to JDBC Connections or DataSources,
but provide jOOQ with a Connection through an ExecuteListener. See the
manual and Javadoc for details:
- http://www.jooq.org/manual/ADVANCED/ExecuteListener/
- http://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html
Option 2. probably being the simplest
Note, the "best" integration of DataSources in jOOQ is still an open
point. If you see room for improvement, feel free to participate in
the discussions on this list. There had been several opinions on the
topic. My current feeling is that jOOQ will keep the API as it is (for
convenience and JDBC-compliance reasons), but publish 3-4 DataSource
implementations that will fulfill most end-user needs (for additional
convenience). This will be somewhat redundant to the data sources
already provided in APIs like Spring Data.
Cheers
Lukas
Lukas Eder
Jul 27, 2012, 3:14:46 AM7/27/12
to jooq...@googlegroups.com
Hi Stephen,
> Dealing directly with Connection sure allows fine grained control, however
> it requires much more code as you will be responsible for opening, closing,
> commiting, rollbacking and rethrowing checked SqlExceptions and a mix of
> both approaches idoes not seem very convenient. I think there could be room
> for a nicer solution for people who are just looking for a nice way to query
> their database without relying on big frameworks.
With jOOQ 2.4.0, delegate methods to transaction-related methods in a
JDBC Connection have been introduced, in order to wrap SQLException in
jOOQ's unchecked DataAccessException. An example:
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#commit%28%29
> A SqlSessionManager, just like a jooq Datasource based Factory is what one
> uses to actually query the database.
Good to know!
> However, when transaction control is needed, you can start a "managed
> session" by writing such code [...]
Transaction handling really seems very simple at first, but it is not.
Adding actual transaction handling to jOOQ has been something I was
always reluctant to do so far. The fact that DataSource support has
been added led to a lot of questions concerning a Connection's
lifecycle. With transactions, it gets even more complex. For instance,
how you would handle JNDI-registered
javax.transaction.UserTransactions with a simple session manager?
Anyway, myBatis has come a long way, and they have probably thought
about the subject thoroughly, so I'll re-consider supporting
transactions in jOOQ:
https://github.com/jOOQ/jOOQ/issues/1629
> - executing isolated statement does not require more than one line of code,
> all the connection management is taken care of internally.
Agreed
> - when needed, managing transactions only require minimal code, no
> try-catch-rollback-rethrow polluting every method
Yes, those exceptions really shouldn't be checked
> [...] What do you think?
I guess that simple transaction handling would improve using jOOQ
along with a DataSource, and answer some of those questions about the
Connection lifecycle. I might wait with the implementation until jOOQ
3.0, as I'm planning to better separate concerns in a lot of fields.
One issue that has been on my mind for a while is a cleaner separation
of query construction from query execution:
https://github.com/jOOQ/jOOQ/issues/1372
I'm not sure yet, about how this will be implemented. Adding
transaction support might align well with the many changes in jOOQ
3.0. But this has to be done carefully in order not to confuse users
who do not want to rely on such simplifications provided by jOOQ
Thanks for your ideas
Cheers
Lukas
> Dealing directly with Connection sure allows fine grained control, however
> it requires much more code as you will be responsible for opening, closing,
> commiting, rollbacking and rethrowing checked SqlExceptions and a mix of
> both approaches idoes not seem very convenient. I think there could be room
> for a nicer solution for people who are just looking for a nice way to query
> their database without relying on big frameworks.
With jOOQ 2.4.0, delegate methods to transaction-related methods in a
JDBC Connection have been introduced, in order to wrap SQLException in
jOOQ's unchecked DataAccessException. An example:
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#commit%28%29
> A SqlSessionManager, just like a jooq Datasource based Factory is what one
> uses to actually query the database.
Good to know!
> However, when transaction control is needed, you can start a "managed
> session" by writing such code [...]
Transaction handling really seems very simple at first, but it is not.
Adding actual transaction handling to jOOQ has been something I was
always reluctant to do so far. The fact that DataSource support has
been added led to a lot of questions concerning a Connection's
lifecycle. With transactions, it gets even more complex. For instance,
how you would handle JNDI-registered
javax.transaction.UserTransactions with a simple session manager?
Anyway, myBatis has come a long way, and they have probably thought
about the subject thoroughly, so I'll re-consider supporting
transactions in jOOQ:
https://github.com/jOOQ/jOOQ/issues/1629
> - executing isolated statement does not require more than one line of code,
> all the connection management is taken care of internally.
Agreed
> - when needed, managing transactions only require minimal code, no
> try-catch-rollback-rethrow polluting every method
Yes, those exceptions really shouldn't be checked
> [...] What do you think?
I guess that simple transaction handling would improve using jOOQ
along with a DataSource, and answer some of those questions about the
Connection lifecycle. I might wait with the implementation until jOOQ
3.0, as I'm planning to better separate concerns in a lot of fields.
One issue that has been on my mind for a while is a cleaner separation
of query construction from query execution:
https://github.com/jOOQ/jOOQ/issues/1372
I'm not sure yet, about how this will be implemented. Adding
transaction support might align well with the many changes in jOOQ
3.0. But this has to be done carefully in order not to confuse users
who do not want to rely on such simplifications provided by jOOQ
Thanks for your ideas
Cheers
Lukas
Lukas Eder
Jul 27, 2012, 3:26:55 AM7/27/12
to jooq...@googlegroups.com
Hi Stephen,
> Out of curiousity, I wonder why you did not opt for different Factory
> classes, letting an abstract base class handle the common code. You could
> have had a default DS implementation fetching and releasing connections on a
> per call basis while still leaving room for other strategies. As it is now,
> giving to a child object the role of closing its parent, thus overriding
> JDBC behavios makes the code a bit hard to follow, moreover, there are some
> methods that don't make much sense in the datasource context, about
> everything that has to do with transactions, perhaps some hints that these 2
> variants could have been 2 different classes. [...]
The reason for wrapping a DataSource in a Connection implementation is
simple. All of jOOQ's internals prior to jOOQ 2.4 depend on the
availability of a Connection. Introducing DataSource support in this
way was the path of least resistance in a minor release.
While new users would encourage change, existing users rely on API
stability. I may have underestimated the impact of adding DataSource
support, though. The current solution works well, but may not be
intuitive to all users.
> How about letting the Factory be an interface [...]
That interface is currently called FactoryOperations.
> [...] and letting DataSource and
> Connection based classes have their own implementation?
Yes that sounds like a good idea along the lines of separating query
construction and execution (as mentioned in my previous mail):
https://github.com/jOOQ/jOOQ/issues/1372
Adding such big API change in a minor release is difficult if not
impossible. That doesn't mean I shouldn't think about this for jOOQ
3.0, the next major release. Many users have struggled with this, and
provided solutions that suited their needs. Some of those solutions
have made it into the core and may now feel a bit off, as they do not
stand in the context of a global "datasource, connection, transaction,
session, etc." design.
There's a lot of work ahead, for jOOQ 3.0!
Cheers
Lukas
> Out of curiousity, I wonder why you did not opt for different Factory
> classes, letting an abstract base class handle the common code. You could
> have had a default DS implementation fetching and releasing connections on a
> per call basis while still leaving room for other strategies. As it is now,
> giving to a child object the role of closing its parent, thus overriding
> JDBC behavios makes the code a bit hard to follow, moreover, there are some
> methods that don't make much sense in the datasource context, about
> everything that has to do with transactions, perhaps some hints that these 2
> variants could have been 2 different classes. [...]
The reason for wrapping a DataSource in a Connection implementation is
simple. All of jOOQ's internals prior to jOOQ 2.4 depend on the
availability of a Connection. Introducing DataSource support in this
way was the path of least resistance in a minor release.
While new users would encourage change, existing users rely on API
stability. I may have underestimated the impact of adding DataSource
support, though. The current solution works well, but may not be
intuitive to all users.
> How about letting the Factory be an interface [...]
That interface is currently called FactoryOperations.
> [...] and letting DataSource and
> Connection based classes have their own implementation?
Yes that sounds like a good idea along the lines of separating query
construction and execution (as mentioned in my previous mail):
https://github.com/jOOQ/jOOQ/issues/1372
Adding such big API change in a minor release is difficult if not
impossible. That doesn't mean I shouldn't think about this for jOOQ
3.0, the next major release. Many users have struggled with this, and
provided solutions that suited their needs. Some of those solutions
have made it into the core and may now feel a bit off, as they do not
stand in the context of a global "datasource, connection, transaction,
session, etc." design.
There's a lot of work ahead, for jOOQ 3.0!
Cheers
Lukas
Lukas Eder
Jul 27, 2012, 5:19:24 AM7/27/12
to jooq...@googlegroups.com
Hi Stephane,
> What are your plans for v3?
Many
> Are you going to let the Factory stuff as is and opt for a new set of
> session articulated classes? After all, the current code is perfectly
> suitable for people just willing to feed JDBC Connections to the jooq API.
jOOQ 3.0 will be backwards-compatible in large parts. Even if there
will be fundamental changes in handling sessions/connections/data
sources/transactions, this should not impact the overall API. The
current state of favourable ideas includes these:
1. Better integrate Settings and Configuration. The two have similar
semantics. Their separation is confusing
2. Split Factory into its current query construction and and query
execution parts. Query construction should be completely stateless and
static (an open point is the current convenience of actually executing
an "attached" Query, rather than passing it to an executor. But I'll
find a solution to that)
3. Separate Configuration and Factory. Factory should be stateless
(this could be redundant with 2)
4. Implement session/connection/transaction lifecycle strategies that
can be fully customised. With step 2. implemented, this will be easier
to achieve
Other, minor improvements can be seen here:
https://github.com/jOOQ/jOOQ/issues?milestone=17&state=open
> As an API break you may think about "murdering" the Datasource support the
> way it is, after all the connection-per-statement pattern is pretty limited [...]
Yes. As many other things, this was designed to suit one set of users.
Those with J2EE distributed transactions, and spring-managed data
sources, that behave the same way.
> and somewhat inconsistent with FactoryOperation interface which has JDBC
> transaction support,
Yes. That was added to suit another set of users who want to operate
on their standalone connections, handling transactions directly on
those connections. They don't want to deal with SQLExceptions
> moreover it seems to complicate the code by requiring
> many wrappers, creating at least as many problems than it solves).
For incremental API building, I guess the current solution is as good
as it could get. Short-term improvement suggestions are welcome, but I
think I'd better focus on 3.0, in that field.
> Then
> maybe you could introduce datasource support by other means, for example
> something as simple as a class taking a DataSource in its constructor and
> responsible for providing Connection-based Factory objects on demand. It
> would just require the factory to take ownership of the Connection and have
> some close() / closeQuietly() methods.
That would be resolved with those "query executors" mentioned in step
2. When query building and query execution are cleanly separated, it
will be easy to manage several strategies for the connection
lifecycle.
One idea that I will keep in mind is that by Aaron Digulla, who
recently claimed that a ConnectionProvider would solve many problems.
In essence, those "query executors" would in fact be such connection
providers to jOOQ's internals.
> What are your plans for v3?
Many
> Are you going to let the Factory stuff as is and opt for a new set of
> session articulated classes? After all, the current code is perfectly
> suitable for people just willing to feed JDBC Connections to the jooq API.
jOOQ 3.0 will be backwards-compatible in large parts. Even if there
will be fundamental changes in handling sessions/connections/data
sources/transactions, this should not impact the overall API. The
current state of favourable ideas includes these:
1. Better integrate Settings and Configuration. The two have similar
semantics. Their separation is confusing
2. Split Factory into its current query construction and and query
execution parts. Query construction should be completely stateless and
static (an open point is the current convenience of actually executing
an "attached" Query, rather than passing it to an executor. But I'll
find a solution to that)
3. Separate Configuration and Factory. Factory should be stateless
(this could be redundant with 2)
4. Implement session/connection/transaction lifecycle strategies that
can be fully customised. With step 2. implemented, this will be easier
to achieve
Other, minor improvements can be seen here:
https://github.com/jOOQ/jOOQ/issues?milestone=17&state=open
> As an API break you may think about "murdering" the Datasource support the
> way it is, after all the connection-per-statement pattern is pretty limited [...]
Yes. As many other things, this was designed to suit one set of users.
Those with J2EE distributed transactions, and spring-managed data
sources, that behave the same way.
> and somewhat inconsistent with FactoryOperation interface which has JDBC
> transaction support,
Yes. That was added to suit another set of users who want to operate
on their standalone connections, handling transactions directly on
those connections. They don't want to deal with SQLExceptions
> moreover it seems to complicate the code by requiring
> many wrappers, creating at least as many problems than it solves).
For incremental API building, I guess the current solution is as good
as it could get. Short-term improvement suggestions are welcome, but I
think I'd better focus on 3.0, in that field.
> Then
> maybe you could introduce datasource support by other means, for example
> something as simple as a class taking a DataSource in its constructor and
> responsible for providing Connection-based Factory objects on demand. It
> would just require the factory to take ownership of the Connection and have
> some close() / closeQuietly() methods.
That would be resolved with those "query executors" mentioned in step
2. When query building and query execution are cleanly separated, it
will be easy to manage several strategies for the connection
lifecycle.
One idea that I will keep in mind is that by Aaron Digulla, who
recently claimed that a ConnectionProvider would solve many problems.
In essence, those "query executors" would in fact be such connection
providers to jOOQ's internals.
Lukas Eder
Jul 27, 2012, 7:43:56 AM7/27/12
to jooq...@googlegroups.com
>> Yes. As many other things, this was designed to suit one set of users.
>> Those with J2EE distributed transactions, and spring-managed data
>> sources, that behave the same way.
>
> I guess those tools have Connection proxies or implementations that let a
>> Those with J2EE distributed transactions, and spring-managed data
>> sources, that behave the same way.
>
> transaction span over many close() calls
Precisely
> my ideas were more focused on the
> typical DataSource you would obtain from JDBC pools.
Yes, that is not really supported, right now.
> Yes of course, all the suggestions in my post were about a future v3.0 or
> perhaps v4 release.
They will be in v3.0
> I see many ways to use a provider to fetch connections from, but they all
> bring the question of who will be responsible for disposing them. I guess I
> just need to wait until this "executor" API becomes reality, so I'll have
> have an overall view of the new design. I think I would be able to implement
> the session-on-demand pattern discussed above on top of the current API but
> I'd rather wait until some of your v3 ideas have come to life.
Beware that I won't start implementing jOOQ 3.0 before the end of 2012, though
Reply all
Reply to author
Forward
0 new messages