0
# JDBC Connection Utilities
1

2
Utility functions for JDBC connection management, query execution, and resource cleanup with robust error handling and connection validation. These utilities provide a higher-level interface for database operations in the testing framework.
3

4
## Capabilities
5

6
### JDBCConnectionUtil
7

8
Main utility object providing JDBC operation helpers and connection management functions.
9

10
```scala { .api }
11
/**
12
 * Utility object for JDBC connection management and operations
13
 * Provides helper functions for common JDBC tasks with proper error handling
14
 */
15
object JDBCConnectionUtil {
16
  
17
  /**
18
   * Create a JDBC connection with properties
19
   * @param url JDBC connection URL
20
   * @param properties Connection properties (username, password, etc.)
21
   * @return Active Connection object
22
   */
23
  def createConnection(url: String, properties: Properties): Connection
24
  
25
  /**
26
   * Create a JDBC connection with username and password
27
   * @param url JDBC connection URL
28
   * @param username Database username
29
   * @param password Database password
30
   * @return Active Connection object
31
   */
32
  def createConnection(url: String, username: String, password: String): Connection
33
  
34
  /**
35
   * Execute a SQL query and return ResultSet
36
   * @param connection Active database connection
37
   * @param sql SQL query to execute
38
   * @return ResultSet containing query results
39
   */
40
  def executeQuery(connection: Connection, sql: String): ResultSet
41
  
42
  /**
43
   * Execute a SQL update statement
44
   * @param connection Active database connection
45
   * @param sql SQL update/insert/delete statement
46
   * @return Number of affected rows
47
   */
48
  def executeUpdate(connection: Connection, sql: String): Int
49
  
50
  /**
51
   * Validate database connection health
52
   * @param connection Connection to validate
53
   * @return true if connection is valid and responsive
54
   */
55
  def validateConnection(connection: Connection): Boolean
56
  
57
  /**
58
   * Close JDBC resources safely
59
   * @param resources Variable number of AutoCloseable resources
60
   */
61
  def closeResources(resources: AutoCloseable*): Unit
62
  
63
  /**
64
   * Execute SQL with automatic resource cleanup
65
   * @param connection Database connection
66
   * @param sql SQL statement to execute
67
   * @param handler Function to process ResultSet
68
   * @return Result of handler function
69
   */
70
  def withStatement[T](connection: Connection, sql: String)(handler: ResultSet => T): T
71
  
72
  /**
73
   * Get database metadata information
74
   * @param connection Database connection
75
   * @return DatabaseMetaData object
76
   */
77
  def getMetaData(connection: Connection): DatabaseMetaData
78
}
79
```
80

81
**Usage Examples:**
82

83
```scala
84
import java.util.Properties
85

86
// Create connection with properties
87
val props = new Properties()
88
props.setProperty("user", "testuser")
89
props.setProperty("password", "testpass")
90
val connection = JDBCConnectionUtil.createConnection(jdbcUrl, props)
91

92
// Execute query with automatic cleanup
93
val result = JDBCConnectionUtil.withStatement(connection, "SELECT COUNT(*) FROM users") { rs =>
94
  rs.next()
95
  rs.getInt(1)
96
}
97

98
// Validate connection
99
if (JDBCConnectionUtil.validateConnection(connection)) {
100
  // Connection is healthy
101
}
102

103
// Clean up resources
104
JDBCConnectionUtil.closeResources(connection)
105
```
106

107
### Connection Management
108

109
Functions for creating and managing database connections.
110

111
```scala { .api }
112
/**
113
 * Create connection with default properties
114
 * @param url JDBC URL
115
 * @param username Database username
116
 * @param password Database password
117
 * @return Connection object
118
 */
119
def createConnection(url: String, username: String, password: String): Connection
120

121
/**
122
 * Create connection with custom properties
123
 * @param url JDBC URL
124
 * @param properties Connection properties
125
 * @return Connection object
126
 */
127
def createConnection(url: String, properties: Properties): Connection
128

129
/**
130
 * Create connection with timeout
131
 * @param url JDBC URL  
132
 * @param username Database username
133
 * @param password Database password
134
 * @param timeoutSeconds Connection timeout in seconds
135
 * @return Connection object
136
 */
137
def createConnectionWithTimeout(url: String, username: String, password: String, timeoutSeconds: Int): Connection
138

139
/**
140
 * Test connection without creating full connection
141
 * @param url JDBC URL
142
 * @param username Database username
143
 * @param password Database password
144
 * @return true if connection test succeeds
145
 */
146
def testConnection(url: String, username: String, password: String): Boolean
147
```
148

149
### Query Execution
150

151
Functions for executing SQL queries and statements.
152

153
```scala { .api }
154
/**
155
 * Execute SQL query and return ResultSet
156
 * @param connection Database connection
157
 * @param sql SQL query
158
 * @return ResultSet with query results
159
 */
160
def executeQuery(connection: Connection, sql: String): ResultSet
161

162
/**
163
 * Execute parameterized query
164
 * @param connection Database connection
165
 * @param sql SQL query with parameters (?)
166
 * @param params Parameter values
167
 * @return ResultSet with query results
168
 */
169
def executeQuery(connection: Connection, sql: String, params: Any*): ResultSet
170

171
/**
172
 * Execute update statement (INSERT, UPDATE, DELETE)
173
 * @param connection Database connection
174
 * @param sql SQL statement
175
 * @return Number of affected rows
176
 */
177
def executeUpdate(connection: Connection, sql: String): Int
178

179
/**
180
 * Execute parameterized update statement
181
 * @param connection Database connection
182
 * @param sql SQL statement with parameters (?)
183
 * @param params Parameter values
184
 * @return Number of affected rows
185
 */
186
def executeUpdate(connection: Connection, sql: String, params: Any*): Int
187

188
/**
189
 * Execute batch of SQL statements
190
 * @param connection Database connection
191
 * @param sqlStatements List of SQL statements
192
 * @return Array of update counts
193
 */
194
def executeBatch(connection: Connection, sqlStatements: List[String]): Array[Int]
195
```
196

197
### Resource Management
198

199
Functions for managing JDBC resources and cleanup.
200

201
```scala { .api }
202
/**
203
 * Close multiple AutoCloseable resources safely
204
 * Handles exceptions and ensures all resources are closed
205
 * @param resources Variable arguments of AutoCloseable resources
206
 */
207
def closeResources(resources: AutoCloseable*): Unit
208

209
/**
210
 * Execute operation with automatic resource cleanup
211
 * @param connection Database connection
212
 * @param sql SQL statement
213
 * @param handler Function to process ResultSet
214
 * @return Result of handler function
215
 */
216
def withStatement[T](connection: Connection, sql: String)(handler: ResultSet => T): T
217

218
/**
219
 * Execute operation with PreparedStatement
220
 * @param connection Database connection
221
 * @param sql SQL with parameters
222
 * @param params Parameter values
223
 * @param handler Function to process ResultSet
224
 * @return Result of handler function
225
 */
226
def withPreparedStatement[T](connection: Connection, sql: String, params: Any*)(handler: ResultSet => T): T
227

228
/**
229
 * Execute operation with transaction management
230
 * @param connection Database connection
231
 * @param operation Function to execute within transaction
232
 * @return Result of operation
233
 */
234
def withTransaction[T](connection: Connection)(operation: Connection => T): T
235
```
236

237
### Connection Validation
238

239
Functions for testing and validating database connections.
240

241
```scala { .api }
242
/**
243
 * Validate database connection health
244
 * Tests if connection is active and responsive
245
 * @param connection Connection to validate
246
 * @return true if connection is valid
247
 */
248
def validateConnection(connection: Connection): Boolean
249

250
/**
251
 * Validate connection with timeout
252
 * @param connection Connection to validate
253
 * @param timeoutSeconds Timeout for validation
254
 * @return true if connection is valid within timeout
255
 */
256
def validateConnection(connection: Connection, timeoutSeconds: Int): Boolean
257

258
/**
259
 * Check if connection is closed
260
 * @param connection Connection to check
261
 * @return true if connection is closed
262
 */
263
def isConnectionClosed(connection: Connection): Boolean
264

265
/**
266
 * Test database connectivity with simple query
267
 * @param connection Database connection
268
 * @return true if test query succeeds
269
 */
270
def testConnectivity(connection: Connection): Boolean
271

272
/**
273
 * Get connection information for debugging
274
 * @param connection Database connection
275
 * @return ConnectionInfo with details
276
 */
277
def getConnectionInfo(connection: Connection): ConnectionInfo
278
```
279

280
### Database Metadata
281

282
Functions for retrieving database schema and metadata information.
283

284
```scala { .api }
285
/**
286
 * Get database metadata
287
 * @param connection Database connection
288
 * @return DatabaseMetaData object
289
 */
290
def getMetaData(connection: Connection): DatabaseMetaData
291

292
/**
293
 * Get list of tables in database
294
 * @param connection Database connection
295
 * @param schema Schema name (optional)
296
 * @return List of table names
297
 */
298
def getTables(connection: Connection, schema: Option[String] = None): List[String]
299

300
/**
301
 * Get table column information
302
 * @param connection Database connection
303
 * @param tableName Table name
304
 * @param schema Schema name (optional)
305
 * @return List of ColumnInfo objects
306
 */
307
def getColumns(connection: Connection, tableName: String, schema: Option[String] = None): List[ColumnInfo]
308

309
/**
310
 * Check if table exists
311
 * @param connection Database connection
312
 * @param tableName Table name to check
313
 * @param schema Schema name (optional)
314
 * @return true if table exists
315
 */
316
def tableExists(connection: Connection, tableName: String, schema: Option[String] = None): Boolean
317

318
/**
319
 * Get database product information
320
 * @param connection Database connection
321
 * @return DatabaseProduct with name, version, etc.
322
 */
323
def getDatabaseProduct(connection: Connection): DatabaseProduct
324
```
325

326
### Test Data Generation
327

328
Utilities for generating test data and setting up test database schemas.
329

330
```scala { .api }
331
/**
332
 * Object for generating test data and managing test database schemas
333
 * Provides utilities for creating consistent test datasets across different databases
334
 */
335
object TestDataGenerator {
336
  
337
  /**
338
   * Generate sample data based on schema definition
339
   * @param schema StructType defining the data schema
340
   * @param rowCount Number of rows to generate
341
   * @return DataFrame with generated test data
342
   */
343
  def generateSampleData(schema: StructType, rowCount: Int = 100): DataFrame
344
  
345
  /**
346
   * Create test tables in the database
347
   * @param connection Database connection
348
   * @param tableDefinitions List of table creation SQL statements
349
   * @return Number of tables created
350
   */
351
  def createTestTables(connection: Connection, tableDefinitions: List[String]): Int
352
  
353
  /**
354
   * Create test tables with predefined schemas
355
   * @param connection Database connection
356
   * @param tableSchemas Map of table name to schema definition
357
   * @return Number of tables created
358
   */
359
  def createTestTables(connection: Connection, tableSchemas: Map[String, StructType]): Int
360
  
361
  /**
362
   * Populate test data into database tables
363
   * @param connection Database connection
364
   * @param tableName Name of table to populate
365
   * @param data DataFrame containing test data
366
   * @return Number of rows inserted
367
   */
368
  def populateTestData(connection: Connection, tableName: String, data: DataFrame): Int
369
  
370
  /**
371
   * Populate multiple tables with test data
372
   * @param connection Database connection
373
   * @param tableData Map of table name to DataFrame
374
   * @return Map of table name to number of rows inserted
375
   */
376
  def populateTestData(connection: Connection, tableData: Map[String, DataFrame]): Map[String, Int]
377
  
378
  /**
379
   * Generate common test data types (users, products, orders, etc.)
380
   * @param dataType Type of test data to generate
381
   * @param count Number of records
382
   * @return DataFrame with generated data
383
   */
384
  def generateCommonTestData(dataType: String, count: Int = 100): DataFrame
385
  
386
  /**
387
   * Create database-specific test schema
388
   * @param connection Database connection
389
   * @param databaseType Database type (postgresql, mysql, etc.)
390
   * @return List of created table names
391
   */
392
  def createDatabaseSpecificSchema(connection: Connection, databaseType: String): List[String]
393
  
394
  /**
395
   * Clean up test data and tables
396
   * @param connection Database connection
397
   * @param tableNames List of table names to drop
398
   */
399
  def cleanupTestData(connection: Connection, tableNames: List[String]): Unit
400
}
401
```
402

403
**Usage Examples:**
404

405
```scala
406
import org.apache.spark.sql.types._
407
import org.apache.spark.sql.types.DataTypes._
408

409
// Generate sample data with custom schema
410
val schema = StructType(Array(
411
  StructField("id", IntegerType, false),
412
  StructField("name", StringType, true),
413
  StructField("email", StringType, true),
414
  StructField("age", IntegerType, true)
415
))
416
val testData = TestDataGenerator.generateSampleData(schema, 1000)
417

418
// Create and populate test tables
419
val connection = JDBCConnectionUtil.createConnection(jdbcUrl, username, password)
420
val tableSchemas = Map(
421
  "users" -> schema,
422
  "products" -> productSchema
423
)
424
TestDataGenerator.createTestTables(connection, tableSchemas)
425
TestDataGenerator.populateTestData(connection, "users", testData)
426

427
// Generate common test data types
428
val userData = TestDataGenerator.generateCommonTestData("users", 500)
429
val orderData = TestDataGenerator.generateCommonTestData("orders", 200)
430

431
// Database-specific schema setup
432
val tableNames = TestDataGenerator.createDatabaseSpecificSchema(connection, "postgresql")
433

434
// Cleanup after tests
435
TestDataGenerator.cleanupTestData(connection, tableNames)
436
```
437

438
## Types
439

440
```scala { .api }
441
case class ConnectionInfo(
442
  url: String,
443
  username: String,
444
  driverClass: String,
445
  isValid: Boolean,
446
  isClosed: Boolean,
447
  autoCommit: Boolean
448
)
449

450
case class ColumnInfo(
451
  columnName: String,
452
  dataType: String,
453
  typeName: String,
454
  columnSize: Int,
455
  isNullable: Boolean,
456
  isPrimaryKey: Boolean
457
)
458

459
case class DatabaseProduct(
460
  productName: String,
461
  productVersion: String,
462
  driverName: String,
463
  driverVersion: String,
464
  jdbcMajorVersion: Int,
465
  jdbcMinorVersion: Int
466
)
467
```
468

469
## Error Handling
470

471
The utility functions provide comprehensive error handling:
472

473
- Connection failures with detailed error messages
474
- SQL execution errors with query context
475
- Resource cleanup even when exceptions occur
476
- Timeout handling for long-running operations
477
- Transaction rollback on errors
478

479
## Best Practices
480

481
When using JDBC utilities:
482

483
1. Always use `withStatement` or `withPreparedStatement` for automatic cleanup
484
2. Validate connections before use in long-running tests
485
3. Use parameterized queries to prevent SQL injection
486
4. Handle timeouts appropriately for slow databases
487
5. Close resources explicitly when not using helper methods