The Complete Production SOP: How to Deploy and Run jOOQ Typesafe Fluent SQL Query Builder for Java in Autonomous AI Agents

---
name: "jOOQ-typesafe-fluent-sql-java"
description: "Write raw, powerful SQL queries in Java with full compile-time database schema type safety."
metadata:
  author: "Data Geekery"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "write jooq fluent query"
  - "generate jooq classes maven"
  - "jooq dslcontext"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.19.6</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-meta</artifactId>
    <version>3.19.6</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen</artifactId>
    <version>3.19.6</version>
</dependency>

package com.example.repository;

import org.jooq.DSLContext;
import org.jooq.Record3;
import org.jooq.Result;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import static com.example.jooq.generated.Tables.USERS;
import static com.example.jooq.generated.Tables.ORDERS;

@Repository
public class OrderRepository {
    private final DSLContext dsl;

    public OrderRepository(DSLContext dsl) {
        this.dsl = dsl;
    }

    public Result<Record3<Long, String, BigDecimal>> fetchCustomerOrderTotals() {
        return dsl.select(USERS.ID, USERS.NAME, ORDERS.TOTAL)
                  .from(USERS)
                  .join(ORDERS).on(USERS.ID.eq(ORDERS.USER_ID))
                  .where(ORDERS.STATUS.eq("COMPLETED"))
                  .fetch();
    }
}

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>3.19.6</version>
    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <jdbc>
            <driver>org.postgresql.Driver</driver>
            <url>${env.DB_URL}</url>
            <user>${env.DB_USER}</user>
            <password>${env.DB_PASS}</password>
        </jdbc>
        <generator>
            <database>
                <name>org.jooq.meta.postgres.PostgresDatabase</name>
                <inputSchema>public</inputSchema>
            </database>
            <target>
                <packageName>com.example.jooq.generated</packageName>
                <directory>target/generated-sources/jooq</directory>
            </target>
        </generator>
    </configuration>
</plugin>