0
# Utilities and Tools
1

2
Comprehensive utility classes for JDBC operations, JSON processing, CSV handling, reflection, and R2DBC integration. Includes testing utilities and mock database providers.
3

4
## Capabilities
5

6
### JDBC Utilities
7

8
Essential utilities for working with JDBC connections, result sets, and database metadata.
9

10
```java { .api }
11
public class JDBCUtils {
12
    /**
13
     * Safely close a JDBC connection, ignoring any exceptions
14
     * @param connection Connection to close
15
     */
16
    public static void safeClose(Connection connection);
17
    
18
    /**
19
     * Safely close a JDBC statement, ignoring any exceptions
20
     * @param statement Statement to close
21
     */
22
    public static void safeClose(Statement statement);
23
    
24
    /**
25
     * Safely close a JDBC result set, ignoring any exceptions
26
     * @param resultSet ResultSet to close
27
     */
28
    public static void safeClose(ResultSet resultSet);
29
    
30
    /**
31
     * Safely close multiple JDBC resources, ignoring any exceptions
32
     * @param closeables Resources to close
33
     */
34
    public static void safeClose(AutoCloseable... closeables);
35
    
36
    /**
37
     * Detect the SQL dialect from a JDBC connection
38
     * @param connection JDBC connection to analyze
39
     * @return SQLDialect detected from the connection
40
     */
41
    public static SQLDialect dialect(Connection connection);
42
    
43
    /**
44
     * Detect the SQL dialect from a database URL
45
     * @param url JDBC URL to analyze
46
     * @return SQLDialect detected from the URL
47
     */
48
    public static SQLDialect dialect(String url);
49
    
50
    /**
51
     * Check if a connection is still valid
52
     * @param connection Connection to test
53
     * @param timeout Timeout in seconds for the test
54
     * @return true if connection is valid
55
     */
56
    public static boolean isValid(Connection connection, int timeout);
57
    
58
    /**
59
     * Get database product name from connection metadata
60
     * @param connection JDBC connection
61
     * @return Database product name
62
     */
63
    public static String getDatabaseProductName(Connection connection);
64
    
65
    /**
66
     * Get database product version from connection metadata
67
     * @param connection JDBC connection
68
     * @return Database product version
69
     */
70
    public static String getDatabaseProductVersion(Connection connection);
71
    
72
    /**
73
     * Create a data source from a JDBC URL
74
     * @param url JDBC connection URL
75
     * @param username Database username
76
     * @param password Database password
77
     * @return DataSource instance
78
     */
79
    public static DataSource dataSource(String url, String username, String password);
80
}
81
```
82

83
**Usage Examples:**
84

85
```java
86
// Safe resource cleanup
87
Connection conn = null;
88
Statement stmt = null;
89
ResultSet rs = null;
90

91
try {
92
    conn = DriverManager.getConnection("jdbc:h2:mem:test");
93
    stmt = conn.createStatement();
94
    rs = stmt.executeQuery("SELECT * FROM users");
95
    
96
    // Process results...
97
    
98
} catch (SQLException e) {
99
    // Handle error
100
} finally {
101
    // Safe cleanup - no exceptions thrown
102
    JDBCUtils.safeClose(rs, stmt, conn);
103
}
104

105
// Dialect detection
106
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/test");
107
SQLDialect dialect = JDBCUtils.dialect(conn);
108
System.out.println("Detected dialect: " + dialect); // POSTGRES
109

110
// Connection validation
111
if (JDBCUtils.isValid(conn, 5)) {
112
    // Connection is still good, use it
113
    DSLContext create = using(conn, dialect);
114
} else {
115
    // Connection is stale, get a new one
116
    conn = dataSource.getConnection();
117
}
118
```
119

120
### Mock Testing Utilities
121

122
Utilities for creating mock databases and testing jOOQ code without real database connections.
123

124
```java { .api }
125
public interface MockDataProvider {
126
    /**
127
     * Provide mock results for a SQL execution
128
     * @param ctx Mock execution context with SQL and parameters
129
     * @return Array of mock results
130
     * @throws SQLException if mock execution should simulate an error
131
     */
132
    MockResult[] execute(MockExecuteContext ctx) throws SQLException;
133
}
134

135
public class MockConnection implements Connection {
136
    /**
137
     * Create a mock connection with a data provider
138
     * @param provider MockDataProvider for generating results
139
     */
140
    public MockConnection(MockDataProvider provider);
141
    
142
    /**
143
     * Create a mock connection with multiple data providers
144
     * @param providers Array of MockDataProvider instances
145
     */
146
    public MockConnection(MockDataProvider... providers);
147
}
148

149
public class MockResult {
150
    /**
151
     * Create a mock result for a query
152
     * @param rows Number of rows affected/returned
153
     * @param data Result data as jOOQ Result
154
     */
155
    public MockResult(int rows, Result<? extends Record> data);
156
    
157
    /**
158
     * Create a mock result for DML operations
159
     * @param rows Number of rows affected
160
     */
161
    public MockResult(int rows);
162
    
163
    /**
164
     * Get the number of rows affected
165
     * @return Row count
166
     */
167
    public int rows();
168
    
169
    /**
170
     * Get the result data
171
     * @return Result data or null for DML operations
172
     */
173
    public Result<? extends Record> data();
174
}
175

176
public interface MockExecuteContext {
177
    /**
178
     * Get the SQL being executed
179
     * @return SQL string
180
     */
181
    String sql();
182
    
183
    /**
184
     * Get the bind values
185
     * @return Array of parameter values
186
     */
187
    Object[] bindings();
188
    
189
    /**
190
     * Get the SQL with inlined parameters
191
     * @return SQL with parameters substituted
192
     */
193
    String sqlInlined();
194
}
195
```
196

197
**Usage Examples:**
198

199
```java
200
// Simple mock data provider
201
MockDataProvider provider = new MockDataProvider() {
202
    @Override
203
    public MockResult[] execute(MockExecuteContext ctx) throws SQLException {
204
        String sql = ctx.sql();
205
        
206
        if (sql.toUpperCase().startsWith("SELECT")) {
207
            // Mock SELECT results
208
            DSLContext create = using(SQLDialect.H2);
209
            Result<Record> result = create.newResult(
210
                field("id", Integer.class),
211
                field("name", String.class)
212
            );
213
            
214
            result.add(create.newRecord(result.fields())
215
                .values(1, "John Doe"));
216
            result.add(create.newRecord(result.fields())
217
                .values(2, "Jane Smith"));
218
                
219
            return new MockResult[] { new MockResult(2, result) };
220
            
221
        } else if (sql.toUpperCase().startsWith("INSERT")) {
222
            // Mock INSERT result
223
            return new MockResult[] { new MockResult(1) };
224
            
225
        } else {
226
            throw new SQLException("Unexpected SQL: " + sql);
227
        }
228
    }
229
};
230

231
// Use mock connection in tests
232
Connection mockConn = new MockConnection(provider);
233
DSLContext create = using(mockConn, SQLDialect.H2);
234

235
// This will use the mock provider
236
Result<Record> authors = create.select()
237
    .from("authors")
238
    .fetch();
239

240
assertEquals(2, authors.size());
241
assertEquals("John Doe", authors.get(0).get("name"));
242
```
243

244
### Type Conversion Utilities
245

246
Utilities for converting between Java types and database values.
247

248
```java { .api }
249
public class Convert {
250
    /**
251
     * Convert a value to a specific type
252
     * @param from Value to convert
253
     * @param toClass Target type class
254
     * @return Converted value
255
     */
256
    public static <T> T convert(Object from, Class<T> toClass);
257
    
258
    /**
259
     * Convert a value using a specific converter
260
     * @param from Value to convert
261
     * @param converter Converter instance
262
     * @return Converted value
263
     */
264
    public static <T, U> U convert(T from, Converter<T, U> converter);
265
    
266
    /**
267
     * Check if conversion is possible between two types
268
     * @param from Source type
269
     * @param to Target type
270
     * @return true if conversion is supported
271
     */
272
    public static boolean canConvert(Class<?> from, Class<?> to);
273
    
274
    /**
275
     * Convert an array of values to a specific type
276
     * @param from Array of values to convert
277
     * @param toClass Target component type
278
     * @return Array with converted values
279
     */
280
    public static <T> T[] convertArray(Object[] from, Class<T> toClass);
281
    
282
    /**
283
     * Convert a collection to a list of specific type
284
     * @param from Collection to convert
285
     * @param toClass Target element type
286
     * @return List with converted elements
287
     */
288
    public static <T> List<T> convertCollection(Collection<?> from, Class<T> toClass);
289
}
290
```
291

292
### JSON Processing Utilities
293

294
Utilities for working with JSON data in database operations.
295

296
```java { .api }
297
public class JSONObject {
298
    /**
299
     * Create an empty JSON object
300
     */
301
    public JSONObject();
302
    
303
    /**
304
     * Create a JSON object from a string
305
     * @param json JSON string to parse
306
     */
307
    public JSONObject(String json);
308
    
309
    /**
310
     * Put a key-value pair in the JSON object
311
     * @param key JSON key
312
     * @param value JSON value
313
     * @return This JSONObject for chaining
314
     */
315
    public JSONObject put(String key, Object value);
316
    
317
    /**
318
     * Get a value by key
319
     * @param key JSON key
320
     * @return Value or null if not found
321
     */
322
    public Object get(String key);
323
    
324
    /**
325
     * Get a typed value by key
326
     * @param key JSON key
327
     * @param type Expected value type
328
     * @return Typed value or null if not found
329
     */
330
    public <T> T get(String key, Class<T> type);
331
    
332
    /**
333
     * Convert to JSON string
334
     * @return JSON string representation
335
     */
336
    public String toJSONString();
337
}
338

339
public class JSONArray {
340
    /**
341
     * Create an empty JSON array
342
     */
343
    public JSONArray();
344
    
345
    /**
346
     * Create a JSON array from a string
347
     * @param json JSON array string to parse
348
     */
349
    public JSONArray(String json);
350
    
351
    /**
352
     * Add a value to the array
353
     * @param value Value to add
354
     * @return This JSONArray for chaining
355
     */
356
    public JSONArray add(Object value);
357
    
358
    /**
359
     * Get a value by index
360
     * @param index Array index
361
     * @return Value at index
362
     */
363
    public Object get(int index);
364
    
365
    /**
366
     * Get array size
367
     * @return Number of elements
368
     */
369
    public int size();
370
    
371
    /**
372
     * Convert to JSON string
373
     * @return JSON array string representation
374
     */
375
    public String toJSONString();
376
}
377

378
public class JSONParser {
379
    /**
380
     * Parse a JSON string into an object
381
     * @param json JSON string to parse
382
     * @return JSONObject or JSONArray
383
     */
384
    public static Object parse(String json);
385
    
386
    /**
387
     * Parse a JSON string into a JSONObject
388
     * @param json JSON object string
389
     * @return JSONObject instance
390
     */
391
    public static JSONObject parseObject(String json);
392
    
393
    /**
394
     * Parse a JSON string into a JSONArray
395
     * @param json JSON array string
396
     * @return JSONArray instance
397
     */
398
    public static JSONArray parseArray(String json);
399
}
400
```
401

402
**Usage Examples:**
403

404
```java
405
// Working with JSON data
406
JSONObject author = new JSONObject()
407
    .put("id", 1)
408
    .put("firstName", "John")
409
    .put("lastName", "Doe")
410
    .put("active", true);
411

412
String jsonString = author.toJSONString();
413
// {"id":1,"firstName":"John","lastName":"Doe","active":true}
414

415
// Parse JSON from database
416
Result<Record1<String>> jsonResults = create
417
    .select(field("author_data", String.class))
418
    .from("authors")
419
    .fetch();
420

421
for (Record1<String> record : jsonResults) {
422
    JSONObject authorData = JSONParser.parseObject(record.value1());
423
    String name = authorData.get("firstName", String.class);
424
    Integer id = authorData.get("id", Integer.class);
425
}
426

427
// Type conversion with JSON
428
Object value = "123";
429
Integer intValue = Convert.convert(value, Integer.class);
430
String stringValue = Convert.convert(intValue, String.class);
431
```
432

433
### CSV Processing Utilities
434

435
Utilities for reading and writing CSV data for bulk operations.
436

437
```java { .api }
438
public class CSVReader {
439
    /**
440
     * Create a CSV reader from a file
441
     * @param file CSV file to read
442
     */
443
    public CSVReader(File file);
444
    
445
    /**
446
     * Create a CSV reader from an input stream
447
     * @param inputStream Stream containing CSV data
448
     */
449
    public CSVReader(InputStream inputStream);
450
    
451
    /**
452
     * Read the next CSV record
453
     * @return Array of field values or null if end of file
454
     */
455
    public String[] readNext();
456
    
457
    /**
458
     * Read all CSV records
459
     * @return List of string arrays representing records
460
     */
461
    public List<String[]> readAll();
462
    
463
    /**
464
     * Close the CSV reader
465
     */
466
    public void close();
467
}
468

469
public class CSVParser {
470
    /**
471
     * Parse a CSV line into fields
472
     * @param csvLine CSV line to parse
473
     * @return Array of field values
474
     */
475
    public static String[] parseLine(String csvLine);
476
    
477
    /**
478
     * Parse CSV line with custom separator
479
     * @param csvLine CSV line to parse
480
     * @param separator Field separator character
481
     * @return Array of field values
482
     */
483
    public static String[] parseLine(String csvLine, char separator);
484
}
485
```
486

487
### Reflection Utilities
488

489
Enhanced reflection utilities for working with Java objects and jOOQ records.
490

491
```java { .api }
492
public class Reflect {
493
    /**
494
     * Create a Reflect wrapper around an object
495
     * @param object Object to wrap
496
     * @return Reflect instance
497
     */
498
    public static Reflect on(Object object);
499
    
500
    /**
501
     * Create a Reflect wrapper around a class
502
     * @param clazz Class to wrap
503
     * @return Reflect instance
504
     */
505
    public static Reflect on(Class<?> clazz);
506
    
507
    /**
508
     * Create a new instance of the wrapped class
509
     * @param args Constructor arguments
510
     * @return New Reflect instance wrapping the created object
511
     */
512
    public Reflect create(Object... args);
513
    
514
    /**
515
     * Call a method on the wrapped object
516
     * @param name Method name
517
     * @param args Method arguments
518
     * @return Reflect instance wrapping the method result
519
     */
520
    public Reflect call(String name, Object... args);
521
    
522
    /**
523
     * Get a field value from the wrapped object
524
     * @param name Field name
525
     * @return Reflect instance wrapping the field value
526
     */
527
    public Reflect field(String name);
528
    
529
    /**
530
     * Set a field value on the wrapped object
531
     * @param name Field name
532
     * @param value New field value
533
     * @return This Reflect instance for chaining
534
     */
535
    public Reflect set(String name, Object value);
536
    
537
    /**
538
     * Get the wrapped object
539
     * @return The underlying object
540
     */
541
    public <T> T get();
542
    
543
    /**
544
     * Get the wrapped object as a specific type
545
     * @param type Target type class
546
     * @return The underlying object cast to the target type
547
     */
548
    public <T> T get(Class<T> type);
549
}
550
```
551

552
**Usage Examples:**
553

554
```java
555
// Reflection utilities for POJO mapping
556
public class AuthorPojo {
557
    private String firstName;
558
    private String lastName;
559
    
560
    // getters and setters...
561
}
562

563
// Use reflection to create and populate POJO
564
Reflect authorReflect = Reflect.on(AuthorPojo.class).create();
565
authorReflect
566
    .set("firstName", "John")
567
    .set("lastName", "Doe");
568

569
AuthorPojo author = authorReflect.get(AuthorPojo.class);
570

571
// Call methods via reflection
572
String fullName = Reflect.on(author)
573
    .call("getFirstName")
574
    .get(String.class) + " " + 
575
    Reflect.on(author)
576
    .call("getLastName")
577
    .get(String.class);
578

579
// CSV bulk loading example
580
try (CSVReader reader = new CSVReader(new File("authors.csv"))) {
581
    List<String[]> records = reader.readAll();
582
    
583
    for (String[] record : records) {
584
        create.insertInto(AUTHOR)
585
            .set(AUTHOR.FIRST_NAME, record[0])
586
            .set(AUTHOR.LAST_NAME, record[1])
587
            .set(AUTHOR.EMAIL, record[2])
588
            .execute();
589
    }
590
}
591
```
592

593
### R2DBC Integration Utilities
594

595
Utilities for reactive database access using R2DBC with jOOQ.
596

597
```java { .api }
598
public class R2DBCUtils {
599
    /**
600
     * Convert a reactive R2DBC result to jOOQ Result
601
     * @param publisher R2DBC result publisher
602
     * @param recordType Target record type
603
     * @return CompletableFuture with jOOQ Result
604
     */
605
    public static <R extends Record> CompletableFuture<Result<R>> 
606
        collect(Publisher<R> publisher, Class<R> recordType);
607
    
608
    /**
609
     * Execute a jOOQ query reactively with R2DBC
610
     * @param query jOOQ query to execute
611
     * @param connection R2DBC connection
612
     * @return Publisher of records
613
     */
614
    public static <R extends Record> Publisher<R> 
615
        executeAsync(Query query, io.r2dbc.spi.Connection connection);
616
}
617
```
618

619
**Usage Examples:**
620

621
```java
622
// Reactive query execution
623
io.r2dbc.spi.ConnectionFactory factory = 
624
    ConnectionFactories.get("r2dbc:postgresql://localhost/test");
625

626
DSLContext create = using(factory);
627

628
// Reactive SELECT
629
Publisher<AuthorRecord> authorsPublisher = create
630
    .selectFrom(AUTHOR)
631
    .where(AUTHOR.ACTIVE.eq(true))
632
    .fetchPublisher();
633

634
// Convert to CompletableFuture
635
CompletableFuture<Result<AuthorRecord>> authorsFuture = 
636
    R2DBCUtils.collect(authorsPublisher, AuthorRecord.class);
637

638
authorsFuture.thenAccept(authors -> {
639
    System.out.println("Found " + authors.size() + " active authors");
640
});
641
```