Skip to content
/ jOOQ Public
forked from jOOQ/jOOQ

jOOQ is the best way to write SQL in Java

License

Notifications You must be signed in to change notification settings

seeq12/jOOQ

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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:

Examples

Typesafe, embedded SQL

jOOQ's main feature is typesafe, embedded SQL, allowing for IDE auto completion of SQL syntax...

image

... as well as of schema meta data:

image

This allows for preventing errors of various types, including typos of identifiers:

image

Or data type mismatches:

image

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:

multiset

And here you see the nested result in action from the logs:

execute

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.

About

jOOQ is the best way to write SQL in Java

Resources

License

Security policy

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages

  • Java 98.2%
  • Kotlin 0.5%
  • PLpgSQL 0.5%
  • Xtend 0.5%
  • Scala 0.3%
  • HTML 0.0%