jOOQ

Language: Java

Database/ORM

jOOQ was developed to bridge the gap between relational databases and Java by providing a domain-specific language for SQL. Unlike traditional ORM frameworks, jOOQ emphasizes writing actual SQL statements while ensuring compile-time safety, making it ideal for complex queries, reporting, and enterprise applications. jOOQ also provides code generation tools to generate Java classes from database schemas.

jOOQ (Java Object Oriented Querying) is a fluent API for typesafe SQL query construction in Java. It allows developers to write SQL in a natural, type-checked way while mapping results directly to Java objects, combining the flexibility of SQL with the safety of Java.

Installation

maven:

Add dependencies in pom.xml:
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>3.20.5</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>3.20.5</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>3.20.5</version>
</dependency>

gradle:

Add dependencies in build.gradle:
implementation 'org.jooq:jooq:3.20.5'
implementation 'org.jooq:jooq-meta:3.20.5'
implementation 'org.jooq:jooq-codegen:3.20.5'

Usage

jOOQ allows developers to build SQL queries using a fluent Java API. Queries are typesafe, and code generation ensures table and column references match the database schema. jOOQ supports SELECT, INSERT, UPDATE, DELETE, joins, transactions, and advanced SQL features like window functions and CTEs.

Simple SELECT query

DSLContext create = DSL.using(connection, SQLDialect.POSTGRES);
Result<Record> result = create.select().from(USERS).where(USERS.ID.eq(1)).fetch();
for (Record r : result) {
    System.out.println(r.getValue(USERS.NAME));
}

Fetches a user with ID 1 from the USERS table using jOOQ’s fluent API.

INSERT example

create.insertInto(USERS)
      .columns(USERS.NAME, USERS.EMAIL)
      .values("Alice", "alice@example.com")
      .execute();

Inserts a new record into the USERS table.

UPDATE with condition

create.update(USERS)
      .set(USERS.EMAIL, "newemail@example.com")
      .where(USERS.ID.eq(1))
      .execute();

Updates the email of the user with ID 1.

JOIN query

Result<Record> result = create.select()
    .from(USERS)
    .join(ORDERS).on(USERS.ID.eq(ORDERS.USER_ID))
    .fetch();

Performs an inner join between USERS and ORDERS tables.

Transactions

create.transaction(configuration -> {
    DSLContext ctx = DSL.using(configuration);
    ctx.insertInto(USERS).columns(USERS.NAME).values("Bob").execute();
    ctx.insertInto(ORDERS).columns(ORDERS.USER_ID, ORDERS.AMOUNT).values(1, 100).execute();
});

Executes multiple queries in a single transaction.

Code generation from schema

org.jooq.codegen.GenerationTool.generate(new Configuration()
    .withJdbc(new Jdbc().withDriver("org.postgresql.Driver")
                     .withUrl("jdbc:postgresql://localhost:5432/mydb")
                     .withUser("user")
                     .withPassword("pass"))
    .withGenerator(new Generator()
        .withDatabase(new Database().withName("org.jooq.meta.postgres.PostgresDatabase"))
        .withTarget(new Target().withPackageName("com.example.jooq")
                                  .withDirectory("src/main/java"))));

Generates Java classes for database tables for type-safe queries.

Error Handling

DataAccessException: Occurs for database access errors. Check SQL syntax, connection, and transaction configuration.

InvalidResultException: Occurs when result mapping fails. Ensure column names/types match the generated Java classes.

SQLDialectNotSupportedException: Ensure the correct SQLDialect is configured for your database.

Best Practices

Use code generation for compile-time type safety.

Use DSLContext for all queries instead of raw SQL.

Keep complex queries readable with method chaining.

Use transactions for multi-step operations to ensure data integrity.

Combine jOOQ with Spring Boot for easier dependency injection and transaction management.

Official Docs Github