0
# Multi-Database Support
1

2
Comprehensive database dialect system supporting 40+ database types including MySQL, PostgreSQL, Oracle, SQL Server, and specialized databases. MyBatis-Flex automatically handles database-specific SQL generation and optimization.
3

4
## Capabilities
5

6
### DbType Enumeration
7

8
Comprehensive enumeration of supported database types with automatic dialect detection and SQL generation.
9

10
```java { .api }
11
/**
12
 * Enumeration of supported database types
13
 */
14
public enum DbType {
15
    // Major Commercial Databases
16
    MYSQL, MARIADB, POSTGRESQL, ORACLE, SQL_SERVER, DB2,
17
    
18
    // Embedded Databases
19
    H2, SQLITE, DERBY, HSQL,
20
    
21
    // Cloud Databases
22
    REDSHIFT, SNOWFLAKE, BIGQUERY,
23
    
24
    // Chinese Databases
25
    DM, KINGBASE, GBASE, OSCAR, OPENGAUSS,
26
    
27
    // In-Memory Databases
28
    SAP_HANA, IGNITE,
29
    
30
    // Big Data & Analytics
31
    CLICKHOUSE, HIVE, TRINO, IMPALA, PRESTO,
32
    
33
    // NoSQL with SQL Interface
34
    CASSANDRA, ELASTICSEARCH,
35
    
36
    // Other Databases
37
    FIREBIRD, INFORMIX, SYBASE, ACCESS,
38
    PHOENIX, KYLIN, CUBRID, EDB,
39
    
40
    // Time Series Databases
41
    INFLUXDB, QUESTDB,
42
    
43
    // Additional Chinese Databases
44
    HIGHGO, VASTBASE, YASHANDB, ANTDB,
45
    
46
    // Specialized Databases
47
    LEALONE, SUNDB, XUGU, GREENPLUM;
48
    
49
    /**
50
     * Get database type from JDBC URL
51
     * @param jdbcUrl JDBC connection URL
52
     * @return detected DbType
53
     */
54
    public static DbType getDbType(String jdbcUrl);
55
    
56
    /**
57
     * Check if database supports specific feature
58
     * @param feature database feature to check
59
     * @return true if supported
60
     */
61
    public boolean supports(DatabaseFeature feature);
62
}
63
```
64

65
**Database Type Detection:**
66

67
```java
68
import com.mybatisflex.core.dialect.DbType;
69

70
public class DatabaseDetectionExample {
71
    public void demonstrateDetection() {
72
        // Automatic detection from JDBC URL
73
        String mysqlUrl = "jdbc:mysql://localhost:3306/testdb";
74
        DbType mysqlType = DbType.getDbType(mysqlUrl);
75
        // Returns: DbType.MYSQL
76
        
77
        String postgresUrl = "jdbc:postgresql://localhost:5432/testdb";
78
        DbType postgresType = DbType.getDbType(postgresUrl);
79
        // Returns: DbType.POSTGRESQL
80
        
81
        String oracleUrl = "jdbc:oracle:thin:@localhost:1521:xe";
82
        DbType oracleType = DbType.getDbType(oracleUrl);
83
        // Returns: DbType.ORACLE
84
        
85
        String sqlServerUrl = "jdbc:sqlserver://localhost:1433;databaseName=testdb";
86
        DbType sqlServerType = DbType.getDbType(sqlServerUrl);
87
        // Returns: DbType.SQL_SERVER
88
        
89
        // Chinese databases
90
        String dmUrl = "jdbc:dm://localhost:5236/testdb";
91
        DbType dmType = DbType.getDbType(dmUrl);
92
        // Returns: DbType.DM
93
        
94
        String kingbaseUrl = "jdbc:kingbase8://localhost:54321/testdb";
95
        DbType kingbaseType = DbType.getDbType(kingbaseUrl);
96
        // Returns: DbType.KINGBASE
97
    }
98
}
99
```
100

101
### IDialect Interface
102

103
Database-specific SQL generation interface that handles dialect differences and optimizations.
104

105
```java { .api }
106
/**
107
 * Database dialect interface for SQL generation
108
 */
109
public interface IDialect {
110
    /**
111
     * Generate SELECT SQL for query
112
     * @param queryWrapper query wrapper
113
     * @return generated SQL string
114
     */
115
    String forSelectByQuery(QueryWrapper queryWrapper);
116
    
117
    /**
118
     * Generate paginated SELECT SQL
119
     * @param queryWrapper query wrapper
120
     * @param offset result offset
121
     * @param rows number of rows
122
     * @return paginated SQL string
123
     */
124
    String forPaginateQuery(QueryWrapper queryWrapper, long offset, long rows);
125
    
126
    /**
127
     * Generate INSERT SQL for entity
128
     * @param entityClass entity class
129
     * @return INSERT SQL template
130
     */
131
    String forInsert(Class<?> entityClass);
132
    
133
    /**
134
     * Generate UPDATE SQL for entity
135
     * @param entityClass entity class
136
     * @return UPDATE SQL template
137
     */
138
    String forUpdate(Class<?> entityClass);
139
    
140
    /**
141
     * Generate DELETE SQL for entity
142
     * @param entityClass entity class
143
     * @return DELETE SQL template
144
     */
145
    String forDelete(Class<?> entityClass);
146
    
147
    /**
148
     * Wrap keyword with database-specific quotes
149
     * @param keyword SQL keyword or identifier
150
     * @return wrapped keyword
151
     */
152
    String wrap(String keyword);
153
    
154
    /**
155
     * Get limit offset processor for pagination
156
     * @return LimitOffsetProcessor instance
157
     */
158
    LimitOffsetProcessor getLimitOffsetProcessor();
159
    
160
    /**
161
     * Get keyword wrapper for identifiers
162
     * @return KeywordWrap instance
163
     */
164
    KeywordWrap getKeywordWrap();
165
    
166
    /**
167
     * Get data source key for multi-datasource scenarios
168
     * @return datasource key
169
     */
170
    String getDataSourceKey();
171
}
172
```
173

174
**Dialect Usage Examples:**
175

176
```java
177
import com.mybatisflex.core.dialect.IDialect;
178
import com.mybatisflex.core.dialect.impl.*;
179

180
public class DialectExample {
181
    public void demonstrateDialects() {
182
        // MySQL dialect
183
        IDialect mysqlDialect = new MysqlDialect();
184
        String mysqlWrapped = mysqlDialect.wrap("user");
185
        // Returns: `user`
186
        
187
        // PostgreSQL dialect
188
        IDialect postgresDialect = new PostgresqlDialect();
189
        String postgresWrapped = postgresDialect.wrap("user");
190
        // Returns: "user"
191
        
192
        // Oracle dialect
193
        IDialect oracleDialect = new OracleDialect();
194
        String oracleWrapped = oracleDialect.wrap("user");
195
        // Returns: "USER"
196
        
197
        // SQL Server dialect
198
        IDialect sqlServerDialect = new SqlserverDialectImpl();
199
        String sqlServerWrapped = sqlServerDialect.wrap("user");
200
        // Returns: [user]
201
        
202
        // Generate paginated query
203
        QueryWrapper query = QueryWrapper.create()
204
            .select()
205
            .from(User.class)
206
            .where(USER.ACTIVE.eq(true));
207
        
208
        String mysqlPaginated = mysqlDialect.forPaginateQuery(query, 0, 10);
209
        // MySQL: SELECT ... LIMIT 10 OFFSET 0
210
        
211
        String sqlServerPaginated = sqlServerDialect.forPaginateQuery(query, 0, 10);
212
        // SQL Server: SELECT ... OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
213
    }
214
}
215
```
216

217
### Database-Specific Implementations
218

219
Concrete dialect implementations for major database systems.
220

221
```java { .api }
222
// MySQL/MariaDB
223
public class MysqlDialect implements IDialect {
224
    // MySQL-specific SQL generation
225
}
226

227
// PostgreSQL
228
public class PostgresqlDialect implements IDialect {
229
    // PostgreSQL-specific SQL generation
230
}
231

232
// Oracle
233
public class OracleDialect implements IDialect {
234
    // Oracle-specific SQL generation
235
}
236

237
// SQL Server
238
public class SqlserverDialectImpl implements IDialect {
239
    // SQL Server-specific SQL generation
240
}
241

242
// DB2
243
public class DB2Dialect implements IDialect {
244
    // DB2-specific SQL generation
245
}
246

247
// ClickHouse
248
public class ClickhouseDialectImpl implements IDialect {
249
    // ClickHouse-specific SQL generation
250
}
251

252
// Chinese Database Dialects
253
public class DmDialect implements IDialect {
254
    // DM (达梦) database-specific SQL generation
255
}
256

257
public class KingbaseDialect implements IDialect {
258
    // Kingbase (人大金仓) database-specific SQL generation
259
}
260

261
public class GbaseDialect implements IDialect {
262
    // GBase database-specific SQL generation
263
}
264
```
265

266
### LimitOffsetProcessor
267

268
Handles database-specific pagination SQL generation.
269

270
```java { .api }
271
/**
272
 * Processes LIMIT and OFFSET clauses for different databases
273
 */
274
public interface LimitOffsetProcessor {
275
    /**
276
     * Process pagination for query
277
     * @param sql original SQL
278
     * @param offset result offset
279
     * @param limit maximum rows
280
     * @return paginated SQL
281
     */
282
    String process(String sql, long offset, long limit);
283
    
284
    /**
285
     * Check if database supports OFFSET clause
286
     * @return true if supported
287
     */
288
    boolean supportsOffset();
289
    
290
    /**
291
     * Check if database supports LIMIT clause
292
     * @return true if supported
293
     */
294
    boolean supportsLimit();
295
}
296
```
297

298
**Pagination Examples by Database:**
299

300
```java
301
public class PaginationExamples {
302
    public void demonstratePagination() {
303
        String baseSql = "SELECT id, name FROM users WHERE active = 1";
304
        
305
        // MySQL/PostgreSQL: LIMIT OFFSET
306
        String mysqlPaginated = "SELECT id, name FROM users WHERE active = 1 LIMIT 10 OFFSET 20";
307
        
308
        // SQL Server 2012+: OFFSET FETCH
309
        String sqlServerPaginated = "SELECT id, name FROM users WHERE active = 1 " +
310
            "ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY";
311
        
312
        // Oracle: ROWNUM (traditional)
313
        String oraclePaginated = "SELECT * FROM (" +
314
            "SELECT ROWNUM rn, t.* FROM (" +
315
            "SELECT id, name FROM users WHERE active = 1" +
316
            ") t WHERE ROWNUM <= 30" +
317
            ") WHERE rn > 20";
318
        
319
        // Oracle 12c+: OFFSET FETCH
320
        String oracle12cPaginated = "SELECT id, name FROM users WHERE active = 1 " +
321
            "ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY";
322
    }
323
}
324
```
325

326
### KeywordWrap
327

328
Handles database-specific identifier quoting and keyword escaping.
329

330
```java { .api }
331
/**
332
 * Handles keyword wrapping for different databases
333
 */
334
public interface KeywordWrap {
335
    /**
336
     * Wrap identifier with database-specific quotes
337
     * @param keyword identifier to wrap
338
     * @return wrapped identifier
339
     */
340
    String wrap(String keyword);
341
    
342
    /**
343
     * Check if keyword needs wrapping
344
     * @param keyword identifier to check
345
     * @return true if wrapping needed
346
     */
347
    boolean needsWrap(String keyword);
348
    
349
    /**
350
     * Get start quote character
351
     * @return start quote
352
     */
353
    String getStartQuote();
354
    
355
    /**
356
     * Get end quote character
357
     * @return end quote
358
     */
359
    String getEndQuote();
360
}
361
```
362

363
**Keyword Wrapping Examples:**
364

365
```java
366
public class KeywordWrappingExamples {
367
    public void demonstrateWrapping() {
368
        // MySQL: Backticks
369
        String mysqlUser = "`user`";
370
        String mysqlOrder = "`order`";
371
        
372
        // PostgreSQL: Double quotes
373
        String postgresUser = "\"user\"";
374
        String postgresOrder = "\"order\"";
375
        
376
        // SQL Server: Square brackets
377
        String sqlServerUser = "[user]";
378
        String sqlServerOrder = "[order]";
379
        
380
        // Oracle: Double quotes (uppercase)
381
        String oracleUser = "\"USER\"";
382
        String oracleOrder = "\"ORDER\"";
383
        
384
        // H2: Double quotes
385
        String h2User = "\"user\"";
386
        String h2Order = "\"order\"";
387
    }
388
}
389
```
390

391
### DatabaseFeature Enumeration
392

393
Defines database capabilities and features for feature detection.
394

395
```java { .api }
396
/**
397
 * Database feature enumeration for capability detection
398
 */
399
public enum DatabaseFeature {
400
    // SQL Standard Features
401
    LIMIT_OFFSET,
402
    WINDOW_FUNCTIONS,
403
    COMMON_TABLE_EXPRESSIONS,
404
    RECURSIVE_CTE,
405
    
406
    // Join Types
407
    FULL_OUTER_JOIN,
408
    CROSS_JOIN,
409
    NATURAL_JOIN,
410
    
411
    // Data Types
412
    JSON_TYPE,
413
    ARRAY_TYPE,
414
    UUID_TYPE,
415
    BOOLEAN_TYPE,
416
    
417
    // Advanced Features
418
    UPSERT,
419
    MERGE_STATEMENT,
420
    RETURNING_CLAUSE,
421
    BULK_INSERT,
422
    
423
    // Index Features
424
    PARTIAL_INDEX,
425
    EXPRESSION_INDEX,
426
    UNIQUE_INDEX,
427
    
428
    // Transaction Features
429
    SAVEPOINTS,
430
    READ_UNCOMMITTED,
431
    SERIALIZABLE,
432
    
433
    // Specific SQL Features
434
    REGEXP_OPERATOR,
435
    CASE_INSENSITIVE_LIKE,
436
    NULLS_FIRST_LAST,
437
    
438
    // Chinese Database Features
439
    ENCRYPT_COLUMN,
440
    AUDIT_TRAIL,
441
    ROW_LEVEL_SECURITY;
442
}
443
```
444

445
**Feature Detection Examples:**
446

447
```java
448
public class FeatureDetectionExample {
449
    public void demonstrateFeatureDetection() {
450
        // Check database capabilities
451
        boolean mysqlSupportsJson = DbType.MYSQL.supports(DatabaseFeature.JSON_TYPE);
452
        // Returns: true (MySQL 5.7+)
453
        
454
        boolean sqliteSupportsWindowFunctions = DbType.SQLITE.supports(DatabaseFeature.WINDOW_FUNCTIONS);
455
        // Returns: true (SQLite 3.25+)
456
        
457
        boolean h2SupportsUpsert = DbType.H2.supports(DatabaseFeature.UPSERT);
458
        // Returns: true
459
        
460
        boolean accessSupportsFullOuterJoin = DbType.ACCESS.supports(DatabaseFeature.FULL_OUTER_JOIN);
461
        // Returns: false
462
        
463
        // Use feature detection in code
464
        if (DbType.POSTGRESQL.supports(DatabaseFeature.RETURNING_CLAUSE)) {
465
            // Use PostgreSQL RETURNING clause
466
            String sql = "INSERT INTO users (name, email) VALUES (?, ?) RETURNING id";
467
        } else {
468
            // Fallback approach
469
            String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
470
        }
471
    }
472
}
473
```
474

475
### Configuration Integration
476

477
Integration with global configuration for automatic dialect selection.
478

479
```java { .api }
480
/**
481
 * Database configuration in FlexGlobalConfig
482
 */
483
public class FlexGlobalConfig {
484
    /**
485
     * Set database type for automatic dialect selection
486
     * @param dbType database type
487
     */
488
    public void setDbType(DbType dbType);
489
    
490
    /**
491
     * Get configured database type
492
     * @return database type
493
     */
494
    public DbType getDbType();
495
    
496
    /**
497
     * Set custom dialect implementation
498
     * @param dialect custom dialect
499
     */
500
    public void setDialect(IDialect dialect);
501
    
502
    /**
503
     * Get current dialect
504
     * @return dialect implementation
505
     */
506
    public IDialect getDialect();
507
}
508
```
509

510
**Configuration Examples:**
511

512
```java
513
public class DatabaseConfigurationExample {
514
    public void configureDatabase() {
515
        // Automatic configuration
516
        FlexGlobalConfig config = FlexGlobalConfig.getDefaultConfig();
517
        config.setDbType(DbType.POSTGRESQL);
518
        
519
        // Custom dialect
520
        IDialect customDialect = new CustomPostgresDialect();
521
        config.setDialect(customDialect);
522
        
523
        // Multi-database configuration
524
        FlexGlobalConfig mysqlConfig = new FlexGlobalConfig();
525
        mysqlConfig.setDbType(DbType.MYSQL);
526
        FlexGlobalConfig.setConfig("mysql", mysqlConfig, false);
527
        
528
        FlexGlobalConfig postgresConfig = new FlexGlobalConfig();
529
        postgresConfig.setDbType(DbType.POSTGRESQL);
530
        FlexGlobalConfig.setConfig("postgres", postgresConfig, false);
531
    }
532
}
533
```
534

535
## Types
536

537
```java { .api }
538
// Core dialect types
539
public enum DbType {
540
    MYSQL, POSTGRESQL, ORACLE, SQL_SERVER, DB2,
541
    H2, SQLITE, CLICKHOUSE, DM, KINGBASE;
542
    
543
    public static DbType getDbType(String jdbcUrl);
544
    public boolean supports(DatabaseFeature feature);
545
}
546

547
public interface IDialect {
548
    String forSelectByQuery(QueryWrapper queryWrapper);
549
    String forPaginateQuery(QueryWrapper queryWrapper, long offset, long rows);
550
    String wrap(String keyword);
551
}
552

553
// Feature detection
554
public enum DatabaseFeature {
555
    LIMIT_OFFSET, WINDOW_FUNCTIONS, JSON_TYPE, UPSERT, RETURNING_CLAUSE;
556
}
557

558
// Pagination processing
559
public interface LimitOffsetProcessor {
560
    String process(String sql, long offset, long limit);
561
    boolean supportsOffset();
562
    boolean supportsLimit();
563
}
564

565
// Keyword wrapping
566
public interface KeywordWrap {
567
    String wrap(String keyword);
568
    boolean needsWrap(String keyword);
569
    String getStartQuote();
570
    String getEndQuote();
571
}
572
```