0
# Query Building
1

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

4
## Capabilities
5

6
### SELECT Query Building
7

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

10
```java { .api }
11
/**
12
 * Create an empty SELECT statement
13
 * @return SelectSelectStep for adding fields
14
 */
15
public static SelectSelectStep<Record> select();
16

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

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

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

38
/**
39
 * Create a SELECT COUNT(*) statement
40
 * @return SelectSelectStep selecting count
41
 */
42
public static SelectSelectStep<Record1<Integer>> selectCount();
43

44
/**
45
 * Create a SELECT 1 statement
46
 * @return SelectSelectStep selecting literal 1
47
 */
48
public static SelectSelectStep<Record1<Integer>> selectOne();
49

50
/**
51
 * Create a SELECT 0 statement
52
 * @return SelectSelectStep selecting literal 0
53
 */
54
public static SelectSelectStep<Record1<Integer>> selectZero();
55

56
/**
57
 * Create a SELECT FROM table statement
58
 * @param table Table to select from
59
 * @return SelectWhereStep for the table's record type
60
 */
61
public static <R extends Record> SelectWhereStep<R> selectFrom(Table<R> table);
62
```
63

64
### SELECT Interface and Chain Methods
65

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

68
```java { .api }
69
public interface Select<R extends Record> extends Query {
70
    /**
71
     * Add WHERE clause condition
72
     * @param condition Boolean condition for filtering
73
     * @return Select with WHERE clause added
74
     */
75
    Select<R> where(Condition condition);
76
    
77
    /**
78
     * Add GROUP BY clause
79
     * @param fields Fields to group by
80
     * @return Select with GROUP BY added
81
     */
82
    Select<R> groupBy(GroupField... fields);
83
    
84
    /**
85
     * Add HAVING clause condition (requires GROUP BY)
86
     * @param condition Condition for filtered groups
87
     * @return Select with HAVING clause added
88
     */
89
    Select<R> having(Condition condition);
90
    
91
    /**
92
     * Add ORDER BY clause
93
     * @param fields Fields to order by
94
     * @return Select with ORDER BY added
95
     */
96
    Select<R> orderBy(OrderField<?>... fields);
97
    
98
    /**
99
     * Add LIMIT clause
100
     * @param numberOfRows Maximum number of rows to return
101
     * @return Select with LIMIT added
102
     */
103
    Select<R> limit(int numberOfRows);
104
    
105
    /**
106
     * Add OFFSET clause (skip rows)
107
     * @param numberOfRows Number of rows to skip
108
     * @return Select with OFFSET added
109
     */
110
    Select<R> offset(int numberOfRows);
111
    
112
    /**
113
     * UNION with another SELECT
114
     * @param select SELECT to union with
115
     * @return Combined SELECT with UNION
116
     */
117
    Select<R> union(Select<? extends R> select);
118
    
119
    /**
120
     * UNION ALL with another SELECT
121
     * @param select SELECT to union with (including duplicates)
122
     * @return Combined SELECT with UNION ALL
123
     */
124
    Select<R> unionAll(Select<? extends R> select);
125
    
126
    /**
127
     * EXCEPT (subtract) another SELECT
128
     * @param select SELECT to subtract
129
     * @return SELECT with EXCEPT operation
130
     */
131
    Select<R> except(Select<? extends R> select);
132
    
133
    /**
134
     * INTERSECT with another SELECT
135
     * @param select SELECT to intersect with
136
     * @return SELECT with INTERSECT operation
137
     */
138
    Select<R> intersect(Select<? extends R> select);
139
}
140
```
141

142
**Usage Examples:**
143

144
```java
145
// Simple SELECT with WHERE and ORDER BY
146
Result<Record> result = create
147
    .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
148
    .from(AUTHOR)
149
    .where(AUTHOR.FIRST_NAME.eq("John"))
150
    .orderBy(AUTHOR.LAST_NAME.asc())
151
    .fetch();
152

153
// Complex query with JOIN, GROUP BY, and HAVING
154
Result<Record3<String, String, Integer>> authorBooks = create
155
    .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count().as("book_count"))
156
    .from(AUTHOR)
157
    .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
158
    .where(BOOK.PUBLISHED_IN.greaterThan(2000))
159
    .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
160
    .having(count().greaterThan(2))
161
    .orderBy(count().desc())
162
    .fetch();
163

164
// Set operations
165
Select<Record1<String>> modernAuthors = create
166
    .select(AUTHOR.LAST_NAME)
167
    .from(AUTHOR)
168
    .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
169
    .where(BOOK.PUBLISHED_IN.greaterThan(2010));
170
    
171
Select<Record1<String>> classicAuthors = create
172
    .select(AUTHOR.LAST_NAME)
173
    .from(AUTHOR)
174
    .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
175
    .where(BOOK.PUBLISHED_IN.lessThan(1950));
176

177
Result<Record1<String>> allAuthors = modernAuthors.union(classicAuthors).fetch();
178
```
179

180
### INSERT Query Building
181

182
Methods for building INSERT statements with various value sources.
183

184
```java { .api }
185
/**
186
 * Create an INSERT statement for a table
187
 * @param into Table to insert into
188
 * @return InsertSetStep for setting values
189
 */
190
public static <R extends Record> InsertSetStep<R> insertInto(Table<R> into);
191

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

200
public interface InsertSetStep<R extends Record> {
201
    /**
202
     * Set a field value in the INSERT
203
     * @param field Field to set
204
     * @param value Value to insert
205
     * @return InsertSetStep for chaining more sets
206
     */
207
    <T> InsertSetMoreStep<R> set(Field<T> field, T value);
208
    
209
    /**
210
     * Set field values from a record
211
     * @param record Record containing values to insert
212
     * @return InsertReturningStep for optional RETURNING clause
213
     */
214
    InsertReturningStep<R> set(Record record);
215
}
216

217
public interface InsertValuesStep<R extends Record> {
218
    /**
219
     * Add values for one row
220
     * @param values Values corresponding to the fields
221
     * @return InsertValuesStep for adding more rows
222
     */
223
    InsertValuesStep<R> values(Object... values);
224
    
225
    /**
226
     * Insert values from a SELECT statement
227
     * @param select SELECT providing values to insert
228
     * @return InsertReturningStep for optional RETURNING clause
229
     */
230
    InsertReturningStep<R> select(Select<? extends Record> select);
231
}
232
```
233

234
**Usage Examples:**
235

236
```java
237
// INSERT with set() method
238
int result = create
239
    .insertInto(AUTHOR)
240
    .set(AUTHOR.FIRST_NAME, "Jane")
241
    .set(AUTHOR.LAST_NAME, "Smith")
242
    .set(AUTHOR.DATE_OF_BIRTH, LocalDate.of(1980, 5, 15))
243
    .execute();
244

245
// INSERT with values() method
246
int result = create
247
    .insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
248
    .values("John", "Doe")
249
    .values("Alice", "Johnson")
250
    .execute();
251

252
// INSERT from SELECT
253
int result = create
254
    .insertInto(AUTHOR_BACKUP)
255
    .select(create.selectFrom(AUTHOR).where(AUTHOR.ACTIVE.eq(false)))
256
    .execute();
257

258
// INSERT with RETURNING (PostgreSQL)
259
AuthorRecord newAuthor = create
260
    .insertInto(AUTHOR)
261
    .set(AUTHOR.FIRST_NAME, "Bob")
262
    .set(AUTHOR.LAST_NAME, "Wilson")
263
    .returning()
264
    .fetchOne();
265
```
266

267
### UPDATE Query Building
268

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

271
```java { .api }
272
/**
273
 * Create an UPDATE statement for a table
274
 * @param table Table to update
275
 * @return UpdateSetFirstStep for setting field values
276
 */
277
public static <R extends Record> UpdateSetFirstStep<R> update(Table<R> table);
278

279
public interface UpdateSetFirstStep<R extends Record> {
280
    /**
281
     * Set the first field value in the UPDATE
282
     * @param field Field to update
283
     * @param value New value
284
     * @return UpdateSetMoreStep for setting additional fields
285
     */
286
    <T> UpdateSetMoreStep<R> set(Field<T> field, T value);
287
    
288
    /**
289
     * Set field values from a record
290
     * @param record Record containing new values
291
     * @return UpdateFromStep for optional FROM clause
292
     */
293
    UpdateFromStep<R> set(Record record);
294
}
295

296
public interface UpdateSetMoreStep<R extends Record> extends UpdateFromStep<R> {
297
    /**
298
     * Set an additional field value
299
     * @param field Field to update
300
     * @param value New value
301
     * @return UpdateSetMoreStep for chaining more sets
302
     */
303
    <T> UpdateSetMoreStep<R> set(Field<T> field, T value);
304
}
305

306
public interface UpdateWhereStep<R extends Record> extends UpdateReturningStep<R> {
307
    /**
308
     * Add WHERE clause to UPDATE
309
     * @param condition Condition for rows to update
310
     * @return UpdateReturningStep for optional RETURNING clause
311
     */
312
    UpdateReturningStep<R> where(Condition condition);
313
}
314
```
315

316
**Usage Examples:**
317

318
```java
319
// Simple UPDATE with WHERE
320
int result = create
321
    .update(AUTHOR)
322
    .set(AUTHOR.FIRST_NAME, "Johnny")
323
    .where(AUTHOR.ID.eq(1))
324
    .execute();
325

326
// UPDATE multiple fields
327
int result = create
328
    .update(BOOK)
329
    .set(BOOK.TITLE, "New Title")
330
    .set(BOOK.PUBLISHED_IN, 2023)
331
    .set(BOOK.UPDATED_AT, LocalDateTime.now())
332
    .where(BOOK.ID.eq(5))
333
    .execute();
334

335
// UPDATE with complex WHERE condition  
336
int result = create
337
    .update(AUTHOR)
338
    .set(AUTHOR.ACTIVE, false)
339
    .where(AUTHOR.LAST_LOGIN.lessThan(LocalDateTime.now().minusMonths(6)))
340
    .and(AUTHOR.BOOK_COUNT.eq(0))
341
    .execute();
342
```
343

344
### DELETE Query Building
345

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

348
```java { .api }
349
/**
350
 * Create a DELETE statement for a table
351
 * @param table Table to delete from
352
 * @return DeleteUsingStep for optional USING clause
353
 */
354
public static <R extends Record> DeleteUsingStep<R> deleteFrom(Table<R> table);
355

356
public interface DeleteWhereStep<R extends Record> extends DeleteReturningStep<R> {
357
    /**
358
     * Add WHERE clause to DELETE
359
     * @param condition Condition for rows to delete
360
     * @return DeleteReturningStep for optional RETURNING clause
361
     */
362
    DeleteReturningStep<R> where(Condition condition);
363
}
364

365
public interface DeleteReturningStep<R extends Record> extends Query {
366
    /**
367
     * Add RETURNING clause to return deleted data
368
     * @param fields Fields to return from deleted rows
369
     * @return Query with RETURNING clause
370
     */
371
    Query returning(SelectField<?>... fields);
372
}
373
```
374

375
**Usage Examples:**
376

377
```java
378
// Simple DELETE with WHERE
379
int result = create
380
    .deleteFrom(BOOK)
381
    .where(BOOK.PUBLISHED_IN.lessThan(1900))
382
    .execute();
383

384
// DELETE with complex condition
385
int result = create
386
    .deleteFrom(AUTHOR)
387
    .where(AUTHOR.ACTIVE.eq(false))
388
    .and(not(exists(
389
        selectOne().from(BOOK).where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
390
    )))
391
    .execute();
392

393
// DELETE with RETURNING
394
Result<Record1<Integer>> deletedIds = create
395
    .deleteFrom(BOOK)
396
    .where(BOOK.OUT_OF_PRINT.eq(true))
397
    .returning(BOOK.ID)
398
    .fetch();
399
```
400

401
## Window Functions and Advanced Features
402

403
```java { .api }
404
/**
405
 * Create a window function expression
406
 * @param function Aggregate or window function
407
 * @return WindowSpecificationOrderByStep for defining window
408
 */
409
public static <T> WindowSpecificationOrderByStep<T> over(Field<T> function);
410

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

418
/**
419
 * Create a recursive CTE
420
 * @param name Name of the recursive CTE
421
 * @return CommonTableExpressionStep for defining recursive CTE
422
 */
423
public static CommonTableExpressionStep<Record> withRecursive(String name);
424
```
425

426
**Usage Examples:**
427

428
```java
429
// Window function
430
Result<Record3<String, Integer, Integer>> rankedBooks = create
431
    .select(
432
        BOOK.TITLE,
433
        BOOK.PAGES,
434
        rowNumber().over(partitionBy(BOOK.AUTHOR_ID).orderBy(BOOK.PAGES.desc())).as("rank")
435
    )
436
    .from(BOOK)
437
    .fetch();
438

439
// Common Table Expression (CTE)
440
Result<Record> authorStats = create
441
    .with("author_stats").as(
442
        select(AUTHOR.ID, count().as("book_count"))
443
        .from(AUTHOR)
444
        .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
445
        .groupBy(AUTHOR.ID)
446
    )
447
    .select()
448
    .from(table("author_stats"))
449
    .where(field("book_count", Integer.class).greaterThan(5))
450
    .fetch();
451
```