0
# jOOQ
1

2
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.
3

4
## Package Information
5

6
- **Package Name**: org.jooq:jooq
7
- **Package Type**: maven
8
- **Language**: Java
9
- **Installation**: `<dependency><groupId>org.jooq</groupId><artifactId>jooq</artifactId><version>3.20.3</version></dependency>`
10

11
## Core Imports
12

13
```java
14
import static org.jooq.impl.DSL.*;
15
import org.jooq.*;
16
```
17

18
For specific functionality:
19

20
```java
21
import org.jooq.DSLContext;
22
import org.jooq.Field;
23
import org.jooq.Table;
24
import org.jooq.Record;
25
import org.jooq.Result;
26
import org.jooq.SQLDialect;
27
```
28

29
For R2DBC reactive operations:
30

31
```java
32
import io.r2dbc.spi.Connection;
33
import io.r2dbc.spi.ConnectionFactory;
34
import org.jooq.DSLContext;
35
import static org.jooq.impl.DSL.using;
36
```
37

38
## Basic Usage
39

40
```java
41
import static org.jooq.impl.DSL.*;
42
import org.jooq.*;
43
import java.sql.Connection;
44
import java.sql.DriverManager;
45

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

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

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

63
// Complex JOIN query
64
Result<Record3<String, String, Integer>> books = create
65
    .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
66
    .from(AUTHOR)
67
    .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
68
    .where(BOOK.PUBLISHED_IN.greaterThan(2000))
69
    .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
70
    .having(count().greaterThan(3))
71
    .fetch();
72

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

81
## Architecture
82

83
jOOQ is built around several key components:
84

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

93
## Capabilities
94

95
### DSL Context and Query Execution
96

97
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.
98

99
```java { .api }
100
public static DSLContext using(SQLDialect dialect);
101
public static DSLContext using(Connection connection, SQLDialect dialect);
102
public static DSLContext using(DataSource datasource, SQLDialect dialect);
103
public static CloseableDSLContext using(String url, String username, String password);
104

105
public interface DSLContext {
106
    <R extends Record> Result<R> fetch(Table<R> table);
107
    int execute(Query query);
108
    <T> T transactionResult(ContextTransactionalCallable<T> transactional);
109
}
110
```
111

112
[Query Execution and Contexts](./query-execution.md)
113

114
### Query Building and SQL Construction
115

116
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.
117

118
```java { .api }
119
public static SelectSelectStep<Record> select();
120
public static <T1> SelectSelectStep<Record1<T1>> select(SelectField<T1> field1);
121
public static <R extends Record> InsertSetStep<R> insertInto(Table<R> into);
122
public static <R extends Record> UpdateSetFirstStep<R> update(Table<R> table);
123
public static <R extends Record> DeleteUsingStep<R> deleteFrom(Table<R> table);
124

125
public interface Select<R extends Record> extends Query {
126
    Select<R> where(Condition condition);
127
    Select<R> groupBy(GroupField... fields);
128
    Select<R> having(Condition condition);
129
    Select<R> orderBy(OrderField<?>... fields);
130
    Select<R> limit(int numberOfRows);
131
}
132
```
133

134
[Query Building](./query-building.md)
135

136
### Fields, Tables, and Schema Objects
137

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

140
```java { .api }
141
public static <T> Field<T> field(String name, Class<T> type);
142
public static Table<Record> table(String name);
143

144
public interface Field<T> extends SelectField<T>, GroupField {
145
    String getName();
146
    Class<T> getType();
147
    Field<T> as(String alias);
148
    Condition eq(T value);
149
    Condition in(T... values);
150
}
151

152
public interface Table<R extends Record> {
153
    RecordType<R> recordType();
154
    UniqueKey<R> getPrimaryKey();
155
    List<ForeignKey<R, ?>> getReferences();
156
    Table<R> as(String alias);
157
}
158
```
159

160
[Schema Objects](./schema-objects.md)
161

162
### Records and Data Access
163

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

166
```java { .api }
167
public interface Record {
168
    <T> T get(Field<T> field);
169
    <T> Record set(Field<T> field, T value);
170
    boolean changed();
171
    Record original();
172
    int size();
173
    Object[] intoArray();
174
}
175

176
public interface UpdatableRecord<R extends UpdatableRecord<R>> extends Record {
177
    int store();
178
    int update();
179
    int delete();
180
    void refresh();
181
}
182
```
183

184
[Records and Data Handling](./records.md)
185

186
### Configuration and Settings
187

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

190
```java { .api }
191
public class Settings {
192
    public RenderQuotedNames getRenderQuotedNames();
193
    public StatementType getStatementType();
194
    public Boolean getExecuteLogging();
195
    public Settings withRenderQuotedNames(RenderQuotedNames value);
196
}
197

198
public interface Configuration {
199
    SQLDialect dialect();
200
    Settings settings();
201
    ConnectionProvider connectionProvider();
202
}
203
```
204

205
[Configuration](./configuration.md)
206

207
### Exception Handling and Error Management
208

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

211
```java { .api }
212
public class DataAccessException extends RuntimeException;
213
public class InvalidResultException extends DataAccessException;
214
public class NoDataFoundException extends InvalidResultException;
215
public class TooManyRowsException extends InvalidResultException;
216
```
217

218
[Exception Handling](./exceptions.md)
219

220
### Utilities and Tools
221

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

224
```java { .api }
225
public class JDBCUtils {
226
    public static void safeClose(Connection connection);
227
    public static SQLDialect dialect(Connection connection);
228
}
229

230
public interface MockDataProvider {
231
    MockResult[] execute(MockExecuteContext ctx) throws SQLException;
232
}
233
```
234

235
[Utilities and Tools](./utilities.md)
236

237
## Types
238

239
```java { .api }
240
public enum SQLDialect {
241
    DEFAULT, CLICKHOUSE, CUBRID, DERBY, DUCKDB, FIREBIRD, HSQLDB, IGNITE, 
242
    MARIADB, MYSQL, POSTGRES, SQLITE, TRINO, YUGABYTEDB
243
}
244

245
public enum JoinType {
246
    JOIN, INNER_JOIN, LEFT_JOIN, RIGHT_JOIN, FULL_JOIN, CROSS_JOIN
247
}
248

249
public interface DSLContext extends Scope {
250
    Parser parser();
251
    Meta meta();
252
    Connection parsingConnection();
253
}
254
```