connections and data sources

[Garret Wilson]

So our application is almost in production, and we realized that the proof-of-concept RESTful interface I created (using a jOOQ configuration wrapping a JDBC connection) had never been changed, so all requests were using the same connection! I'm now reviewing another teammate's changes and I wanted to review the jOOQ documentation on this, as this is a little beyond my expertise. So I looked for the documentation that used to exist, but the version 3.4 manual doesn't seem to have anything to say on the subject!

Where can I read up on how to create a single jOOQ configuration that I pass around that uses a data source or some other connection factory (that I can e.g. put a connection pool behind)? Where's the new documentation for that? Thanks.

[Lukas Eder]

Hello,

2014-11-13 4:02 GMT+01:00 Garret Wilson <gar...@globalmentor.com>:

So our application is almost in production, and we realized that the proof-of-concept RESTful interface I created (using a jOOQ configuration wrapping a JDBC connection) had never been changed, so all requests were using the same connection! I'm now reviewing another teammate's changes and I wanted to review the jOOQ documentation on this, as this is a little beyond my expertise. So I looked for the documentation that used to exist, but the version 3.4 manual doesn't seem to have anything to say on the subject!

The section has moved here:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/connection-vs-datasource/

 

Where can I read up on how to create a single jOOQ configuration that I pass around that uses a data source or some other connection factory (that I can e.g. put a connection pool behind)? Where's the new documentation for that? Thanks.

Another good page that explains this is the tutorial about using jOOQ with Apache DBCP and Spring:

http://www.jooq.org/doc/latest/manual/getting-started/tutorials/jooq-with-spring/ 

Essentially, you will just need to replace "passing a Connection instance to jOOQ" by "passing a DataSource instance to jOOQ". Whether you want to keep a global jOOQ Configuration, or just a global DataSource creating the jOOQ Configuration afresh at every query usually doesn't really matter.

Hope this helps. Let me know if you have any concrete issues.

[Garret Wilson]

Thanks for the reply, Lukas. So here's where it gets interesting, taking us back to the whole "with jOOQ you don't need an ORM" issue, which I don't think is as clear-cut as some comments might make it appear.

Let's say I have a Car class (identified by some vehicle identification number VIN) and a Wheel class, and I want to store a car in a database along with its four wheels. If two people try to store the Car in the database at the same time, I don't care who wins, but I want the whole operation to be atomic, and I want it to replace whatever Car is already in the database (if any) with the same VIN.

I also have a CarManager class (which has an implementation JOOQCarManager) along with a WheelManager class (with implementation JOOQWheelManager).

I set up a dbcp2 PoolingDataSource (for example) and then create a jOOQ configuration using:

    final org.jooq.Configuration jooqConfiguration = new DefaultConfiguration()
        .set(dataSource).set(SQLDialect.POSTGRES).set(new Settings().withExecuteLogging(true));

Somehow that jooqConfiguration instance gets put in some dependency injection container; we don't care how, we only care that the managers have access to it.

So inside JOOQCarManager.storeCar(Car car) I create a DSLContext:

    DSLContext dslContext=DSL.using(getConfiguration())

Question 1: So what do I do now? Do I just start using the DSLContext and it will get a connection as needed? Or do I do this?

    Connection connection = dslContext.configuration().connectionProvider().acquire();

Question 2: How do I start a transaction? The code I'm reviewing (a mix of several people's code) does this:

    DSLContext dslTransaction = DSL.using(connection);
    dslTransaction.execute("SET CONSTRAINTS ALL DEFERRED");
    ... //and then goes on to delete the Car if it exists
    final int deleteCount = dslTransaction.delete(CAR).where(CAR.VIN.equal(vin).execute();
    final CarRecord carRecord = dslTransaction.newRecord(CAR);
    ... //etc. to store the Car info

Question 3: So what happens when we want to store the wheels? Does JOOQCarManager call its instance of JOOQWheelManager four times? But how does JOOQWheelManager make sure that it's part of the same transaction? If JOOQWheelManager creates a new DSLContext from the jOOQ Configuration from the DI container, won't that start a new transaction? Do we have to pass the current DSLContext to the WheelManager? Or do we pass the JDBC Connection to the WheelManager, which will use it to form a DSLContext that uses the same connection?

I apologize if some of these questions have obvious answers. In fact I hope they do. I am not a relational database expert, but unfortunately I'm the one who gets to research all this stuff for my client's application. I would think that the above scenario is very common and in fact "standard" to some extent when storing an instance tree in a database. I'm hoping there is a straightforward and "standard" solution.

[Lukas Eder]

Hi Garret,

2014-11-13 20:42 GMT+01:00 Garret Wilson <gar...@globalmentor.com>:

Thanks for the reply, Lukas. So here's where it gets interesting, taking us back to the whole "with jOOQ you don't need an ORM" issue, which I don't think is as clear-cut as some comments might make it appear.

Let's say I have a Car class (identified by some vehicle identification number VIN) and a Wheel class, and I want to store a car in a database along with its four wheels. If two people try to store the Car in the database at the same time, I don't care who wins, but I want the whole operation to be atomic, and I want it to replace whatever Car is already in the database (if any) with the same VIN.

I also have a CarManager class (which has an implementation JOOQCarManager) along with a WheelManager class (with implementation JOOQWheelManager).

I set up a dbcp2 PoolingDataSource (for example) and then create a jOOQ configuration using:

    final org.jooq.Configuration jooqConfiguration = new DefaultConfiguration()
        .set(dataSource).set(SQLDialect.POSTGRES).set(new Settings().withExecuteLogging(true));

Somehow that jooqConfiguration instance gets put in some dependency injection container; we don't care how, we only care that the managers have access to it.

So inside JOOQCarManager.storeCar(Car car) I create a DSLContext:

    DSLContext dslContext=DSL.using(getConfiguration())

Question 1: So what do I do now? Do I just start using the DSLContext and it will get a connection as needed? Or do I do this?

    Connection connection = dslContext.configuration().connectionProvider().acquire();

No, you don't need to do this. jOOQ does this. ConnectionProvider is a SPI (Service Provider Interface). You don't call the SPI, you implement it (or choose a default implementation).

In other words, your first suggestion is correct:

DSLContext dslContext=DSL.using(getConfiguration())

 

Question 2: How do I start a transaction? The code I'm reviewing (a mix of several people's code) does this:

    DSLContext dslTransaction = DSL.using(connection);
    dslTransaction.execute("SET CONSTRAINTS ALL DEFERRED");
    ... //and then goes on to delete the Car if it exists
    final int deleteCount = dslTransaction.delete(CAR).where(CAR.VIN.equal(vin).execute();
    final CarRecord carRecord = dslTransaction.newRecord(CAR);
    ... //etc. to store the Car info

This dslTransaction object isn't really a transaction. I'm not sure why it is named this way.

You essentially have two options

1. Use jOOQ's transaction API. This is mainly useful for standalone batch processes. The default implementation of the jOOQ transaction API uses JDBC transactions, not container transactions.

2. Use your JavaEE container / Spring to manage transactions for you. In the long run, this is probably the way to go

I'm pretty sure that 2) is the way to go in your case.

 

Question 3: So what happens when we want to store the wheels? Does JOOQCarManager call its instance of JOOQWheelManager four times?

I guess it does, in your case. Another option would be to make a single call to DSLContext.batchStore():

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#batchStore-org.jooq.UpdatableRecord...-

 

But how does JOOQWheelManager make sure that it's part of the same transaction?

Ideally, you'll be using container-managed transactions via JavaEE or Spring.

 

If JOOQWheelManager creates a new DSLContext from the jOOQ Configuration from the DI container, won't that start a new transaction?

That depends entirely on how you configure your DataSource. The jOOQ / Spring tutorial shows how to use jOOQ with Spring either with

- explicit transactions (using Spring's DataSourceTransactionManager)

- declarative transactions (using Spring's @Transactional annotations)

http://www.jooq.org/doc/latest/manual/getting-started/tutorials/jooq-with-spring/

I personally find the @Transactional annotations easier to get right. As you can see on the Transactional.propagation() Javadoc, the default is Propagation.REQUIRED, which means:

> Support a current transaction, create a new one if none exists.

> Analogous to EJB transaction attribute of the same name. 

This is exactly what you need. If JOOQWheelManager.save() is called in the context of an existing transaction, then reuse that existing transaction. Otherwise, create a new one.

- http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/transaction/annotation/Transactional.html#propagation--

- http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/transaction/annotation/Propagation.html#REQUIRED

Do we have to pass the current DSLContext to the WheelManager? Or do we pass the JDBC Connection to the WheelManager, which will use it to form a DSLContext that uses the same connection?

When using a DataSource, the concrete Connection instance is abstracted by the DataSource implementation (e.g. DBCP, or Spring). Typically, you will be using a pool of Connections where every Connection is temporarily (for the scope of a transaction) associated with exactly one Thread (internally via ThreadLocal). Whenever that Thread accesses the DataSource.getConnection() method within the same transaction, the DataSource implementation will make sure that the *same* Connection instance will be provided.

jOOQ's ConnectionProvider fits very well in that very common transaction and connection pool model. By default, when you supply jOOQ with a DataSource (via jOOQ's DataSourceConnectionProvider), jOOQ will hook into the above lifecycle, and every query will "just work" in the scope of the transaction that it is executed in.

All that's left for you to do is:

- Configure such a DataSource, e.g. as in the aforementioned jOOQ/Spring tutorial

- Make all relevant methods in your jOOQCarManager and jOOQWheelManager @Transactional

 

I apologize if some of these questions have obvious answers. In fact I hope they do. I am not a relational database expert, but unfortunately I'm the one who gets to research all this stuff for my client's application.

Why not switch the above sentence into: "Heck, I need to know this anyway. I'll need this in *every* future application that I'll ever build with Java, because it's always the same, even regardless if I'm using jOOQ or some other SQL API"

You *want* to research all this stuff, believe me :)

 

I would think that the above scenario is very common and in fact "standard" to some extent when storing an instance tree in a database. I'm hoping there is a straightforward and "standard" solution.

As far as transactions are concerned, yes. All of the above is "standard" and has been "standard" since the early days of JavaEE. In fact, declarative transactions (via XDoclet) were the very essence of JavaEE (or J2EE as it was called back then).

Instance tree (or object graph) persistence, well, that is also "very standard" - when you're using Hibernate. The design goal for jOOQ was to implement SQL, which is something quite different from instance tree persistence, much more useful for reporting, bulk data manipulation, and querying. I might have linked to this page before in our discussions: http://www.hibernate-alternative.com. When you're doing SQL, you're not really thinking in terms of trees (object orientation), but in terms of relations (relational).

But that is an entirely different discussion, and I believe, it is a bit late in your project :)

Hope this helps,

Have a nice weekend,

Lukas

[Garret Wilson]

Thanks so much, Lukas. I'm immediately going to be hitting the books.

I want to mention that we are not using Spring. We are using an embedded Jetty instance with a RESTEasy REST interface on one side and a Wicket web interface on the other. As you know I'm a bit behind on the latest JavaEE database stuff, so if Servlet 3 and Jetty help us out, great. (I will know more after reading this weekend.) Otherwise, the only thing "taking care of things for us" will be jOOQ and JDBC, so I'm not sure if @Transactional et. al. is going to help us out.

[Roger Thomas]

On Friday, November 14, 2014 10:42:08 PM UTC, Garret Wilson wrote:

Thanks so much, Lukas. I'm immediately going to be hitting the books.

I want to mention that we are not using Spring. We are using an embedded Jetty instance with a RESTEasy REST interface on one side and a Wicket web interface on the other. As you know I'm a bit behind on the latest JavaEE database stuff, so if Servlet 3 and Jetty help us out, great. (I will know more after reading this weekend.) Otherwise, the only thing "taking care of things for us" will be jOOQ and JDBC, so I'm not sure if @Transactional et. al. is going to help us out.

While Spring looks very much like a complete platform that in your case would cause issues as you are already using Jetty, you can use parts of Spring such as their transaction library within your own environment.

All the examples on the JOOQ site try and simplify the code by not including all the imports (at times I find this just complicates things). There is an example on the Spring page at the following link, that does show a more complete basic example of adding Spring TX to JDBC based code.

           http://spring.io/guides/gs/managing-transactions/

The page also gives example build scripts so you can see what is needed where.

Roger
 

[Witold Szczerba]

Hi guys,
The problems with JDBC transactions is that you either have to use raw
connections to perform operations like commit and rollback, or you
have to use some heavy heavy stuff like EJB or Spring.

When using jOOQ, there is also third option, introduced by Lukas in
version 3.4.x, but it is... well, it is useless in anything other than
a script (sorry for being straight), because one have to pass a
context/configuration/whatever_you_call_it object everywhere, working
essentially as a transaction. So in anything other than a simple
script, we are left alone with "go use Spring or EJB".

As Roger pointed, one could also use Sprint TX module of Spring
framework. I was almost convinced to do it, since I have just created
a simple microservice in Java with jOOQ and I need transactions.
Question: did you see the Spring source code? After 30 minutes of this
painful experience I decided to never, ever think about using Spring
anywhere anytime.

I remember having a discussion with Lukas, trying to stop him
introducing transactions into jOOQ API. My proposition was to create a
"jOOT", an ultra lightweight transaction manager which could be used
anywhere we want tx management with JDBC (plain JDBC, jOOQ, QueryDSL,
standalone Hibernate, Toplink or other home-grown lib).
I have even created https://github.com/witoldsz/joot-playground

Months passed and I am here, with jOOQ/JDBC microservice and missing
transaction management, so I have decided to implement the concept
described above myself. The problem is I have to figure out the name,
I cannot use jOOT of course. My first shot is "ULTM" - The Ultra
Lightweight Transaction Management for JDBC. The beta version, with
limited capabilities (for start: no nested transactions and some other
restrictions) is like almost ready. It took me few hours so far.

The very basic concept is like this: "you give me your DataSource and
I will give back TxManager and another, managed DataSource and you
will use that DataSource instead of the original one". And all that
with few dozen lines of code (I hope) with no other, external
dependencies.

Let me know if you are interested in it. I will let you know once the
project is usable.

Regards,
Witold Szczerba

> --
> 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/d/optout.

[Roger Thomas]

Spring is like so much of IT you end up taking it on blind faith, the last thing I would do is read the source code as it would just make it clear how little I know or understand :) You also end up pulling Spring into projects as you go as it has a lot of sub-systems that work together, at which point you just live with it.

If you are building a simple wrapper solution, please keep posting as you get it working with JOOQ. I can handle simple :) Such an approach also works well for smaller projects where you don't want the learning curve or support of a major framework. Even without nested transaction support your model of

    [name].transaction(() -> { work to be done }

 
Maps nicley on the unit of work design pattern I first picked up from being around C# developers. Such a pattern also removes much of the need for nested transactions in the first place.

I have to warn you that getting your solution accepted/used is going to be slow, just because so many of us are stuck in Java 7 worlds. Your example joot-playground is yet another example of what Java 8 will allow.

Roger

[Witold Szczerba]

OK, so here it is, the first beta version:
https://github.com/witoldsz/ultm

Volunteers are welcome! Most important things:
- code review,
- improvement suggestions,
- Java 7 port.

So go ahead, fork it, play with it :)

Also, I have never released Open Source Java project, so I could use
some basic help, in few steps, how to do a Maven release.

Thanks,
Witold Szczerba

[Roger Thomas]

You may find the following helpful in getting yor code published

java.dzone.com/articles/deploy-maven-central

Also creating a google+ group would seem a good idea. It may just end up with just a few users, but at least the messages will be held in a single location. 

[Witold Szczerba]

Thanks for the link. Will check that DZone article.

The google group might be an overkill. But it's a good point. My other
small github project experience shows the github's issues are just
fine for reporting bugs, suggestions and questions.
Maybe I should point it out in the README...

Regards,
Witold Szczerba

[Lukas Eder]

Witold, I'm intrigued :)

(and we were missing you at GeeCON Prague!)

2014-11-16 13:59 GMT+01:00 Witold Szczerba <pljos...@gmail.com>:

Hi guys,
The problems with JDBC transactions is that you either have to use raw
connections to perform operations like commit and rollback, or you
have to use some heavy heavy stuff like EJB or Spring.

When using jOOQ, there is also third option, introduced by Lukas in
version 3.4.x, but it is... well, it is useless in anything other than
a script (sorry for being straight), because one have to pass a
context/configuration/whatever_you_call_it object everywhere, working
essentially as a transaction. So in anything other than a simple
script, we are left alone with "go use Spring or EJB".

Yes, that was the main design goal so far.

 

As Roger pointed, one could also use Sprint TX module of Spring
framework. I was almost convinced to do it, since I have just created
a simple microservice in Java with jOOQ and I need transactions.
Question: did you see the Spring source code? After 30 minutes of this
painful experience I decided to never, ever think about using Spring
anywhere anytime.

Well... :-)

 

I remember having a discussion with Lukas, trying to stop him
introducing transactions into jOOQ API. My proposition was to create a
"jOOT", an ultra lightweight transaction manager which could be used
anywhere we want tx management with JDBC (plain JDBC, jOOQ, QueryDSL,
standalone Hibernate, Toplink or other home-grown lib).
I have even created https://github.com/witoldsz/joot-playground

Months passed and I am here, with jOOQ/JDBC microservice and missing
transaction management, so I have decided to implement the concept
described above myself. The problem is I have to figure out the name,
I cannot use jOOT of course. My first shot is "ULTM" - The Ultra
Lightweight Transaction Management for JDBC. The beta version, with
limited capabilities (for start: no nested transactions and some other
restrictions) is like almost ready. It took me few hours so far.

The very basic concept is like this: "you give me your DataSource and
I will give back TxManager and another, managed DataSource and you
will use that DataSource instead of the original one". And all that
with few dozen lines of code (I hope) with no other, external
dependencies.

So, how is this essentially different from "you give me your Configuration and I will give back a 'managed' Configuration and you will use that Configuration instead of the original one" ?

You essentially implemented the same thing as we did in jOOQ 3.4 - except that you're doing things on a lower level with JDBC objects, not with jOOQ objects, making your library more universally usable.

[Witold Szczerba]

Hi Lukas,
The difference is crucial. The configuration object is created while creating transaction, in jOOQ one have to pass it around manually. Let's say I am opening tx on message arrival, then router routes to some handler, it routes further somewhere (like DAO or something). Typical case for even tiny app. Passing config object is not an option.

My solution allows one to configure DSLContext at the boot stage of an app (like in EJB or Spring) and then just use it. And this is how I use it in my microservice.

Regards,
Witold Szczerba

--

[Lukas Eder]

Yes, I understand that part of the difference - and I remember your criticism. Essentially, jOOQ currently expects you to pass the transaction reference (as contained in a derived Configuration) around, whereas ULTM probably keeps it somewhere in a ThreadLocal. In jOOQ, the "ThreadLocal solution" might emerge in a future version as well, in parallel to the existing explicit version (https://github.com/jOOQ/jOOQ/issues/2732). I can't promise any ETA, as this might introduce significant changes to all of jOOQ's internals.

But having both models in parallel isn't so uncommon. In Spring, for instance, you can also choose to use "implicit" transactions via @Transactional annotations, or "explicit" ones via a transaction manager that you have to keep around. In the end, there are these two models. I don't see a clear advantage of one over the other, apart from personal taste. But I agree that jOOQ currently doesn't offer the "implicit" model.

Having said so, I believe that it is a great idea to publish your transaction framework! I suggest you put a couple of well-documented examples in a README.md file of your GitHub repository. You don't necessarily need a Google Group, I think. For starters, communication via GitHub issues might be sufficient.

If you're interested in publishing a guest post on the jOOQ blog, let me know. We're syndicated with DZone, JCG, JAXenter.com, Tech.Pro, and we're also talking to voxxed.com (a new collaboration between Devoxx and Parleys). This might help you kick-start your transaction API.

Cheers,

Lukas

[Witold Szczerba]

Hi,
I am not sure I am following.
You mentioned Spring transactions are working in two modes: implicit
by annotations and explicit using tx manager API. That is true. It is
the same in EJB, where you can use UserTransaction or declarative
transactions. But none of them are forcing users to pass some kind of
context object around. This is not an option anywhere but scripts.
Imagine adding jooq.Configuration (or something like it) to every
method signature, including business interfaces?

It is not like the current jOOQ transaction management is equivalent
of Spring or EJB "manual" mode. The ULTM or jOOT are and since JDBC is
a blocking API by design, all of them are working essentially as you
described, using ThreadLocal.

BTW: the way jOOQ transactions works now, with nesting support, are
much more advanced than what I have done in ULTM. I wish I haven't had
to create ULTM in the first place :)

Regards,
Witold Szczerba

[Lukas Eder]

2014-11-17 13:47 GMT+01:00 Witold Szczerba <pljos...@gmail.com>:

Hi,
I am not sure I am following.
You mentioned Spring transactions are working in two modes: implicit
by annotations and explicit using tx manager API. That is true. It is
the same in EJB, where you can use UserTransaction or declarative
transactions. But none of them are forcing users to pass some kind of
context object around.

Of course not. In a typical EJB context, the objects are registered globally in a JNDI tree. Which is where you can put the jOOQ Configuration. We just haven't implemented this mode yet. And once we implement it, we'll also overload the transaction() methods to accept a no-argument-lambda, like the one you have. The Configuration argument to the lambda is a mere tool, in case you do want access to that context.

Again, implementing ThreadLocal semantics will drastically impact *all* of jOOQ's API, not just the parts about transactions. This is why I personally prefer postponing that step.

 

This is not an option anywhere but scripts.
Imagine adding jooq.Configuration (or something like it) to every
method signature, including business interfaces?

Why? Implement the TransactionProvider SPI and register the Configuration globally in a ThreadLocal in your implementation:

http://www.jooq.org/javadoc/latest/org/jooq/TransactionProvider.html

You will never have to worry about that Configuration anymore. It will always be the right one, depending on the local context.

 

It is not like the current jOOQ transaction management is equivalent
of Spring or EJB "manual" mode. The ULTM or jOOT are and since JDBC is
a blocking API by design, all of them are working essentially as you
described, using ThreadLocal.

Yes.

*but*: We don't enforce the ThreadLocal semantics. It's an option of how our SPIs can be implemented by users, or by future alternative implementations that we ship. I see this as a big plus, as the jOOQ API and its contracts are completely async-ready, if only JDBC weren't blocking.

AOP and ThreadLocal magic are heavy influencers on your application architecture. We don't enforce these things by offering SPIs that can be implemented with whatever semantics you seem fit. We just don't default to the status quo in JavaEE and Spring, without preventing it in the implementations.

 

BTW: the way jOOQ transactions works now, with nesting support, are
much more advanced than what I have done in ULTM. I wish I haven't had
to create ULTM in the first place :)

No one forced you to do that ;-)

[Witold Szczerba]

All you said above about all the complicated stuff that comes with
transaction management proves that maybe it should not be done in the
jOOQ core. As you said, no solution suits everyone. My solution works
with ThreadLocals and I hadn't have to change a single line of code in
jOOQ. jOOT, as a separate project could handle all the issues the way
ULTM does, but there is no need for jOOT anymore, at least not for
me...

And using ULTM in scripts is as easy as using jOOQ transactions, maybe
extra single line of code is required.

But that is just the echo of our conversations from long long time ago :)

BTW: I was actually forced to create ULTM, because as I said, I had
microservice with no transaction support.

Regards,
Witold Szczerba

[Lukas Eder]

We can discuss this for ages, Witold, and we're both right in a way... :)

*You* are an excellent engineer, you know exactly what model best suits your particular use-case, and you can implement ULTM easily yourself, and that's perfect.

Whereas *we* get all sorts of support requests for things like Spring, JavaEE and the likes - like the original request in this post. We want to offer an easy, out-of-the-box solution that ultimately works in all use cases. We don't want to be supporting Spring TX five years from now.  Those people who know how Spring TX works or who have been using Spring TX all over their applications can continue to use it with jOOQ. Those people who don't know how Spring TX works, will be able to do everything with jOOQ. This will obviously extend to connection pools, eventually.

You're absolutely right. ULTM is as easy as using jOOQ transactions. So, to get back at Garret's original questions:

- ULTM right now will probably be better suited for Garret's use case than jOOQ transactions

- Even better right now for Garret's use case is to use Spring TX (in my opinion)

Not sure if Garret's still reading this, though ;-)

[Garret Wilson]

Yes, Lukas, this is all helping immensely. I'm away from my computer, but ULTM sounds like it's what we could use. I was hoping not to bring in even a fingernail on the monster that is Spring. G

[Andrey Antukh]

Andrey.

Sent from my Nexus 4

On Nov 17, 2014 11:37 AM, "Lukas Eder" <lukas...@gmail.com> wrote:
>
> Yes, I understand that part of the difference - and I remember your criticism. Essentially, jOOQ currently expects you to pass the transaction reference (as contained in a derived Configuration) around, whereas ULTM probably keeps it somewhere in a ThreadLocal. In jOOQ, the "ThreadLocal solution" might emerge in a future version as well, in parallel to the existing explicit version (https://github.com/jOOQ/jOOQ/issues/2732). I can't promise any ETA, as this might introduce significant changes to all of jOOQ's internals.
>
> But having both models in parallel isn't so uncommon. In Spring, for instance, you can also choose to use "implicit" transactions via @Transactional annotations, or "explicit" ones via a transaction manager that you have to keep around. In the end, there are these two models. I don't see a clear advantage of one over the other, apart from personal taste. But I agree that jOOQ currently doesn't offer the "implicit" model.
>

Implicit is always better than implicit. Personally I prefer the current tx jOOQ api over any other like spring...
It is very flexible and allow implement own implicit  tx management  without much of problems.

Implicit tx management is not suitable for all cases. In my opinion any implicit transaction management should be out of scope of jOOQ (different library is a good place for it...)

> Having said so, I believe that it is a great idea to publish your transaction framework! I suggest you put a couple of well-documented examples in a README.md file of your GitHub repository. You don't necessarily need a Google Group, I think. For starters, communication via GitHub issues might be sufficient.
>
> If you're interested in publishing a guest post on the jOOQ blog, let me know. We're syndicated with DZone, JCG, JAXenter.com, Tech.Pro, and we're also talking to voxxed.com (a new collaboration between Devoxx and Parleys). This might help you kick-start your transaction API.
>
> Cheers,
> Lukas
>

My two cents.

Greetings.
Andrey

[Lukas Eder]

2014-11-17 15:45 GMT+01:00 Garret Wilson <gar...@globalmentor.com>:

Yes, Lukas, this is all helping immensely. I'm away from my computer, but ULTM sounds like it's what we could use. I was hoping not to bring in even a fingernail on the monster that is Spring. G

Fair enough :-) 

[Lukas Eder]

Hi Andrey,

2014-11-17 15:51 GMT+01:00 Andrey Antukh <ni...@niwi.be>:

Andrey.

Sent from my Nexus 4
On Nov 17, 2014 11:37 AM, "Lukas Eder" <lukas...@gmail.com> wrote:
>
> Yes, I understand that part of the difference - and I remember your criticism. Essentially, jOOQ currently expects you to pass the transaction reference (as contained in a derived Configuration) around, whereas ULTM probably keeps it somewhere in a ThreadLocal. In jOOQ, the "ThreadLocal solution" might emerge in a future version as well, in parallel to the existing explicit version (https://github.com/jOOQ/jOOQ/issues/2732). I can't promise any ETA, as this might introduce significant changes to all of jOOQ's internals.
>
> But having both models in parallel isn't so uncommon. In Spring, for instance, you can also choose to use "implicit" transactions via @Transactional annotations, or "explicit" ones via a transaction manager that you have to keep around. In the end, there are these two models. I don't see a clear advantage of one over the other, apart from personal taste. But I agree that jOOQ currently doesn't offer the "implicit" model.
>

Implicit is always better than implicit. Personally I prefer the current tx jOOQ api over any other like spring...
It is very flexible and allow implement own implicit  tx management  without much of problems.

Implicit tx management is not suitable for all cases. In my opinion any implicit transaction management should be out of scope of jOOQ (different library is a good place for it...)

For the group: Andrey has implemented Suricatta (http://niwibe.github.io/suricatta). A SQL API built on top of jOOQ for Clojure.

Andrey: I haven't had a look at the implementation details of your TransactionProvider yet. If I'm not mistaken, the sources are these ones here, right?

https://github.com/niwibe/suricatta/blob/7af06380bf566edce092091ff3ff2410fd81107e/src/suricatta/core.clj#L109

Or explicitly:

(defn atomic-apply
	"Execute a function in one transaction
	or subtransaction."
	[^Context ctx func & args]
	(let [^Configuration conf (.derive (proto/get-configuration ctx))
	^TransactionContext txctx (transaction-context conf)
	^TransactionProvider provider (.transactionProvider conf)]
	(doto conf
	(.data "suricatta.rollback" false)
	(.data "suricatta.transaction" true))
	(try
	(.begin provider txctx)
	(let [result (apply func (types/->context conf) args)
	rollback? (.data conf "suricatta.rollback")]
	(if rollback?
	(.rollback provider txctx)
	(.commit provider txctx))
	result)
	(catch Exception cause
	(.rollback provider (.cause txctx cause))
	(if (instance? RuntimeException cause)
	(throw cause)
	(throw (DataAccessException. "Rollback caused" cause)))))))

What would your TransactionProvider look like in plain Java code, more or less?

[Andrey Antukh]

Hi everyone! 

2014-11-18 16:23 GMT+01:00 Lukas Eder <lukas...@gmail.com>:

Hi Andrey,

2014-11-17 15:51 GMT+01:00 Andrey Antukh <ni...@niwi.be>:

Andrey.

Sent from my Nexus 4
On Nov 17, 2014 11:37 AM, "Lukas Eder" <lukas...@gmail.com> wrote:
>
> Yes, I understand that part of the difference - and I remember your criticism. Essentially, jOOQ currently expects you to pass the transaction reference (as contained in a derived Configuration) around, whereas ULTM probably keeps it somewhere in a ThreadLocal. In jOOQ, the "ThreadLocal solution" might emerge in a future version as well, in parallel to the existing explicit version (https://github.com/jOOQ/jOOQ/issues/2732). I can't promise any ETA, as this might introduce significant changes to all of jOOQ's internals.
>
> But having both models in parallel isn't so uncommon. In Spring, for instance, you can also choose to use "implicit" transactions via @Transactional annotations, or "explicit" ones via a transaction manager that you have to keep around. In the end, there are these two models. I don't see a clear advantage of one over the other, apart from personal taste. But I agree that jOOQ currently doesn't offer the "implicit" model.
>

Implicit is always better than implicit. Personally I prefer the current tx jOOQ api over any other like spring...
It is very flexible and allow implement own implicit  tx management  without much of problems.

Implicit tx management is not suitable for all cases. In my opinion any implicit transaction management should be out of scope of jOOQ (different library is a good place for it...)

For the group: Andrey has implemented Suricatta (http://niwibe.github.io/suricatta). A SQL API built on top of jOOQ for Clojure.

Thanks for the presentation. 

Andrey: I haven't had a look at the implementation details of your TransactionProvider yet. If I'm not mistaken, the sources are these ones here, right?

https://github.com/niwibe/suricatta/blob/7af06380bf566edce092091ff3ff2410fd81107e/src/suricatta/core.clj#L109

Yes, you are right. 

This code is representing something that jOOQ has already implemented (very similar, concretely on https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/DefaultDSLContext.java#L299). Obviously it has different semantics, because it also handles explicit rollback flag instead of using exceptions. But this uses a default transaction provider of jOOQ, because it is simple and sufficient for implement over it any sugar syntax.

The java code maybe can a little bit verbose, but only for java syntax and types declaration, but it would be mostly the same. The grace of use clojure in this case, is its flexible syntax that makes use of explicit transaction management (without thread locals) very intuitive. Let see an example:

(atomic ctx

  (do-something-with-ctx ctx)

  (atomic ctx

    (do-something-other-in-a-subtransaction-with-ctx ctx)))

(`atomic` is a clojure macro that behind the schenes uses the `atomic-apply`, see previous code example)

Something like this in java7 would be very very verbose, because it implies creating a lot of anonymous clases.

With java8 this verbosity is reduced obviously.

But with clojure syntax flexibility, it allows use the explicit transaction management, without any threadlocal in very comfortable way.

Implement something implicit is also very easy, but is less necessary if a language that are you using allows some facilities for work with that.

Personally, I don't understand the advantages of using something like sprint tx. They mostly provides accidental complexity and try to solve something that language can not solve. jOOQ currently provides simple and flexible transaction provider interface, that enables that any one can implement over it the concrete use case of that any one can need. In this way, I think jOOQ is more agnostic of frameworks, that is good. (It obviously make things easy for implement something like suricatta with own transaction semantics on top of the jOOQ transaction provider without much problems).

Obviously, is a subjective opinion. ;)

Greetings.

Andrey

 

--
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/d/optout.

--

Andrey Antukh - Андрей Антух - <andrei....@kaleidos.net> / <ni...@niwi.be>

http://www.niwi.be

https://github.com/niwibe

[Witold Szczerba]

OK, I get it now. It is just that for me it was not acceptable to pass context object around from one layer to another. Let me show you, with code example, to demonstrate, what was the problem with jOOQ transactions in my case. 

This post is targeted to anyone interested in this subject, but Lukas, you can just skip it as there is nothing new for you here :)

The piece of code below is a generic message handler, part of a shared common lib inside my project which is made of dozens of microservices with 3 of them being in JVM. The handler is invoked when messages arrive. Such a code snippets I had in many other projects, but this one is the first when I do not use any application server (and I like it that way), so I had to take care of transactions by myself instead of making it done automagically by EJB. 

Part of this is a RabbitMQ SPI, but it would be almost the same in any other scenario like simple HTTP handler, servlet, JAX-RS or the like. 

// full version there:

// http://pastebin.com/nga6SeZK

//

MQueue mQueue = new MQueue(channel, ...);

[...]

// few lines later

@Override

public void handleDelivery(...) {

  try {

    txManager.txChecked(() -> mQueue.onMessage(message));

    // look how it would be with jOOQ:

    // but this tiny message handling project knows nothing about jOOQ

    // jooq.transaction(ctx -> mQueue.onMessage(message, ctx));

    if (!p.autoAck) channel.basicAck(...);

  } catch (Exception ex) {

    if (!p.autoAck) channel.basicNack(...);

  }

}

There are other ways to make it work without storing transaction context in ThreadLocal, in the future - who knows, maybe I would add other ways of fetching tx context, but I guess they would have to depend on environment, like Guice's scopes. 

Of course I could extend the mQueue#onMessage method, so it would use some proprietary transaction/configuration object from jOOQ, but this is just a generic message handler, it knows nothing about who will actually consume that message, maybe the consumer does not know anything about jOOQ at all and do not have it on it's classpath.

Another problem distributing by reference passing some kind of context is that the MQueue class, which is responsible for routing the exact message to the correct object, would have pass that context yet again (and then maybe yet again), so now my tiny and generic message handling library is not only packed with jOOQ API (which is actually not included there at all) but it also forces all the target consumers to declare dependency on jOOQ context on each handler even if they do not use it at all.

Now, the transaction manager as I asked from the very beginning would work in a different way. What I did with ULTM was actually a carbon-copy of what I used to in big and fat environments like EJB. The only exception is that it is like gazillion times simpler, the only thing that is left is the idea of transparent transaction management. So, I do not care what kind of JDBC library my consumers will use. All I want is to make my library wrap message destination handler within generic transaction (which depends on raw JDBC DataSource) and do a commit/rollback at the end of processing. And if there was no JDBC calls at all - then nothing is happening.

And last, but not least, the "transparent" JDBC transaction management is simple to use, yet universal. It does not add complexity to any JDBC library. It allows me to use jOOQ, plain JDBC, Hibernate or whatever together or separably and the outer layer of my application (or microservice) does not care. I heard there are people using jOOQ with JPA/Hibernate. They would benefit as well, because the transaction would span them all.

Having said all that, I can see the use case of the transaction management built into jOOQ. I do not like it, but at least I can see others who do and it works for them.

Thanks,

Witold Szczerba

[Lukas Eder]

Eek, this was lying around in my inbox too long. Sorry for that.

2014-11-19 0:54 GMT+01:00 Witold Szczerba <pljos...@gmail.com>:

OK, I get it now. It is just that for me it was not acceptable to pass context object around from one layer to another.

Yes, I know, and again, you were right - for your use-case :-)

From the jOOQ perspective, your use-case is a special case - an implementation detail that I chose to defer until later.

 

Let me show you, with code example, to demonstrate, what was the problem with jOOQ transactions in my case. 

This post is targeted to anyone interested in this subject, but Lukas, you can just skip it as there is nothing new for you here :)

The piece of code below is a generic message handler, part of a shared common lib inside my project which is made of dozens of microservices with 3 of them being in JVM. The handler is invoked when messages arrive. Such a code snippets I had in many other projects, but this one is the first when I do not use any application server (and I like it that way), so I had to take care of transactions by myself instead of making it done automagically by EJB. 

Part of this is a RabbitMQ SPI, but it would be almost the same in any other scenario like simple HTTP handler, servlet, JAX-RS or the like. 

// full version there:

// http://pastebin.com/nga6SeZK

//

MQueue mQueue = new MQueue(channel, ...);

[...]

// few lines later

@Override

public void handleDelivery(...) {

  try {

    txManager.txChecked(() -> mQueue.onMessage(message));

    // look how it would be with jOOQ:

    // but this tiny message handling project knows nothing about jOOQ

    // jooq.transaction(ctx -> mQueue.onMessage(message, ctx));

Not necessarily. You could've just written

  jooq.transaction(ctx -> mQueue.onMessage(message));

... just the same. Your implementation of jOOQ's TransactionProvider could have done all that for you.

 

    if (!p.autoAck) channel.basicAck(...);

  } catch (Exception ex) {

    if (!p.autoAck) channel.basicNack(...);

  }

}

There are other ways to make it work without storing transaction context in ThreadLocal, in the future - who knows, maybe I would add other ways of fetching tx context, but I guess they would have to depend on environment, like Guice's scopes. 

Interesting. I'll have to read up on Guice's scopes. jOOQ's various Configuration/Context objects also act like "scopes" (New type: http://www.jooq.org/javadoc/latest/org/jooq/Scope.html), given that they have a Map<Object, Object> where scope implementors can locally register data.

I sometimes wish there was an implicit Map on the JVM stack, in which every stack frame can register things that would be available to all child frames, and that would be automatically cleaned up once the frame is "closed". E.g. an implicit:

public void method() {

    try {

        scope.init("method()");

        // User method body here

    }

    finally {

        scope.close();

    }

}

Given that the JVM bytecode is really a stack machine, I'm surprised that nothing like that exists. The difference to local variables (also on the stack) would be the fact that all methods called by the above "method()" would get (read-only) access to all the values from the "outer" scope. Obviously, resolving ambiguities would be a challenge, but many things would be much easier to implement than via AOP or via ThreadLocal hacks. But that's another discussion :-)

Of course I could extend the mQueue#onMessage method, so it would use some proprietary transaction/configuration object from jOOQ, but this is just a generic message handler, it knows nothing about who will actually consume that message, maybe the consumer does not know anything about jOOQ at all and do not have it on it's classpath.

Another problem distributing by reference passing some kind of context is that the MQueue class, which is responsible for routing the exact message to the correct object, would have pass that context yet again (and then maybe yet again), so now my tiny and generic message handling library is not only packed with jOOQ API (which is actually not included there at all) but it also forces all the target consumers to declare dependency on jOOQ context on each handler even if they do not use it at all.

Now, the transaction manager as I asked from the very beginning would work in a different way. What I did with ULTM was actually a carbon-copy of what I used to in big and fat environments like EJB. The only exception is that it is like gazillion times simpler, the only thing that is left is the idea of transparent transaction management. So, I do not care what kind of JDBC library my consumers will use. All I want is to make my library wrap message destination handler within generic transaction (which depends on raw JDBC DataSource) and do a commit/rollback at the end of processing. And if there was no JDBC calls at all - then nothing is happening.

Which is why you don't use Spring TX or JavaEE, right? ;-)

 

And last, but not least, the "transparent" JDBC transaction management is simple to use, yet universal. It does not add complexity to any JDBC library. It allows me to use jOOQ, plain JDBC, Hibernate

Your assumptions are obviously true for all non-cached database interactions on a JDBC level, including plain JDBC and jOOQ. But I'm actually very curious about how you'll manage to interact with Hibernate on that level. What happens to the Hibernate second-level cache if a third party chooses to roll back a transaction? Will all entities go stale and revert their state to a well-defined, third-party provided savepoint? And after the UNDO, can I still REDO some changes?

 

or whatever together or separably and the outer layer of my application (or microservice) does not care. I heard there are people using jOOQ with JPA/Hibernate.

So far, we're aware of people who use them in very separate parts of the application, perhaps even running queries against different tables. Because of second-level caching, I doubt that they're using jOOQ and JPA within the same transaction - people tend to not even use views with JPA, when second-level caching is active.

For example: JPA / Hibernate is used for persistence of transactional data, and jOOQ for reporting / OLAP. We're going to be working together with Red Hat (in particular with Arun Gupta) to look into various ways to integrate with Java EE. You might be interested to add your own questions here:

https://github.com/javaee-samples/webinars/issues/4

Other upcoming webinars by Arun can be seen here:

https://github.com/javaee-samples/webinars

They would benefit as well, because the transaction would span them all.

Having said all that, I can see the use case of the transaction management built into jOOQ. I do not like it, but at least I can see others who do and it works for them.

Thanks for the feedback :-)

Cheers

Lukas 

[Witold Szczerba]

The ULTM v.1.0 is released and published (Maven Central and jCenter at Bintray).
Project page is here:
https://github.com/witoldsz/ultm

Thank you,
Witold Szczerba

On Mon, Nov 17, 2014 at 2:33 AM, Roger Thomas <rithom...@gmail.com> wrote:

[Garret Wilson]

Witold, thanks for the update. I haven't yet talked to our main database
developer, but on first glance it looks like ULTM would be great for
us!! Let me clarify a few things.

Our code has an application that creates a jOOQ Configuration from a
data source:

final org.jooq.Configuration jooqConfiguration = new
DefaultConfiguration()

.set(datasource).set(SQLDialect.POSTGRES).set(new
Settings().withExecuteLogging(true));

We store this jOOQ Configuration in e.g. some DI container. In all the
manager layer, we access jOOQ like this:

return DSL.using(getConfiguration());

We therefore have one jOOQ Configuration (created from a single data
source) for the entire application, and when we need to do database
access we create a DSLContext on the fly from the configuration. I
frankly don't know the details of how jOOQ then creates JDBC connections
under the hood; this is not my area of expertise.

So with ULTM, would we create one TxManager (also from the data source)
at the same level as the jOOQ Configuration, and make it available via
the DI container? If we create multiple jOOQ DSLContexts on the fly from
the jOOQ Configuration, how does that interact with ULTM?

Let's further suppose that I have a CarManager and a WheelManager.
Storing a Car using CarManager.storeCar() would I assume use TxManager
to wrap the logic in a transaction. But inside that logic it calls
WheelManager.storeWheel() four times. Does WheelManager simply do its
work without worrying about transactions? Do I need to pass the
TxManager to WheelManager? Will the DSLContext create a new connection
that isn't part of the same transaction? Do I need to pass the
DSLContext to WheelManager? Do I need to pass the JDBC connection to
WheelManager? Or is TXManager somehow managing all this under the hood?

I'm sure if I had more SQL expertise the answers to some of these
questions might be obvious. Thanks in advance for your help.

Garret

[Witold Szczerba]

Hi,
you should have one ULTM (TxManager + managed DataSource) per
application (assuming you have only one database).

What seems strange to me is why do you use
"DSL.using(getConfiguration())" everywhere instead of creating
DSLContext once and use that everywhere? There is no need to create it
all the time, one instance should be enough.

Do you use DI? It's brilliant design pattern. The basic idea is to
split object creation code from business logic, so I create TxManager
and DSLContext once and inject it everywhere I need (using Guice or if
you do not want it, by manually creating and configuring objects when
bootstrapping application). It works flawlessly.

There is no need to pass TxManager everywhere. I would use it on some
external layer, like some gateway, to begin and commit or rollback,
but your case can be a little bit different, so I cannot tell.
Referring to your example with CarManager and WheelManager, it would
look odd to operate with TxManager in one and not use it in another
(but you can do this). However, I would think about getting rid of
transaction management from those manager, because if you start
transaction yet elsewhere, in some ThirdManager, then you cannot call
CarManager (because it handles transactions by itself) but you can
call WheelManager (because it doesn't).

In my app, there is only one "point of entry", but there might be many
(like JAX-RS endpoints).

There is no mailing list for ULTM, but fell free to ask question on
issue tracker, because I am not sure if people here are interested :)

Regards,
Witold Szczerba

[Witold Szczerba]

Ah, now I can see you wrote you use DI and I asked that question. Sorry about that.

Regards,
Witold Szczerba

--
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+unsubscribe@googlegroups.com.

[Lukas Eder]

2014-12-08 18:06 GMT+01:00 Witold Szczerba <pljos...@gmail.com>:

There is no mailing list for ULTM, but fell free to ask question on
issue tracker, because I am not sure if people here are interested :)

While you should certainly decide yourself where you'd like to offer support for ULTM, I don't think that ULTM+jOOQ questions are off-topic on this list. Moreover, this thread is called "connection and data sources", which is certainly a central topic for anyone working with a database access solution like jOOQ - so even if they might not be using ULTM, they might be implementing something similar and find some inspiration from your E-Mails.

So, feel free to go on :-)

Cheers,

Lukas

[Garret Wilson]

So I guess I still have questions about the whole thing.

* So is ULTM really just a wrapper around JDBC Connection.commit() and
Connection.rollback()?

* So how is TxManager.tx(...) different from
DSLContext.transaction(...)? Is it simply that I don't have to pass
around a DSLContext? But don't I still have to pass around a TxManager?
(I consider a DI container still "passing around" a TXManager---it's
just that the DI container does the passing for you.)

* How does TxManager interact with DSLContext? If TxManager makes a
Connection.commit(), how do I know that the DSLContext is using the same
connection? How do I know that the DSLContext hasn't pulled another
connection out of the data pool?

* In fact I'm still unsure how DSLContext gets connections. One page
(http://www.jooq.org/doc/2.6/manual/sql-building/factory/connection-vs-datasource/)
seems to indicate that DSLContext will use a different connection for
each query. (Yes, I realize this documentation is really old, but Lukas,
the link there for 3.5 is broken. I finally found it;
http://www.jooq.org/doc/3.5/manual/sql-building/dsl-context/connection-vs-datasource/
seems to say the same thing.)

I realize I have asked some of these questions before, but the answers
never seem to tie together and we've been trying to release a new
product and I've been doing construction in a new apartment in another
country (not in my native language) and my mind is about to become
frazzled...

Garret

[Witold Szczerba]

Hi, see my comments inline.

On Tue, Dec 9, 2014 at 12:59 AM, Garret Wilson <gar...@globalmentor.com> wrote:
> So I guess I still have questions about the whole thing.
>
> * So is ULTM really just a wrapper around JDBC Connection.commit() and
> Connection.rollback()?

More or less. It is tracking the connections retrieved from the
managed DataSource and when you call txManager.commit/rollback, it
knows which connection to use for that purpose.

The whole concept revolves around the fact that JDBC is a blocking
API, so we can distinguish connections by threads using them. This is
how "the magic" works, but there is no magic really. It's all as
simple as ThreadLocal is (acting as mentioned connection tracker).

>
> * So how is TxManager.tx(...) different from DSLContext.transaction(...)? Is
> it simply that I don't have to pass around a DSLContext? But don't I still
> have to pass around a TxManager? (I consider a DI container still "passing
> around" a TXManager---it's just that the DI container does the passing for
> you.)

The key difference comes from what I said above plus the fact that you
can mix jOOQ with something else under the hood of one TxManager (but
it's not important here, so let's skip it).
What's important is that you can centralize transaction management in
one place. That means there is no need to pass TxManager around. Of
course you still can do it, and if you do, you use one instance of
TxManager per application. The jOOQ transaction context is created
when you start transaction and dies at the end, so you cannot just
create it once and inject everywhere, you have to pass it around
manually (adding it to method signatures) or build some kind of proxy,
which, at the end, would lead you to the place where ULTM is, with the
difference that ULTM does not depend on jOOQ, it's universal.

>
> * How does TxManager interact with DSLContext? If TxManager makes a
> Connection.commit(), how do I know that the DSLContext is using the same
> connection? How do I know that the DSLContext hasn't pulled another
> connection out of the data pool?

As I said above, when you use DataSource managed by ULTM to produce
the DSLContext, ULTM will track connections used by DSLContext. jOOQ
will be under control of ULTM which means the TxManager will be able
to commit/rollback on connections the jOOQ were using. The only thing
to remember is that in managed environments like this (same applies to
EJB and Spring), you cannot jump from one thread to another while
preforming the commit-able "unit of work". However, using different
threads does not make sense when talking to databases through JDBC.

>
> * In fact I'm still unsure how DSLContext gets connections. One page
> (http://www.jooq.org/doc/2.6/manual/sql-building/factory/connection-vs-datasource/)
> seems to indicate that DSLContext will use a different connection for each
> query. (Yes, I realize this documentation is really old, but Lukas, the link
> there for 3.5 is broken. I finally found it;
> http://www.jooq.org/doc/3.5/manual/sql-building/dsl-context/connection-vs-datasource/
> seems to say the same thing.)
>

DSLContext asks for connections from some kind of jOOQ's connection
provider. It's part of API, but there is no need to implement it,
because there is the DataSource adapter built-in. So, when you create
DSLContext with DataSource, that DataSource becomes indirectly the
connection provider. Since connections are tracked by ULTM - TxManager
can do it's job. You can experiment like this: create TxManager and
managed DataSource, create DSLContext using that datasource. Modify
the data using jOOQ then manually fetch the JDBC connection from that
DataSource and you will get exactly the same one used by jOOQ. You can
use that connection to modify something else, then txManager rollback
will revert both changes of what you have done with jOOQ and
connection itself.

> I realize I have asked some of these questions before, but the answers never
> seem to tie together and we've been trying to release a new product and I've
> been doing construction in a new apartment in another country (not in my
> native language) and my mind is about to become frazzled...
>
> Garret

I hope it's a little bit clearer right now. You asked good questions
here, maybe I should create some kind of wiki based on those... I wish
I were better organized...

Regards,
Witold Szczerba

[Garret Wilson]

On 12/8/2014 11:05 PM, Witold Szczerba wrote:
> ...

>> * How does TxManager interact with DSLContext? If TxManager makes a
>> Connection.commit(), how do I know that the DSLContext is using the same
>> connection? How do I know that the DSLContext hasn't pulled another
>> connection out of the data pool?
> As I said above, when you use DataSource managed by ULTM to produce
> the DSLContext, ULTM will track connections used by DSLContext. jOOQ
> will be under control of ULTM which means the TxManager will be able
> to commit/rollback on connections the jOOQ were using. The only thing
> to remember is that in managed environments like this (same applies to
> EJB and Spring), you cannot jump from one thread to another while
> preforming the commit-able "unit of work". However, using different
> threads does not make sense when talking to databases through JDBC.

So are you saying that each thread only gets a single JDBC connection?
So no matter how many times jOOQ asks for a connection, it gets the same
one? But that doesn't make sense...

So if I use ULTM to create this "managed data source", then it will
ensure that jOOQ gets the same connection every time it asks for one?
But doesn't that defeat the purpose of connection pooling? I'm afraid
I'm completely lost.

Let me take this step-by-step:

1. I create a data source:

HikariDataSource datasource = new HikariDataSource();

2. I create ULTM:

ULTM ultm = new ULTM(pgDataSource);

3. I create a managed data source:

DataSource ultmDataSource = ultm.getManagedDataSource();

4. I create a transaction manager:

TxManager txManager = ultm.getTxManager();

5. I create a jOOQ configuration:

org.jooq.Configuration jooqConfiguration = new DefaultConfiguration()

.set(ultmDataSource).set(SQLDialect.POSTGRES).set(new
Settings().withExecuteLogging(true));

6. I create a DSLContext:

DSLContext jooq=DSL.using(jooqConfiguration);

7. OK, now let's do something. Let's say I have a method
CarManager.storeCar(Car car). At the start of the method I want to start
a transaction. Let's assume I get the TxManager from the DI container
somehow:

txManager.begin();

I assume that this set connection.setAutoCommit(false) somewhere? But on
what connection? Or does it wait for jOOQ to get a connection?

8. So let's start storing the car. First I want to delete whatever car
that is already there, which (which cascading deletes will delete all
car properties, all wheels, etc.):

int deleteCount =
dslTransaction.delete(CAR).where(CAR.ID.equal(car.getID()).execute();

Wait, so jOOQ just went and asked for a connection... right? Did it get
the same connection that txManager used above to turn of autocommit? Or
did TxManager wait until just now to turn off autocommit on the new
connection that jOOQ asked for from the pool?

So now that this SQL statement is finished and executed, jOOQ will
"close" the connection, which normally would return it to the pool. Does
TxManager step in and keep the connection from going back into the pool?

9. So now let's start storing the car:

CarRecord carRecord = dslTransaction.newRecord(CAR);
...
carRecord.store();

Again, where does jOOQ get its connection? Does the managed data source
ensure that it gets the same connection as before? After the transaction
is over, does the manage data source simply pass the connection requests
(that are not part of any transaction) on to the underlying data source?
If this is how it all works, then I suppose it's starting to make sense.

10. What if I were to create a new DSLContext in the middle of the
transaction:

DSLContext jooq2=DSL.using(jooqConfiguration)

Does this DSLContext, because it is based on the same ULTM-managed data
source, wind up using the same JDBC connection?

11. Now I want to store the car wheels. I call
WheelManager.storeWheel(Wheel wheel) four times. The wheel manager has
no knowledge of transactions or ULTM or whatever. It just creates yet
another DSLContext and starts storing wheels. If the new DSLContext is
based on a jOOQConfiguration that is based on a ULTM-managed data
source, then all the statements use the same JDBC connection. Do I have
this right now?

12. Let's say that inside WhellManager.storeWheel(Wheel wheel), after
storing all the wheels, I use the new DSLContext to check the number of
wheels in the table; we expect four of them. If there are not four of
them, I want to roll back the transaction. How do I do that? Can I just
do that straight from jOOQ? Will ULTM know about it, or think it's still
in a transaction? How would I reach out to ULTM? Would I need access to
the TxManager?

I'll stop there for now, because I've made several assumptions on what
the answers are, and I want to see if I'm correct before going further.

Thanks for bearing with me!

Garret

>> email tojooq-user...@googlegroups.com.
>> For more options, visithttps://groups.google.com/d/optout.

[Lukas Eder]

2014-12-09 0:59 GMT+01:00 Garret Wilson <gar...@globalmentor.com>:

Yes, I realize this documentation is really old, but Lukas, the link there for 3.5 is broken.

Thanks for pointing this out. We'll fix it, soon:

https://github.com/jOOQ/jOOQ/issues/3850

[Witold Szczerba]

Yes, it does wait for someone to actually get a connection and checks
if autocommit is disabled.

>
> 8. So let's start storing the car. First I want to delete whatever car that
> is already there, which (which cascading deletes will delete all car
> properties, all wheels, etc.):
>
> int deleteCount =
> dslTransaction.delete(CAR).where(CAR.ID.equal(car.getID()).execute();
>
> Wait, so jOOQ just went and asked for a connection... right? Did it get the
> same connection that txManager used above to turn of autocommit? Or did
> TxManager wait until just now to turn off autocommit on the new connection
> that jOOQ asked for from the pool?

Yes, the connection gets pulled from your original data source now.

>
> So now that this SQL statement is finished and executed, jOOQ will "close"
> the connection, which normally would return it to the pool. Does TxManager
> step in and keep the connection from going back into the pool?

Yes, it does. Connection does not go back to pool yet.

>
> 9. So now let's start storing the car:
>
> CarRecord carRecord = dslTransaction.newRecord(CAR);
> ...
> carRecord.store();
>
> Again, where does jOOQ get its connection? Does the managed data source
> ensure that it gets the same connection as before? After the transaction is
> over, does the manage data source simply pass the connection requests (that
> are not part of any transaction) on to the underlying data source? If this
> is how it all works, then I suppose it's starting to make sense.

As I said in previous posts, jOOQ will get the same connection each
time within TxManager transaction. It won't close, commit, rollback or
give it back to the pool until TxManager say so.

>
> 10. What if I were to create a new DSLContext in the middle of the
> transaction:
>
> DSLContext jooq2=DSL.using(jooqConfiguration)
>
> Does this DSLContext, because it is based on the same ULTM-managed data
> source, wind up using the same JDBC connection?

Yes.

>
> 11. Now I want to store the car wheels. I call WheelManager.storeWheel(Wheel
> wheel) four times. The wheel manager has no knowledge of transactions or
> ULTM or whatever. It just creates yet another DSLContext and starts storing
> wheels. If the new DSLContext is based on a jOOQConfiguration that is based
> on a ULTM-managed data source, then all the statements use the same JDBC
> connection. Do I have this right now?

Yes.

>
> 12. Let's say that inside WhellManager.storeWheel(Wheel wheel), after
> storing all the wheels, I use the new DSLContext to check the number of
> wheels in the table; we expect four of them. If there are not four of them,
> I want to roll back the transaction.

You can, but that would be so strange. You do not want to mess with
transactions like this, I guess. I mean, I wouldn't, but as I said
previously, I do not know the details of your system, so maybe you are
right and you should just rollback there.

> How do I do that? Can I just do that
> straight from jOOQ? Will ULTM know about it, or think it's still in a
> transaction? How would I reach out to ULTM? Would I need access to the
> TxManager?

The only way to rollback is to call txManager.rollback() or to throw
an exception within "unit of work".

>
> I'll stop there for now, because I've made several assumptions on what the
> answers are, and I want to see if I'm correct before going further.
>
> Thanks for bearing with me!
>
> Garret

You are welcome. Also, I would strongly advise reading the source
code. This is one of the primary reason of creating the ULTM. It is so
small and easy to reason about.

Before writing ULTM I was hoping I could use the Spring Transaction
module. I have spent like 30 minutes trying to go through that (I was
asking myself questions more or less like the you just did) and I gave
up. It was _horrible_ experience.

Regards,
Witold Szczerba

[Garret Wilson]

On 12/9/2014 7:52 AM, Witold Szczerba wrote:
>> 12. Let's say that inside WhellManager.storeWheel(Wheel wheel), after
>> storing all the wheels, I use the new DSLContext to check the number of
>> wheels in the table; we expect four of them. If there are not four of them,
>> I want to roll back the transaction.
> You can, but that would be so strange. You do not want to mess with
> transactions like this, I guess. I mean, I wouldn't, but as I said
> previously, I do not know the details of your system, so maybe you are
> right and you should just rollback there.

Ha---actually I just threw that in for good measure, from some JDBC
examples I had seen. I have no idea if that is the correct thing to do.
If we have two people storing cars and wheels at the same time, I
believe there are some instances where we would want to check to make
sure things are stored as we expected and that another connection wasn't
writing something conflicting. But this is hypothetical and outside my
current expertise.

Garret

[Garret Wilson]

On 12/9/2014 7:52 AM, Witold Szczerba wrote:

...

So now that this SQL statement is finished and executed, jOOQ will "close"
the connection, which normally would return it to the pool. Does TxManager
step in and keep the connection from going back into the pool?

Yes, it does. Connection does not go back to pool yet.

9. So now let's start storing the car:

       CarRecord carRecord = dslTransaction.newRecord(CAR);
       ...
       carRecord.store();

Again, where does jOOQ get its connection? Does the managed data source
ensure that it gets the same connection as before? After the transaction is
over, does the manage data source simply pass the connection requests (that
are not part of any transaction) on to the underlying data source? If this
is how it all works, then I suppose it's starting to make sense.

As I said in previous posts, jOOQ will get the same connection each
time within TxManager transaction. It won't close, commit, rollback or
give it back to the pool until TxManager say so.

OK, Witold, that makes sense and I like it. But just to clear up in my mind how this all works, I wonder about the default jOOQ behavior if I simply have a jOOQ Configuration wrapping a data source (with no Spring and no pooling and no anything else).

Lukas, if I do not use ULTM, and if I manually turn off autocommit on a connection and then issue several jOOQ SQL commands, will jOOQ (via the DataSourceConnectionProvider) request a different connection for each command? (I read that jOOQ will close the connection after execution of each command.) If so, then it would seem that all of the jOOQ commands are using separate connections, because in this scenario I have no connection pool and I don't have ULTM installed.

To simplify my question: with a DSLContext created from a jOOQ configuration backed by a data source, if I manage transactions manually on a connection, what is stopping jOOQ from creating new connections for each command using the default DataSourceConnectionProvider?

(I am beginning to think that the answer is, "this won't work, because jOOQ will indeed request a new connection for each command. I suspect it only works in our application because the developer is passing around a JDBC connection instance during a transaction, and forming a DSLContext via DSL.using(connection) from the transaction connection rather than DSL.using(jooqConfiguration).)

Garret

[Lukas Eder]

Garret,

Just checking, have you already used a debugger to step through all the jOOQ / ULTM code, etc?

2014-12-10 16:04 GMT+01:00 Garret Wilson <gar...@globalmentor.com>:

On 12/9/2014 7:52 AM, Witold Szczerba wrote:

...

So now that this SQL statement is finished and executed, jOOQ will "close"
the connection, which normally would return it to the pool. Does TxManager
step in and keep the connection from going back into the pool?

Yes, it does. Connection does not go back to pool yet.

9. So now let's start storing the car:

       CarRecord carRecord = dslTransaction.newRecord(CAR);
       ...
       carRecord.store();

Again, where does jOOQ get its connection? Does the managed data source
ensure that it gets the same connection as before? After the transaction is
over, does the manage data source simply pass the connection requests (that
are not part of any transaction) on to the underlying data source? If this
is how it all works, then I suppose it's starting to make sense.

As I said in previous posts, jOOQ will get the same connection each
time within TxManager transaction. It won't close, commit, rollback or
give it back to the pool until TxManager say so.

OK, Witold, that makes sense and I like it. But just to clear up in my mind how this all works, I wonder about the default jOOQ behavior if I simply have a jOOQ Configuration wrapping a data source (with no Spring and no pooling and no anything else).

Lukas, if I do not use ULTM, and if I manually turn off autocommit on a connection and then issue several jOOQ SQL commands, will jOOQ (via the DataSourceConnectionProvider) request a different connection for each command?

Where would you turn off autocommit manually, if you use a DataSource? Did you implement your own DataSource?

 

(I read that jOOQ will close the connection after execution of each command.)

Yes. Here:

https://github.com/jOOQ/jOOQ/blob/c6730094b662b228b121a88013e8251a78c28cb6/jOOQ/src/main/java/org/jooq/impl/DataSourceConnectionProvider.java#L96

 

If so, then it would seem that all of the jOOQ commands are using separate connections, because in this scenario I have no connection pool and I don't have ULTM installed.

jOOQ wouldn't know because jOOQ's DataSourceConnectionProvider just takes whatever Connection comes out of the DataSource:

https://github.com/jOOQ/jOOQ/blob/c6730094b662b228b121a88013e8251a78c28cb6/jOOQ/src/main/java/org/jooq/impl/DataSourceConnectionProvider.java#L86

 

To simplify my question: with a DSLContext created from a jOOQ configuration backed by a data source, if I manage transactions manually on a connection,

what is stopping jOOQ from creating new connections for each command using the default DataSourceConnectionProvider?

jOOQ will always ask YOU for a Connection, either because you supply a standalone Connection, or because your DataSource supplies one.

 

(I am beginning to think that the answer is, "this won't work, because jOOQ will indeed request a new connection for each command.

No. jOOQ will request ANY Connection for each command. jOOQ doesn't care how YOU produce that Connection. That's your business.

 

I suspect it only works in our application because the developer is passing around a JDBC connection instance during a transaction, and forming a DSLContext via DSL.using(connection) from the transaction connection rather than DSL.using(jooqConfiguration).)

That will always work, yes.

[Garret Wilson]

On 12/10/2014 2:12 PM, Lukas Eder wrote:

Just checking, have you already used a debugger to step through all the jOOQ / ULTM code, etc?

Lukas, I confess I haven't. Things have been crazy with several projects flying around, and today I'm a little extra lazy because it's my birthday.

Lukas, if I do not use ULTM, and if I manually turn off autocommit on a connection and then issue several jOOQ SQL commands, will jOOQ (via the DataSourceConnectionProvider) request a different connection for each command?

Where would you turn off autocommit manually, if you use a DataSource? Did you implement your own DataSource?

I'm reviewing code of a colleague, and it looks like this:

connection = dslContext.configuration().connectionProvider().acquire();
connection.setAutoCommit(false);

Thereafter the code uses jOOQ to do stuff, but the code gets a DSLContext by:

DSL.using(connection)

So my question was just to make sure I understood things. If the code instead used DSL.using(jooqConfiguration), then it would break the entire transaction, because jOOQ would request a new connection which wouldn't be the one we're working on above.

 

(I am beginning to think that the answer is, "this won't work, because jOOQ will indeed request a new connection for each command.

No. jOOQ will request ANY Connection for each command. jOOQ doesn't care how YOU produce that Connection. That's your business.

But I don't produce the connection. The ConnectionProvider produces the connection, right? And in my example earlier, I indicated "I simply have a jOOQ Configuration wrapping a data source (with no Spring and no pooling and no anything else)", in which case jOOQ uses a DataSourceConnectionProvider, correct? So jOOQ will request a connection for each command, and that will wind up being a new connection from the data source. That's how I understand it anyway.

Thanks, Lukas. I'll be less lazy and step through all this stuff eventually.

Garret

[Lukas Eder]

Hi Garret

2014-12-10 23:19 GMT+01:00 Garret Wilson <gar...@globalmentor.com>:

On 12/10/2014 2:12 PM, Lukas Eder wrote:

Just checking, have you already used a debugger to step through all the jOOQ / ULTM code, etc?

Lukas, I confess I haven't. Things have been crazy with several projects flying around, and today I'm a little extra lazy because it's my birthday.

Happy birthday! ;-)

From my experience, debugging is about 100x more efficient than guessing. The truth is only in the code, and it's Open Source (or "opened source" if you have a commercial subscription). So, I dare say that actual debugging should be the tool of choice for the lazy person (I'm incredibly lazy), because writing E-Mails and expressing abstract concepts in English after the fact is much more work ;-)

Anyway, this is still a useful discussion also for future readers of this thread, and I learned a lot of things for a pending blog post that explains all these concepts very thoroughly...

Lukas, if I do not use ULTM, and if I manually turn off autocommit on a connection and then issue several jOOQ SQL commands, will jOOQ (via the DataSourceConnectionProvider) request a different connection for each command?

Where would you turn off autocommit manually, if you use a DataSource? Did you implement your own DataSource?

I'm reviewing code of a colleague, and it looks like this:

connection = dslContext.configuration().connectionProvider().acquire();
connection.setAutoCommit(false);

Thereafter the code uses jOOQ to do stuff, but the code gets a DSLContext by:

DSL.using(connection)

So my question was just to make sure I understood things. If the code instead used DSL.using(jooqConfiguration), then it would break the entire transaction, because jOOQ would request a new connection which wouldn't be the one we're working on above.

Clearly, this code implements a "whatever works for me right now" strategy :-)

Here's the point: ConnectionProvider is an SPI (http://en.wikipedia.org/wiki/Service_provider_interface), not an "ordinary" API (http://en.wikipedia.org/wiki/Application_programming_interface). This means that you provide an implementation for jOOQ to call (SPI), instead of jOOQ providing an implementation for you to call (API).

Things might still work, but they *feel* quirky, right? Intuition is usually a good guide. When something doesn't "feel" right, it probably isn't.

So, the bottom line is: If you really want to call setAutoCommit() yourself, then don't pass the DataSource to jOOQ. Instead, do this:

try (Connection connection = datasource.getConnection()) {

    connection.setAutoCommit(false);

    try {

        DSL.using(connection) ... // <<-- The only actually interesting code is here

        connection.commit();

    }

    catch (Exception e) {

        try {

            connection.rollback();

        }

        catch (Exception e2) {

            e.addSuppressed(e2);

        }

        throw e;

    }

}

And by now, you'll realise "Jeez, all that code EVERY TIME I want to run a transaction??" - and by that time you probably throw away this solution, and either implement your own ULTM, or use JavaEE, Spring, ULTM, or jOOQ transactions APIs instead, which all do exactly the above, more or less (probably more, e.g. JavaEE, Spring, and jOOQ transaction APIs also implement nested transactions with Savepoints).

So, I know you said that your project is in a bit of a hurry and that you're lazy, etc :-)

But getting things right up front is the best way to be truly lazy afterwards. Getting them to work as quickly as possible is the best way to spend the rest of the project fixing funny infrastructure issues all the time.

(I am beginning to think that the answer is, "this won't work, because jOOQ will indeed request a new connection for each command.

No. jOOQ will request ANY Connection for each command. jOOQ doesn't care how YOU produce that Connection. That's your business.

But I don't produce the connection. The ConnectionProvider produces the connection, right? And in my example earlier, I indicated "I simply have a jOOQ Configuration wrapping a data source (with no Spring and no pooling and no anything else)", in which case jOOQ uses a DataSourceConnectionProvider, correct? So jOOQ will request a connection for each command, and that will wind up being a new connection from the data source. That's how I understand it anyway.

Well, I still don't know how you created that DataSource and I thus don't know why you're assuming that it is creating a new Connection every time for jOOQ. It might be. But that's the DataSource's responsibility, not jOOQ's. So again, how *did* you implement that DataSource? (And why don't you use some connection pool?)

[Garret Wilson]

On 12/11/2014 7:15 AM, Lukas Eder wrote:

...

So, I know you said that your project is in a bit of a hurry and that you're lazy, etc :-)

Yes, the project is in too much of a hurry and it's understaffed, etc. But one point I think you missed is the part where I mentioned, "I'm reviewing code of a colleague". :) (In fact this isn't even my part of the project; I merely introduced jOOQ and then handed it over to my colleague. Now I'm doing a code review.)

...

Well, I still don't know how you created that DataSource and I thus don't know why you're assuming that it is creating a new Connection every time for jOOQ. It might be. But that's the DataSource's responsibility, not jOOQ's. So again, how *did* you implement that DataSource? (And why don't you use some connection pool?)

The other part you may have missed is that the exact configuration is hypothetical, removing all other variables (such as connection pooling, etc.) just so that I can understand how jOOQ works and fully evaluate my colleague's code.

The main point I was trying to make is the following: the only way my colleague's code works with jOOQ after setting autocommit manually on a manually-retrieved connection, is that my colleague's code passes around the connection and forms new DSLContext instances directly from that connection. If this code were to instead create new DSLContext instances from the jOOQ configuration (which in turn is formed directly from the data source, as per my hypothetical code at the start of this thread), for each command those new DSLContext instances would request anew a connection directly from the data source, which isn't guaranteed to be the connection on which autocommit was set (and which isn't part of the transaction), so that wouldn't work.

So I understand jOOQ (and indeed Spring and JavaEE and ULTM) connection usage much better now from this discussion by considering this hypothetical configuration. Thanks for bearing with me; it was very helpful.

Garret

[Lukas Eder]

2014-12-11 15:14 GMT+01:00 Garret Wilson <gar...@globalmentor.com>:

On 12/11/2014 7:15 AM, Lukas Eder wrote:

...

So, I know you said that your project is in a bit of a hurry and that you're lazy, etc :-)

Yes, the project is in too much of a hurry and it's understaffed, etc. But one point I think you missed is the part where I mentioned, "I'm reviewing code of a colleague". :) (In fact this isn't even my part of the project; I merely introduced jOOQ and then handed it over to my colleague. Now I'm doing a code review.)

Yeah, I got that part and I smirked. It's always "them colleagues", right?

http://www.osnews.com/story/19266/WTFs_m

;-)

 

...

Well, I still don't know how you created that DataSource and I thus don't know why you're assuming that it is creating a new Connection every time for jOOQ. It might be. But that's the DataSource's responsibility, not jOOQ's. So again, how *did* you implement that DataSource? (And why don't you use some connection pool?)

The other part you may have missed is that the exact configuration is hypothetical, removing all other variables (such as connection pooling, etc.) just so that I can understand how jOOQ works and fully evaluate my colleague's code.

The main point I was trying to make is the following: the only way my colleague's code works with jOOQ after setting autocommit manually on a manually-retrieved connection, is that my colleague's code passes around the connection and forms new DSLContext instances directly from that connection.

Correct. But then, there's no real point in having that original Configuration anymore.

 

If this code were to instead create new DSLContext instances from the jOOQ configuration (which in turn is formed directly from the data source, as per my hypothetical code at the start of this thread), for each command those new DSLContext instances would request anew a connection directly from the data source, which isn't guaranteed to be the connection on which autocommit was set (and which isn't part of the transaction), so that wouldn't work.

So I understand jOOQ (and indeed Spring and JavaEE and ULTM) connection usage much better now from this discussion by considering this hypothetical configuration. Thanks for bearing with me; it was very helpful.

You're welcome! 

[Garret Wilson]

On 12/11/2014 12:48 PM, Lukas Eder wrote:

...

Yes, the project is in too much of a hurry and it's understaffed, etc. But one point I think you missed is the part where I mentioned, "I'm reviewing code of a colleague". :) (In fact this isn't even my part of the project; I merely introduced jOOQ and then handed it over to my colleague. Now I'm doing a code review.)

Yeah, I got that part and I smirked. It's always "them colleagues", right?

http://www.osnews.com/story/19266/WTFs_m

;-)

Hahaha! Nice.

G

[Garret Wilson]

On 12/11/2014 7:15 AM, Lukas Eder wrote:
> ...

> Here's the point: ConnectionProvider is an SPI
> (http://en.wikipedia.org/wiki/Service_provider_interface), not an
> "ordinary" API
> (http://en.wikipedia.org/wiki/Application_programming_interface). This
> means that you provide an implementation for jOOQ to call (SPI),
> instead of jOOQ providing an implementation for you to call (API).
>
> Things might still work, but they *feel* quirky, right? Intuition is
> usually a good guide. When something doesn't "feel" right, it probably
> isn't.

To my colleague's defense, he did go ahead and release the connection later:

} finally {
try {
if (connection != null) {
connection.setAutoCommit(originalAutoCommit);
dslContext.configuration().connectionProvider().release(connection);
}
} catch (SQLException e) {
throw new IOException(e.getMessage(), e);
}
}

So basically he was doing what jOOQ would have done, which gave him a
connection to pass around. This was probably the best way to get a
connection without access to the original data source. Of course I wish
he would have passed around something that wasn't so low-level, like
putting a jOOQ transaction in front of it. Now ULTM would provide
something higher-level to pass around, if one really wants to pass
around something.

Garret