TL;DR — We were using JOOQ in the last year or so and think to move to JDBI. This post has some thoughts about it.
It’s amazing that in the Java ecosystem there are so many options for SQL DB access. The ecosystem is very rich from one side, but surprisingly no single framework became the standard. Well, maybe hibernate did, but it’s very controversial and I prefer to avoid it.
Actually, if you’re using Spring there is Spring Data. But we are not using Spring so will not use that.
The first option comes to mind is using plain JDBC. I tried it in some of the projects. It is mostly ok, but quite “low level”. As an evidence to that is the fact that there are so many frameworks on top of it. But if you don’t have an experience with DB access in Java I recommend this simple “no-magic” approach that JDBC takes.
A year ago we chose JOOQ because:
- It has nice type safety.
- It’s pretty similar to SQL.
- It’s popular.
- It has decent documentation.
- It has good support by Lukas Eder — The guy is very responsive.
The last bullet is something we learned along the way.
In our case JOOQ also comes with few drawbacks:
- It’s still not SQL, and sometimes it wasn’t obvious how to translate queries. Especially if queries are more complex. See for example: https://stackoverflow.com/questions/65129169/updating-table-rows-in-postgres-using-subquery-with-jooq
- We are using the code-gen feature. We also use flyway and our setup is not ideal. We need flyway to update the schema (for example by commit) and only then we run the code gen to create another commit with the generated JOOQ classes. This is quite cumbersome. I guess it’s possible to automate it better than how we did it with some effort, but it’s not that obvious.
So for the reasons above we thought to move to JDBI.
JDBI is even more similar to SQL than JOOQ. It’s SQL other than some placeholders and syntactic sugars.
From the first impression it looks like it have the following drawbacks:
- It’s less popular than JOOQ. For example we need JTS Geometry binding extension. JOOQ has extension for that but JDBI doesn’t.
- The docs are not always that clear. It has good docs for the simple use cases, but some stuff is not obvious.
- The API has 2 flavors: Fluent and Declarative. Some examples and code snippets are not for the kind we’re using. We started with the Fluent API as it is easier to migrate to it from JOOQ because it’s more similar. But many examples uses the Declarative style.
- It’s open source, with open source kind of (best effort) support. Some questions on StackOverflow remains unanswered.
Final Thoughts
I don’t think there is a clear cut here. It’s mostly a matter of personal taste. Some developers will see the type safety as a big advantage. In our case we have good tests coverage of integration tests that are using db.
Both frameworks allows pretty easily to fallback to plain JDBC as well, and we sometimes use that approach.
