jOOQ
jOOQ is an internal DSL and source code generator, modelling the SQL language as a type safe Java API to help you write better SQL.
Its main features include:
Secondary features include:
- DAOs
- Data export and import
- Data type conversion
- DDL statement support
- DML statement support
- Diagnostics
- Dialect agnosticity for 30+ RDBMS
- Embedded types
- Formatting and pretty printing
- Implicit joins
- Kotlin support
- Mapping
- Meta data API
- Mocking API for JDBC
- Model API for use in traversal and replacement
MULTISETandROWnested collections and records- Multitenancy
- Parser (and translator)
- Pattern based transformation
- Plain SQL templating
- Policies
- Procedural logic API
- Reactive support via R2DBC
- Readonly columns
- Scala support
- Schema diff
- SQL transformation
- SQL translation
- Stored procedure support
- Transaction API
- UpdatableRecords for simplified CRUD, with opt-in optimistic locking
- And much more
Examples
Typesafe, embedded SQL
jOOQ's main feature is typesafe, embedded SQL, allowing for IDE auto completion of SQL syntax...
... as well as of schema meta data:
This allows for preventing errors of various types, including typos of identifiers:
Or data type mismatches:
The examples are from the code generation blog post.
A more powerful example using nested collections
For many more examples, please have a look at the demo. A key example showing jOOQ's various strengths is from the MULTISET operator announcement blog post:
Given these target DTOs:
record Actor(String firstName, String lastName) {}
record Film(
String title,
List<Actor> actors,
List<String> categories
) {}
You can now write the following query to fetch films, their nested actors and their nested categorise in a single, type safe query:
List<Film> result =
dsl.select(
FILM.TITLE,
multiset(
select(
FILM.actor().FIRST_NAME,
FILM.actor().LAST_NAME)
.from(FILM.actor())
).as("actors").convertFrom(r -> r.map(mapping(Actor::new))),
multiset(
select(FILM.category().NAME)
.from(FILM.category())
).as("categories").convertFrom(r -> r.map(Record1::value1))
)
.from(FILM)
.orderBy(FILM.TITLE)
.fetch(mapping(Film::new));
The query is completely type safe. Change a column type, name, or the target DTO, and it will stop compiling! Trust only your own eyes:
And here you see the nested result in action from the logs:
How does it work? Look at this annotated example:
List<Film> result =
dsl.select(
FILM.TITLE,
// MULTISET is a standard SQL operator that allows for nesting collections
// directly in SQL. It is either
// - supported natively
// - emulated using SQL/JSON or SQL/XML
multiset(
// Implicit path based joins allow for simpler navigation of foreign
// key relationships.
select(
FILM.actor().FIRST_NAME,
FILM.actor().LAST_NAME)
// Implicit correlation to outer queries allows for avoiding repetitive
// writing of predicates.
.from(FILM.actor())
// Ad-hoc conversion allows for mapping structural Record2<String, String>
// types to your custom DTO using constructor references
).as("actors").convertFrom(r -> r.map(mapping(Actor::new))),
multiset(
select(FILM.category().NAME)
.from(FILM.category())
).as("categories").convertFrom(r -> r.map(Record1::value1))
)
.from(FILM)
.orderBy(FILM.TITLE)
.fetch(mapping(Film::new));
The generated SQL query might look like this, in PostgreSQL:
select
film.title,
(
select coalesce(
jsonb_agg(jsonb_build_object(
'first_name', t.first_name,
'last_name', t.last_name
)),
jsonb_build_array()
)
from (
select
alias_78509018.first_name,
alias_78509018.last_name
from (
film_actor
join actor as alias_78509018
on film_actor.actor_id = alias_78509018.actor_id
)
where film_actor.film_id = film.film_id
) as t
) as actors,
(
select coalesce(
jsonb_agg(jsonb_build_object('name', t.name)),
jsonb_build_array()
)
from (
select alias_130639425.name
from (
film_category
join category as alias_130639425
on film_category.category_id = alias_130639425.category_id
)
where film_category.film_id = film.film_id
) as t
) as categories
from film
order by film.title
This particular example is explained more in detail in the MULTISET operator announcement blog post. For many more examples, please have a look at the demo.