0
# Database Templates
1

2
QueryDSL SQL provides database-specific SQL dialect support through template classes that handle SQL syntax differences, function mappings, data type handling, and database-specific optimizations for 17+ major databases.
3

4
## Capabilities
5

6
### Base SQL Templates
7

8
Core template functionality providing the foundation for database-specific implementations.
9

10
```java { .api }
11
/**
12
 * Base class for all database-specific SQL templates
13
 */
14
public abstract class SQLTemplates {
15
    /**
16
     * Gets the SQL type name for a JDBC type code
17
     * @param code JDBC type code
18
     * @return SQL type name for this database
19
     */
20
    public String getTypeNameForCode(int code);
21
    
22
    /**
23
     * Checks if this database supports SQL arrays
24
     * @return true if arrays are supported
25
     */
26
    public boolean isArraysSupported();
27
    
28
    /**
29
     * Checks if LIMIT/OFFSET clauses are supported
30
     * @return true if LIMIT/OFFSET is supported
31
     */
32
    public boolean isLimitOffsetSupported();
33
    
34
    /**
35
     * Checks if batch operations are supported
36
     * @return true if batch operations are supported
37
     */
38
    public boolean isBatchSupported();
39
    
40
    /**
41
     * Gets the quote character for identifiers
42
     * @return Quote character (usually " or `)
43
     */
44
    public char getQuoteChar();
45
    
46
    /**
47
     * Checks if identifiers should be quoted
48
     * @return true if identifiers need quoting
49
     */
50
    public boolean isQuoteIdentifiers();
51
}
52
```
53

54
### PostgreSQL Templates
55

56
PostgreSQL-specific template with support for advanced features like arrays, JSON, and custom operators.
57

58
```java { .api }
59
/**
60
 * PostgreSQL database templates with advanced feature support
61
 */
62
public class PostgreSQLTemplates extends SQLTemplates {
63
    /**
64
     * Creates PostgreSQL templates with default configuration
65
     */
66
    public PostgreSQLTemplates();
67
    
68
    /**
69
     * Creates PostgreSQL templates with custom quote character
70
     * @param quote Character to use for quoting identifiers
71
     */
72
    public PostgreSQLTemplates(char quote);
73
    
74
    /**
75
     * Creates a builder for configuring PostgreSQL templates
76
     * @return Template builder
77
     */
78
    public static Builder builder();
79
    
80
    /**
81
     * Builder for PostgreSQL template configuration
82
     */
83
    public static class Builder {
84
        public Builder quote(char quote);
85
        public Builder escape(char escape);
86
        public Builder printSchema();
87
        public Builder useSchema(String schema);
88
        public PostgreSQLTemplates build();
89
    }
90
}
91
```
92

93
**Usage Examples:**
94

95
```java
96
// Default PostgreSQL templates
97
SQLTemplates templates = PostgreSQLTemplates.builder().build();
98

99
// PostgreSQL with custom configuration
100
SQLTemplates templates = PostgreSQLTemplates.builder()
101
    .quote('"')
102
    .printSchema()
103
    .useSchema("public")
104
    .build();
105

106
// Configuration with templates
107
Configuration config = new Configuration(templates);
108
SQLQueryFactory queryFactory = new SQLQueryFactory(config, dataSource);
109
```
110

111
### MySQL Templates
112

113
MySQL-specific templates with support for MySQL syntax variations and storage engines.
114

115
```java { .api }
116
/**
117
 * MySQL database templates with MySQL-specific optimizations
118
 */
119
public class MySQLTemplates extends SQLTemplates {
120
    /**
121
     * Creates MySQL templates with default configuration
122
     */
123
    public MySQLTemplates();
124
    
125
    /**
126
     * Creates MySQL templates with custom quote character
127
     * @param quote Character to use for quoting identifiers
128
     */
129
    public MySQLTemplates(char quote);
130
    
131
    /**
132
     * Creates a builder for configuring MySQL templates
133
     * @return Template builder
134
     */
135
    public static Builder builder();
136
}
137
```
138

139
### Oracle Templates
140

141
Oracle-specific templates with support for Oracle SQL syntax and PL/SQL features.
142

143
```java { .api }
144
/**
145
 * Oracle database templates with Oracle-specific features
146
 */
147
public class OracleTemplates extends SQLTemplates {
148
    /**
149
     * Creates Oracle templates with default configuration
150
     */
151
    public OracleTemplates();
152
    
153
    /**
154
     * Creates a builder for configuring Oracle templates
155
     * @return Template builder
156
     */
157
    public static Builder builder();
158
}
159
```
160

161
### SQL Server Templates
162

163
SQL Server templates with support for multiple SQL Server versions and T-SQL features.
164

165
```java { .api }
166
/**
167
 * Base SQL Server templates
168
 */
169
public class SQLServerTemplates extends SQLTemplates;
170

171
/**
172
 * SQL Server 2005 specific templates
173
 */
174
public class SQLServer2005Templates extends SQLServerTemplates;
175

176
/**
177
 * SQL Server 2008 specific templates with enhanced features
178
 */
179
public class SQLServer2008Templates extends SQLServer2005Templates;
180

181
/**
182
 * SQL Server 2012 templates with additional T-SQL features
183
 */
184
public class SQLServer2012Templates extends SQLServer2008Templates;
185
```
186

187
### Other Database Templates
188

189
Templates for additional database systems with their specific SQL dialects.
190

191
```java { .api }
192
/**
193
 * H2 database templates
194
 */
195
public class H2Templates extends SQLTemplates;
196

197
/**
198
 * HSQLDB database templates
199
 */
200
public class HSQLDBTemplates extends SQLTemplates;
201

202
/**
203
 * Apache Derby templates
204
 */
205
public class DerbyTemplates extends SQLTemplates;
206

207
/**
208
 * IBM DB2 templates
209
 */
210
public class DB2Templates extends SQLTemplates;
211

212
/**
213
 * SQLite templates
214
 */
215
public class SQLiteTemplates extends SQLTemplates;
216

217
/**
218
 * Firebird database templates
219
 */
220
public class FirebirdTemplates extends SQLTemplates;
221

222
/**
223
 * CUBRID database templates
224
 */
225
public class CUBRIDTemplates extends SQLTemplates;
226

227
/**
228
 * Teradata database templates
229
 */
230
public class TeradataTemplates extends SQLTemplates;
231
```
232

233
### Database-Specific Query Factories
234

235
Specialized query factories that provide database-specific optimizations and features.
236

237
```java { .api }
238
/**
239
 * PostgreSQL-optimized query factory
240
 */
241
public class PostgreSQLQueryFactory extends AbstractSQLQueryFactory<PostgreSQLQuery<?>> {
242
    public PostgreSQLQueryFactory(Configuration configuration, DataSource dataSource);
243
    public PostgreSQLQuery<?> query();
244
}
245

246
/**
247
 * MySQL-optimized query factory
248
 */
249
public class MySQLQueryFactory extends AbstractSQLQueryFactory<MySQLQuery<?>> {
250
    public MySQLQueryFactory(Configuration configuration, DataSource dataSource);
251
    public MySQLQuery<?> query();
252
    public MySQLReplaceClause replace(RelationalPath<?> path);
253
}
254

255
/**
256
 * Oracle-optimized query factory
257
 */
258
public class OracleQueryFactory extends AbstractSQLQueryFactory<OracleQuery<?>> {
259
    public OracleQueryFactory(Configuration configuration, DataSource dataSource);
260
    public OracleQuery<?> query();
261
}
262

263
/**
264
 * SQL Server-optimized query factory
265
 */
266
public class SQLServerQueryFactory extends AbstractSQLQueryFactory<SQLServerQuery<?>> {
267
    public SQLServerQueryFactory(Configuration configuration, DataSource dataSource);
268
    public SQLServerQuery<?> query();
269
}
270
```
271

272
### Database-Specific Query Classes
273

274
Specialized query implementations with database-specific features and optimizations.
275

276
```java { .api }
277
/**
278
 * PostgreSQL query with PostgreSQL-specific features
279
 */
280
public class PostgreSQLQuery<T> extends AbstractPostgreSQLQuery<T, PostgreSQLQuery<T>> {
281
    public PostgreSQLQuery(Connection conn, PostgreSQLTemplates templates);
282
    public PostgreSQLQuery<T> forShare();
283
    public PostgreSQLQuery<T> forUpdate();
284
}
285

286
/**
287
 * MySQL query with MySQL-specific features
288
 */
289
public class MySQLQuery<T> extends AbstractMySQLQuery<T, MySQLQuery<T>> {
290
    public MySQLQuery(Connection conn, MySQLTemplates templates);
291
    public MySQLQuery<T> straightJoin();
292
    public MySQLQuery<T> forUpdate();
293
}
294

295
/**
296
 * Oracle query with Oracle-specific features
297
 */
298
public class OracleQuery<T> extends AbstractOracleQuery<T, OracleQuery<T>> {
299
    public OracleQuery(Connection conn, OracleTemplates templates);
300
    public OracleQuery<T> hint(String hint);
301
    public OracleQuery<T> connectBy(Predicate cond);
302
}
303

304
/**
305
 * SQL Server query with T-SQL features
306
 */
307
public class SQLServerQuery<T> extends AbstractSQLServerQuery<T, SQLServerQuery<T>> {
308
    public SQLServerQuery(Connection conn, SQLServerTemplates templates);
309
    public SQLServerQuery<T> tableHints(SQLServerTableHints... hints);
310
    public SQLServerQuery<T> with(String... options);
311
}
312
```
313

314
### Template Registry
315

316
Central registry for managing and discovering database-specific templates.
317

318
```java { .api }
319
/**
320
 * Registry for database-specific SQL templates
321
 */
322
public class SQLTemplatesRegistry {
323
    /**
324
     * Gets templates for a specific database based on connection metadata
325
     * @param connection Database connection
326
     * @return Appropriate SQL templates for the database
327
     */
328
    public static SQLTemplates getTemplates(Connection connection);
329
    
330
    /**
331
     * Gets templates by database product name
332
     * @param productName Database product name
333
     * @return Appropriate SQL templates
334
     */
335
    public static SQLTemplates getTemplates(String productName);
336
    
337
    /**
338
     * Registers custom templates for a database product
339
     * @param productName Database product name
340
     * @param templates SQL templates to register
341
     */
342
    public static void register(String productName, SQLTemplates templates);
343
}
344
```
345

346
**Usage Examples:**
347

348
```java
349
// Automatic template detection
350
Connection connection = dataSource.getConnection();
351
SQLTemplates templates = SQLTemplatesRegistry.getTemplates(connection);
352
Configuration config = new Configuration(templates);
353

354
// Manual template selection by product name
355
SQLTemplates templates = SQLTemplatesRegistry.getTemplates("PostgreSQL");
356

357
// Register custom templates
358
class CustomPostgreSQLTemplates extends PostgreSQLTemplates {
359
    // Custom implementation
360
}
361

362
SQLTemplatesRegistry.register("PostgreSQL", new CustomPostgreSQLTemplates());
363
```
364

365
### Database-Specific Features
366

367
Specialized features available for specific databases through their template implementations.
368

369
```java { .api }
370
/**
371
 * MySQL-specific REPLACE operation
372
 */
373
public class MySQLReplaceClause extends AbstractSQLInsertClause<MySQLReplaceClause> {
374
    public MySQLReplaceClause(Connection conn, MySQLTemplates templates, RelationalPath<?> entity);
375
    public <T> MySQLReplaceClause set(Path<T> path, T value);
376
    public long execute();
377
}
378

379
/**
380
 * SQL Server table hints enumeration
381
 */
382
public enum SQLServerTableHints {
383
    NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, 
384
    SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX,
385
    PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, SNAPSHOT
386
}
387

388
/**
389
 * Teradata-specific query band support
390
 */
391
public class SetQueryBandClause {
392
    public SetQueryBandClause for(String target);
393
    public SetQueryBandClause set(String key, String value);
394
    public void execute();
395
}
396
```
397

398
**Usage Examples:**
399

400
```java
401
// MySQL REPLACE operation
402
MySQLQueryFactory mysqlFactory = new MySQLQueryFactory(config, dataSource);
403
mysqlFactory
404
    .replace(qUser)
405
    .set(qUser.name, "John Doe")  
406
    .set(qUser.email, "john@example.com")
407
    .execute();
408

409
// SQL Server with table hints
410
SQLServerQueryFactory sqlServerFactory = new SQLServerQueryFactory(config, dataSource);
411
List<User> users = sqlServerFactory
412
    .selectFrom(qUser)
413
    .tableHints(SQLServerTableHints.NOLOCK)
414
    .fetch();
415

416
// Oracle with hints
417
OracleQueryFactory oracleFactory = new OracleQueryFactory(config, dataSource);
418
List<User> users = oracleFactory
419
    .selectFrom(qUser)
420
    .hint("/*+ INDEX(user, user_email_idx) */")
421
    .fetch();
422
```
423

424
### Template Configuration
425

426
Configuration options for customizing template behavior and SQL generation.
427

428
```java { .api }
429
/**
430
 * Template builder pattern for configuration
431
 */
432
public static class TemplateBuilder {
433
    /**
434
     * Sets the quote character for identifiers
435
     * @param quote Quote character
436
     * @return Builder for method chaining
437
     */
438
    public TemplateBuilder quote(char quote);
439
    
440
    /**
441
     * Sets the escape character
442
     * @param escape Escape character
443
     * @return Builder for method chaining
444
     */
445
    public TemplateBuilder escape(char escape);
446
    
447
    /**
448
     * Enables schema printing in SQL
449
     * @return Builder for method chaining
450
     */
451
    public TemplateBuilder printSchema();
452
    
453
    /**
454
     * Sets default schema for operations
455
     * @param schema Schema name
456
     * @return Builder for method chaining
457
     */
458
    public TemplateBuilder useSchema(String schema);
459
    
460
    /**
461
     * Builds the configured templates
462
     * @return Configured SQL templates
463
     */
464
    public SQLTemplates build();
465
}
466
```
467

468
**Usage Examples:**
469

470
```java
471
// Custom PostgreSQL configuration
472
PostgreSQLTemplates templates = PostgreSQLTemplates.builder()
473
    .quote('`')                    // Use backticks instead of quotes
474
    .printSchema()                 // Include schema in SQL
475
    .useSchema("app_schema")       // Default schema
476
    .build();
477

478
// H2 configuration for testing
479
H2Templates h2Templates = H2Templates.builder()
480
    .quote('"')
481
    .build();
482

483
// SQL Server with custom settings
484
SQLServerTemplates sqlServerTemplates = SQLServerTemplates.builder()
485
    .quote('[')                    // Use square brackets
486
    .escape(']')
487
    .build();
488
```