0
# Database Connection Management
1

2
The database connection management system provides efficient PostgreSQL connectivity using HikariCP connection pooling. This system handles all database connections for metadata operations, providing both DataSource and Connection-level access with proper resource management.
3

4
## Capabilities
5

6
### DBConnector Class
7

8
The central connection management class providing singleton access to database connections with HikariCP pooling.
9

10
```java { .api }
11
/**
12
 * Database connection management using HikariCP connection pooling
13
 * Provides singleton access to database connections for PostgreSQL backend
14
 */
15
public class DBConnector {
16
    // Static methods for singleton access - no public constructor
17
}
18
```
19

20
### Connection Access
21

22
Methods for obtaining database connections and data sources with proper resource management.
23

24
```java { .api }
25
/**
26
 * Get HikariCP DataSource instance (singleton)
27
 * Creates new connection pool on first access using database configuration
28
 * @return DataSource instance for connection pooling
29
 */
30
public static synchronized DataSource getDS();
31

32
/**
33
 * Get database connection from connection pool
34
 * @return Connection instance from HikariCP pool
35
 * @throws SQLException if connection cannot be obtained
36
 */
37
public static synchronized Connection getConn() throws SQLException;
38
```
39

40
### Connection Cleanup
41

42
Methods for properly closing connections and managing connection pool lifecycle.
43

44
```java { .api }
45
/**
46
 * Close all connections and shutdown connection pool
47
 * Should be called during application shutdown
48
 */
49
public static synchronized void closeAllConnections();
50

51
/**
52
 * Close specific database connection
53
 * @param conn Connection to close (null-safe)
54
 */
55
public static void closeConn(Connection conn);
56

57
/**
58
 * Close statement and connection
59
 * @param statement Statement to close (null-safe)
60
 * @param conn Connection to close (null-safe)
61
 */
62
public static void closeConn(Statement statement, Connection conn);
63

64
/**
65
 * Close result set, statement, and connection
66
 * @param set ResultSet to close (null-safe)
67
 * @param statement Statement to close (null-safe)  
68
 * @param conn Connection to close (null-safe)
69
 */
70
public static void closeConn(ResultSet set, Statement statement, Connection conn);
71
```
72

73
### Database Configuration
74

75
Database configuration is managed through the `DBUtil` and `DataBaseProperty` classes which provide connection parameters.
76

77
```java { .api }
78
/**
79
 * Utility class for database configuration management
80
 */
81
public class DBUtil {
82
    // Configuration keys for database connection
83
    public static final String urlKey = "lakesoul.pg.url";
84
    public static final String usernameKey = "lakesoul.pg.username"; 
85
    public static final String passwordKey = "lakesoul.pg.password";
86
    public static final String domainKey = "lakesoul.current.domain";
87
    
88
    /**
89
     * Get database connection information from configuration
90
     * @return DataBaseProperty object with connection details
91
     */
92
    public static DataBaseProperty getDBInfo();
93
    
94
    /**
95
     * Fill HikariCP DataSource configuration with connection parameters
96
     * @param config HikariConfig object to configure
97
     */
98
    public static void fillDataSourceConfig(HikariConfig config);
99
}
100

101
/**
102
 * Data transfer object for database connection properties
103
 */
104
public class DataBaseProperty {
105
    /**
106
     * Get JDBC driver class name
107
     * @return Driver class name (typically "org.postgresql.Driver")
108
     */
109
    public String getDriver();
110
    
111
    /**
112
     * Get database connection URL
113
     * @return JDBC URL for PostgreSQL connection
114
     */
115
    public String getUrl();
116
    
117
    /**
118
     * Get database username
119
     * @return Username for database authentication
120
     */
121
    public String getUsername();
122
    
123
    /**
124
     * Get database password
125
     * @return Password for database authentication
126
     */
127
    public String getPassword();
128
    
129
    /**
130
     * Get database name
131
     * @return Name of the target database
132
     */
133
    public String getDbName();
134
    
135
    /**
136
     * Get database host
137
     * @return Hostname or IP address of database server
138
     */
139
    public String getHost();
140
    
141
    /**
142
     * Get database port
143
     * @return Port number for database connection
144
     */
145
    public int getPort();
146
    
147
    /**
148
     * Get maximum commit attempt count
149
     * @return Maximum number of retry attempts for commits
150
     */
151
    public int getMaxCommitAttempt();
152
    
153
    // Corresponding setter methods
154
    public void setDriver(String driver);
155
    public void setUrl(String url);
156
    public void setUsername(String username);
157
    public void setPassword(String password);
158
    public void setDbName(String dbName);
159
    public void setHost(String host);
160
    public void setPort(int port);
161
    public void setMaxCommitAttempt(int maxCommitAttempt);
162
    
163
    /**
164
     * String representation of database properties
165
     * @return Formatted string with connection details (password masked)
166
     */
167
    public String toString();
168
}
169
```
170

171
### DAO Factory
172

173
The `DBFactory` class provides singleton access to Data Access Objects that use the connection management system.
174

175
```java { .api }
176
/**
177
 * Factory class for creating DAO instances using singleton pattern
178
 * All DAOs use the DBConnector for database access
179
 */
180
public class DBFactory {
181
    /**
182
     * Get NamespaceDao singleton instance
183
     * @return NamespaceDao instance for namespace operations
184
     */
185
    public static NamespaceDao getNamespaceDao();
186
    
187
    /**
188
     * Get TableInfoDao singleton instance
189
     * @return TableInfoDao instance for table metadata operations
190
     */
191
    public static TableInfoDao getTableInfoDao();
192
    
193
    /**
194
     * Get TableNameIdDao singleton instance
195
     * @return TableNameIdDao instance for table name mapping operations
196
     */
197
    public static TableNameIdDao getTableNameIdDao();
198
    
199
    /**
200
     * Get TablePathIdDao singleton instance
201
     * @return TablePathIdDao instance for table path mapping operations
202
     */
203
    public static TablePathIdDao getTablePathIdDao();
204
    
205
    /**
206
     * Get DataCommitInfoDao singleton instance
207
     * @return DataCommitInfoDao instance for data commit operations
208
     */
209
    public static DataCommitInfoDao getDataCommitInfoDao();
210
    
211
    /**
212
     * Get PartitionInfoDao singleton instance
213
     * @return PartitionInfoDao instance for partition metadata operations
214
     */
215
    public static PartitionInfoDao getPartitionInfoDao();
216
}
217
```
218

219
**Usage Examples:**
220

221
```java
222
import com.dmetasoul.lakesoul.meta.DBConnector;
223
import com.dmetasoul.lakesoul.meta.DBUtil;
224
import com.dmetasoul.lakesoul.meta.DataBaseProperty;
225
import javax.sql.DataSource;
226
import java.sql.*;
227

228
// Basic connection usage
229
public class DatabaseExample {
230
    
231
    public void basicConnectionExample() throws SQLException {
232
        // Get connection from pool
233
        Connection conn = null;
234
        PreparedStatement stmt = null;
235
        ResultSet rs = null;
236
        
237
        try {
238
            // Get connection from HikariCP pool
239
            conn = DBConnector.getConn();
240
            
241
            // Execute query
242
            stmt = conn.prepareStatement("SELECT * FROM table_info WHERE table_id = ?");
243
            stmt.setString(1, "table_123");
244
            rs = stmt.executeQuery();
245
            
246
            // Process results
247
            while (rs.next()) {
248
                String tableName = rs.getString("table_name");
249
                String tablePath = rs.getString("table_path");
250
                System.out.println("Table: " + tableName + " at " + tablePath);
251
            }
252
            
253
        } finally {
254
            // Proper cleanup - connection returned to pool
255
            DBConnector.closeConn(rs, stmt, conn);
256
        }
257
    }
258
    
259
    public void dataSourceExample() {
260
        // Get DataSource for advanced usage
261
        DataSource ds = DBConnector.getDS();
262
        
263
        // Use with connection pools, frameworks, etc.
264
        try (Connection conn = ds.getConnection()) {
265
            // Your database operations here
266
            DatabaseMetaData metaData = conn.getMetaData();
267
            System.out.println("Database: " + metaData.getDatabaseProductName());
268
            System.out.println("Version: " + metaData.getDatabaseProductVersion());
269
        } catch (SQLException e) {
270
            e.printStackTrace();
271
        }
272
    }
273
    
274
    public void configurationExample() {
275
        // Get database configuration
276
        DataBaseProperty dbProps = DBUtil.getDBInfo();
277
        
278
        System.out.println("Database URL: " + dbProps.getUrl());
279
        System.out.println("Database Name: " + dbProps.getDbName());
280
        System.out.println("Host: " + dbProps.getHost());
281
        System.out.println("Port: " + dbProps.getPort());
282
        System.out.println("Username: " + dbProps.getUsername());
283
        System.out.println("Max Commit Attempts: " + dbProps.getMaxCommitAttempt());
284
        
285
        // Note: Password is not exposed for security
286
    }
287
    
288
    public void shutdownExample() {
289
        // During application shutdown
290
        try {
291
            // Close all connections and shutdown pool
292
            DBConnector.closeAllConnections();
293
            System.out.println("Database connections closed successfully");
294
        } catch (Exception e) {
295
            System.err.println("Error closing database connections: " + e.getMessage());
296
        }
297
    }
298
}
299
```
300

301
**Configuration Requirements:**
302

303
The database connection requires the following system properties or environment variables:
304

305
```properties
306
# Required PostgreSQL connection settings
307
lakesoul.pg.url=jdbc:postgresql://localhost:5432/lakesoul_test
308
lakesoul.pg.username=lakesoul_test
309
lakesoul.pg.password=lakesoul_test
310

311
# Optional security domain setting
312
lakesoul.current.domain=public
313
```
314

315
**Connection Pool Configuration:**
316

317
The HikariCP connection pool is automatically configured with optimal settings:
318

319
- **Driver**: `org.postgresql.Driver`
320
- **Connection Validation**: Automatic validation on borrow
321
- **Pool Sizing**: Dynamic based on system resources
322
- **Connection Timeout**: Configurable via HikariCP settings
323
- **Idle Timeout**: Automatic cleanup of idle connections
324
- **Maximum Lifetime**: Prevents connection leaks
325

326
**Error Handling:**
327

328
The connection management system handles various error scenarios:
329

330
- **Connection Failures**: Automatic retry with exponential backoff
331
- **Pool Exhaustion**: Blocking wait with timeout for available connections
332
- **Database Unavailability**: Proper exception propagation with detailed error messages
333
- **Configuration Errors**: Clear error messages during initialization
334

335
**Thread Safety:**
336

337
All connection management operations are thread-safe:
338

339
- **Singleton Initialization**: Thread-safe lazy initialization using synchronized blocks
340
- **Connection Pool**: HikariCP provides thread-safe connection pooling
341
- **Resource Cleanup**: Safe cleanup operations with null checks
342
- **Concurrent Access**: Multiple threads can safely obtain connections simultaneously