DOCSEARCHbyAlgolia
Submit Docs

jOOQ Documentation

repository·main·Indexed 27 days ago

https://github.com/jooq/jooq

A type-safe SQL library for Java providing an internal DSL for constructing queries and a source code generator to map database schemas to Java code. It supports 30+ RDBMS dialects, Kotlin, Scala, and Reactive support via R2DBC, featuring advanced SQL capabilities like the MULTISET operator for nested collections.

Tokens
693
Snippets
1
Records
3
Agent score
42%

What's inside jOOQ

  1. Overview of jOOQ

    main

    jOOQ is an internal DSL and source code generator that models the SQL language as a type-safe Java API. It helps developers write better SQL by providing compile-time checks for syntax and schema metadata, preventing errors like identifier typos and data type mismatches.

    Key features include:

    • Source Code Generation: Generates a type-safe API based on your database schema.
    • DSL API: Provides a type-safe way to construct embedded and dynamic SQL.
    • Advanced SQL Features: Supports MULTISET and ROW for nested collections, implicit joins, and DDL/DML statements.
    • Extensive Support: Dialect agnosticism for 30+ RDBMS, Kotlin and Scala support, and Reactive support via R2DBC.

  3. Fetch nested collections using the MULTISET operator

    main

    jOOQ allows you to fetch nested collections (like a list of actors for a film) in a single, type-safe query using the multiset operator. This operator can be supported natively by your database or emulated using SQL/JSON or SQL/XML.

    When using multiset, you can leverage:

    • Implicit path-based joins: Simplify navigation of foreign key relationships.
    • Implicit correlation: Avoid repetitive predicates in subqueries.
    • Ad-hoc conversion: Use .convertFrom() with mapping() to transform structural Record types directly into custom DTOs (Data Transfer Objects).
    record Actor(String firstName, String lastName) {}
record Film(
  String title,
  List<Actor> actors,
  List<String> categories
) {}

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));