On the jOOQ user group, we’re often being asked how to perform transaction management with jOOQ. And we have an easy answer ready: You don’t do that with jOOQ. You choose your favourite transaction management API, be it:
- JDBC
- Spring
- JEE JTA (e.g. as supported by Weblogic)
- Bitronix TM
- Hibernate
And the above list is far from being exhaustive. Transaction management is something very delicate, and it certainly should not be imposed by a library whose main purpose is not transaction management, because any such library / framework will provide you with at most a very leaky abstraction of its transaction model. In other words, if you just slightly want to deviate from “the standard” model (e.g. as imposed by Hibernate), you will suffer greatly, as soon as you want to run 2-3 queries outside of Hibernate – e.g. batch or reporting statements through jOOQ.
MyBatis’ Alternative Transaction Management
MyBatis is a SQL templating engine that provides a couple of features on top of alternative templating engines, such as Velocity, or StringTemplate. One of these features built on top of templating is precisely transaction management, as can be seen in the docs.
From what we can read in the docs, it looks as though MyBatis’ transaction managers can be overriden by Spring, for instance. However, it is not easy to see how this is done. In fact, given that MyBatis also solves Connection pooling (for which there are also very viable alternatives, such as c3p0 and DBCP), and mapping (which could be solved more easily with custom transformers, such as offered by Spring’s JdbcTemplate, or jOOQ’s RecordMapper).
As many frameworks, MyBatis tries to solve problems outside its core scope, which is SQL templating. While this may be a good thing as you only rely on a single dependency, it is also quite a lock-in, in case you have a more complex model. In the case of transaction management, we believe that this was not a good idea by MyBatis.
Thoughts from MyBatis users?
I totally agree with your decision. Transaction Managers are very difficult to get them right. That’s why I love Bitronix, it works like a charm, and it works for both DBs and JMSs DataSources. Many years ago I had to implement a JCA addaptor for File based Transactions on top of Apache file transactions and I had to delve into the XA specification labyrinth and it’s just pain.
“Framework composition” is a natural choice: slf4j does logging, spring core does DI and JOOQ makes SQL querying a commodity.
Really looking forward to that article of yours, about the various transaction managers and models! :-)
Actually, I come to realize I might push it forward into my pending article queue. There is so much about Transactions (db, business, jdbc connection, why orm always need to run in a transaction, spring transactions, jee, jms), there is no wonder people get puzzled on this matter.
+1! I think this topic would even deserve a series of blog posts. Or a “Top 10 Things People Misunderstand About Transactions” post, as in xkcd’s Headlines.
I think most user looking for example to integrate with each of these transaction framework.
I like custom framework which gives me maximum control.
Yes, I agree that better examples would be good. We’ll be improving the jOOQ+Spring examples and perhaps even showing how to integrate jOOQ with JTA in WLS or other JEE containers.
I guess it’s not black or white like that…
There is another (little advertised) solution for Transaction management with mybatis that relies on Threadlocals,
As an ex-mybatis user, I found it great that the library gave me a decent API to do both Transaction management and connection pooling which are in fact unavoidable subjects in any database oriented application. Their goal was to ease the use of JDBC, and in this regard, they did an excellent job.
Have a look how many lines you need to get a connection, try-commit-catch-rollback-rethrow-close (and should I include trycatchinrollback?) to handle a simple transaction in pure JDBC and how easy it is with mybatis compared to that.
Oh yes, you don’t have to do that yourself, you just need to delegate that to “insert framework name here”, design your app the way it requires you to, and ship another black box with you app. Mybatis offered me simple solutions that solved common problems without the need of voodoo magic and I think I had more success with that approach than many people using more complicated stacks.
Things often go wrong when you overcomplicate things or use stuff you don’t understand instead of taking simpler routes. Like illustrated in that old satiric article.
http://discuss.joelonsoftware.com/default.asp?joel.3.219431.12
Joel’s rants never go out of fashion :-) In a way, following Joel’s arguments, you’re advertising MyBatis – the hammer – as a hammer-saw-level-nailbox.
I think our point might have come across a bit too black/whitish. We favour easy solutions ourselves, also for future versions of jOOQ, which might provide default transactional behaviour (and maybe even connection pooling). But the API design should be done in a way that the default is very easy to override. In fact, it should be very clear that it is a default. Alternatively, there could be optional extensions that ship with jOOQ, providing example behaviour. The point here is that a tool should have a single problem focus and not rely on default/example solutions to other problems. In other words, jOOQ is just a hammer. We can ship the hammer with a sample saw, though.
From the documentation, it looks like MyBatis’ “defaults” are not that easy to override…
It can be as easy as calling public SqlSessionManager openSession (Connection connection) and then it is somewhat similar to Jooq Factory. An API with only a connection as dependency.
You need that big Session manager object because something has to read and understand all these query mapping files you created, cache all that stuff obtained via reflection…
Also, it is good to remember that at the time iBatis was created, there was pure JDBC, one or two big ORMs and really not much in between. According to my personal taste, I also think jooq is a better solution, but it came years later…
Thanks for the insight.
That is another hot topic :-) I’ve never found a good solution to caching these things. It is easy to get a cache working. But what if users use OSGi or other complex class loaders…?
I wish I had created jOOQ 8 years ago, right with Java 5 and generics…
Hi Lukas.
Sorry, I am a bit late.
I would not say that MyBatis provides a transaction manager. That is not the intention for sure.
MyBatis wraps Jdbc and tries to expose the simplest possible API. If you have a look at SqlSession you will just find a few methods to open, commit and close session and to query or update the database.
Now, If you have a look at Spring’s JdbcTemplate you will see that its API is way more complex. That is what you have to pay if you go a bit down to Jdbc. I would not say this is bad or good, I suppose there are 100 JdbcTemplate users for each MyBatis user so probably their approach is great. MyBatis just does it different.
MyBatis holds some data in its own session (SqlSession). Basically:
– a jdbc connection
– cached data (1st level, 2nd level pending buffer)
– batches buffer
There is not transaction managing in the way that a container manages the transaction like Spring. An SqlSession#commit() just passes the commit to Jdbc, that is all.
Note that MyBatis also supports mapping statements to interface methods (“mappers”) so you can for inject a mapper to an object and call SQL statements without using the MyBatis API at all: http://mybatis.github.io/cdi/getting-started.html
This is the simplest API I can imagine: there is no API! But I am afraid you cannot do this while exposing Jdbc stuff out.
Stéphane talked about the SqlSessionManager object. This object has a history. It is indeed a basic transaction manager object that keeps SqlSessions in thread locals. This object comes from iBATIS 2.x that had a lot of transactional handing stuff. MyBatis 3 got rid of all it and this object was added later on to MyBatis code base moslty because it was useful for the devs to port their existing code from IB2 to MB3. It is completely separated from the main API and… it is not documented :) (though we promise we will not remove it!! ;) )
Hi Eduardo,
Thanks for the nice explanations. It’s never late! :-)
For the upcoming jOOQ 3.4, we’re also going to be implementing a simple transaction API with a couple of service provider interfaces that allow for overriding the semantics of such transactions. The default SPI implementation will also forward transaction calls to JDBC, but we would like the same API to work also with Spring-TX, JTA, Guice, or whatever.
We’ll certainly have another look at your implementation, then.
Cheers
Lukas