Configuring multiple database connections in a Spring environment requires a centralized routing mechanism. The foundation involves defining a base data source template, followed by specific implementations for each target database. These instances are then aggregated into a custom routing bean that delegates connection requests based on a runtime key.

<!-- Base template for shared connection properties -->
<bean id="baseDatabaseConfig" class="org.springframework.jdbc.datasource.DriverManagerDataSource" abstract="true"/>

<!-- Primary MySQL instance -->
<bean id="primaryMySql" parent="baseDatabaseConfig">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://db-host-1:3306/app_schema"/>
    <property name="username" value="${db.user.primary}"/>
    <property name="password" value="${db.pass.primary}"/>
</bean>

<!-- Secondary PostgreSQL instance -->
<bean id="secondaryPostgres" parent="baseDatabaseConfig">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://db-host-2:5432/analytics_db"/>
    <property name="username" value="${db.user.secondary}"/>
    <property name="password" value="${db.pass.secondary}"/>
</bean>

<!-- Central routing data source -->
<bean id="dynamicRouter" class="com.example.routing.DynamicRoutingDataSource">
    <property name="targetDataSources">
        <map key-type="java.lang.String">
            <entry key="DB_PRIMARY" value-ref="primaryMySql"/>
            <entry key="DB_SECONDARY" value-ref="secondaryPostgres"/>
        </map>
    </property>
    <property name="defaultTargetDataSource" ref="primaryMySql"/>
</bean>

<!-- Transaction management bound to the router -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dynamicRouter"/>
</bean>

<!-- MyBatis integration -->
<bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dynamicRouter"/>
    <property name="mapperLocations" value="classpath*:mappers/**/*.xml"/>
</bean>

<bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.example.persistence"/>
    <property name="sqlSessionFactoryBeanName" value="sessionFactory"/>
</bean>

Routing keys must be explicitly defined to match the map entries configured in the routing bean. Using a dedicated constants class prevents typos and centralizes key management.

public final class DatabaseRoutingKeys {
    public static final String PRIMARY = "DB_PRIMARY";
    public static final String SECONDARY = "DB_SECONDARY";

    private DatabaseRoutingKeys() {
        // Prevent instantiation
    }
}

The core routing logic extends Spring's AbstractRoutingDataSource. By overriding the lookup method, the framework dynamically resolves which physical connection pool to use during each database operation.

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    @Nullable
    protected Object determineCurrentLookupKey() {
        return DatabaseContextResolver.resolveActiveKey();
    }
}

Thread-local storage isolates the routing context per request or execution thread. A utility class manages the lifecycle of the active database identifier, ensuring that concurrent operations do not interfere with each other.

public final class DatabaseContextResolver {

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

    public static void activate(String routingKey) {
        if (routingKey == null || routingKey.trim().isEmpty()) {
            throw new IllegalArgumentException("Routing key cannot be null or empty");
        }
        ACTIVE_DB_CONTEXT.set(routingKey);
    }

    @Nullable
    public static String resolveActiveKey() {
        return ACTIVE_DB_CONTEXT.get();
    }

    public static void reset() {
        ACTIVE_DB_CONTEXT.remove();
    }
}

When executing business logic, invoke DatabaseContextResolver.activate(DatabaseRoutingKeys.SECONDARY) before the data access layer is triggered. The routing data source will intercept the call, retrieve the thread-bound key, and delegate to the corresponding connection pool. Always invoke reset() in a finally block or via an AOP advice to prevent thread-pool contamination in web containers.