0
# SQL Expressions and Functions
1

2
QueryDSL SQL provides a comprehensive expression builder for SQL functions, operators, database-specific features, and advanced SQL constructs including window functions, aggregate functions, and conditional expressions.
3

4
## Capabilities
5

6
### Core Expression Factory
7

8
Central factory class for creating SQL expressions, functions, and operators.
9

10
```java { .api }
11
/**
12
 * Factory class for SQL expressions and functions
13
 */
14
public class SQLExpressions {
15
    /**
16
     * Creates a constant expression with the specified value
17
     * @param value Constant value
18
     * @return Expression representing the constant
19
     */
20
    public static <T> SimpleExpression<T> constant(T value);
21
    
22
    /**
23
     * Creates a case expression builder
24
     * @return CaseBuilder for constructing CASE statements
25
     */
26
    public static CaseBuilder cases();
27
    
28
    /**
29
     * Creates a SQL fragment expression
30
     * @param sql Raw SQL fragment
31
     * @param args Arguments for the SQL fragment
32
     * @return Expression wrapping the SQL fragment
33
     */
34
    public static SimpleExpression<Object> template(String sql, Object... args);
35
    
36
    /**
37
     * Creates a parameterized SQL template
38
     * @param type Return type of the expression
39
     * @param template SQL template with placeholders
40
     * @param args Template arguments
41
     * @return Typed expression from template
42
     */
43
    public static <T> SimpleExpression<T> template(Class<? extends T> type, String template, Object... args);
44
}
45
```
46

47
**Usage Examples:**
48

49
```java
50
// Constant expressions
51
Expression<String> statusConstant = SQLExpressions.constant("ACTIVE");
52
Expression<Integer> zeroConstant = SQLExpressions.constant(0);
53

54
// SQL templates for database-specific features
55
Expression<Integer> customFunction = SQLExpressions.template(Integer.class, 
56
    "my_custom_function({0}, {1})", qUser.id, qUser.name);
57

58
// Case expressions
59
Expression<String> statusLabel = SQLExpressions.cases()
60
    .when(qUser.active.isTrue()).then("Active")
61
    .when(qUser.suspended.isTrue()).then("Suspended")
62
    .otherwise("Inactive");
63
```
64

65
### String Functions
66

67
SQL string manipulation functions with type-safe expression building.
68

69
```java { .api }
70
/**
71
 * String concatenation function
72
 * @param args String expressions to concatenate
73
 * @return String expression for concatenated result
74
 */
75
public static StringExpression concat(Expression<String>... args);
76

77
/**
78
 * String length function
79
 * @param str String expression
80
 * @return Integer expression for string length
81
 */
82
public static NumberExpression<Integer> length(Expression<String> str);
83

84
/**
85
 * Substring function
86
 * @param str Source string expression
87
 * @param start Starting position (1-based)
88
 * @param length Length of substring
89
 * @return String expression for substring result
90
 */
91
public static StringExpression substring(Expression<String> str, int start, int length);
92

93
/**
94
 * String trimming function
95
 * @param str String expression to trim
96
 * @return String expression with whitespace removed
97
 */
98
public static StringExpression trim(Expression<String> str);
99

100
/**
101
 * Converts string to uppercase
102
 * @param str String expression
103
 * @return Uppercase string expression
104
 */
105
public static StringExpression upper(Expression<String> str);
106

107
/**
108
 * Converts string to lowercase
109
 * @param str String expression
110
 * @return Lowercase string expression
111
 */
112
public static StringExpression lower(Expression<String> str);
113
```
114

115
**Usage Examples:**
116

117
```java
118
// String concatenation
119
StringExpression fullName = SQLExpressions.concat(qUser.firstName, 
120
    SQLExpressions.constant(" "), qUser.lastName);
121

122
// String manipulation in queries
123
List<Tuple> results = queryFactory
124
    .select(qUser.name, 
125
        SQLExpressions.upper(qUser.name).as("upper_name"),
126
        SQLExpressions.length(qUser.email).as("email_length"))
127
    .from(qUser)
128
    .fetch();
129

130
// Substring operations
131
List<String> areaCodes = queryFactory
132
    .select(SQLExpressions.substring(qUser.phone, 1, 3))
133
    .from(qUser)
134
    .where(qUser.phone.isNotNull())
135
    .fetch();
136
```
137

138
### Numeric Functions
139

140
Mathematical functions and numeric operations for calculations.
141

142
```java { .api }
143
/**
144
 * Absolute value function
145
 * @param number Numeric expression
146
 * @return Numeric expression for absolute value
147
 */
148
public static <T extends Number & Comparable<T>> NumberExpression<T> abs(Expression<T> number);
149

150
/**
151
 * Ceiling function (round up)
152
 * @param number Numeric expression
153
 * @return Numeric expression rounded up to nearest integer
154
 */
155
public static NumberExpression<Double> ceil(Expression<? extends Number> number);
156

157
/**
158
 * Floor function (round down)
159
 * @param number Numeric expression
160
 * @return Numeric expression rounded down to nearest integer
161
 */
162
public static NumberExpression<Double> floor(Expression<? extends Number> number);
163

164
/**
165
 * Round function
166
 * @param number Numeric expression to round
167
 * @param precision Number of decimal places
168
 * @return Rounded numeric expression
169
 */
170
public static NumberExpression<Double> round(Expression<? extends Number> number, int precision);
171

172
/**
173
 * Square root function
174
 * @param number Numeric expression
175
 * @return Square root expression
176
 */
177
public static NumberExpression<Double> sqrt(Expression<? extends Number> number);
178

179
/**
180
 * Power function
181
 * @param base Base expression
182
 * @param exponent Exponent expression
183
 * @return Power expression
184
 */
185
public static NumberExpression<Double> power(Expression<? extends Number> base, 
186
                                           Expression<? extends Number> exponent);
187
```
188

189
**Usage Examples:**
190

191
```java
192
// Mathematical calculations
193
List<Tuple> calculations = queryFactory
194
    .select(qOrder.amount,
195
        SQLExpressions.abs(qOrder.amount.subtract(qOrder.discount)).as("net_amount"),
196
        SQLExpressions.round(qOrder.amount.multiply(0.1), 2).as("tax"),
197
        SQLExpressions.sqrt(qOrder.quantity).as("sqrt_qty"))
198
    .from(qOrder)
199
    .fetch();
200

201
// Price calculations with rounding
202
NumberExpression<Double> finalPrice = SQLExpressions.round(
203
    qProduct.price.multiply(1.0 - qDiscount.percentage.divide(100.0)), 2);
204
```
205

206
### Date and Time Functions
207

208
Date and time manipulation functions for temporal operations.
209

210
```java { .api }
211
/**
212
 * Current timestamp function
213
 * @return Expression for current database timestamp
214
 */
215
public static DateTimeExpression<Date> currentTimestamp();
216

217
/**
218
 * Current date function
219
 * @return Expression for current database date
220
 */
221
public static DateExpression<Date> currentDate();
222

223
/**
224
 * Current time function
225
 * @return Expression for current database time
226
 */
227
public static TimeExpression<Time> currentTime();
228

229
/**
230
 * Date addition function
231
 * @param date Date expression
232
 * @param interval Interval to add
233
 * @param part Date part to add (day, month, year, etc.)
234
 * @return Date expression with added interval
235
 */
236
public static DateTimeExpression<Date> dateAdd(Expression<Date> date, int interval, DatePart part);
237

238
/**
239
 * Date difference function
240
 * @param date1 First date expression
241
 * @param date2 Second date expression
242
 * @param part Date part for difference calculation
243
 * @return Integer expression for date difference
244
 */
245
public static NumberExpression<Integer> dateDiff(DatePart part, 
246
                                               Expression<Date> date1, 
247
                                               Expression<Date> date2);
248

249
/**
250
 * Extract date part function
251
 * @param part Date part to extract
252
 * @param date Date expression
253
 * @return Integer expression for extracted part
254
 */
255
public static NumberExpression<Integer> extract(DatePart part, Expression<Date> date);
256
```
257

258
**Usage Examples:**
259

260
```java
261
// Current timestamp operations
262
List<User> recentUsers = queryFactory
263
    .selectFrom(qUser)
264
    .where(qUser.createdAt.gt(SQLExpressions.dateAdd(
265
        SQLExpressions.currentTimestamp(), -7, DatePart.day)))
266
    .fetch();
267

268
// Date calculations
269
List<Tuple> ageCalculations = queryFactory
270
    .select(qUser.name,
271
        SQLExpressions.dateDiff(DatePart.year, qUser.birthDate, 
272
            SQLExpressions.currentDate()).as("age"),
273
        SQLExpressions.extract(DatePart.month, qUser.birthDate).as("birth_month"))
274
    .from(qUser)
275
    .fetch();
276

277
// Date formatting and extraction
278
List<Integer> birthYears = queryFactory
279
    .select(SQLExpressions.extract(DatePart.year, qUser.birthDate))
280
    .from(qUser)
281
    .distinct()
282
    .orderBy(SQLExpressions.extract(DatePart.year, qUser.birthDate).asc())
283
    .fetch();
284
```
285

286
### Aggregate Functions
287

288
SQL aggregate functions for data summarization and statistical operations.
289

290
```java { .api }
291
/**
292
 * Count function
293
 * @param expr Expression to count (use * for row count)
294
 * @return Count expression
295
 */
296
public static NumberExpression<Long> count(Expression<?> expr);
297

298
/**
299
 * Count distinct function
300
 * @param expr Expression to count distinct values
301
 * @return Count distinct expression
302
 */
303
public static NumberExpression<Long> countDistinct(Expression<?> expr);
304

305
/**
306
 * Sum function
307
 * @param expr Numeric expression to sum
308
 * @return Sum expression
309
 */
310
public static <T extends Number> NumberExpression<T> sum(Expression<T> expr);
311

312
/**
313
 * Average function
314
 * @param expr Numeric expression to average
315
 * @return Average expression
316
 */
317
public static NumberExpression<Double> avg(Expression<? extends Number> expr);
318

319
/**
320
 * Maximum function
321
 * @param expr Expression to find maximum value
322
 * @return Maximum expression
323
 */
324
public static <T extends Comparable<T>> ComparableExpression<T> max(Expression<T> expr);
325

326
/**
327
 * Minimum function
328
 * @param expr Expression to find minimum value
329
 * @return Minimum expression
330
 */
331
public static <T extends Comparable<T>> ComparableExpression<T> min(Expression<T> expr);
332
```
333

334
**Usage Examples:**
335

336
```java
337
// Basic aggregations
338
Tuple stats = queryFactory
339
    .select(SQLExpressions.count(qOrder.id).as("total_orders"),
340
        SQLExpressions.sum(qOrder.amount).as("total_amount"),
341
        SQLExpressions.avg(qOrder.amount).as("avg_amount"),
342
        SQLExpressions.max(qOrder.amount).as("max_amount"),
343
        SQLExpressions.min(qOrder.createdAt).as("first_order"))
344
    .from(qOrder)
345
    .fetchOne();
346

347
// Group aggregations
348
List<Tuple> customerStats = queryFactory
349
    .select(qOrder.customerId,
350
        SQLExpressions.count(qOrder.id).as("order_count"),
351
        SQLExpressions.sum(qOrder.amount).as("total_spent"),
352
        SQLExpressions.countDistinct(qOrderItem.productId).as("unique_products"))
353
    .from(qOrder)
354
    .join(qOrderItem).on(qOrder.id.eq(qOrderItem.orderId))
355
    .groupBy(qOrder.customerId)
356
    .fetch();
357
```
358

359
### Window Functions
360

361
Advanced SQL window functions for analytical operations and ranking.
362

363
```java { .api }
364
/**
365
 * ROW_NUMBER() window function
366
 * @return Window function for row numbering
367
 */
368
public static WindowFunction<Long> rowNumber();
369

370
/**
371
 * RANK() window function
372
 * @return Window function for ranking with gaps
373
 */
374
public static WindowFunction<Long> rank();
375

376
/**
377
 * DENSE_RANK() window function
378
 * @return Window function for ranking without gaps
379
 */
380
public static WindowFunction<Long> denseRank();
381

382
/**
383
 * LAG() window function - access previous row value
384
 * @param expr Expression to get previous value of
385
 * @param offset Number of rows back (default 1)
386
 * @return Window function for accessing previous row
387
 */
388
public static <T> WindowFunction<T> lag(Expression<T> expr, int offset);
389

390
/**
391
 * LEAD() window function - access next row value
392
 * @param expr Expression to get next value of
393
 * @param offset Number of rows forward (default 1)
394
 * @return Window function for accessing next row
395
 */
396
public static <T> WindowFunction<T> lead(Expression<T> expr, int offset);
397

398
/**
399
 * FIRST_VALUE() window function
400
 * @param expr Expression to get first value of
401
 * @return Window function for first value in partition
402
 */
403
public static <T> WindowFunction<T> firstValue(Expression<T> expr);
404

405
/**
406
 * LAST_VALUE() window function
407
 * @param expr Expression to get last value of
408
 * @return Window function for last value in partition
409
 */
410
public static <T> WindowFunction<T> lastValue(Expression<T> expr);
411
```
412

413
**Usage Examples:**
414

415
```java
416
// Row numbering and ranking
417
List<Tuple> rankedProducts = queryFactory
418
    .select(qProduct.name, qProduct.price,
419
        SQLExpressions.rowNumber().over(qProduct.price.desc()).as("price_rank"),
420
        SQLExpressions.rank().over(qProduct.categoryId.asc(), qProduct.price.desc()).as("category_rank"))
421
    .from(qProduct)
422
    .fetch();
423

424
// Running calculations with window functions
425
List<Tuple> runningTotals = queryFactory
426
    .select(qOrder.date, qOrder.amount,
427
        SQLExpressions.sum(qOrder.amount).over(qOrder.date.asc()).as("running_total"),
428
        SQLExpressions.avg(qOrder.amount).over(qOrder.date.asc()).as("running_avg"))
429
    .from(qOrder)
430
    .orderBy(qOrder.date.asc())
431
    .fetch();
432

433
// Lag/Lead for comparisons
434
List<Tuple> priceChanges = queryFactory
435
    .select(qStockPrice.date, qStockPrice.price,
436
        SQLExpressions.lag(qStockPrice.price, 1).over(qStockPrice.date.asc()).as("prev_price"),
437
        SQLExpressions.lead(qStockPrice.price, 1).over(qStockPrice.date.asc()).as("next_price"))
438
    .from(qStockPrice)
439
    .where(qStockPrice.symbol.eq("AAPL"))
440
    .orderBy(qStockPrice.date.asc())
441
    .fetch();
442
```
443

444
### Conditional Expressions
445

446
SQL conditional logic including CASE statements, COALESCE, and NULL handling.
447

448
```java { .api }
449
/**
450
 * COALESCE function - returns first non-null value
451
 * @param exprs Expressions to evaluate in order
452
 * @return Expression with first non-null value
453
 */
454
public static <T> SimpleExpression<T> coalesce(Expression<T>... exprs);
455

456
/**
457
 * NULLIF function - returns null if expressions are equal
458
 * @param expr1 First expression
459
 * @param expr2 Second expression
460
 * @return Null if equal, otherwise first expression
461
 */
462
public static <T> SimpleExpression<T> nullif(Expression<T> expr1, Expression<T> expr2);
463

464
/**
465
 * Creates a CASE expression builder
466
 * @return CaseBuilder for constructing CASE statements
467
 */
468
public static CaseBuilder cases();
469

470
/**
471
 * CASE expression builder interface
472
 */
473
public interface CaseBuilder {
474
    /**
475
     * Adds a WHEN condition
476
     * @param condition Boolean condition to test
477
     * @return Case when builder for specifying result
478
     */
479
    CaseWhen when(Predicate condition);
480
    
481
    /**
482
     * Case when builder for specifying THEN result
483
     */
484
    interface CaseWhen {
485
        /**
486
         * Specifies result when condition is true
487
         * @param expr Result expression
488
         * @return Case builder for additional conditions
489
         */
490
        <T> CaseBuilder then(Expression<T> expr);
491
    }
492
    
493
    /**
494
     * Specifies default result for ELSE clause
495
     * @param expr Default expression
496
     * @return Final case expression
497
     */
498
    <T> SimpleExpression<T> otherwise(Expression<T> expr);
499
}
500
```
501

502
**Usage Examples:**
503

504
```java
505
// CASE expressions for conditional logic
506
Expression<String> orderStatus = SQLExpressions.cases()
507
    .when(qOrder.shippedAt.isNotNull()).then("Shipped")
508
    .when(qOrder.paidAt.isNotNull()).then("Paid")
509
    .when(qOrder.createdAt.isNotNull()).then("Pending")
510
    .otherwise("Unknown");
511

512
// COALESCE for null handling
513
Expression<String> displayName = SQLExpressions.coalesce(
514
    qUser.nickname, qUser.firstName, qUser.username, 
515
    SQLExpressions.constant("Anonymous"));
516

517
// Complex conditional aggregations
518
Expression<Long> activeOrderCount = SQLExpressions.cases()
519
    .when(qOrder.status.eq("ACTIVE")).then(1L)
520
    .otherwise(0L);
521

522
List<Tuple> customerSummary = queryFactory
523
    .select(qCustomer.name,
524
        SQLExpressions.sum(activeOrderCount).as("active_orders"),
525
        SQLExpressions.coalesce(SQLExpressions.max(qOrder.amount), 
526
            SQLExpressions.constant(0.0)).as("max_order"))
527
    .from(qCustomer)
528
    .leftJoin(qOrder).on(qCustomer.id.eq(qOrder.customerId))
529
    .groupBy(qCustomer.id, qCustomer.name)
530
    .fetch();
531
```
532

533
### Database-Specific Functions
534

535
Specialized functions for specific database systems and their unique features.
536

537
```java { .api }
538
/**
539
 * PostgreSQL-specific functions
540
 */
541
public class PostgreSQLExpressions {
542
    /**
543
     * PostgreSQL array contains operator
544
     * @param array Array expression
545
     * @param element Element to check for
546
     * @return Boolean expression for containment check
547
     */
548
    public static BooleanExpression arrayContains(Expression<?> array, Expression<?> element);
549
    
550
    /**
551
     * PostgreSQL JSONB path query
552
     * @param jsonb JSONB column expression
553
     * @param path JSON path expression
554
     * @return Expression for JSONB path result
555
     */
556
    public static SimpleExpression<String> jsonbPath(Expression<String> jsonb, String path);
557
}
558

559
/**
560
 * MySQL-specific functions
561
 */
562
public class MySQLExpressions {
563
    /**
564
     * MySQL MATCH AGAINST full-text search
565
     * @param columns Columns to search in
566
     * @param searchText Search text
567
     * @return Boolean expression for full-text match
568
     */
569
    public static BooleanExpression match(Expression<?>[] columns, String searchText);
570
}
571

572
/**
573
 * Oracle-specific functions
574
 */
575
public class OracleExpressions {
576
    /**
577
     * Oracle CONNECT BY hierarchical query
578
     * @param condition Connect by condition
579
     * @return Hierarchical query expression
580
     */
581
    public static BooleanExpression connectBy(Predicate condition);
582
    
583
    /**
584
     * Oracle ROWNUM pseudo-column
585
     * @return Row number expression
586
     */
587
    public static NumberExpression<Long> rownum();
588
}
589
```
590

591
**Usage Examples:**
592

593
```java
594
// PostgreSQL array operations
595
List<User> usersWithTag = queryFactory
596
    .selectFrom(qUser)
597
    .where(PostgreSQLExpressions.arrayContains(qUser.tags, 
598
        SQLExpressions.constant("premium")))
599
    .fetch();
600

601
// PostgreSQL JSONB queries
602
List<String> emailsFromJson = queryFactory
603
    .select(PostgreSQLExpressions.jsonbPath(qUser.metadata, "$.email"))
604
    .from(qUser)
605
    .where(qUser.metadata.isNotNull())
606
    .fetch();
607

608
// MySQL full-text search
609
List<Article> searchResults = queryFactory
610
    .selectFrom(qArticle)
611
    .where(MySQLExpressions.match(
612
        new Expression<?>[]{qArticle.title, qArticle.content}, 
613
        "java spring boot"))
614
    .fetch();
615

616
// Oracle hierarchical queries
617
List<Employee> hierarchy = queryFactory
618
    .selectFrom(qEmployee)
619
    .where(OracleExpressions.connectBy(qEmployee.managerId.eq(qEmployee.id)))
620
    .fetch();
621
```
622

623
### Custom Function Registration
624

625
Framework for registering and using custom database functions.
626

627
```java { .api }
628
/**
629
 * Represents a custom SQL function call
630
 * @param <T> Return type of the function
631
 */
632
public class RelationalFunctionCall<T> extends SimpleExpression<T> {
633
    /**
634
     * Creates a custom function call
635
     * @param type Return type
636
     * @param function Function name
637
     * @param args Function arguments
638
     */
639
    public RelationalFunctionCall(Class<? extends T> type, String function, Expression<?>... args);
640
}
641
```
642

643
**Usage Examples:**
644

645
```java
646
// Custom function registration
647
public class CustomFunctions {
648
    public static NumberExpression<Double> calculateDistance(
649
            NumberExpression<Double> lat1, NumberExpression<Double> lon1,
650
            NumberExpression<Double> lat2, NumberExpression<Double> lon2) {
651
        return new RelationalFunctionCall<>(Double.class, "calculate_distance", 
652
            lat1, lon1, lat2, lon2);
653
    }
654
    
655
    public static StringExpression formatCurrency(NumberExpression<? extends Number> amount, 
656
                                                StringExpression currencyCode) {
657
        return new RelationalFunctionCall<>(String.class, "format_currency", 
658
            amount, currencyCode);
659
    }
660
}
661

662
// Usage of custom functions
663
List<Tuple> storeDistances = queryFactory
664
    .select(qStore.name,
665
        CustomFunctions.calculateDistance(
666
            SQLExpressions.constant(40.7128), // NYC latitude
667
            SQLExpressions.constant(-74.0060), // NYC longitude
668
            qStore.latitude,
669
            qStore.longitude).as("distance"))
670
    .from(qStore)
671
    .orderBy(CustomFunctions.calculateDistance(
672
        SQLExpressions.constant(40.7128),
673
        SQLExpressions.constant(-74.0060),
674
        qStore.latitude,
675
        qStore.longitude).asc())
676
    .fetch();
677
```