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

—

Pending

Overview

Eval results

Files

Query Building

Name: tessl/maven-org-jooq--jooq
Author: tessl

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.

Capabilities

SELECT Query Building

Comprehensive SELECT query construction with support for complex clauses, subqueries, and set operations.

/**
 * Create an empty SELECT statement
 * @return SelectSelectStep for adding fields
 */
public static SelectSelectStep<Record> select();

/**
 * Create a SELECT statement with one field
 * @param field1 Field to select
 * @return SelectSelectStep for the specified field type
 */
public static <T1> SelectSelectStep<Record1<T1>> select(SelectField<T1> field1);

/**
 * Create a SELECT statement with multiple fields
 * @param fields Fields to select
 * @return SelectSelectStep for Record type
 */
public static SelectSelectStep<Record> select(SelectField<?>... fields);

/**
 * Create a SELECT DISTINCT statement
 * @param fields Fields to select distinctly
 * @return SelectSelectStep with DISTINCT modifier
 */
public static SelectSelectStep<Record> selectDistinct(SelectField<?>... fields);

/**
 * Create a SELECT COUNT(*) statement
 * @return SelectSelectStep selecting count
 */
public static SelectSelectStep<Record1<Integer>> selectCount();

/**
 * Create a SELECT 1 statement
 * @return SelectSelectStep selecting literal 1
 */
public static SelectSelectStep<Record1<Integer>> selectOne();

/**
 * Create a SELECT 0 statement
 * @return SelectSelectStep selecting literal 0
 */
public static SelectSelectStep<Record1<Integer>> selectZero();

/**
 * Create a SELECT FROM table statement
 * @param table Table to select from
 * @return SelectWhereStep for the table's record type
 */
public static <R extends Record> SelectWhereStep<R> selectFrom(Table<R> table);

SELECT Interface and Chain Methods

The Select interface provides the fluent chain for building complex SELECT statements.

public interface Select<R extends Record> extends Query {
    /**
     * Add WHERE clause condition
     * @param condition Boolean condition for filtering
     * @return Select with WHERE clause added
     */
    Select<R> where(Condition condition);
    
    /**
     * Add GROUP BY clause
     * @param fields Fields to group by
     * @return Select with GROUP BY added
     */
    Select<R> groupBy(GroupField... fields);
    
    /**
     * Add HAVING clause condition (requires GROUP BY)
     * @param condition Condition for filtered groups
     * @return Select with HAVING clause added
     */
    Select<R> having(Condition condition);
    
    /**
     * Add ORDER BY clause
     * @param fields Fields to order by
     * @return Select with ORDER BY added
     */
    Select<R> orderBy(OrderField<?>... fields);
    
    /**
     * Add LIMIT clause
     * @param numberOfRows Maximum number of rows to return
     * @return Select with LIMIT added
     */
    Select<R> limit(int numberOfRows);
    
    /**
     * Add OFFSET clause (skip rows)
     * @param numberOfRows Number of rows to skip
     * @return Select with OFFSET added
     */
    Select<R> offset(int numberOfRows);
    
    /**
     * UNION with another SELECT
     * @param select SELECT to union with
     * @return Combined SELECT with UNION
     */
    Select<R> union(Select<? extends R> select);
    
    /**
     * UNION ALL with another SELECT
     * @param select SELECT to union with (including duplicates)
     * @return Combined SELECT with UNION ALL
     */
    Select<R> unionAll(Select<? extends R> select);
    
    /**
     * EXCEPT (subtract) another SELECT
     * @param select SELECT to subtract
     * @return SELECT with EXCEPT operation
     */
    Select<R> except(Select<? extends R> select);
    
    /**
     * INTERSECT with another SELECT
     * @param select SELECT to intersect with
     * @return SELECT with INTERSECT operation
     */
    Select<R> intersect(Select<? extends R> select);
}

Usage Examples:

// Simple SELECT with WHERE and ORDER BY
Result<Record> result = create
    .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .from(AUTHOR)
    .where(AUTHOR.FIRST_NAME.eq("John"))
    .orderBy(AUTHOR.LAST_NAME.asc())
    .fetch();

// Complex query with JOIN, GROUP BY, and HAVING
Result<Record3<String, String, Integer>> authorBooks = create
    .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count().as("book_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(2))
    .orderBy(count().desc())
    .fetch();

// Set operations
Select<Record1<String>> modernAuthors = create
    .select(AUTHOR.LAST_NAME)
    .from(AUTHOR)
    .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
    .where(BOOK.PUBLISHED_IN.greaterThan(2010));
    
Select<Record1<String>> classicAuthors = create
    .select(AUTHOR.LAST_NAME)
    .from(AUTHOR)
    .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
    .where(BOOK.PUBLISHED_IN.lessThan(1950));

Result<Record1<String>> allAuthors = modernAuthors.union(classicAuthors).fetch();

INSERT Query Building

Methods for building INSERT statements with various value sources.

/**
 * Create an INSERT statement for a table
 * @param into Table to insert into
 * @return InsertSetStep for setting values
 */
public static <R extends Record> InsertSetStep<R> insertInto(Table<R> into);

/**
 * Create an INSERT statement with specific columns
 * @param into Table to insert into
 * @param fields Columns to insert into
 * @return InsertValuesStep for providing values
 */
public static <R extends Record> InsertValuesStep<R> insertInto(Table<R> into, Field<?>... fields);

public interface InsertSetStep<R extends Record> {
    /**
     * Set a field value in the INSERT
     * @param field Field to set
     * @param value Value to insert
     * @return InsertSetStep for chaining more sets
     */
    <T> InsertSetMoreStep<R> set(Field<T> field, T value);
    
    /**
     * Set field values from a record
     * @param record Record containing values to insert
     * @return InsertReturningStep for optional RETURNING clause
     */
    InsertReturningStep<R> set(Record record);
}

public interface InsertValuesStep<R extends Record> {
    /**
     * Add values for one row
     * @param values Values corresponding to the fields
     * @return InsertValuesStep for adding more rows
     */
    InsertValuesStep<R> values(Object... values);
    
    /**
     * Insert values from a SELECT statement
     * @param select SELECT providing values to insert
     * @return InsertReturningStep for optional RETURNING clause
     */
    InsertReturningStep<R> select(Select<? extends Record> select);
}

Usage Examples:

// INSERT with set() method
int result = create
    .insertInto(AUTHOR)
    .set(AUTHOR.FIRST_NAME, "Jane")
    .set(AUTHOR.LAST_NAME, "Smith")
    .set(AUTHOR.DATE_OF_BIRTH, LocalDate.of(1980, 5, 15))
    .execute();

// INSERT with values() method
int result = create
    .insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .values("John", "Doe")
    .values("Alice", "Johnson")
    .execute();

// INSERT from SELECT
int result = create
    .insertInto(AUTHOR_BACKUP)
    .select(create.selectFrom(AUTHOR).where(AUTHOR.ACTIVE.eq(false)))
    .execute();

// INSERT with RETURNING (PostgreSQL)
AuthorRecord newAuthor = create
    .insertInto(AUTHOR)
    .set(AUTHOR.FIRST_NAME, "Bob")
    .set(AUTHOR.LAST_NAME, "Wilson")
    .returning()
    .fetchOne();

UPDATE Query Building

Methods for building UPDATE statements with WHERE conditions and JOIN support.

/**
 * Create an UPDATE statement for a table
 * @param table Table to update
 * @return UpdateSetFirstStep for setting field values
 */
public static <R extends Record> UpdateSetFirstStep<R> update(Table<R> table);

public interface UpdateSetFirstStep<R extends Record> {
    /**
     * Set the first field value in the UPDATE
     * @param field Field to update
     * @param value New value
     * @return UpdateSetMoreStep for setting additional fields
     */
    <T> UpdateSetMoreStep<R> set(Field<T> field, T value);
    
    /**
     * Set field values from a record
     * @param record Record containing new values
     * @return UpdateFromStep for optional FROM clause
     */
    UpdateFromStep<R> set(Record record);
}

public interface UpdateSetMoreStep<R extends Record> extends UpdateFromStep<R> {
    /**
     * Set an additional field value
     * @param field Field to update
     * @param value New value
     * @return UpdateSetMoreStep for chaining more sets
     */
    <T> UpdateSetMoreStep<R> set(Field<T> field, T value);
}

public interface UpdateWhereStep<R extends Record> extends UpdateReturningStep<R> {
    /**
     * Add WHERE clause to UPDATE
     * @param condition Condition for rows to update
     * @return UpdateReturningStep for optional RETURNING clause
     */
    UpdateReturningStep<R> where(Condition condition);
}

Usage Examples:

// Simple UPDATE with WHERE
int result = create
    .update(AUTHOR)
    .set(AUTHOR.FIRST_NAME, "Johnny")
    .where(AUTHOR.ID.eq(1))
    .execute();

// UPDATE multiple fields
int result = create
    .update(BOOK)
    .set(BOOK.TITLE, "New Title")
    .set(BOOK.PUBLISHED_IN, 2023)
    .set(BOOK.UPDATED_AT, LocalDateTime.now())
    .where(BOOK.ID.eq(5))
    .execute();

// UPDATE with complex WHERE condition  
int result = create
    .update(AUTHOR)
    .set(AUTHOR.ACTIVE, false)
    .where(AUTHOR.LAST_LOGIN.lessThan(LocalDateTime.now().minusMonths(6)))
    .and(AUTHOR.BOOK_COUNT.eq(0))
    .execute();

DELETE Query Building

Methods for building DELETE statements with WHERE conditions and JOIN support.

/**
 * Create a DELETE statement for a table
 * @param table Table to delete from
 * @return DeleteUsingStep for optional USING clause
 */
public static <R extends Record> DeleteUsingStep<R> deleteFrom(Table<R> table);

public interface DeleteWhereStep<R extends Record> extends DeleteReturningStep<R> {
    /**
     * Add WHERE clause to DELETE
     * @param condition Condition for rows to delete
     * @return DeleteReturningStep for optional RETURNING clause
     */
    DeleteReturningStep<R> where(Condition condition);
}

public interface DeleteReturningStep<R extends Record> extends Query {
    /**
     * Add RETURNING clause to return deleted data
     * @param fields Fields to return from deleted rows
     * @return Query with RETURNING clause
     */
    Query returning(SelectField<?>... fields);
}

Usage Examples:

// Simple DELETE with WHERE
int result = create
    .deleteFrom(BOOK)
    .where(BOOK.PUBLISHED_IN.lessThan(1900))
    .execute();

// DELETE with complex condition
int result = create
    .deleteFrom(AUTHOR)
    .where(AUTHOR.ACTIVE.eq(false))
    .and(not(exists(
        selectOne().from(BOOK).where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
    )))
    .execute();

// DELETE with RETURNING
Result<Record1<Integer>> deletedIds = create
    .deleteFrom(BOOK)
    .where(BOOK.OUT_OF_PRINT.eq(true))
    .returning(BOOK.ID)
    .fetch();

Window Functions and Advanced Features

/**
 * Create a window function expression
 * @param function Aggregate or window function
 * @return WindowSpecificationOrderByStep for defining window
 */
public static <T> WindowSpecificationOrderByStep<T> over(Field<T> function);

/**
 * Create a common table expression (CTE)
 * @param name Name of the CTE
 * @return CommonTableExpressionStep for defining CTE
 */
public static CommonTableExpressionStep<Record> with(String name);

/**
 * Create a recursive CTE
 * @param name Name of the recursive CTE
 * @return CommonTableExpressionStep for defining recursive CTE
 */
public static CommonTableExpressionStep<Record> withRecursive(String name);

Usage Examples:

// Window function
Result<Record3<String, Integer, Integer>> rankedBooks = create
    .select(
        BOOK.TITLE,
        BOOK.PAGES,
        rowNumber().over(partitionBy(BOOK.AUTHOR_ID).orderBy(BOOK.PAGES.desc())).as("rank")
    )
    .from(BOOK)
    .fetch();

// Common Table Expression (CTE)
Result<Record> authorStats = create
    .with("author_stats").as(
        select(AUTHOR.ID, count().as("book_count"))
        .from(AUTHOR)
        .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
        .groupBy(AUTHOR.ID)
    )
    .select()
    .from(table("author_stats"))
    .where(field("book_count", Integer.class).greaterThan(5))
    .fetch();

Install with Tessl CLI