or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

configuration.mdexceptions.mdindex.mdquery-building.mdquery-execution.mdrecords.mdschema-objects.mdutilities.md
tile.json

tessl/maven-org-jooq--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

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
mavenpkg:maven/org.jooq/jooq@3.20.x

To install, run

npx @tessl/cli install tessl/maven-org-jooq--jooq@3.20.0

index.mddocs/

jOOQ

jOOQ (Java Object Oriented Querying) is a comprehensive SQL toolkit and type-safe DSL for Java that bridges the gap between Java applications and relational databases. As a mature library, it provides a fluent API for constructing SQL queries in a type-safe manner, eliminating runtime SQL errors through compile-time validation. The library supports code generation from database schemas to create strongly-typed Java classes representing database tables, records, and stored procedures, enabling seamless object-relational mapping without the complexity of traditional ORM frameworks.

Package Information

  • Package Name: org.jooq:jooq
  • Package Type: maven
  • Language: Java
  • Installation: <dependency><groupId>org.jooq</groupId><artifactId>jooq</artifactId><version>3.20.3</version></dependency>

Core Imports

import static org.jooq.impl.DSL.*;
import org.jooq.*;

For specific functionality:

import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Table;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;

For R2DBC reactive operations:

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactory;
import org.jooq.DSLContext;
import static org.jooq.impl.DSL.using;

Basic Usage

import static org.jooq.impl.DSL.*;
import org.jooq.*;
import java.sql.Connection;
import java.sql.DriverManager;

// Create DSLContext with JDBC connection
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
DSLContext create = using(conn, SQLDialect.H2);

// Execute simple SELECT query
Result<Record> result = create.select()
    .from(table("AUTHOR"))
    .where(field("FIRST_NAME").eq("John"))
    .fetch();

// Type-safe queries with generated code (typical usage)
Result<AuthorRecord> authors = create.selectFrom(AUTHOR)
    .where(AUTHOR.FIRST_NAME.eq("John"))
    .and(AUTHOR.LAST_NAME.like("D%"))
    .orderBy(AUTHOR.LAST_NAME)
    .fetch();

// Complex JOIN query
Result<Record3<String, String, Integer>> books = create
    .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
    .from(AUTHOR)
    .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
    .where(BOOK.PUBLISHED_IN.greaterThan(2000))
    .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .having(count().greaterThan(3))
    .fetch();

// R2DBC reactive usage
DSLContext reactiveCreate = using("r2dbc:h2:mem:///test", "sa", "");
Mono<Result<Record>> asyncResult = reactiveCreate.select()
    .from(table("AUTHOR"))
    .where(field("FIRST_NAME").eq("John"))
    .fetchAsync();

Architecture

jOOQ is built around several key components:

  • DSL Class: Static factory methods for creating jOOQ objects and entry point to the library
  • DSLContext Interface: Primary interface for executing queries in an "attached" manner where queries can be executed immediately
  • Field Interface: Represents column expressions and database fields with type safety
  • Table Interface: Represents database tables with metadata and relationship information
  • Record Interface: Handles database records with change tracking and type conversion
  • Query Interface: Base for all executable SQL statements with parameter binding and execution control
  • Configuration System: Extensive settings for SQL rendering, execution behavior, and database-specific optimizations

Capabilities

DSL Context and Query Execution

Core functionality for creating database contexts, executing queries, and managing connections. The DSLContext serves as the primary entry point for all database operations with support for both JDBC and R2DBC.

public static DSLContext using(SQLDialect dialect);
public static DSLContext using(Connection connection, SQLDialect dialect);
public static DSLContext using(DataSource datasource, SQLDialect dialect);
public static CloseableDSLContext using(String url, String username, String password);

public interface DSLContext {
    <R extends Record> Result<R> fetch(Table<R> table);
    int execute(Query query);
    <T> T transactionResult(ContextTransactionalCallable<T> transactional);
}

Query Execution and Contexts

Query Building and SQL Construction

Fluent API for building SELECT, INSERT, UPDATE, DELETE, and DDL statements with full type safety and SQL dialect support. Includes support for complex operations like window functions, CTEs, and advanced joins.

public static SelectSelectStep<Record> select();
public static <T1> SelectSelectStep<Record1<T1>> select(SelectField<T1> field1);
public static <R extends Record> InsertSetStep<R> insertInto(Table<R> into);
public static <R extends Record> UpdateSetFirstStep<R> update(Table<R> table);
public static <R extends Record> DeleteUsingStep<R> deleteFrom(Table<R> table);

public interface Select<R extends Record> extends Query {
    Select<R> where(Condition condition);
    Select<R> groupBy(GroupField... fields);
    Select<R> having(Condition condition);
    Select<R> orderBy(OrderField<?>... fields);
    Select<R> limit(int numberOfRows);
}

Query Building

Fields, Tables, and Schema Objects

Type-safe representations of database schema objects including fields, tables, constraints, and relationships. Provides metadata access and supports both generated and dynamic schema definitions.

public static <T> Field<T> field(String name, Class<T> type);
public static Table<Record> table(String name);

public interface Field<T> extends SelectField<T>, GroupField {
    String getName();
    Class<T> getType();
    Field<T> as(String alias);
    Condition eq(T value);
    Condition in(T... values);
}

public interface Table<R extends Record> {
    RecordType<R> recordType();
    UniqueKey<R> getPrimaryKey();
    List<ForeignKey<R, ?>> getReferences();
    Table<R> as(String alias);
}

Schema Objects

Records and Data Access

Record handling with change tracking, type conversion, and data manipulation capabilities. Supports both generated record types and generic record operations.

public interface Record {
    <T> T get(Field<T> field);
    <T> Record set(Field<T> field, T value);
    boolean changed();
    Record original();
    int size();
    Object[] intoArray();
}

public interface UpdatableRecord<R extends UpdatableRecord<R>> extends Record {
    int store();
    int update();
    int delete();
    void refresh();
}

Records and Data Handling

Configuration and Settings

Comprehensive configuration system for customizing SQL rendering, execution behavior, debugging, and dialect-specific optimizations. Includes support for schema mapping, naming strategies, and performance tuning.

public class Settings {
    public RenderQuotedNames getRenderQuotedNames();
    public StatementType getStatementType();
    public Boolean getExecuteLogging();
    public Settings withRenderQuotedNames(RenderQuotedNames value);
}

public interface Configuration {
    SQLDialect dialect();
    Settings settings();
    ConnectionProvider connectionProvider();
}

Configuration

Exception Handling and Error Management

jOOQ-specific exception hierarchy for handling database errors, data access issues, and configuration problems with detailed error information and recovery strategies.

public class DataAccessException extends RuntimeException;
public class InvalidResultException extends DataAccessException;
public class NoDataFoundException extends InvalidResultException;
public class TooManyRowsException extends InvalidResultException;

Exception Handling

Utilities and Tools

Comprehensive utility classes for JDBC operations, JSON processing, CSV handling, reflection, and R2DBC integration. Includes testing utilities and mock database providers.

public class JDBCUtils {
    public static void safeClose(Connection connection);
    public static SQLDialect dialect(Connection connection);
}

public interface MockDataProvider {
    MockResult[] execute(MockExecuteContext ctx) throws SQLException;
}

Utilities and Tools

Types

public enum SQLDialect {
    DEFAULT, CLICKHOUSE, CUBRID, DERBY, DUCKDB, FIREBIRD, HSQLDB, IGNITE, 
    MARIADB, MYSQL, POSTGRES, SQLITE, TRINO, YUGABYTEDB
}

public enum JoinType {
    JOIN, INNER_JOIN, LEFT_JOIN, RIGHT_JOIN, FULL_JOIN, CROSS_JOIN
}

public interface DSLContext extends Scope {
    Parser parser();
    Meta meta();
    Connection parsingConnection();
}