# Dynamic Data Source Routing with HikariCP

> AbstractRoutingDataSource does not create a pool; it routes a connection request to one of the independent HikariCP pools according to the key selected before the transaction starts.

- Author: Muhammet Ali Köker
- Language: en
- Canonical: https://alikoker.com.tr/en/dynamic-data-source-routing-with-hikaricp
- Translation: https://alikoker.com.tr/hikaricp-ile-dinamik-veri-kaynagi-yonlendirme
- Published: 2021-10-15T12:00:00+03:00
- Modified: 2026-08-31T22:50:00+03:00
- Verified: 2026-08-07T11:00:00+03:00
- Type: article

`AbstractRoutingDataSource` does not create a [connection pool](/en/wiki/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. If each schema owns a separate HikariCP pool, `maximumPoolSize` limits only that individual pool; the theoretical total is the sum of `maximumPoolSize` across all pools. Even when some pools are never used, `minimumIdle`, startup behavior and the connection life cycle affect the total session count.

This design grew out of a data-access approach I developed for systems where many Oracle schemas are routed through a single application layer. With an independent pool per schema, one `maximumPoolSize` value does not represent the system-wide connection count; I therefore had to model the routing key, transaction boundary, and total connection budget together.

## 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`:

```java
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:

```java
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:

```java
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:

```java
@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](/en/wiki/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:

```java
@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](/en/wiki/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:

```text
C_total = Σ maximumPoolSize_i
```

Giving the same `maximumPoolSize` to a large set of pools does not mean that every schema needs the same simultaneous connection capacity. 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](/en/wiki/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.

## Broader data-system context

Dynamic routing is implemented at the connection-pool boundary, but transaction and data access belong to a wider systems problem. I connect those concepts in [Database and Data Systems](/en/database-and-data-systems).

## References

- Brett Wooldridge et al. (n.d.). HikariCP - High-performance JDBC connection pool. HikariCP project. [URL](https://github.com/brettwooldridge/HikariCP)

- Ron Pressler; Alan Bateman. (2023). JEP 444: [Virtual Threads](/en/wiki/virtual-thread). OpenJDK. [URL](https://openjdk.org/jeps/444)

- Spring Framework. (2020). AbstractRoutingDataSource - Spring Framework 5.3 API. VMware / Spring. [URL](https://docs.spring.io/spring-framework/docs/5.3.0/javadoc-api/org/springframework/jdbc/datasource/lookup/AbstractRoutingDataSource.html)

## Cite This Work

Köker, M. A. (2021). Dynamic Data Source Routing with HikariCP. alikoker.com.tr. https://alikoker.com.tr/en/dynamic-data-source-routing-with-hikaricp

- BibTeX: https://alikoker.com.tr/en/dynamic-data-source-routing-with-hikaricp.bib
- RIS: https://alikoker.com.tr/en/dynamic-data-source-routing-with-hikaricp.ris
- CSL-JSON: https://alikoker.com.tr/en/dynamic-data-source-routing-with-hikaricp.csl.json