Replacing handrolled JDBC DAOs with JOOQ - worth the trouble?

[Cathal King]

I'd be interested in hearing from anyone who has experience of introducing usage of JOOQ http://www.jooq.org/ into their application as a replacement for, say, handrolled JDBC-based DAOs.

Where there any unexpected gotchas that you would've like to have known about up-front?
Is your build process more complex as a result? Are you ok with that? (I'm thinking here about the code generation aspects of JOOQ and what this might imply for version controlling your DB schema through out your environments)
In hindsight do you feel it has been worth the investment?

I'm asking from the perspective of someone who works on relatively humble Enterprise Web App with a significant amount of JDBC DAO dependent CRUD functionality.
We're about to evolve our DB schema (Oracle) quite a bit over the coming months as part of functional changes to the application's user entitlements model.

So, if the benefits are enticing enough, this might be an opportune time to leverage a tool such as JOOQ as part of that effort.

[Lukas Eder]

My answer is obviously biased as I work for Data Geekery, the company behind jOOQ. We've helped a couple of clients migrate their JDBC-based applications to jOOQ - none of them have ever looked back, especially when using Oracle. The main advantages as you've already noticed are:

Being able to version control the generated representation of the database schema. The jOOQ code generator produces a kind of "snapshot" of your schema, which many jOOQ users like to check into version control. It is very easy to see what version of the schema (with individual tables, columns) corresponded to what version of the client code

The same feature allows you to track changes and their implications very easily at compile time, rather than at run time. When you evolve your database schema, you'll immediately notice what parts of your code needs to be adapted as columns have been added / removed, or as relations have gone from 1:1 to 1:N or even to M:N. This is particularly useful for your coming months, as you will be evolving your DB schema heavily. It's just much easier to keep the Java client code in sync when there's something for the compiler to compile

If you're using stored procedures, integrating them into your SQL statements type safely, or calling them standalone from your Java code becomes much easier. The same is true for Oracle AQ, or OBJECT and TABLE types

Regarding the very interesting question of building and version controlling generated source code, we've written a post on the subject:

- http://blog.jooq.org/2014/09/08/look-no-further-the-final-answer-to-where-to-put-generated-code/

The post is also based on this discussion on the jOOQ User Group, including valuable advice:

- https://groups.google.com/forum/#!msg/jooq-user/kQO757qJPbE/UszW4aUODdQJ

I'm very happy to also respond to other, particular questions that you may have.

[Cathal King]

Lukas, thanks for the response. That's given me some food for thought.

[Lukas Eder]

Great, Cathal. Let me know if you have any further questions - I will be more than happy to help