0
# DataSource Management
1

2
Spring JDBC provides comprehensive DataSource implementations and utilities for connection management, including basic DataSource implementations, embedded database support, connection proxies, and DataSource lookup mechanisms. These components handle connection pooling, transaction awareness, and resource management.
3

4
## Capabilities
5

6
### Basic DataSource Implementations
7

8
Core DataSource implementations for various connection management scenarios.
9

10
```java { .api }
11
/**
12
 * Simple DataSource implementation using DriverManager
13
 * Suitable for testing and simple applications
14
 */
15
public class DriverManagerDataSource extends AbstractDriverBasedDataSource {
16
    public DriverManagerDataSource();
17
    public DriverManagerDataSource(String url, String username, String password);
18
    
19
    public void setDriverClassName(String driverClassName);
20
    public void setUrl(String url);
21
    public void setUsername(String username);
22
    public void setPassword(String password);
23
    public void setConnectionProperties(Properties connectionProperties);
24
}
25

26
/**
27
 * DataSource using java.sql.Driver directly
28
 * More efficient than DriverManagerDataSource
29
 */
30
public class SimpleDriverDataSource extends AbstractDriverBasedDataSource {
31
    public SimpleDriverDataSource();
32
    public SimpleDriverDataSource(Driver driver, String url, String username, String password);
33
    
34
    public void setDriver(Driver driver);
35
    public void setUrl(String url);
36
    public void setUsername(String username);
37
    public void setPassword(String password);
38
    public void setConnectionProperties(Properties connectionProperties);
39
}
40

41
/**
42
 * DataSource that returns a single shared Connection
43
 * Useful for testing with in-memory databases
44
 */
45
public class SingleConnectionDataSource extends DriverManagerDataSource {
46
    public SingleConnectionDataSource();
47
    public SingleConnectionDataSource(String url, String username, String password, 
48
                                    boolean suppressClose);
49
    
50
    public void setSuppressClose(boolean suppressClose);
51
    public void setAutoCommit(boolean autoCommit);
52
}
53
```
54

55
**Usage Examples:**
56

57
```java
58
// Basic DriverManagerDataSource
59
DriverManagerDataSource dataSource = new DriverManagerDataSource();
60
dataSource.setDriverClassName("org.postgresql.Driver");
61
dataSource.setUrl("jdbc:postgresql://localhost:5432/mydb");
62
dataSource.setUsername("user");
63
dataSource.setPassword("password");
64

65
// With connection properties
66
Properties props = new Properties();
67
props.setProperty("characterEncoding", "UTF-8");
68
props.setProperty("useSSL", "true");
69
dataSource.setConnectionProperties(props);
70

71
// SimpleDriverDataSource (more efficient)
72
SimpleDriverDataSource simpleDataSource = new SimpleDriverDataSource();
73
simpleDataSource.setDriver(new org.postgresql.Driver());
74
simpleDataSource.setUrl("jdbc:postgresql://localhost:5432/mydb");
75
simpleDataSource.setUsername("user");
76
simpleDataSource.setPassword("password");
77

78
// SingleConnectionDataSource for testing
79
SingleConnectionDataSource testDataSource = new SingleConnectionDataSource(
80
    "jdbc:h2:mem:testdb", "sa", "", true
81
);
82
testDataSource.setAutoCommit(false); // For transaction testing
83
```
84

85
### DataSource Proxies and Adapters
86

87
Proxy implementations adding functionality to existing DataSources.
88

89
```java { .api }
90
/**
91
 * Base class for DataSource proxies
92
 */
93
public class DelegatingDataSource implements DataSource {
94
    public DelegatingDataSource();
95
    public DelegatingDataSource(DataSource targetDataSource);
96
    
97
    public void setTargetDataSource(DataSource targetDataSource);
98
    public DataSource getTargetDataSource();
99
}
100

101
/**
102
 * Proxy that is aware of Spring-managed transactions
103
 * Returns same Connection for same transaction
104
 */
105
public class TransactionAwareDataSourceProxy extends DelegatingDataSource {
106
    public TransactionAwareDataSourceProxy();
107
    public TransactionAwareDataSourceProxy(DataSource targetDataSource);
108
    
109
    public void setReobtainTransactionalConnections(boolean reobtainTransactionalConnections);
110
    public boolean shouldObtainFixedConnection(Connection con);
111
}
112

113
/**
114
 * Proxy that lazily obtains physical Connection
115
 * Useful for optimizing connection usage
116
 */
117
public class LazyConnectionDataSourceProxy extends DelegatingDataSource {
118
    public LazyConnectionDataSourceProxy();
119
    public LazyConnectionDataSourceProxy(DataSource targetDataSource);
120
    
121
    public void setDefaultAutoCommit(boolean defaultAutoCommit);
122
    public void setDefaultTransactionIsolation(int defaultTransactionIsolation);
123
    public void setDefaultReadOnly(boolean defaultReadOnly);
124
}
125

126
/**
127
 * Adapter allowing per-user credentials
128
 */
129
public class UserCredentialsDataSourceAdapter extends DelegatingDataSource {
130
    public UserCredentialsDataSourceAdapter();
131
    public UserCredentialsDataSourceAdapter(DataSource targetDataSource);
132
    
133
    public void setCredentialsForCurrentThread(String username, String password);
134
    public void removeCredentialsFromCurrentThread();
135
    protected Connection doGetConnection(String username, String password);
136
}
137

138
/**
139
 * Adapter for specific transaction isolation level
140
 */
141
public class IsolationLevelDataSourceAdapter extends UserCredentialsDataSourceAdapter {
142
    public IsolationLevelDataSourceAdapter();
143
    
144
    public void setIsolationLevel(int isolationLevel);
145
    public void setIsolationLevelName(String isolationLevelName);
146
    public Integer getIsolationLevel();
147
}
148
```
149

150
**Usage Examples:**
151

152
```java
153
// Transaction-aware proxy
154
DataSource actualDataSource = createConnectionPoolDataSource();
155
TransactionAwareDataSourceProxy txProxy = 
156
    new TransactionAwareDataSourceProxy(actualDataSource);
157

158
// Within transaction, same Connection is returned
159
JdbcTemplate jdbcTemplate = new JdbcTemplate(txProxy);
160

161
// Lazy connection proxy
162
LazyConnectionDataSourceProxy lazyProxy = 
163
    new LazyConnectionDataSourceProxy(actualDataSource);
164
lazyProxy.setDefaultAutoCommit(false);
165
lazyProxy.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
166

167
// Per-user credentials
168
UserCredentialsDataSourceAdapter userAdapter = 
169
    new UserCredentialsDataSourceAdapter(actualDataSource);
170

171
// In web request or service method
172
userAdapter.setCredentialsForCurrentThread("john_doe", "secret");
173
try {
174
    // Database operations use john_doe credentials
175
    performUserSpecificOperations();
176
} finally {
177
    userAdapter.removeCredentialsFromCurrentThread();
178
}
179

180
// Isolation level adapter
181
IsolationLevelDataSourceAdapter isolationAdapter = 
182
    new IsolationLevelDataSourceAdapter();
183
isolationAdapter.setTargetDataSource(actualDataSource);
184
isolationAdapter.setIsolationLevelName("TRANSACTION_SERIALIZABLE");
185
```
186

187
### Embedded Database Support
188

189
Complete embedded database creation and management with script execution.
190

191
```java { .api }
192
/**
193
 * Handle to an embedded database instance
194
 */
195
public interface EmbeddedDatabase extends DataSource {
196
    void shutdown();
197
}
198

199
/**
200
 * Supported embedded database types
201
 */
202
public enum EmbeddedDatabaseType {
203
    HSQL, H2, DERBY
204
}
205

206
/**
207
 * Fluent API for building embedded databases
208
 */
209
public class EmbeddedDatabaseBuilder {
210
    public EmbeddedDatabaseBuilder();
211
    
212
    // Database configuration
213
    public EmbeddedDatabaseBuilder setType(EmbeddedDatabaseType type);
214
    public EmbeddedDatabaseBuilder setName(String databaseName);
215
    public EmbeddedDatabaseBuilder setDataSourceFactory(DataSourceFactory dataSourceFactory);
216
    
217
    // Script configuration
218
    public EmbeddedDatabaseBuilder addScript(String script);
219
    public EmbeddedDatabaseBuilder addScripts(String... scripts);
220
    public EmbeddedDatabaseBuilder addDefaultScripts();
221
    public EmbeddedDatabaseBuilder ignoreFailedDrops(boolean ignoreFailedDrops);
222
    public EmbeddedDatabaseBuilder continueOnError(boolean continueOnError);
223
    public EmbeddedDatabaseBuilder setSeparator(String separator);
224
    public EmbeddedDatabaseBuilder setCommentPrefix(String commentPrefix);
225
    
226
    // Build database
227
    public EmbeddedDatabase build();
228
}
229

230
/**
231
 * Factory bean for embedded databases in Spring context
232
 */
233
public class EmbeddedDatabaseFactoryBean extends EmbeddedDatabaseFactory 
234
    implements FactoryBean<EmbeddedDatabase>, DisposableBean {
235
    
236
    public void setDatabaseName(String databaseName);
237
    public void setDatabaseType(EmbeddedDatabaseType type);
238
    public void setDatabasePopulator(DatabasePopulator databasePopulator);
239
    public void setDatabaseCleaner(DatabasePopulator databaseCleaner);
240
}
241
```
242

243
**Usage Examples:**
244

245
```java
246
// Basic embedded database
247
EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
248
    .setType(EmbeddedDatabaseType.H2)
249
    .setName("testdb")
250
    .addScript("schema.sql")
251
    .addScript("data.sql")
252
    .build();
253

254
JdbcTemplate jdbcTemplate = new JdbcTemplate(db);
255
// Use jdbcTemplate...
256

257
// Shutdown when done
258
db.shutdown();
259

260
// Advanced configuration
261
EmbeddedDatabase advancedDb = new EmbeddedDatabaseBuilder()
262
    .setType(EmbeddedDatabaseType.H2)
263
    .setName("advanced_test")
264
    .addScripts("classpath:schema.sql", "classpath:test-data.sql")
265
    .ignoreFailedDrops(true)
266
    .continueOnError(false)
267
    .setSeparator(";;")
268
    .setCommentPrefix("--")
269
    .build();
270

271
// Spring configuration
272
@Configuration
273
public class DatabaseConfig {
274
    
275
    @Bean
276
    public EmbeddedDatabase embeddedDatabase() {
277
        return new EmbeddedDatabaseBuilder()
278
            .setType(EmbeddedDatabaseType.H2)
279
            .addScript("classpath:schema.sql")
280
            .addScript("classpath:test-data.sql")
281
            .build();
282
    }
283
    
284
    @Bean
285
    public JdbcTemplate jdbcTemplate(EmbeddedDatabase dataSource) {
286
        return new JdbcTemplate(dataSource);
287
    }
288
}
289

290
// Test usage
291
@TestConfiguration
292
static class TestConfig {
293
    @Bean
294
    @Primary
295
    public DataSource testDataSource() {
296
        return new EmbeddedDatabaseBuilder()
297
            .setType(EmbeddedDatabaseType.H2)
298
            .setName("test-" + UUID.randomUUID())
299
            .addScript("classpath:schema.sql")
300
            .addScript("classpath:test-data.sql")
301
            .build();
302
    }
303
}
304
```
305

306
### DataSource Lookup and Routing
307

308
DataSource lookup mechanisms and routing DataSources for multi-database scenarios.
309

310
```java { .api }
311
/**
312
 * Strategy for looking up DataSources by name
313
 */
314
public interface DataSourceLookup {
315
    DataSource getDataSource(String dataSourceName) throws DataSourceLookupFailureException;
316
}
317

318
/**
319
 * Abstract DataSource routing to different target DataSources
320
 */
321
public abstract class AbstractRoutingDataSource extends AbstractDataSource 
322
    implements InitializingBean {
323
    
324
    public void setTargetDataSources(Map<Object, Object> targetDataSources);
325
    public void setDefaultTargetDataSource(Object defaultTargetDataSource);
326
    public void setLenientFallback(boolean lenientFallback);
327
    public void setDataSourceLookup(DataSourceLookup dataSourceLookup);
328
    
329
    // Abstract method to determine current lookup key
330
    protected abstract Object determineCurrentLookupKey();
331
    protected DataSource determineTargetDataSource();
332
}
333

334
/**
335
 * DataSourceLookup using Spring BeanFactory
336
 */
337
public class BeanFactoryDataSourceLookup implements DataSourceLookup, BeanFactoryAware {
338
    public void setBeanFactory(BeanFactory beanFactory);
339
    public DataSource getDataSource(String dataSourceName);
340
}
341

342
/**
343
 * JNDI-based DataSource lookup
344
 */
345
public class JndiDataSourceLookup extends JndiLocatorSupport implements DataSourceLookup {
346
    public DataSource getDataSource(String dataSourceName);
347
}
348

349
/**
350
 * Map-based DataSource lookup
351
 */
352
public class MapDataSourceLookup implements DataSourceLookup {
353
    public MapDataSourceLookup();
354
    public MapDataSourceLookup(Map<String, DataSource> dataSources);
355
    
356
    public void setDataSources(Map<String, DataSource> dataSources);
357
    public void addDataSource(String dataSourceName, DataSource dataSource);
358
    public DataSource getDataSource(String dataSourceName);
359
}
360
```
361

362
**Usage Examples:**
363

364
```java
365
// Custom routing DataSource
366
public class DatabaseRoutingDataSource extends AbstractRoutingDataSource {
367
    
368
    @Override
369
    protected Object determineCurrentLookupKey() {
370
        // Route based on current user, tenant, or request context
371
        return DatabaseContextHolder.getCurrentDatabase();
372
    }
373
}
374

375
// Configuration
376
@Configuration
377
public class MultiDatabaseConfig {
378
    
379
    @Bean
380
    public DataSource routingDataSource() {
381
        DatabaseRoutingDataSource routingDataSource = new DatabaseRoutingDataSource();
382
        
383
        Map<Object, Object> targetDataSources = new HashMap<>();
384
        targetDataSources.put("primary", primaryDataSource());
385
        targetDataSources.put("secondary", secondaryDataSource());
386
        targetDataSources.put("readonly", readOnlyDataSource());
387
        
388
        routingDataSource.setTargetDataSources(targetDataSources);
389
        routingDataSource.setDefaultTargetDataSource(primaryDataSource());
390
        
391
        return routingDataSource;
392
    }
393
    
394
    @Bean
395
    public DataSource primaryDataSource() {
396
        return new DriverManagerDataSource(
397
            "jdbc:postgresql://primary:5432/app", "user", "pass"
398
        );
399
    }
400
    
401
    @Bean  
402
    public DataSource secondaryDataSource() {
403
        return new DriverManagerDataSource(
404
            "jdbc:postgresql://secondary:5432/app", "user", "pass"
405
        );
406
    }
407
}
408

409
// Usage with context switching
410
public class DatabaseContextHolder {
411
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
412
    
413
    public static void setCurrentDatabase(String database) {
414
        contextHolder.set(database);
415
    }
416
    
417
    public static String getCurrentDatabase() {
418
        return contextHolder.get();
419
    }
420
    
421
    public static void clear() {
422
        contextHolder.remove();
423
    }
424
}
425

426
@Service
427
public class MultiDatabaseService {
428
    
429
    @Autowired
430
    private JdbcTemplate jdbcTemplate;
431
    
432
    public void performPrimaryOperation() {
433
        DatabaseContextHolder.setCurrentDatabase("primary");
434
        try {
435
            // Operations use primary database
436
            jdbcTemplate.update("INSERT INTO audit_log VALUES (?, ?)", 
437
                              "primary_op", Timestamp.from(Instant.now()));
438
        } finally {
439
            DatabaseContextHolder.clear();
440
        }
441
    }
442
    
443
    public List<String> performReadOnlyQuery() {
444
        DatabaseContextHolder.setCurrentDatabase("readonly");
445
        try {
446
            // Operations use read-only database
447
            return jdbcTemplate.queryForList(
448
                "SELECT name FROM users WHERE active = ?", 
449
                String.class, true
450
            );
451
        } finally {
452
            DatabaseContextHolder.clear();
453
        }
454
    }
455
}
456

457
// Map-based lookup
458
MapDataSourceLookup lookup = new MapDataSourceLookup();
459
lookup.addDataSource("primary", primaryDataSource);
460
lookup.addDataSource("secondary", secondaryDataSource);
461

462
DataSource ds = lookup.getDataSource("primary");
463
```
464

465
### DataSource Utilities
466

467
Utility classes for DataSource operations and connection management.
468

469
```java { .api }
470
/**
471
 * Utility methods for DataSource access
472
 */
473
public abstract class DataSourceUtils {
474
    // Connection management
475
    public static Connection getConnection(DataSource dataSource) 
476
        throws CannotGetJdbcConnectionException;
477
    public static void releaseConnection(Connection con, DataSource dataSource);
478
    
479
    // Transaction support  
480
    public static boolean isConnectionTransactional(Connection con, DataSource dataSource);
481
    public static void applyTransactionTimeout(Statement stmt, DataSource dataSource);
482
    public static void applyTimeout(Statement stmt, DataSource dataSource, int timeout);
483
    
484
    // Connection properties
485
    public static Integer prepareConnectionForTransaction(Connection con, 
486
        TransactionDefinition definition);
487
    public static void resetConnectionAfterTransaction(Connection con, 
488
        Integer previousIsolationLevel, boolean resetReadOnly);
489
}
490

491
/**
492
 * SmartDataSource extension with resource management hints
493
 */
494
public interface SmartDataSource extends DataSource {
495
    boolean shouldClose(Connection con);
496
}
497
```
498

499
**Usage Examples:**
500

501
```java
502
// Manual connection management (usually not needed with templates)
503
DataSource dataSource = getDataSource();
504
Connection con = DataSourceUtils.getConnection(dataSource);
505
try {
506
    // Use connection...
507
    PreparedStatement ps = con.prepareStatement("SELECT COUNT(*) FROM users");
508
    ResultSet rs = ps.executeQuery();
509
    // Process results...
510
} catch (SQLException ex) {
511
    DataSourceUtils.releaseConnection(con, dataSource);
512
    throw new DataAccessException("Query failed", ex) {};
513
} finally {
514
    DataSourceUtils.releaseConnection(con, dataSource);
515
}
516

517
// Custom SmartDataSource implementation
518
public class PooledSmartDataSource implements SmartDataSource {
519
    private final DataSource delegate;
520
    
521
    public PooledSmartDataSource(DataSource delegate) {
522
        this.delegate = delegate;
523
    }
524
    
525
    @Override
526
    public boolean shouldClose(Connection con) {
527
        // Return to pool instead of closing
528
        return false;
529
    }
530
    
531
    // Delegate other methods to wrapped DataSource...
532
}
533
```
534

535
## Core Interfaces
536

537
```java { .api }
538
/**
539
 * Standard DataSource interface from javax.sql
540
 */
541
public interface DataSource extends CommonDataSource, Wrapper {
542
    Connection getConnection() throws SQLException;
543
    Connection getConnection(String username, String password) throws SQLException;
544
}
545

546
/**
547
 * Extended DataSource with resource management hints
548
 */
549
public interface SmartDataSource extends DataSource {
550
    boolean shouldClose(Connection con);
551
}
552

553
/**
554
 * Strategy for DataSource lookup by name
555
 */
556
public interface DataSourceLookup {
557
    DataSource getDataSource(String dataSourceName) throws DataSourceLookupFailureException;
558
}
559

560
/**
561
 * Handle to embedded database instance
562
 */
563
public interface EmbeddedDatabase extends DataSource {
564
    void shutdown();
565
}
566
```