0
# Exception Handling and Utilities
1

2
Spring JDBC provides comprehensive exception translation from SQLException to Spring's DataAccessException hierarchy, along with utility classes for JDBC operations, metadata access, and resource management. This ensures consistent error handling and simplifies JDBC operations through helper methods and abstractions.
3

4
## Capabilities
5

6
### Exception Translation
7

8
Sophisticated exception translation system converting SQLExceptions to Spring's DataAccessException hierarchy.
9

10
```java { .api }
11
/**
12
 * Strategy interface for translating SQLExceptions to DataAccessExceptions
13
 */
14
public interface SQLExceptionTranslator {
15
    DataAccessException translate(String task, String sql, SQLException ex);
16
}
17

18
/**
19
 * SQLExceptionTranslator using database-specific error codes
20
 * Primary translator used by JdbcTemplate and related classes
21
 */
22
public class SQLErrorCodeSQLExceptionTranslator extends AbstractFallbackSQLExceptionTranslator {
23
    public SQLErrorCodeSQLExceptionTranslator();
24
    public SQLErrorCodeSQLExceptionTranslator(DataSource dataSource);
25
    public SQLErrorCodeSQLExceptionTranslator(String databaseName);
26
    
27
    public void setDataSource(DataSource dataSource);
28
    public void setSqlErrorCodes(SQLErrorCodes sqlErrorCodes);
29
    public void setCustomTranslations(CustomSQLErrorCodesTranslation... customTranslations);
30
}
31

32
/**
33
 * SQLExceptionTranslator using JDBC 4.0 exception subclasses
34
 */
35
public class SQLExceptionSubclassTranslator extends AbstractFallbackSQLExceptionTranslator {
36
    protected DataAccessException doTranslate(String task, String sql, SQLException ex);
37
}
38

39
/**
40
 * SQLExceptionTranslator using SQL state codes
41
 */
42
public class SQLStateSQLExceptionTranslator extends AbstractFallbackSQLExceptionTranslator {
43
    protected DataAccessException doTranslate(String task, String sql, SQLException ex);
44
}
45

46
/**
47
 * Abstract base class providing fallback translation support
48
 */
49
public abstract class AbstractFallbackSQLExceptionTranslator implements SQLExceptionTranslator {
50
    public void setFallbackTranslator(SQLExceptionTranslator fallback);
51
    public SQLExceptionTranslator getFallbackTranslator();
52
    
53
    protected abstract DataAccessException doTranslate(String task, String sql, SQLException ex);
54
}
55
```
56

57
**Usage Examples:**
58

59
```java
60
// Custom exception translator configuration
61
@Configuration
62
public class ExceptionTranslationConfig {
63
    
64
    @Bean
65
    public SQLExceptionTranslator sqlExceptionTranslator(DataSource dataSource) {
66
        SQLErrorCodeSQLExceptionTranslator translator = 
67
            new SQLErrorCodeSQLExceptionTranslator(dataSource);
68
        
69
        // Add custom error code translations
70
        CustomSQLErrorCodesTranslation customTranslation = 
71
            new CustomSQLErrorCodesTranslation();
72
        customTranslation.setErrorCodes("23001", "23505"); // Unique constraint violations
73
        customTranslation.setExceptionClass(DuplicateKeyException.class);
74
        
75
        translator.setCustomTranslations(customTranslation);
76
        
77
        // Set fallback translator
78
        translator.setFallbackTranslator(new SQLStateSQLExceptionTranslator());
79
        
80
        return translator;
81
    }
82
    
83
    @Bean
84
    public JdbcTemplate jdbcTemplate(DataSource dataSource, 
85
                                   SQLExceptionTranslator exceptionTranslator) {
86
        JdbcTemplate template = new JdbcTemplate(dataSource);
87
        template.setExceptionTranslator(exceptionTranslator);
88
        return template;
89
    }
90
}
91

92
// Manual exception translation
93
public class ExceptionTranslationExample {
94
    
95
    private final SQLExceptionTranslator exceptionTranslator;
96
    
97
    public ExceptionTranslationExample(DataSource dataSource) {
98
        this.exceptionTranslator = new SQLErrorCodeSQLExceptionTranslator(dataSource);
99
    }
100
    
101
    public void performDatabaseOperation() {
102
        try (Connection con = dataSource.getConnection();
103
             PreparedStatement ps = con.prepareStatement("INSERT INTO users (email) VALUES (?)")) {
104
            
105
            ps.setString(1, "duplicate@example.com");
106
            ps.executeUpdate();
107
            
108
        } catch (SQLException ex) {
109
            // Translate SQLException to Spring DataAccessException
110
            DataAccessException translated = exceptionTranslator.translate(
111
                "Insert user", "INSERT INTO users (email) VALUES (?)", ex
112
            );
113
            
114
            if (translated instanceof DuplicateKeyException) {
115
                throw new BusinessException("Email already exists", translated);
116
            } else if (translated instanceof DataIntegrityViolationException) {
117
                throw new BusinessException("Data integrity violation", translated);
118
            } else {
119
                throw translated;
120
            }
121
        }
122
    }
123
}
124
```
125

126
### Error Code Configuration
127

128
Configuration classes for database-specific error code mappings.
129

130
```java { .api }
131
/**
132
 * JavaBean for holding JDBC error codes for a database
133
 */
134
public class SQLErrorCodes {
135
    // Error code arrays for different exception types
136
    public void setBadSqlGrammarCodes(String... badSqlGrammarCodes);
137
    public void setDataIntegrityViolationCodes(String... dataIntegrityViolationCodes);  
138
    public void setDataAccessResourceFailureCodes(String... dataAccessResourceFailureCodes);
139
    public void setCannotAcquireLockCodes(String... cannotAcquireLockCodes);
140
    public void setDeadlockLoserCodes(String... deadlockLoserCodes);
141
    public void setCannotSerializeTransactionCodes(String... cannotSerializeTransactionCodes);
142
    public void setDuplicateKeyCodes(String... duplicateKeyCodes);
143
    
144
    // Configuration properties
145
    public void setUseSqlStateForTranslation(boolean useSqlStateForTranslation);
146
    public void setCustomTranslations(CustomSQLErrorCodesTranslation... customTranslations);
147
}
148

149
/**
150
 * Factory for loading SQLErrorCodes configuration
151
 */
152
public class SQLErrorCodesFactory {
153
    public static SQLErrorCodesFactory getInstance();
154
    
155
    public SQLErrorCodes getErrorCodes(String databaseName);
156
    public SQLErrorCodes getErrorCodes(DataSource dataSource);
157
    public void setDataSource(DataSource dataSource);
158
}
159

160
/**
161
 * Custom SQL error code to exception class mapping
162
 */
163
public class CustomSQLErrorCodesTranslation {
164
    public void setErrorCodes(String... errorCodes);
165
    public void setExceptionClass(Class<?> exceptionClass);
166
    public String[] getErrorCodes();
167
    public Class<?> getExceptionClass();
168
}
169
```
170

171
**Usage Examples:**
172

173
```java
174
// Custom error codes configuration
175
public class CustomErrorCodesConfig {
176
    
177
    public SQLErrorCodes createCustomErrorCodes() {
178
        SQLErrorCodes errorCodes = new SQLErrorCodes();
179
        
180
        // Configure error codes for specific database
181
        errorCodes.setBadSqlGrammarCodes("42000", "42001", "42102");
182
        errorCodes.setDataIntegrityViolationCodes("23000", "23001", "23505");
183
        errorCodes.setDataAccessResourceFailureCodes("08001", "08006");
184
        errorCodes.setCannotAcquireLockCodes("40001");
185
        errorCodes.setDeadlockLoserCodes("40P01");
186
        
187
        // Custom translations
188
        CustomSQLErrorCodesTranslation businessErrorTranslation = 
189
            new CustomSQLErrorCodesTranslation();
190
        businessErrorTranslation.setErrorCodes("99001", "99002");
191
        businessErrorTranslation.setExceptionClass(BusinessRuleException.class);
192
        
193
        errorCodes.setCustomTranslations(businessErrorTranslation);
194
        
195
        return errorCodes;
196
    }
197
}
198

199
// Loading error codes for different databases
200
public class DatabaseErrorCodeExample {
201
    
202
    public void demonstrateErrorCodeLoading() {
203
        SQLErrorCodesFactory factory = SQLErrorCodesFactory.getInstance();
204
        
205
        // Load by database name
206
        SQLErrorCodes postgresErrorCodes = factory.getErrorCodes("PostgreSQL");
207
        SQLErrorCodes mysqlErrorCodes = factory.getErrorCodes("MySQL");
208
        SQLErrorCodes oracleErrorCodes = factory.getErrorCodes("Oracle");
209
        
210
        // Load by DataSource (automatically detects database)
211
        SQLErrorCodes autoDetected = factory.getErrorCodes(dataSource);
212
    }
213
}
214
```
215

216
### JDBC Utilities
217

218
Comprehensive utility methods for JDBC operations, resource management, and result set processing.
219

220
```java { .api }
221
/**
222
 * Generic utility methods for working with JDBC
223
 */
224
public abstract class JdbcUtils {
225
    // Resource cleanup
226
    public static void closeConnection(Connection con);
227
    public static void closeStatement(Statement stmt);
228
    public static void closeResultSet(ResultSet rs);
229
    
230
    // ResultSet value extraction
231
    public static Object getResultSetValue(ResultSet rs, int index) throws SQLException;
232
    public static Object getResultSetValue(ResultSet rs, int index, Class<?> requiredType) 
233
        throws SQLException;
234
    
235
    // Column name handling
236
    public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) 
237
        throws SQLException;
238
    public static String convertUnderscoreNameToPropertyName(String name);
239
    public static String convertPropertyNameToUnderscoreName(String name);
240
    
241
    // Database metadata
242
    public static <T> T extractDatabaseMetaData(DataSource dataSource, 
243
        DatabaseMetaDataCallback<T> action) throws MetaDataAccessException;
244
    public static CommonDataSource unwrapDataSource(DataSource dataSource);
245
    
246
    // Database capabilities
247
    public static boolean supportsBatchUpdates(Connection con);
248
    public static boolean supportsResultSetType(Connection con, int resultSetType);
249
}
250

251
/**
252
 * Callback interface for processing DatabaseMetaData
253
 */
254
@FunctionalInterface
255
public interface DatabaseMetaDataCallback<T> {
256
    T processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException;
257
}
258
```
259

260
**Usage Examples:**
261

262
```java
263
// ResultSet value extraction
264
public class ResultSetProcessor {
265
    
266
    public void processResultSet(ResultSet rs) throws SQLException {
267
        while (rs.next()) {
268
            // Safe value extraction with type conversion
269
            String name = (String) JdbcUtils.getResultSetValue(rs, 1, String.class);
270
            Integer age = (Integer) JdbcUtils.getResultSetValue(rs, 2, Integer.class);
271
            BigDecimal salary = (BigDecimal) JdbcUtils.getResultSetValue(rs, 3, BigDecimal.class);
272
            Date birthDate = (Date) JdbcUtils.getResultSetValue(rs, 4, Date.class);
273
            
274
            // Handle null values gracefully
275
            Boolean active = (Boolean) JdbcUtils.getResultSetValue(rs, 5, Boolean.class);
276
            if (active == null) {
277
                active = false; // Default value
278
            }
279
            
280
            processUser(name, age, salary, birthDate, active);
281
        }
282
    }
283
    
284
    public Map<String, Object> extractRowAsMap(ResultSet rs) throws SQLException {
285
        ResultSetMetaData metaData = rs.getMetaData();
286
        int columnCount = metaData.getColumnCount();
287
        
288
        Map<String, Object> row = new HashMap<>();
289
        for (int i = 1; i <= columnCount; i++) {
290
            String columnName = JdbcUtils.lookupColumnName(metaData, i);
291
            Object value = JdbcUtils.getResultSetValue(rs, i);
292
            
293
            // Convert column name to property name
294
            String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(columnName);
295
            row.put(propertyName, value);
296
        }
297
        
298
        return row;
299
    }
300
}
301

302
// Database metadata extraction
303
public class DatabaseInfoService {
304
    
305
    private final DataSource dataSource;
306
    
307
    public DatabaseInfoService(DataSource dataSource) {
308
        this.dataSource = dataSource;
309
    }
310
    
311
    public DatabaseInfo getDatabaseInfo() {
312
        return JdbcUtils.extractDatabaseMetaData(dataSource, dbmd -> {
313
            DatabaseInfo info = new DatabaseInfo();
314
            info.setDatabaseProductName(dbmd.getDatabaseProductName());
315
            info.setDatabaseProductVersion(dbmd.getDatabaseProductVersion());
316
            info.setDriverName(dbmd.getDriverName());
317
            info.setDriverVersion(dbmd.getDriverVersion());
318
            info.setSupportsBatchUpdates(dbmd.supportsBatchUpdates());
319
            info.setSupportsTransactions(dbmd.supportsTransactions());
320
            info.setSupportsResultSetHoldability(
321
                dbmd.supportsResultSetHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT)
322
            );
323
            return info;
324
        });
325
    }
326
    
327
    public List<String> getTableNames(String catalog, String schema) {
328
        return JdbcUtils.extractDatabaseMetaData(dataSource, dbmd -> {
329
            List<String> tableNames = new ArrayList<>();
330
            try (ResultSet rs = dbmd.getTables(catalog, schema, null, new String[]{"TABLE"})) {
331
                while (rs.next()) {
332
                    tableNames.add(rs.getString("TABLE_NAME"));
333
                }
334
            }
335
            return tableNames;
336
        });
337
    }
338
    
339
    public boolean checkDatabaseCapabilities(Connection con) throws SQLException {
340
        boolean supportsBatch = JdbcUtils.supportsBatchUpdates(con);
341
        boolean supportsScrollable = JdbcUtils.supportsResultSetType(
342
            con, ResultSet.TYPE_SCROLL_INSENSITIVE
343
        );
344
        
345
        return supportsBatch && supportsScrollable;
346
    }
347
}
348

349
// Resource management utilities
350
public class ResourceManagementExample {
351
    
352
    public void performDatabaseOperationWithManualCleanup() {
353
        Connection con = null;
354
        PreparedStatement ps = null;
355
        ResultSet rs = null;
356
        
357
        try {
358
            con = dataSource.getConnection();
359
            ps = con.prepareStatement("SELECT * FROM users WHERE active = ?");
360
            ps.setBoolean(1, true);
361
            rs = ps.executeQuery();
362
            
363
            while (rs.next()) {
364
                processUser(rs);
365
            }
366
            
367
        } catch (SQLException e) {
368
            throw new DataAccessException("Database operation failed", e) {};
369
        } finally {
370
            // Proper resource cleanup
371
            JdbcUtils.closeResultSet(rs);
372
            JdbcUtils.closeStatement(ps);
373
            JdbcUtils.closeConnection(con);
374
        }
375
    }
376
}
377
```
378

379
### Key Generation Support
380

381
Interfaces and classes for handling database-generated keys.
382

383
```java { .api }
384
/**
385
 * Interface for retrieving generated keys from database operations
386
 */
387
public interface KeyHolder {
388
    Number getKey() throws InvalidDataAccessApiUsageException;
389
    Map<String, Object> getKeys() throws InvalidDataAccessApiUsageException;
390
    List<Map<String, Object>> getKeyList();
391
}
392

393
/**
394
 * Default implementation of KeyHolder
395
 */
396
public class GeneratedKeyHolder implements KeyHolder {
397
    public GeneratedKeyHolder();
398
    public GeneratedKeyHolder(List<Map<String, Object>> keyList);
399
    
400
    public Number getKey() throws InvalidDataAccessApiUsageException;
401
    public Map<String, Object> getKeys() throws InvalidDataAccessApiUsageException;
402
    public List<Map<String, Object>> getKeyList();
403
}
404
```
405

406
**Usage Examples:**
407

408
```java
409
// Using KeyHolder for generated keys
410
public class KeyGenerationExample {
411
    
412
    private final JdbcTemplate jdbcTemplate;
413
    
414
    public KeyGenerationExample(JdbcTemplate jdbcTemplate) {
415
        this.jdbcTemplate = jdbcTemplate;
416
    }
417
    
418
    public User createUserWithGeneratedId(User user) {
419
        KeyHolder keyHolder = new GeneratedKeyHolder();
420
        
421
        jdbcTemplate.update(connection -> {
422
            PreparedStatement ps = connection.prepareStatement(
423
                "INSERT INTO users (name, email, department) VALUES (?, ?, ?)",
424
                Statement.RETURN_GENERATED_KEYS
425
            );
426
            ps.setString(1, user.getName());
427
            ps.setString(2, user.getEmail()); 
428
            ps.setString(3, user.getDepartment());
429
            return ps;
430
        }, keyHolder);
431
        
432
        // Get the generated ID
433
        Number generatedId = keyHolder.getKey();
434
        user.setId(generatedId.longValue());
435
        
436
        return user;
437
    }
438
    
439
    public Order createOrderWithMultipleGeneratedValues(Order order) {
440
        KeyHolder keyHolder = new GeneratedKeyHolder();
441
        
442
        jdbcTemplate.update(connection -> {
443
            PreparedStatement ps = connection.prepareStatement(
444
                "INSERT INTO orders (user_id, product_id, quantity) VALUES (?, ?, ?)",
445
                new String[]{"id", "created_date", "order_number"}
446
            );
447
            ps.setLong(1, order.getUserId());
448
            ps.setLong(2, order.getProductId());
449
            ps.setInt(3, order.getQuantity());
450
            return ps;
451
        }, keyHolder);
452
        
453
        // Get all generated values
454
        Map<String, Object> keys = keyHolder.getKeys();
455
        order.setId(((Number) keys.get("id")).longValue());
456
        order.setCreatedDate((Timestamp) keys.get("created_date"));
457
        order.setOrderNumber((String) keys.get("order_number"));
458
        
459
        return order;
460
    }
461
    
462
    public void createMultipleUsersInBatch(List<User> users) {
463
        for (User user : users) {
464
            KeyHolder keyHolder = new GeneratedKeyHolder();
465
            
466
            jdbcTemplate.update(connection -> {
467
                PreparedStatement ps = connection.prepareStatement(
468
                    "INSERT INTO users (name, email) VALUES (?, ?)",
469
                    Statement.RETURN_GENERATED_KEYS
470
                );
471
                ps.setString(1, user.getName());
472
                ps.setString(2, user.getEmail());
473
                return ps;
474
            }, keyHolder);
475
            
476
            // Set generated ID on each user
477
            user.setId(keyHolder.getKey().longValue());
478
        }
479
    }
480
}
481
```
482

483
### Database Startup Validation
484

485
Utility for validating database connectivity and readiness during application startup.
486

487
```java { .api }
488
/**
489
 * Bean that validates database connectivity on startup
490
 */
491
public class DatabaseStartupValidator implements InitializingBean {
492
    public DatabaseStartupValidator();
493
    
494
    public void setDataSource(DataSource dataSource);
495
    public void setValidationQuery(String validationQuery);
496
    public void setTimeout(int timeout);
497
    public void setInterval(int interval);
498
    
499
    public void afterPropertiesSet();
500
    protected void validateDatabase();
501
}
502
```
503

504
**Usage Examples:**
505

506
```java
507
// Database startup validation configuration
508
@Configuration
509
public class DatabaseValidationConfig {
510
    
511
    @Bean
512
    public DatabaseStartupValidator databaseValidator(DataSource dataSource) {
513
        DatabaseStartupValidator validator = new DatabaseStartupValidator();
514
        validator.setDataSource(dataSource);
515
        validator.setValidationQuery("SELECT 1"); // Simple connectivity test
516
        validator.setTimeout(30); // 30 second timeout
517
        validator.setInterval(5); // Retry every 5 seconds
518
        return validator;
519
    }
520
}
521

522
// Custom validation logic
523
public class CustomDatabaseValidator {
524
    
525
    private final DataSource dataSource;
526
    private final JdbcTemplate jdbcTemplate;
527
    
528
    public CustomDatabaseValidator(DataSource dataSource) {
529
        this.dataSource = dataSource;
530
        this.jdbcTemplate = new JdbcTemplate(dataSource);
531
    }
532
    
533
    @PostConstruct
534
    public void validateDatabaseSetup() {
535
        try {
536
            // Test basic connectivity
537
            jdbcTemplate.queryForObject("SELECT 1", Integer.class);
538
            
539
            // Validate required tables exist
540
            validateRequiredTables();
541
            
542
            // Check database version compatibility
543
            validateDatabaseVersion();
544
            
545
            log.info("Database validation completed successfully");
546
            
547
        } catch (Exception e) {
548
            throw new IllegalStateException("Database validation failed", e);
549
        }
550
    }
551
    
552
    private void validateRequiredTables() {
553
        String[] requiredTables = {"users", "orders", "products", "audit_log"};
554
        
555
        for (String table : requiredTables) {
556
            try {
557
                jdbcTemplate.queryForObject(
558
                    "SELECT COUNT(*) FROM " + table + " WHERE 1=0", 
559
                    Integer.class
560
                );
561
            } catch (Exception e) {
562
                throw new IllegalStateException("Required table not found: " + table, e);
563
            }
564
        }
565
    }
566
    
567
    private void validateDatabaseVersion() {
568
        DatabaseInfo info = JdbcUtils.extractDatabaseMetaData(dataSource, dbmd -> {
569
            DatabaseInfo dbInfo = new DatabaseInfo();
570
            dbInfo.setDatabaseProductName(dbmd.getDatabaseProductName());
571
            dbInfo.setDatabaseMajorVersion(dbmd.getDatabaseMajorVersion());
572
            dbInfo.setDatabaseMinorVersion(dbmd.getDatabaseMinorVersion());
573
            return dbInfo;
574
        });
575
        
576
        // Validate minimum version requirements
577
        if ("PostgreSQL".equals(info.getDatabaseProductName()) && 
578
            info.getDatabaseMajorVersion() < 12) {
579
            throw new IllegalStateException(
580
                "PostgreSQL version 12 or higher required, found: " + 
581
                info.getDatabaseMajorVersion() + "." + info.getDatabaseMinorVersion()
582
            );
583
        }
584
    }
585
}
586
```
587

588
## Core Interfaces
589

590
```java { .api }
591
/**
592
 * Strategy interface for SQLException translation
593
 */
594
public interface SQLExceptionTranslator {
595
    DataAccessException translate(String task, String sql, SQLException ex);
596
}
597

598
/**
599
 * Interface for retrieving generated keys
600
 */
601
public interface KeyHolder {
602
    Number getKey() throws InvalidDataAccessApiUsageException;
603
    Map<String, Object> getKeys() throws InvalidDataAccessApiUsageException;
604
    List<Map<String, Object>> getKeyList();
605
}
606

607
/**
608
 * Callback for processing DatabaseMetaData
609
 */
610
@FunctionalInterface
611
public interface DatabaseMetaDataCallback<T> {
612
    T processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException;
613
}
614

615
/**
616
 * Primary exception translator implementation
617
 */
618
public class SQLErrorCodeSQLExceptionTranslator extends AbstractFallbackSQLExceptionTranslator {
619
    public SQLErrorCodeSQLExceptionTranslator(DataSource dataSource);
620
    public void setCustomTranslations(CustomSQLErrorCodesTranslation... customTranslations);
621
}
622

623
/**
624
 * Default KeyHolder implementation
625
 */
626
public class GeneratedKeyHolder implements KeyHolder {
627
    public Number getKey() throws InvalidDataAccessApiUsageException;
628
    public Map<String, Object> getKeys() throws InvalidDataAccessApiUsageException;
629
}
630
```