Library

jOOQ

Overview

jOOQ stands for "Java Object Oriented Querying" and is a library that provides the best way to write SQL in Java. Through database-first DSL (Domain Specific Language), it achieves type-safe query construction with SQL-like fluent API. With a special approach that balances complex SQL and type safety, you can leverage SQL knowledge while benefiting from Java's type system. It adopts an innovative approach that generates Java code from database schemas and guarantees SQL syntax accuracy at compile time.

Details

jOOQ 2025 version has established a solid position as the definitive solution for type-safe database access through SQL-first approach. With a groundbreaking design philosophy that gives developers complete control over SQL that traditional ORMs hide through abstraction, while providing safety through Java's type system. Type-safe class groups automatically generated from database schemas enable compile-time detection of access to non-existent tables or columns. It fully supports major DBs including PostgreSQL, MySQL, Oracle, SQL Server, and can utilize dialect-specific features. It provides advanced enterprise-level features such as nested queries with MULTISET operators, JSON support, and stored procedure execution.

Key Features

• Database-First Approach: Automatic Java code generation from schema definitions
• Type-Safe SQL DSL: Compile-time SQL syntax checking and table/column validation
• Rich SQL Feature Support: Support for complex JOINs, subqueries, window functions
• Multi-Database Support: Complete support for major DB dialects and specific features
• Advanced Data Structures: MULTISET, array, JSON, XML support
• Performance Priority: Execution efficiency equivalent to raw SQL

Pros and Cons

Pros

• High development efficiency with type-safe DSL that directly leverages SQL knowledge
• Significant reduction of runtime exceptions through compile-time error detection
• Complete control over complex queries and performance tuning
• Utilization of rich SQL features and database-specific functionality
• High performance without ORM layer overhead
• Improved maintainability through automatic reflection of schema changes

Cons

• High learning cost, SQL knowledge required
• Complex initial setup requiring code generation from database schema
• Not suitable for object-oriented domain model design
• Code regeneration required when schema changes
• More boilerplate code compared to other ORMs
• License costs may apply (commercial version)

Reference Pages

• jOOQ Official Site
• jOOQ Documentation
• jOOQ GitHub Repository

Code Examples

Project Setup and Code Generation

<!-- Maven pom.xml -->
<dependencies>
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq</artifactId>
        <version>3.19.1</version>
    </dependency>
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-meta</artifactId>
        <version>3.19.1</version>
    </dependency>
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen</artifactId>
        <version>3.19.1</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <version>3.19.1</version>
            <configuration>
                <jdbc>
                    <driver>org.postgresql.Driver</driver>
                    <url>jdbc:postgresql://localhost:5432/testdb</url>
                    <user>username</user>
                    <password>password</password>
                </jdbc>
                <generator>
                    <database>
                        <name>org.jooq.meta.postgres.PostgresDatabase</name>
                        <inputSchema>public</inputSchema>
                    </database>
                    <target>
                        <packageName>com.example.generated</packageName>
                        <directory>target/generated-sources/jooq</directory>
                    </target>
                </generator>
            </configuration>
        </plugin>
    </plugins>
</build>

# Execute code generation
mvn jooq-codegen:generate

# Gradle configuration example
dependencies {
    implementation 'org.jooq:jooq:3.19.1'
    jooqGenerator 'org.postgresql:postgresql:42.7.1'
}

jooq {
    configurations {
        main {
            generationTool {
                jdbc {
                    driver = 'org.postgresql.Driver'
                    url = 'jdbc:postgresql://localhost:5432/testdb'
                    user = 'username'
                    password = 'password'
                }
                generator {
                    database {
                        name = 'org.jooq.meta.postgres.PostgresDatabase'
                        inputSchema = 'public'
                    }
                    target {
                        packageName = 'com.example.generated'
                    }
                }
            }
        }
    }
}

Basic Query Operations (SELECT/INSERT/UPDATE/DELETE)

import static com.example.generated.Tables.*;
import static org.jooq.impl.DSL.*;

// DSLContext setup
DataSource ds = // DataSource configuration
DSLContext dsl = DSL.using(ds, SQLDialect.POSTGRES);

// Basic SELECT query
Result<Record> result = dsl
    .select()
    .from(USERS)
    .where(USERS.AGE.greaterThan(18))
    .orderBy(USERS.NAME)
    .fetch();

// Specific column selection
List<String> names = dsl
    .select(USERS.NAME)
    .from(USERS)
    .where(USERS.EMAIL.like("%@example.com"))
    .fetch(USERS.NAME);

// JOIN query
Result<Record> joinResult = dsl
    .select(USERS.NAME, ORDERS.TOTAL_AMOUNT)
    .from(USERS)
    .innerJoin(ORDERS).on(USERS.ID.eq(ORDERS.USER_ID))
    .where(ORDERS.ORDER_DATE.greaterThan(LocalDate.of(2024, 1, 1)))
    .fetch();

// INSERT operation
UsersRecord newUser = dsl.newRecord(USERS);
newUser.setName("John Doe");
newUser.setEmail("[email protected]");
newUser.setAge(30);
newUser.store(); // Execute INSERT

// Alternative INSERT method
dsl.insertInto(USERS)
    .set(USERS.NAME, "Jane Smith")
    .set(USERS.EMAIL, "[email protected]")
    .set(USERS.AGE, 25)
    .execute();

// Batch INSERT
dsl.insertInto(USERS, USERS.NAME, USERS.EMAIL, USERS.AGE)
    .values("Alice Johnson", "[email protected]", 28)
    .values("Bob Wilson", "[email protected]", 32)
    .values("Carol Brown", "[email protected]", 29)
    .execute();

// UPDATE operation
int updatedCount = dsl
    .update(USERS)
    .set(USERS.AGE, USERS.AGE.add(1))
    .where(USERS.ID.eq(1))
    .execute();

// Conditional UPDATE
dsl.update(USERS)
    .set(USERS.STATUS, "ACTIVE")
    .where(USERS.LAST_LOGIN.greaterThan(LocalDateTime.now().minusDays(30)))
    .execute();

// DELETE operation
int deletedCount = dsl
    .deleteFrom(USERS)
    .where(USERS.STATUS.eq("INACTIVE")
        .and(USERS.LAST_LOGIN.lessThan(LocalDateTime.now().minusYears(1))))
    .execute();

// Safe operations with record types
UsersRecord user = dsl
    .selectFrom(USERS)
    .where(USERS.ID.eq(1))
    .fetchOne();

if (user != null) {
    user.setAge(user.getAge() + 1);
    user.update(); // Execute UPDATE
}

Advanced Queries and MULTISET Features

// Subquery usage
List<UsersRecord> activeUsers = dsl
    .selectFrom(USERS)
    .where(USERS.ID.in(
        select(ORDERS.USER_ID)
        .from(ORDERS)
        .where(ORDERS.ORDER_DATE.greaterThan(LocalDate.now().minusDays(30)))
    ))
    .fetch();

// Window functions
Result<Record4<String, Integer, Integer, Integer>> rankedUsers = dsl
    .select(
        USERS.NAME,
        USERS.AGE,
        count().over().as("total_users"),
        rowNumber().over().orderBy(USERS.AGE.desc()).as("age_rank")
    )
    .from(USERS)
    .orderBy(field("age_rank"))
    .fetch();

// Common Table Expression (WITH clause)
Result<Record> withResult = dsl
    .with("young_users").as(
        select(USERS.ID, USERS.NAME, USERS.AGE)
        .from(USERS)
        .where(USERS.AGE.lessThan(30))
    )
    .select()
    .from(table(name("young_users")))
    .fetch();

// MULTISET for hierarchical data retrieval
record Film(String title, List<Actor> actors, List<String> categories) {}
record Actor(String firstName, String lastName) {}

// MULTISET helper function definition
public static <T> Field<List<T>> multisetArray(SelectFieldOrAsterisk<Record1<T>> select) {
    return field(
        "({0})",
        SQLDataType.CLOB.array(),
        select(coalesce(
            jsonArrayAgg(select),
            jsonArray()
        ))
    ).map(Convert.convert(Object[].class, (List<T>) null));
}

// Hierarchical data retrieval query
List<Film> films = dsl
    .select(
        FILM.TITLE,
        multisetArray(
            select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
            .from(FILM_ACTOR)
            .join(ACTOR).on(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID))
            .where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
        ).as("actors"),
        multisetArray(
            select(CATEGORY.NAME)
            .from(FILM_CATEGORY)
            .join(CATEGORY).on(FILM_CATEGORY.CATEGORY_ID.eq(CATEGORY.CATEGORY_ID))
            .where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
        ).as("categories")
    )
    .from(FILM)
    .orderBy(FILM.TITLE)
    .fetchInto(Film.class);

// Dynamic query construction
Condition condition = trueCondition();

if (nameFilter != null) {
    condition = condition.and(USERS.NAME.containsIgnoreCase(nameFilter));
}
if (minAge != null) {
    condition = condition.and(USERS.AGE.greaterOrEqual(minAge));
}
if (emailDomain != null) {
    condition = condition.and(USERS.EMAIL.like("%" + emailDomain));
}

List<UsersRecord> filteredUsers = dsl
    .selectFrom(USERS)
    .where(condition)
    .orderBy(USERS.NAME)
    .fetch();

Transaction Management and Batch Processing

// Basic transaction
dsl.transaction(configuration -> {
    DSLContext ctx = DSL.using(configuration);
    
    // User creation
    UsersRecord user = ctx.newRecord(USERS);
    user.setName("New User");
    user.setEmail("[email protected]");
    user.store();
    
    // Order creation
    OrdersRecord order = ctx.newRecord(ORDERS);
    order.setUserId(user.getId());
    order.setTotalAmount(new BigDecimal("1000.00"));
    order.store();
    
    // Point allocation
    ctx.insertInto(USER_POINTS)
        .set(USER_POINTS.USER_ID, user.getId())
        .set(USER_POINTS.POINTS, 100)
        .execute();
});

// Nested transactions (savepoints)
dsl.transaction(configuration -> {
    DSLContext ctx = DSL.using(configuration);
    
    UsersRecord user = ctx.newRecord(USERS);
    user.setName("Main User");
    user.store();
    
    try {
        ctx.transaction(nestedConfig -> {
            DSLContext nestedCtx = DSL.using(nestedConfig);
            
            // Risky operation
            nestedCtx.update(USERS)
                .set(USERS.CREDIT_LIMIT, new BigDecimal("999999"))
                .where(USERS.ID.eq(user.getId()))
                .execute();
            
            // Some check processing
            if (someValidationFails()) {
                throw new RuntimeException("Validation failed");
            }
        });
    } catch (RuntimeException e) {
        // Only nested transaction rollback
        logger.warn("Nested processing failed, main processing continues: " + e.getMessage());
    }
});

// Batch processing
List<UsersRecord> usersToUpdate = // Get users to update
dsl.batchUpdate(usersToUpdate).execute();

// Bulk Insert with ON CONFLICT
dsl.insertInto(USERS)
    .columns(USERS.EMAIL, USERS.NAME, USERS.AGE)
    .values("[email protected]", "User 1", 25)
    .values("[email protected]", "User 2", 30)
    .values("[email protected]", "User 3", 35)
    .onConflict(USERS.EMAIL)
    .doUpdate()
    .set(USERS.NAME, excluded(USERS.NAME))
    .set(USERS.AGE, excluded(USERS.AGE))
    .execute();

// Performance measurement of batch operations
Stopwatch stopwatch = Stopwatch.createStarted();
int[] batchResult = dsl.batch(
    dsl.insertInto(USER_ACTIVITY)
        .set(USER_ACTIVITY.USER_ID, (Integer) null)
        .set(USER_ACTIVITY.ACTIVITY_TYPE, (String) null)
        .set(USER_ACTIVITY.TIMESTAMP, (LocalDateTime) null)
)
.bind(1, "LOGIN", LocalDateTime.now())
.bind(2, "VIEW_PAGE", LocalDateTime.now())
.bind(3, "PURCHASE", LocalDateTime.now())
.execute();

stopwatch.stop();
logger.info("Batch processing completed: {}ms, processed count: {}", 
    stopwatch.elapsed(TimeUnit.MILLISECONDS), batchResult.length);

Stored Procedures and Custom Functions

// Stored procedure execution
Result<Record> procedureResult = dsl
    .select()
    .from(table(call("get_user_orders", 
        param("user_id", 123),
        param("start_date", LocalDate.of(2024, 1, 1))
    )))
    .fetch();

// Custom SQL function definition
public static Field<BigDecimal> calculateDiscount(Field<BigDecimal> amount, Field<String> userType) {
    return field("calculate_discount({0}, {1})", SQLDataType.DECIMAL, amount, userType);
}

// Using custom functions
List<Record3<String, BigDecimal, BigDecimal>> discountedOrders = dsl
    .select(
        USERS.NAME,
        ORDERS.TOTAL_AMOUNT,
        calculateDiscount(ORDERS.TOTAL_AMOUNT, USERS.USER_TYPE).as("discounted_amount")
    )
    .from(ORDERS)
    .join(USERS).on(ORDERS.USER_ID.eq(USERS.ID))
    .fetch();

// Utilizing database-specific features (PostgreSQL arrays)
List<String> tags = List.of("java", "database", "orm");
dsl.insertInto(ARTICLES)
    .set(ARTICLES.TITLE, "jOOQ Article")
    .set(ARTICLES.CONTENT, "About jOOQ")
    .set(ARTICLES.TAGS, tags.toArray(new String[0])) // PostgreSQL array
    .execute();

// JSON operations (PostgreSQL)
dsl.update(USERS)
    .set(USERS.PREFERENCES, 
        jsonObject(
            key("theme").value("dark"),
            key("language").value("en"),
            key("notifications").value(true)
        ))
    .where(USERS.ID.eq(1))
    .execute();

// JSON search
List<UsersRecord> darkThemeUsers = dsl
    .selectFrom(USERS)
    .where(USERS.PREFERENCES.extract("$.theme").eq("dark"))
    .fetch();

Error Handling and Log Output

// SQL error handling
try {
    dsl.insertInto(USERS)
        .set(USERS.EMAIL, "[email protected]") // Unique constraint violation
        .execute();
} catch (DataAccessException e) {
    if (e.getCause() instanceof PSQLException) {
        PSQLException psql = (PSQLException) e.getCause();
        if ("23505".equals(psql.getSQLState())) { // UNIQUE_VIOLATION
            logger.warn("Duplicate key error: {}", psql.getMessage());
            // Duplicate handling logic
        }
    }
    throw e;
}

// Query execution time monitoring
ExecuteListener listener = new DefaultExecuteListener() {
    @Override
    public void executeStart(ExecuteContext ctx) {
        logger.debug("Query start: {}", ctx.sql());
    }
    
    @Override
    public void executeEnd(ExecuteContext ctx) {
        logger.debug("Query end: {}ms", ctx.sqlExecutionTime());
        if (ctx.sqlExecutionTime() > 1000) {
            logger.warn("Slow query detected: {}ms - {}", 
                ctx.sqlExecutionTime(), ctx.sql());
        }
    }
};

DSLContext monitoredDsl = DSL.using(connection, SQLDialect.POSTGRES)
    .configuration()
    .derive(listener)
    .dsl();

// SQL output and debugging
DSLContext debugDsl = DSL.using(connection, SQLDialect.POSTGRES)
    .configuration()
    .derive(new DefaultExecuteListener() {
        @Override
        public void renderEnd(ExecuteContext ctx) {
            logger.info("Generated SQL: {}", ctx.sql());
            logger.debug("Bind values: {}", Arrays.toString(ctx.bindings()));
        }
    })
    .dsl();

// Getting execution statistics
ExecuteContext stats = new DefaultExecuteContext();
Result<Record> result = dsl
    .select()
    .from(USERS)
    .where(USERS.STATUS.eq("ACTIVE"))
    .fetch();

logger.info("Execution time: {}ms, fetched count: {}", 
    stats.sqlExecutionTime(), result.size());