0
# Multi-Database Support
1

2
Vendor-specific implementations optimized for different database systems with consistent API across all supported databases, providing native metadata access for 15+ database vendors.
3

4
## Capabilities
5

6
### Supported Database Vendors
7

8
Comprehensive support for major relational database systems with vendor-specific optimizations.
9

10
```java { .api }
11
/**
12
 * PostgreSQL database implementation
13
 * Supports advanced PostgreSQL features including arrays, enums, domains, and extensions
14
 */
15
class PostgresDatabase extends AbstractDatabase { }
16

17
/**
18
 * MySQL database implementation  
19
 * Supports MySQL-specific features including unsigned types and storage engines
20
 */
21
class MySQLDatabase extends AbstractDatabase { }
22

23
/**
24
 * MariaDB database implementation
25
 * Optimized for MariaDB-specific features and compatibility
26
 */
27
class MariaDBDatabase extends AbstractDatabase { }
28

29
/**
30
 * H2 database implementation
31
 * Supports H2 in-memory and file-based modes
32
 */
33
class H2Database extends AbstractDatabase { }
34

35
/**
36
 * SQLite database implementation
37
 * Optimized for SQLite's lightweight architecture
38
 */
39
class SQLiteDatabase extends AbstractDatabase { }
40

41
/**
42
 * HSQLDB database implementation
43
 * Supports HSQLDB in-memory and persistent modes
44
 */
45
class HSQLDBDatabase extends AbstractDatabase { }
46

47
/**
48
 * Derby database implementation
49
 * Supports both embedded and network Derby modes
50
 */
51
class DerbyDatabase extends AbstractDatabase { }
52

53
/**
54
 * Firebird database implementation
55
 * Supports Firebird-specific features and syntax
56
 */
57
class FirebirdDatabase extends AbstractDatabase { }
58

59
/**
60
 * ClickHouse database implementation
61
 * Optimized for ClickHouse analytical database features
62
 */
63
class ClickHouseDatabase extends AbstractDatabase { }
64

65
/**
66
 * YugabyteDB database implementation
67
 * Supports YugabyteDB distributed SQL features
68
 */
69
class YugabyteDBDatabase extends AbstractDatabase { }
70

71
/**
72
 * CUBRID database implementation
73
 * Supports CUBRID object-relational features
74
 */
75
class CUBRIDDatabase extends AbstractDatabase { }
76

77
/**
78
 * Apache Ignite database implementation
79
 * Supports Ignite in-memory computing platform
80
 */
81
class IgniteDatabase extends AbstractDatabase { }
82
```
83

84
**Usage Examples:**
85

86
```java
87
import org.jooq.meta.Database;
88
import org.jooq.meta.postgres.PostgresDatabase;
89
import org.jooq.meta.mysql.MySQLDatabase;
90
import org.jooq.meta.h2.H2Database;
91
import java.sql.Connection;
92
import java.sql.DriverManager;
93

94
// PostgreSQL
95
Database postgresDb = new PostgresDatabase();
96
Connection pgConn = DriverManager.getConnection(
97
    "jdbc:postgresql://localhost:5432/mydb", "user", "pass");
98
postgresDb.setConnection(pgConn);
99

100
// MySQL
101
Database mysqlDb = new MySQLDatabase();
102
Connection mysqlConn = DriverManager.getConnection(
103
    "jdbc:mysql://localhost:3306/mydb", "user", "pass");
104
mysqlDb.setConnection(mysqlConn);
105

106
// H2 (in-memory)
107
Database h2Db = new H2Database();
108
Connection h2Conn = DriverManager.getConnection(
109
    "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");
110
h2Db.setConnection(h2Conn);
111

112
// Using factory method (recommended)
113
Database db = Databases.database(SQLDialect.POSTGRES);
114
db.setConnection(connection);
115
```
116

117
### Generic JDBC Implementation
118

119
Fallback implementation for databases without specific vendor implementations.
120

121
```java { .api }
122
/**
123
 * Generic JDBC database implementation
124
 * Uses standard JDBC metadata methods for unsupported databases
125
 */
126
class JDBCDatabase extends AbstractDatabase {
127
    /**
128
     * Creates JDBC database with specific dialect
129
     * @param dialect - SQL dialect for the target database
130
     */
131
    public JDBCDatabase(SQLDialect dialect);
132
}
133
```
134

135
**Usage Examples:**
136

137
```java
138
import org.jooq.meta.jdbc.JDBCDatabase;
139
import org.jooq.SQLDialect;
140

141
// Generic JDBC for Oracle (when using Oracle-specific features)
142
Database oracleDb = new JDBCDatabase(SQLDialect.ORACLE);
143
Connection oracleConn = DriverManager.getConnection(
144
    "jdbc:oracle:thin:@localhost:1521:xe", "user", "pass");
145
oracleDb.setConnection(oracleConn);
146

147
// Generic JDBC for SQL Server
148
Database sqlServerDb = new JDBCDatabase(SQLDialect.SQLSERVER);
149
Connection sqlConn = DriverManager.getConnection(
150
    "jdbc:sqlserver://localhost:1433;databaseName=mydb", "user", "pass");
151
sqlServerDb.setConnection(sqlConn);
152
```
153

154
### XML-Based Database Implementation
155

156
XML-based database implementation for testing and documentation purposes.
157

158
```java { .api }
159
/**
160
 * XML database implementation
161
 * Reads database metadata from XML files instead of live database connection
162
 */  
163
class XMLDatabase extends AbstractDatabase {
164
    /**
165
     * Sets XML file path for database metadata
166
     * @param xmlFile - Path to XML file containing database structure
167
     */
168
    void setXmlFile(String xmlFile);
169
    
170
    /**
171
     * Gets XML file path
172
     * @returns Path to XML metadata file
173
     */
174
    String getXmlFile();
175
}
176
```
177

178
**Usage Examples:**
179

180
```java
181
import org.jooq.meta.xml.XMLDatabase;
182

183
// XML-based database for testing
184
XMLDatabase xmlDb = new XMLDatabase();
185
xmlDb.setXmlFile("/path/to/database-structure.xml");
186
// No JDBC connection needed for XML database
187

188
List<TableDefinition> tables = xmlDb.getTables();
189
// Reads table definitions from XML file
190
```
191

192
### Database Factory and Discovery
193

194
Utility methods for discovering and creating appropriate database implementations.
195

196
```java { .api }
197
/**
198
 * Gets Database implementation class for SQL dialect
199
 * @param dialect - SQL dialect to get implementation for
200
 * @returns Class object for database implementation
201
 */
202
static Class<? extends Database> databaseClass(SQLDialect dialect);
203

204
/**
205
 * Creates Database instance for SQL dialect
206
 * @param dialect - SQL dialect to create database for
207
 * @returns Database instance for the dialect
208
 */
209
static Database database(SQLDialect dialect);
210

211
/**
212
 * Creates Database instance with immediate connection
213
 * @param dialect - SQL dialect
214
 * @param connection - Database connection
215
 * @returns Connected Database instance
216
 */
217
static Database database(SQLDialect dialect, Connection connection);
218
```
219

220
**Usage Examples:**
221

222
```java
223
import org.jooq.meta.Databases;
224
import org.jooq.SQLDialect;
225

226
// Discover available implementations
227
Class<? extends Database> pgClass = Databases.databaseClass(SQLDialect.POSTGRES);
228
Class<? extends Database> mysqlClass = Databases.databaseClass(SQLDialect.MYSQL);
229

230
System.out.println("PostgreSQL implementation: " + pgClass.getSimpleName());
231
System.out.println("MySQL implementation: " + mysqlClass.getSimpleName());
232

233
// Create instances using factory
234
Database postgres = Databases.database(SQLDialect.POSTGRES);
235
Database mysql = Databases.database(SQLDialect.MYSQL);
236
Database h2 = Databases.database(SQLDialect.H2);
237

238
// Create with immediate connection
239
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");
240
Database h2Connected = Databases.database(SQLDialect.H2, conn);
241
```
242

243
### Vendor-Specific Features
244

245
Each database implementation supports vendor-specific features and optimizations.
246

247
#### PostgreSQL Specific Features
248

249
```java { .api }
250
/**
251
 * PostgreSQL-specific features supported:
252
 * - Array types (integer[], text[], etc.)
253
 * - Enum types with literal values
254
 * - Domain types with constraints
255
 * - PostgreSQL extensions and schemas
256
 * - JSON/JSONB column types
257
 * - Range types and geometric types
258
 * - Composite types and custom aggregates
259
 */
260
class PostgresDatabase extends AbstractDatabase {
261
    // Inherits all Database interface methods
262
    // Optimized for PostgreSQL system catalogs (pg_catalog)
263
}
264
```
265

266
#### MySQL Specific Features
267

268
```java { .api }
269
/**
270
 * MySQL-specific features supported:
271
 * - Unsigned integer types (TINYINT UNSIGNED, etc.)
272
 * - MySQL-specific data types (YEAR, GEOMETRY, etc.)
273
 * - Storage engine information
274
 * - MySQL partitioning metadata
275
 * - Full-text indexes and spatial indexes
276
 * - MySQL-specific constraints and triggers
277
 */
278
class MySQLDatabase extends AbstractDatabase {
279
    // Inherits all Database interface methods
280
    // Optimized for MySQL information_schema and SHOW commands
281
}
282
```
283

284
#### H2 Specific Features
285

286
```java { .api }
287
/**
288
 * H2-specific features supported:
289
 * - In-memory and file-based databases
290
 * - H2-specific data types and functions
291
 * - Mixed-mode connections
292
 * - H2 compatibility modes (MySQL, PostgreSQL, etc.)
293
 * - H2-specific indexes and constraints
294
 */
295
class H2Database extends AbstractDatabase {
296
    // Inherits all Database interface methods
297
    // Optimized for H2 INFORMATION_SCHEMA
298
}
299
```
300

301
### Database-Specific Configuration
302

303
Configuration options specific to individual database implementations.
304

305
```java { .api }
306
/**
307
 * Sets whether database supports unsigned types
308
 * @param supportsUnsignedTypes - true if database supports unsigned types
309
 */
310
void setSupportsUnsignedTypes(boolean supportsUnsignedTypes);
311

312
/**
313
 * Gets unsigned type support flag
314
 * @returns true if database supports unsigned types
315
 */
316
boolean supportsUnsignedTypes();
317

318
/**
319
 * Sets whether to include integer display widths in metadata
320
 * @param integerDisplayWidths - true to include display widths
321
 */
322
void setIntegerDisplayWidths(boolean integerDisplayWidths);
323

324
/**
325
 * Gets integer display width inclusion setting
326
 * @returns true if display widths are included
327
 */
328
boolean integerDisplayWidths();
329

330
/**
331
 * Sets whether to ignore procedure return values
332
 * @param ignoreProcedureReturnValues - true to ignore return values
333
 */
334
void setIgnoreProcedureReturnValues(boolean ignoreProcedureReturnValues);
335

336
/**
337
 * Gets procedure return value handling setting
338
 * @returns true if return values are ignored
339
 */
340
boolean ignoreProcedureReturnValues();
341

342
/**
343
 * Sets whether DATE columns should be treated as TIMESTAMP
344
 * @param dateAsTimestamp - true to treat DATE as TIMESTAMP
345
 */
346
void setDateAsTimestamp(boolean dateAsTimestamp);
347

348
/**
349
 * Gets DATE as TIMESTAMP treatment setting
350
 * @returns true if DATE is treated as TIMESTAMP
351
 */
352
boolean dateAsTimestamp();
353

354
/**
355
 * Sets whether to use java.time types instead of java.sql types
356
 * @param javaTimeTypes - true to use java.time types
357
 */
358
void setJavaTimeTypes(boolean javaTimeTypes);
359

360
/**
361
 * Gets java.time type usage setting
362
 * @returns true if java.time types are used
363
 */
364
boolean javaTimeTypes();
365
```
366

367
**Usage Examples:**
368

369
```java
370
import org.jooq.meta.mysql.MySQLDatabase;
371
import org.jooq.meta.postgres.PostgresDatabase;
372

373
// MySQL-specific configuration
374
MySQLDatabase mysqlDb = new MySQLDatabase();
375
mysqlDb.setSupportsUnsignedTypes(true);      // MySQL supports unsigned types
376
mysqlDb.setIntegerDisplayWidths(true);       // Include MySQL display widths
377
mysqlDb.setConnection(mysqlConnection);
378

379
// PostgreSQL-specific configuration  
380
PostgresDatabase pgDb = new PostgresDatabase();
381
pgDb.setSupportsUnsignedTypes(false);        // PostgreSQL doesn't have unsigned
382
pgDb.setJavaTimeTypes(true);                 // Use java.time types
383
pgDb.setConnection(pgConnection);
384

385
// Database-specific optimizations are automatically applied
386
List<TableDefinition> mysqlTables = mysqlDb.getTables();
387
List<EnumDefinition> pgEnums = pgDb.getEnums(schema);
388
```
389

390
### Performance Optimization
391

392
Database implementations include performance optimizations for metadata extraction.
393

394
```java { .api }
395
/**
396
 * Sets slow query logging threshold
397
 * @param logSlowQueriesAfterSeconds - Threshold in seconds for query logging
398
 */
399
void setLogSlowQueriesAfterSeconds(int logSlowQueriesAfterSeconds);
400

401
/**
402
 * Sets slow result logging threshold  
403
 * @param logSlowResultsAfterSeconds - Threshold in seconds for result logging
404
 */
405
void setLogSlowResultsAfterSeconds(int logSlowResultsAfterSeconds);
406

407
/**
408
 * Checks if array type detection is optimized
409
 * @param dataType - Data type string to check
410
 * @returns true if the type is an array type
411
 */
412
boolean isArrayType(String dataType);
413
```
414

415
## Types
416

417
```java { .api }
418
abstract class AbstractDatabase implements Database {
419
    // Common implementation for all database vendors
420
    // Provides standard JDBC-based metadata extraction
421
    // Subclasses override methods for vendor-specific optimizations
422
}
423

424
interface Database extends AutoCloseable {
425
    // All database implementations provide the same interface
426
    // Vendor-specific optimizations are transparent to users
427
    SQLDialect getDialect();
428
    void setDialect(SQLDialect dialect);
429
    Connection getConnection();
430
    void setConnection(Connection connection);
431
}
432
```
433

434
**Usage Examples:**
435

436
```java
437
import org.jooq.meta.*;
438
import org.jooq.SQLDialect;
439

440
// Multi-database application support
441
public class MultiDatabaseAnalyzer {
442
    
443
    public void analyzeDatabase(SQLDialect dialect, Connection connection) {
444
        try (Database database = Databases.database(dialect)) {
445
            database.setConnection(connection);
446
            
447
            // Same API works across all database vendors
448
            List<SchemaDefinition> schemas = database.getSchemata();
449
            System.out.println("Database: " + dialect.getName());
450
            System.out.println("Schemas: " + schemas.size());
451
            
452
            for (SchemaDefinition schema : schemas) {
453
                List<TableDefinition> tables = database.getTables(schema);
454
                System.out.println("  Schema " + schema.getName() + 
455
                                 ": " + tables.size() + " tables");
456
                
457
                // Vendor-specific features are automatically handled
458
                if (dialect.family() == SQLDialect.POSTGRES) {
459
                    List<EnumDefinition> enums = database.getEnums(schema);
460
                    System.out.println("    Enums: " + enums.size());
461
                }
462
                
463
                if (dialect.family() == SQLDialect.MYSQL) {
464
                    // MySQL unsigned types are properly detected
465
                    tables.stream()
466
                        .flatMap(t -> t.getColumns().stream())
467
                        .filter(c -> c.getType().getType().contains("UNSIGNED"))
468
                        .forEach(c -> System.out.println("    Unsigned: " + 
469
                                                       c.getName()));
470
                }
471
            }
472
        }
473
    }
474
    
475
    public static void main(String[] args) {
476
        MultiDatabaseAnalyzer analyzer = new MultiDatabaseAnalyzer();
477
        
478
        // Analyze PostgreSQL
479
        Connection pgConn = DriverManager.getConnection(/*...*/);
480
        analyzer.analyzeDatabase(SQLDialect.POSTGRES, pgConn);
481
        
482
        // Analyze MySQL  
483
        Connection mysqlConn = DriverManager.getConnection(/*...*/);
484
        analyzer.analyzeDatabase(SQLDialect.MYSQL, mysqlConn);
485
        
486
        // Analyze H2
487
        Connection h2Conn = DriverManager.getConnection(/*...*/);
488
        analyzer.analyzeDatabase(SQLDialect.H2, h2Conn);
489
    }
490
}
491
```