Introduction

Authors: Michael Clausen, ChatGPT — your friendly AI workshop buddy

Our goal is to give you a clear, practical understanding of jOOQ’s power — focusing purely on the database layer: schema design, migrations, and writing clean, type-safe queries. jOOQ turns your database schema into a fluent, type-safe API — so you can write real SQL in Java, with compiler help, autocomplete, and readability.

While jOOQ integrates well with frameworks like Spring and Quarkus, we’ll keep this presentation framework-free to keep the focus sharp. No web layer, no REST API — just the database, migrations, and clean query code, executed and verified through automated tests.

Let’s get started and don’t forget to clone the repository on github.

Why jOOQ?

As mentioned, jOOQ lets you write SQL in Java with full type safety and IDE support, bridging the gap between relational databases and object-oriented code, and we are going through a couple of topics where jOOQ shines:

Domain Modeling

Compared to traditional ORMs, jOOQ offers a lot more flexibility when modeling your domain. With classical ORMs, it’s often tricky to adapt the data model without touching core entities — things like projections, custom views, or handling relationships (eager/lazy loading) can get messy fast.

jOOQ takes a different approach: you can easily build tailored models using DAOs, views, or plain SQL, all without interfering with your main schema or domain logic. This makes it much easier to keep things clean, modular, and focused on your actual use cases.

Code generation

jOOQ generates Java code directly from your database schema.

Tables, columns, types — all become type-safe, IDE-friendly code with no reflection or annotation magic involved. Column names are available as constants, so you don’t have to remember or hardcode them. Your schema becomes compile-time-verified code, and the compiler helps catch broken queries early.

It also aligns perfectly with the build-time principle: fast startup, less runtime overhead.

Writing SQL in JDBC often looks like this:

String title = "Mastering SQL";

String sql = "SELECT * FROM book WHERE title = ?";
PreparedStatement stmt = conn.prepareStatement (sql);
stmt.setString (1, title);
ResultSet rs = stmt.executeQuery();

It’s raw, fragile, just a string — and the compiler can’t help you. With jOOQ, you write:

String title = "Mastering SQL";

ctx.selectFrom (BOOK)
   .where      (BOOK.TITLE.eq (title))
   .fetch ();

Performance

Think of the database as more than just storage — it’s a processing engine.

With jOOQ, you can push logic closer to the data by leveraging SQL, views, stored procedures, and database functions — instead of re-implementing the same logic in Java. This often leads to better performance, fewer round-trips, and reduced bandwidth usage, since you control exactly what data is returned and how it’s shaped.

Rather than pulling too much into memory and filtering in Java, let the database do what it’s optimized for.

Extensibility

jOOQ is highly extensible, especially when used with Kotlin.

Instead of abstracting away SQL, jOOQ embraces dialect-specific features — and with Kotlin’s language features like extension functions and DSLs, it becomes easy to build reusable, composable query logic.

You can tailor filters, projections, or even full queries without modifying core components, and still keep full control over SQL and execution. This makes it straightforward to work with vendor-specific functionality while keeping your code clean and modular.

Testability

No CDI context is needed to run the integration tests. All that’s required is a plain DataSource.

Because jOOQ works directly with the database schema, data-driven integration tests become straightforward — not just for tables, but also for views, stored procedures, functions, and other database components. This makes it easy to test real database interactions without the overhead of full application contexts.

@ExtendWith (DataSourceExtension::class)
internal class BookRepositoryJooqTest {

    @Test
    fun `should make books shine again` (datasource: DataSource) {
        val books = BookRepositoryJooq  (datasource)

        ...
    }

}

Comparison Table

Feature JDBC / ORM jOOQ

SQL visibility

Hidden (ORM) or raw strings

Fluent API, real SQL

Type safety

Low

High

Schema mapping

Implicit / annotation-heavy

Generated, explicit, type-safe

IDE support

Weak (strings, annotations)

Strong (Java code, autocomplete)

Domain Modeling

Medium

High

Performance

Medium

High

Debuggability

Low

High (logs actual SQL)

For more detailed information, documentation, and advanced features, check out the official jOOQ website: https://www.jooq.org/

Curious how to pronounce “jOOQ”? You’re not alone! Visit https://www.jooq.org/how-to-pronounce-jooq to hear the official pronunciation.

Schema

Before we dive into the details, let’s take a quick look at how our database schema is managed. Flyway is one popular tool for database migrations — it’s not required for jOOQ to work, but it fits the philosophy well: Your database schema lives in versioned SQL, and jOOQ reflects it into Java code.

Here’s how the build process evolves as you add more structure. jOOQ brings type-safe SQL. Flyway brings versioned, repeatable schema changes. Together, they form a clean pipeline — from migrations to code generation to production.

Phase JDBC jOOQ jOOQ + Flyway

Initialization

✅ Postgres Container

Pre Processing

✅ Flyway Migration

Source Generation

✅ jOOQ Codegen (manual or IDE task)

✅ jOOQ Codegen using Postgres Container

Compilation

✅ 1. Safety net

✅ 1. Safety net

Verification

✅ 1. Safety net

✅ 2. Safety net

✅ 2. Safety net

jOOQ Code Generation in Gradle

To integrate jOOQ smoothly into your Gradle build process, you can use one of the following community plugins.

These plugins help automate jOOQ code generation during your build lifecycle, making it easy to regenerate code after schema changes or Flyway migrations.

We’ve integrated the monosoul/jooq-gradle-plugin as a working example in this presentation.

Recommended Plugins

  • monosoul/jooq-gradle-plugin

    A lightweight, Kotlin-friendly plugin that offers tight integration with jOOQ and Flyway. Ideal for simple setups and CI pipelines.

    plugins {
    id ("dev.monosoul.jooq") version "x.y.z"
}

  • etiennestuder/gradle-jooq-plugin

    A more advanced and flexible plugin that supports detailed jOOQ configuration. Good for projects with custom generation needs.

    plugins {
    id ("nu.studer.jooq") version "x.y.z"
}

Integration Tip

Make sure jOOQ code generation runs after your Flyway migrations. That way, your Java code always reflects the latest schema version.

Domain: A Simple Book Shop

Let’s start with the domain we’re working with.

We’re building queries and logic around a simplified book shop — something familiar enough to focus on tech, not business rules.

Our domain has three core entities:

  • Customers who buy books

  • Books with basic information like title, author, and price

  • Purchases linking customers to books they’ve bought

To give you a feel for the domain, here’s a snapshot of the data we’ll be using throughout the presentation:

Customer

ID NAME EMAIL SINCE

1

Alice Johnson

alice@example.com

2023-02-10T12:43:04Z

2

Bob Smith

bob@example.com

2023-05-21T15:10:32Z

3

Clara Lee

clara@example.com

2024-01-05T08:01:45Z

Book

ID TITLE AUTHOR PRICE

101

SQL for Beginners

Jane Doe

29.99

102

Mastering Python

John Smith

45.00

103

Data Science 101

Emily Stone

39.95

Purchase

ID CUSTOMER BOOK QUANTITY AT

1001

1

101

1

2024-02-15T13:43:31Z

1002

2

102

2

2024-06-01T08:52:12Z

1003

1

103

1

2024-08-10T10:12:11Z

1004

3

101

1

2024-09-05T17:43:43Z

This domain is intentionally small, but it’s expressive enough to explore:

  • Joins (e.g. purchases + books + customers)

  • Aggregations (e.g. total revenue per book)

  • Filters & ordering (e.g. customers who bought most, books sold last month)

  • Schema-driven code generation via jOOQ

It’s also something you can read and understand at a glance — no business logic needed.

From Data to Schema

We’ve just seen the data as it might appear in a table or application.

Let’s now look at how this is defined in the database schema, and how jOOQ can generate Java code from that schema — letting us write type-safe queries directly from the model.

create table customer (
    id          bigint          not null,
    name        text            not null,
    email       text,
    since       timestamptz     not null,

    primary key (id)
)
;

create table book (
    id  	bigint      not null,
    title	text        not null,
    author	text        not null,
    price	numeric,

    primary key (id)
)
;

create index book_title_author_idx on book (title, author)
;

create table purchase (
    id	        bigint          not null,
    customer	bigint          not null,
    book	    bigint          not null,
    quantity	int             not null,
    at      	timestamptz     not null,

    primary key (id),
    foreign key (customer) references customer (id),
    foreign key (book)     references book     (id)
)
;

Views can also be used as we will see in the code.

create view revenue as
    select c.id, c.name, c.email, c.since, sum (p.quantity * b.price) as amount
        from purchase      p
        join customer c on p.customer = c.id
        join book     b on p.book     = b.id
        group by c.id, c.name, c.email, c.since
;

These definitions will be used by:

  • Flyway, to apply the schema and keep versioning in check

  • jOOQ, to generate Java classes for each table and column

From there, we’ll be ready to query using jOOQ’s fluent, type-safe DSL.

(Optional) Run the Book Shop with Just SQL

If you’d like to explore the database before touching jOOQ, you can spin up the Postgres container and apply the schema + data manually.

This gives you a good feel for the SQL layer underneath — the same schema that jOOQ will later generate code from.

We’ve included a simple Docker Compose setup:

docker-compose up -d

This will start a local PostgreSQL instance with the right settings. You can connect to it using your favorite SQL client or CLI (psql).

Apply the schema from above using the domain data:

insert into customer (id, name, email, since) values
    (1, 'Alice Johnson', 'alice@example.com', '2023-02-10T12:43:04Z'),
    (2, 'Bob Smith'    , 'bob@example.com'  , '2023-05-21T15:10:32Z'),
    (3, 'Clara Lee'    , 'clara@example.com', '2024-01-05T08:01:45Z')
;

insert into book (id, title, author, price) values
    (101, 'SQL for Beginners', 'Jane Doe'   , 29.99),
    (102, 'Mastering Python' , 'John Smith' , 45.00),
    (103, 'Data Science 101' , 'Emily Stone', 39.95)
;

insert into purchase (id, customer, book, quantity, at) values
    (1001, 1, 101, 1, '2024-02-15T13:43:31Z'),
    (1002, 2, 102, 2, '2024-06-01T08:52:12Z'),
    (1003, 1, 103, 1, '2024-08-10T10:12:11Z'),
    (1004, 3, 101, 1, '2024-09-05T17:43:43Z')
;

Once loaded, you can:

  • Run manual SQL queries to explore the data

  • Use joins, filters, etc.

  • Try replicating the queries we’ll later write with jOOQ

This is optional — but highly recommended if you’re new to relational models or just want to see the "bare metal" before using jOOQ’s abstraction.

We’ve prepared some analytical queries to be used as starting point:

-- How many copies of each book have been sold
select b.title, sum (p.quantity) as sold
    from purchase      p
    join book     b on p.book = b.id
    group by      b.title
;

-- How many different books each customer has bought (only including those who bought more than one)
select c.name, count (distinct p.book) as bought
    from purchase      p
    join customer c on p.customer = c.id
    group by      c.name
    having count (distinct p.book) > 1
;

-- How much each customer has spent on books
select c.id, c.name, c.email, c.since, sum (p.quantity * b.price) as amount
    from purchase      p
    join customer c on p.customer = c.id
    join book     b on p.book     = b.id
    group by c.id, c.name, c.email, c.since
;

Wrap-up & Next Steps

Key Takeaways

jOOQ enables strong typing, clean queries, and compiler support. When combined with Flyway and code generation, you get a reliable end-to-end pipeline: from schema definition to safe, production-ready queries. The bookshop domain showed how easily you can move from raw SQL to fluent, type-safe DSL queries.

Next Steps

Start by cloning the repository and running the provided examples. From there, explore more advanced features such as vendor-specific SQL, database views, or stored procedures. Finally, integrate jOOQ code generation into your Gradle or CI/CD pipelines to make schema evolution seamless.

To make the workshop more engaging, we’ve prepared a few hands-on tasks for you to work through using the sample domain:

  • Normalize the schema

    Real databases evolve, and splitting entities into their own tables is one of the most common changes you’ll make. Move authors into their own dedicated table and update the book table to reference them by ID, giving you practice with normalization, migrations, and foreign keys.

  • Preserve historical revenue

    Business data has to stay consistent over time, even as things change. Record the purchase price directly in the purchase record, rather than calculating it from the current book price, so past revenue figures remain stable even if prices change.

  • Change the build process

    Deployments rarely happen in just one environment. Adjust the Flyway and jOOQ build process so the schema is deployed into a different schema namespace, showing how to adapt migrations and code generation to real-world environments.

Offer Help

If you’re considering migrating an existing project or want to explore whether jOOQ fits your stack, we’d be happy to help. We can provide guidance, migration support, or facilitate a focused deep-dive session tailored to your needs.