Dynamic Data Source Routing with HikariCP

AbstractRoutingDataSource does not create a connection pool. It delegates a getConnection() call to another DataSource according to the routing key that is current at that moment. When the targets use HikariCP, each target is an independent HikariDataSource with its own physical connection pool. The routing layer exposes these pools through a single DataSource interface.

This distinction is decisive in systems with many Oracle schemas. For example, defining 84 HikariCP pools for 84 schemas with maximumPoolSize=10 does not make the total limit 10. The theoretical upper bound is 840 physical connections. Even when some pools are never used, minimumIdle, startup behavior, and the connection life cycle affect the total number of sessions. HikariCP applies maximumPoolSize as the actual per-pool upper limit on connections.

Routing time

AbstractRoutingDataSource does not automatically choose the target data source by examining the query text, repository class, or transaction name. The determineCurrentLookupKey() method implemented by a subclass returns a key. Spring searches for that key in the map of previously resolved target data sources and obtains a connection from the selected DataSource. The key type is unrestricted, but the returned value must match the type of the keys in the map.

The simplest implementation stores the current key in a ThreadLocal:

public final class DataSourceContext {

private static final ThreadLocal<String> CURRENT = new ThreadLocal<>();

private DataSourceContext() { }

public static void set(final String key) { CURRENT.remove(); CURRENT.set(java.util.Objects.requireNonNull(key)); }

public static String get() { return CURRENT.get(); }

public static void clear() { CURRENT.remove(); } }

The router class only returns this value:

public final class RoutingDataSource extends org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource {

@Override protected Object determineCurrentLookupKey() { return DataSourceContext.get(); } }

remove() should be used instead of ThreadLocal.set(null). Threads can be reused by later tasks, especially in schedulers, platform-thread pools, or long-lived executors. If the key left by the previous task is not cleared, a new task can be routed to the wrong Oracle schema.

A safe life cycle follows this order:

public void dispatch(final String key) { DataSourceContext.clear(); DataSourceContext.set(key);

try { transactionalService.process(); } finally { DataSourceContext.clear(); } }

Here, transactionalService should be a separate Spring bean. The routing key is assigned before the transaction starts and before the first JDBC access. The finally block guarantees cleanup after a normal return, a checked exception, or a runtime exception.

InheritableThreadLocal is not a safe solution to this problem. Copying a context to a child thread does not transfer the transaction resource or JDBC connection. Spring's imperative transaction management binds resources to the current thread. When work moves to another thread, the same transaction context cannot be assumed to continue.

Transaction boundary

The routing key is not reevaluated before every SQL command. The Spring transaction manager binds the JDBC connection obtained through the routing DataSource to the current thread. JdbcTemplate and other Spring JDBC operations within the same transaction reuse that bound connection.

For this reason, the following flow does not produce the expected result:

@Transactional public void process() { DataSourceContext.set("SCHEMA_A"); repository.insertA();

DataSourceContext.set("SCHEMA_B"); repository.insertB(); }

If the first JDBC access obtains a connection from the SCHEMA_A pool, the same physical connection is used for the duration of the transaction. Even if the context is later changed to SCHEMA_B, the second operation does not automatically move to another pool. The routing key and the bound connection become inconsistent.

When one transaction must write to multiple data sources, AbstractRoutingDataSource alone is insufficient. The operation can be designed as two independent local transactions, a distributed transaction manager can be used, or the workflow can be divided into idempotent steps. If multiple Oracle schemas are accessible through the same database session, executing queries with authorized schema names instead of using separate pools is another architectural option. This decision depends on security boundaries and the schema ownership model.

REQUIRES_NEW can require a new physical transaction and a new connection. The inner transaction waits for a second connection while the outer transaction continues to hold its connection. If every thread in a highly concurrent workflow holds an outer connection and waits for an inner one, the pool can be exhausted. Spring documentation notes that this propagation type can create resource waits that may lead to deadlock when pool capacity is exceeded.

When an AOP aspect assigns the routing key, aspect order also becomes part of the transaction boundary. The routing aspect must prepare the context before the transaction interceptor runs. Otherwise, the transaction manager may obtain a connection from the default or previous data source.

With Spring's default proxy-based transaction management, self-invocation within the same class does not pass an @Transactional method through the proxy. Calling another method directly on the same object after setting the routing context may therefore fail to start the transaction at the expected boundary. Spring documentation explicitly states that proxy mode intercepts only calls that arrive externally through the proxy.

Building the target pools

When a routing data source is created, targets are supplied as key and DataSource pairs:

@Bean public DataSource dataSource(final Map<String, HikariDataSource> pools) { final RoutingDataSource routing = new RoutingDataSource(); final Map<Object, Object> targets = new java.util.HashMap<>();

for (final Map.Entry<String, HikariDataSource> entry : pools.entrySet()) { targets.put(entry.getKey(), entry.getValue()); }

routing.setTargetDataSources(targets); routing.setLenientFallback(false); routing.afterPropertiesSet(); return routing; }

afterPropertiesSet() prepares the internal state and resolves the configured data sources. This call is made automatically for an AbstractRoutingDataSource created within the Spring bean life cycle. If the object is constructed manually and used outside Spring, initialization is the application's responsibility. The map of resolved data sources is exposed externally as an unmodifiable view.

setLenientFallback(false) provides safer behavior in critical systems. The default is true, so a key that is not present in the map can silently fall back to the default data source. A misspelled schema key can then execute against another schema instead of producing an error. When false is used, a non-null but unmatched key produces an IllegalStateException.

A default data source should be used only when a genuine business rule defines one. If omitting the key is a programming error, treating a null value as an error may also be more appropriate. Additional validation can be added around determineCurrentLookupKey() or determineTargetDataSource() for this purpose.

Changing the target map at runtime while continuing to use the same routing object requires care. AbstractRoutingDataSource resolves configured targets during initialization. Adding an element only to the source map is not enough to register a new pool. Reinitialization, concurrent getConnection() calls, and shutdown of the old pool must be managed together. The standard class does not provide a general-purpose lock-free dynamic pool registry.

For a fixed number of Oracle schemas, creating the targets at application startup produces more predictable behavior. If a new data source must be added at runtime, the routing layer should operate on an immutable registry snapshot, publish a new snapshot atomically, and close removed Hikari pools only after active connections have completed.

HikariCP capacity calculation

Because every target is a separate pool, capacity must be calculated at two levels. The first is the concurrency requirement of one schema. The second is the total number of sessions that all pools can create on Oracle.

The theoretical total limit is:

C_total = Σ maximumPoolSize_i

Setting maximumPoolSize=10 for each of 84 pools does not mean that every schema needs ten simultaneous connections. If traffic is concentrated in a few busy schemas, pools can be sized differently. Uniform configuration simplifies management, but it may distribute database capacity incorrectly.

When minimumIdle is not specified, HikariCP recommends behavior close to a fixed-size pool. This reduces connection-creation latency during sudden load. With many rarely used data sources, however, it can cause all pools to retain a large number of idle connections. When minimumIdle < maximumPoolSize, idleTimeout takes effect and can reduce idle connections to the minimum as demand falls.

connectionTimeout is the maximum time a call can wait for a connection while the pool is full. Using virtual threads does not remove this limit. A virtual thread can wait at a lower platform-thread cost, but the number of physical Oracle connections remains bounded by maximumPoolSize. If many tasks wait in front of the pool, system throughput does not increase. Only the queue grows.

maxLifetime should be shorter than the interval after which the network or database infrastructure terminates a connection. HikariCP does not forcibly close a connection while it is in use. It retires the connection after it returns to the pool. keepaliveTime runs only on idle connections and must be less than maxLifetime. For JDBC4-compliant drivers, Connection.isValid() is recommended instead of a custom connectionTestQuery.

Every HikariDataSource must be closed when the application shuts down. If target pools are registered as separate Spring beans, the container can manage the close() life cycle. If pools are created only as local objects while building the routing bean and placed into a map, Spring does not see them as independent beans. In that case, the routing registry class must explicitly close every pool. Spring can detect public close() and shutdown() methods as default destroy methods on beans it manages.

Lazy connections and observability

DataSourceTransactionManager can acquire a connection early when a transaction begins. LazyConnectionDataSourceProxy can defer the actual JDBC connection until the first Statement is created. This prevents transactions that execute no SQL from consuming a pool connection. It can also simplify including read-only or isolation properties in target selection when such routing is used.

Proxy order matters. If TransactionAwareDataSourceProxy is used, it should be the outermost layer. LazyConnectionDataSourceProxy can sit below it, with the routing data source in front of the physical targets. The exact order must be designed according to the transaction manager and access technique in use. When a transaction-aware proxy and a lazy proxy are combined, Spring requires the transaction-aware proxy to be outermost.

In systems that use JPA, the routing key must be established before the EntityManager obtains a physical connection. Changing the key after the persistence context has been created or after the first query has run is not a reliable routing method. A single EntityManagerFactory can operate through a routing DataSource, but every target schema must be compatible with the entity model and Hibernate's expectations.

Monitoring should not be limited to the routing data source. At least the following values should be observed separately for every Hikari pool:

• Active connections
• Idle connections
• Waiting threads
• Total connections
• Connection acquisition time
• Timeout count
• Connection usage time

Spring Boot can expose active, idle, maximum, and minimum connection counts in data source metrics and label them by bean name. In systems with many pools, poolName, the routing key, and the Oracle service name should be consistent. High-cardinality values such as SQL text or user identity should not be used as metric labels.

The routing key itself can be logged for routing failures, but connection passwords, secrets embedded in full JDBC URLs, and personal data must not be written to logs. Logging the routing key at information level for every operation can create unnecessary I/O under high traffic. Errors, timeouts, and unexpected fallback events should be tracked with separate counters.

The main design principle when AbstractRoutingDataSource and HikariCP are used together is clear. The routing key is assigned before the transaction starts, treated as immutable throughout the transaction, and always cleared after the work completes. Every target pool has an independent capacity and life cycle. The total connection limit is the sum across all pools. Silent fallback is disabled, and selection of the wrong schema is treated as a system error rather than a normal operating condition.