JOOQ vs JDBC+tests

Question

What advantage have JOOQ over JDBC+tests?

In JDBC you can write SQL queries direct in code, with JOOQ we calls methods, so JOOQ is by default more slow.

In JOOQ is harder to do mistakes but not impossible. This mistakes can be caught in tests, with JOOQ you also should write these tests, so, no advantage here for JOOQ.

Answer 1

I completely agree with you. It's always better to have tests, regardless if you're using a "dynamic language" (SQL as an external DSL, e.g. JDBC) or a "static language" (SQL as an internal DSL, e.g. jOOQ)

But there's much more than that:

You seem to have scratched only the surface of what jOOQ can do for you. Sure, type safe, embedded SQL is a great feature, but once you have that, you get for free (list is far from exhaustive):

• Active records: With JDBC, you're back to spelling out each individual boring INSERT, UPDATE, DELETE statement manually. jOOQ's UpdatableRecord greatly simplifies this, while offering things like:
  • RecordListener for record lifecycle management
  • Optimistic locking
  • Batching of inserts, updates, deletes
• Dynamic SQL is very easy. Instead of that JDBC string concatenation mess that you'd be getting otherwise, you can just dynamically add clauses to your SQL statements.
• Multi tenancy: You can easily switch schema references and/or table references at runtime in order to run the same query against a different schema.
• Standardisation: The same jOOQ query runs on up to 21 RDBMS because the jOOQ API standardises the generated SQL. This can be seen in the jOOQ manual's section about the LIMIT clause, for instance - one of SQL's most poorly standardised clauses
• Query Lifecycle: There's a simple SPI called ExecuteListener that allows you to hook into the various JDBC interaction steps, including:
  • SQL generation
  • Prepared statement creation
  • Variable binding
  • Execution
  • Result fetching
  • Exceptions
• SQL transformation: The VisitListener SPI allows you to intercept the SQL generation at any arbitrary position in your query expression tree. This can be very useful, e.g. to implement powerful things like row level security.
• Stored procedures: These are rather tedious to bind to with JDBC, especially if you're using more advanced features like:
  • Oracle's TABLE and OBJECT types (imagine implementing SQLData et al.)
  • Oracle's PL/SQL types
  • Implicit cursors
  • Table-valued functions

Of course, you get the compile-time type safety that you've mentioned (and IDE autocompletion) for free. And if you don't want to go all in on the internal DSL that jOOQ is offering, you can still just use plain SQL and then use jOOQ's API in a non-type safe way (which still has tons of features):

// Just one example: CSV exports
String csvExport =
ctx.fetch("SELECT * FROM my_table WHERE id = ?", 3)
   .formatCSV();

TL;DR:

• JDBC is a wire protocol abstraction API
• jOOQ is a SQL API

Disclaimer:

(Of course, this answer is biased as I work for the company behind jOOQ)