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:
MULTISET
and ROW
nested collections and recordsjOOQ'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.
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.