jOOQ itself isn’t opinionated and tries to accommodate all possible use-cases. Every jOOQ Query can render its SQL using Query.getSQL(), and produce bind values with Query.getBindValues(), so in principle, executing jOOQ queries elsewhere is totally possible.

Use-Cases for Executing jOOQ Queries

Fetching Entities with Dynamic Queries in JPA Applications

If you use jOOQ only for 2-3 dynamic queries in an otherwise JPA based application, and you need to fetch entities (not DTOs) with those queries, executing jOOQ queries can be extremely beneficial. For example, you can fetch a nested SQL collection into a Java Map in a type-safe manner.

Slow Migration from JdbcTemplate to jOOQ

On the other hand, using jOOQ solely for the purpose of slowly migrating from JdbcTemplate to jOOQ is not a good use-case for extracting SQL from jOOQ. This approach may not provide the expected benefits and can lead to unnecessary complexity in the codebase.

Type Safety: A Key Benefit of jOOQ

One of jOOQ’s main benefits is its type safety both when writing SQL as well as when maintaining it. jOOQ’s DSL and code generation contribute to achieving this, but there's more to it. Type safety can also be leveraged when executing queries with jOOQ.
For example, you can write a type-safe query that fetches a nested SQL collection into a Java Map. The query itself is type safe, and so is the final fetch call. Trying to achieve this level of type safety with other execution engines, such as JDBC or JPA's DTO fetching APIs, is challenging due to the generic nature of ResultSet content.

Reactive Querying Example

jOOQ's type safety shines when working with reactive queries using R2DBC. Instead of executing the query directly on R2DBC, you can embed the query in a reactor Flux using jOOQ. This allows for automatic execution and mapping of the query.

Mapping and Data Type Conversion

jOOQ provides various options for mapping a jOOQ Record or Record[N] type to a user type. These options include the DefaultRecordMapper, which uses reflection, and the type-safe record mapper that maps Record[N] types onto constructor references.
Additionally, jOOQ offers data type conversion via the Converter and Binding SPIs. These mechanisms enable auto-conversion between database types and user types. Automatic type conversion is beneficial when mapping SQL/XML or SQL/JSON to Java objects.

Execution Emulations

jOOQ emulates certain SQL features at runtime, such as MULTISET (nested collections) and ROW (nested records). These features are not usable outside of jOOQ. The generated SQL for these queries encodes nested collections and records using SQL/XML or SQL/JSON, providing type safety and ease of use.
Another useful execution feature is the Batched Connection, which emulates batching of consecutive SQL statements automatically, without requiring manual intervention.

User Defined Types

jOOQ supports working with user defined types on both the server and client sides. The data type bindings for user defined types are built-in in jOOQ and work out of the box. This allows for fetching user defined types in a type-safe manner, making jOOQ a convenient choice for working with such types.

Stored Procedures

Binding OUT or IN OUT parameters can be cumbersome when using lower-level JDBC, R2DBC, or JPA APIs. With jOOQ, executing stored procedure calls becomes much simpler. The jOOQ API provides an explicit and type-safe way to execute stored procedures and retrieve their results.

Fetching Identity Values

Fetching identity values across different SQL dialects and JDBC drivers can be challenging and error-prone. jOOQ eases this pain by providing native support for specific SQL dialects. In cases where native support is not available, jOOQ handles the complexity internally, allowing for seamless retrieval of identity values.

Simple CRUD Operations

jOOQ simplifies CRUD (Create, Read, Update, Delete) operations when compared to lower-level APIs like JdbcTemplate or JDBC. Using the jOOQ UpdatableRecord API, you can perform CRUD operations easily and efficiently. Manual DML has its place for bulk data processing, but for basic CRUD operations, jOOQ provides a more streamlined approach.

Import and Export of Data

jOOQ supports the import and export of data from/to various data formats, including XML, CSV, JSON, and text. This functionality is built-in in jOOQ, making it convenient to work with different data formats without the need for additional libraries or complex code.

Better Defaults

Compared to JDBC, jOOQ implements better defaults for most developers. By default, jOOQ eagerly fetches query results into memory, allowing for faster closing of resources. However, lazy streaming processing of data is also supported when needed. jOOQ's better defaults help reduce boilerplate code and improve developer productivity.
 

Executing jOOQ queries with jOOQ offers numerous benefits, including type safety, reactive querying, mapping and data type conversion, execution emulations, support for user defined types and stored procedures, simplified CRUD operations, import and export of data, and better defaults. By leveraging these features, you can enhance the efficiency and readability of your database queries while maintaining a high level of code quality.