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.
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: