jDBI and jOOQ

[Lukas Eder]

Hello,

That's a nice litte library with some good ideas around using
annotations in method declarations to declare bind variables. It goes
a lot into a similar direction as JPA, but it looks much more light
weight. Have you considered making a fluent API for SQL itself?

How do you handle transactions, internally, in your handles? Is your
library XA transaction-safe?

How would you compare your tool with jOOQ (of which I am the
developer):
http://www.jooq.org

Do you see common functionality, for instance features that jOOQ could
use from jDBI? I personally like the .fold() method that can be called
on a query. Especially, with Java 8's lambda project, this might turn
out into something very neat!

Keep up the good work
Lukas

[Brian McCallister]

On Monday, July 4, 2011 at 12:07 AM, Lukas Eder wrote:

Hello,

That's a nice litte library with some good ideas around using
annotations in method declarations to declare bind variables. It goes
a lot into a similar direction as JPA, but it looks much more light
weight. Have you considered making a fluent API for SQL itself?

I wrote one many years ago, and have used a couple. When you need dynamically generated SQL they really shine. The way we've done dynamic SQL most recently has been via StringTemplate, which works well, but you get three languages (and no debugger for two of them). In most cases, I have found the SQL in a given application to be static, so it isn't as big a deal.

I really like the approach you took with jOOQ (well, from docs, haven't had a chance to play with it yet) -- code generation and typed operations are a Good Thing (mostly) -- particularly as very few folks have exhaustive integration tests, so breaking the build on incompat schema changes is a Good Thing.

 

I have found, a couple times, that these style APIs work really well for common cases, but get hairy in the weird ones. I think of it as programmatically generating the AST for the statements, which is pleasant, but gets into trouble when the AST the library supports is different from the one the database engine supports -- generally for non-standard SQL constructs. Building an API which supports even just the top four, MySQL, Oracle, PostgreSQL, and SQL Server, naturally is a hard design problem. That said, I really want to see how you addressed it, as my skim of the jOOQ docs left me wanting to use your library!

How do you handle transactions, internally, in your handles? Is your
library XA transaction-safe?

It uses a pluggable transaction handler. For the fluent API, usage looks like: https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/TestTransactions.java 

The transaction handler just looks like: https://github.com/brianm/jdbi/blob/master/src/main/java/org/skife/jdbi/v2/tweak/TransactionHandler.java

There are only two implementations ( https://github.com/brianm/jdbi/tree/master/src/main/java/org/skife/jdbi/v2/tweak/transactions ), ones where the container is handling it underneath you, and one for local (setAutocommit(...)). There used to be one for JTA integration (in a rar, etc) but I removed it a while back as I didn't trust it -- I don't use JTA enough that I think the code is solid, and don't want to imply otherwise. 

How would you compare your tool with jOOQ (of which I am the
developer):
http://www.jooq.org

The seem to approach similar problems, but with very different solutions. I'd be curious to find out how the two different libraries are used, most often. For example, most of the folks I know who use JDBI tend to externalize their SQL, generally in StringTemplate files, as the library includes a nice mechanism for doing that, and the templating can be handy. This is pretty much the inverse of jOOQ, where you explicitly build your SQL. The externalization is usually done for things like easy grepping, finding all the external sql, etc. No small part is also because Java lacks heredocs :-)

Some folks have started to use the new sql object API, http://skife.org/jdbi/java/library/sql/2011/03/16/jdbi-sql-objects.html which is mostly designed to get rid of boilerplate almost everyone who used it created (DAO objects which execute one statement per method invocation).

 

Do you see common functionality, for instance features that jOOQ could
use from jDBI? I personally like the .fold() method that can be called
on a query. Especially, with Java 8's lambda project, this might turn
out into something very neat!

I've wanted, for a a couple years anyway, to be able to support non-string sql, ala:

    handle.createQuery(new StatementBuilder()...).bind(...)...

to allow for cleaner lookup code, and to allow for AST-oriented statement building, as in jOOQ.

Speaking to fold() -- it is super handy, I just wish type parameterization in Java made it less verbose to use :-(

One thing JDBI does which I might change (and probably will try to in 3.0, someday) is to make each of the intermediate objects in the fluent API be immutable. As you invoke the next, you get a new object back, with the state needed for whatever it is doing. While the mutable-statement thing has been handy in places, it has always bugged the heck out of me. Moreover, doing a proper builder would let you do partial objects and keep them around, having the base query, still lacking its binds, just hang out and be used to create actual instances. This works pretty backwards from the "it all starts at the connection" model in JDBI though, as you would need to have statements detached from the connection to have them be long lived objects, and such, so I don't know if I could make an API that made the change a net win, but it feels right to me.

Of course, the current direction JDBI is headed, with the sql object api, moots this from a user perspective -- you never see the statement building, just the dao interface.

-Brian

[Lukas Eder]

Hello Brian,

Thanks a lot for your comprehensive answer!

> [...] but gets into trouble when the AST the library supports is different from

> the one the database engine supports -- generally for non-standard SQL
> constructs. Building an API which supports even just the top four, MySQL,
> Oracle, PostgreSQL, and SQL Server, naturally is a hard design problem. That
> said, I really want to see how you addressed it, as my skim of the jOOQ docs
> left me wanting to use your library!

The essence of jOOQ's internals is documented here:
http://sourceforge.net/apps/trac/jooq/wiki/Manual/JOOQ/QueryPart

With the composite-pattern QueryPart hierarchy, "standardising" SQL
into a single version of AST elements has worked out quite nicely,
even for very complex statements such as the SQL:2003 MERGE statement,
or the non-standard LIMIT .. OFFSET clause, which has to be simulated
using ROW_NUMBER() window functions and nested selects by some major
RDBMS. But it works! :-)

> There used to be one for JTA integration (in a rar,
> etc) but I removed it a while back as I didn't trust it -- I don't use JTA
> enough that I think the code is solid, and don't want to imply otherwise.

That's the problem with the JTA. It's implementations are very very
complex. I guess leaving that work to a container or maybe spring is
the most reasonable idea...

> I'd be curious to find out how the two different libraries are used, most
> often. For example, most of the folks I know who use JDBI tend to
> externalize their SQL, generally in StringTemplate files, as the library
> includes a nice mechanism for doing that, and the templating can be handy.

Yes, that's pretty much the opposite of jOOQ. With this approach,
DBA's can easily patch / fine-tune externalised queries for large and
complex applications. That's the advantage of the string-based
approach. Looks like this is quite simple to achieve with jDBI

> Some folks have started to use the new sql object
> API, http://skife.org/jdbi/java/library/sql/2011/03/16/jdbi-sql-objects.html which
> is mostly designed to get rid of boilerplate almost everyone who used it
> created (DAO objects which execute one statement per method invocation).

True, those use cases need to be adressed. With jOOQ, this can mostly
be done with the Factory.fetch() methods, at line 1836:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/Factory.java

Except that the conditions can be freely adapted. This would make the
jOOQ Factory a universal DAO...

> One thing JDBI does which I might change (and probably will try to in 3.0,
> someday) is to make each of the intermediate objects in the fluent API be
> immutable. As you invoke the next, you get a new object back, with the state
> needed for whatever it is doing.

That's an interesting thought. That would make built objects more
re-usable. For a query builder pattern, this is very useful. Maybe not
so much for a fluent API, where this is irrelevant for client code.
But I can see the use cases for jDBI.

> Moreover, doing a
> proper builder would let you do partial objects and keep them around, having
> the base query, still lacking its binds, just hang out and be used to create
> actual instances.

That makes sense for jDBI, especially considering the fact that your
users externalise their SQL anyway, and have full control over bind
variables. I had thought about that for jOOQ and given up on it.
Unlike jDBI, where binding structures are still flat (indexed from
left to right in a flat string), in jOOQ, bind variables are set at
hierarchical levels in the AST. So injecting them after query creation
is difficult, especially since the QueryPart elements fully abstract
their rendered SQL (see LIMIT .. OFFSET clause), such that proper
indexing cannot be predicted from the API

So I'll be curious about your feedback, should you check out jOOQ.
Thanks for your time, Brian!

Lukas