# jOOQ — Type-Safe SQL in Java with Code Generation

> jOOQ generates Java code from your database schema and provides a fluent DSL for writing type-safe SQL queries, combining the power of raw SQL with compile-time validation.

## Install

Save as a script file and run:

# jOOQ — Type-Safe SQL in Java with Code Generation

## Quick Use
```xml
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.19.15</version>
</dependency>
```
```java
DSLContext create = DSL.using(connection, SQLDialect.POSTGRES);
Result<Record> result = create
    .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .from(AUTHOR)
    .where(AUTHOR.ID.eq(1))
    .fetch();
```

## Introduction
jOOQ (Java Object Oriented Querying) takes the approach that SQL is a first-class citizen. It reverse-engineers your database schema into generated Java classes, then provides a fluent API that mirrors SQL syntax. Queries are validated at compile time against your actual schema, catching column name typos and type mismatches before runtime.

## What jOOQ Does
- Generates Java classes from existing database schemas via JDBC introspection
- Provides a fluent DSL that mirrors SQL syntax for SELECT, INSERT, UPDATE, DELETE, and DDL
- Validates queries at compile time using generated schema metadata
- Supports advanced SQL features like window functions, CTEs, recursive queries, and MERGE
- Works with PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, SQLite, H2, and more

## Architecture Overview
jOOQ's code generator connects to your database, reads the schema catalog, and produces Java classes representing tables, columns, sequences, and routines. The DSL layer wraps these generated types in a query builder that constructs a SQL abstract syntax tree. At execution time, the AST is rendered to the target dialect's SQL string, bound with parameters, and sent via JDBC. This two-phase approach keeps queries database-portable while leveraging vendor-specific features.

## Installation & Configuration
- Add `jooq`, `jooq-meta`, and `jooq-codegen` dependencies via Maven or Gradle
- Configure the code generator with JDBC URL and target package in `pom.xml` or `build.gradle`
- Run code generation as a build step to keep generated classes in sync with the schema
- Use `DSL.using(dataSource, SQLDialect.POSTGRES)` to create a `DSLContext`
- Integrate with Spring Boot via `spring-boot-starter-jooq` for auto-configuration

## Key Features
- SQL-centric: write SQL, not string concatenation, with full IDE autocomplete
- Database-first code generation ensures code matches the actual schema
- Dialect-aware rendering translates queries to the target database's SQL variant
- Active Record pattern for simple CRUD alongside the DSL for complex queries
- Comprehensive support for stored procedures, UDTs, and database-specific types

## Comparison with Similar Tools
- **Hibernate/JPA** — object-first ORM that abstracts SQL away; jOOQ embraces SQL and generates code from the schema
- **MyBatis** — XML-mapped SQL with string templates; jOOQ provides compile-time type safety
- **Exposed** — Kotlin-native DSL without code generation; jOOQ generates classes from the database
- **JDBC Template** — manual SQL with string parameters; jOOQ adds type safety and a fluent API
- **QueryDSL** — similar type-safe approach but less actively maintained; jOOQ has broader dialect support

## FAQ
**Q: Is jOOQ open source?**
A: The open-source edition supports open-source databases (PostgreSQL, MySQL, SQLite, H2). Commercial databases require a paid license.

**Q: How often should I regenerate code?**
A: Run code generation after every schema migration to keep Java classes in sync with the database.

**Q: Can I use jOOQ without code generation?**
A: Yes. The plain SQL API and DSL work without generated code, but you lose compile-time schema validation.

**Q: Does jOOQ work with Kotlin?**
A: Yes. jOOQ's fluent API works naturally in Kotlin, and some users pair it with coroutines for async database access.

## Sources
- https://github.com/jOOQ/jOOQ
- https://www.jooq.org/

---
Source: https://tokrepo.com/en/workflows/asset-f8275aae
Author: Script Depot