Is it possible to use the same Spring Data Repository to hit Two Different Databases (data sources)?

Question

I have two config files like so:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = { "repo" },
    entityManagerFactoryRef = "db1",
    transactionManagerRef = "JpaTxnManager_db1")
public class RepositoryConfigSpringDataDb1 {
}

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = { "repo" },
    entityManagerFactoryRef = "db2",
    transactionManagerRef = "JpaTxnManager_db2")
public class RepositoryConfigSpringDataDb2 {
}

I have a dao class which has many methods. Now in the dao class i thought that I can use the @Transactional annotation specified with the transaction to hit the specific db.

Some example method that would call db1 would be:

@Transactional(transactionManager="JpaTxnManager_db1")
public List<EntityOne> getAllEntitiesById(String id) { 
    return entityOneRepo.findById(id);
}

Some other method that would call db2 woudl be:

@Transactional(transactionManager="JpaTxnManager_db2")
public List<EntityOne> getAllEntitiesById(String id) { 
    return entityOneRepo.findById(id);
}

The repo is defined something like this:

@org.springframework.stereotype.Repository
public interface EntityOneRepository extends PagingAndSortingRepository<EntityOne, String> {
    // ommitted for brevity 

-- i have defined different data sources for these but the second data source i defined is not being hit.

Any idea what I am missing ?

is it possible to use the same EntityOneRepository that extends PagingAndSortingRepository to hit 2 different databases, based on the transactionManagerRef and entityManagerFactoryRef?

Answer 1

I have come up against this problem many times before and solved it using Spring's DelegatingDataSource which allows you to define multiple DataSource objects and delegate to the correct target data source needed by means of some type of lookup key. A sub-class of this which may be a good choice for the code you've displayed in your post may be TransactionAwareDataSourceProxy which is, as the first sentence of the JavaDoc description states:

Proxy for a target JDBC DataSource, adding awareness of Spring-managed transactions. Similar to a transactional JNDI DataSource as provided by a Java EE server.

I generally always use the same target data source in any given thread, so I tend to tuck my lookup key into a ThreadLocal object and have the proxy datasource read this to find the actual target data source whenever a call to DataSource#getConnection is made.

If you create such a delegating (proxy) data source, your EntityManagerFactory can use this as its underlying JDBC data source and delegate to the correct target data source as needed at any given time.

I've been using this type of approach for years with JPA code where I needed to hit multiple data sources with the same persistence unit and it works great for me. Should work just fine with a Spring JPA data repository also.

Below is some implementation code from a project I worked on previously. The code belongs to me, so feel free copy whatever you like and it use it however you wish.

Here is the proxy DataSource class that delegates to an actual target JDBC DataSource. It does not extend Spring's DelegatingDataSource as mentioned above, but it's doing the exact same thing. In case you're not familiar with OSGI declarative services and their annotations (I imagine most people aren't), @Component(property = {"osgi.jndi.service.name=jdbc/customation"} is what puts the DataSource into the JNDI registry such that it may be located by the persistence unit descriptor (persistence.xml) shown a bit further below.

package com.custsoft.client.ds;

import com.custsoft.client.ClientXrefHolder;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.osgi.service.component.annotations.ReferenceCardinality;
import org.osgi.service.component.annotations.ReferencePolicy;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

import static com.custsoft.Constants.CLIENT;

/**
 * Proxy data source that delegates to an actual JDBC data source. 
 * There is one target JDBC data source per client.
 * 
 * Created by eric on 9/29/15.
 */
@Component(property = {"osgi.jndi.service.name=jdbc/customation"},
        service = DataSource.class)
public class ClientDelegatingDataSource implements DataSource {

    private static final Logger logger = LoggerFactory.getLogger(ClientDelegatingDataSource.class);

    private String DEFAULT_CLIENT_XREF = "customation";

    private Map<String, DataSource> clientDataSources = new HashMap<>();

    @Reference(target = "(client=*)",
            cardinality = ReferenceCardinality.MULTIPLE,
            policy = ReferencePolicy.DYNAMIC)
    protected void addDataSource(DataSource dataSource, Map<String, Object> properties) {
        final String clientId = getClientId(properties);
        clientDataSources.put(clientId, dataSource);
    }

    protected void removeDataSource(DataSource dataSource, Map<String, Object> properties) {
        final String clientId = getClientId(properties);
        clientDataSources.remove(clientId);
    }

    private String getClientId(Map<String, Object> properties) {
        return Objects.toString(properties.get(CLIENT), null);
    }

    @Override
    public Connection getConnection() throws SQLException {
        return determineTargetDataSource().getConnection();
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return determineTargetDataSource().getConnection(username, password);
    }

    private DataSource determineTargetDataSource() {

        String clientId = ClientXrefHolder.getClientXref();
        if (clientId == null) {
            clientId = DEFAULT_CLIENT_XREF;
        }

        DataSource dataSource = clientDataSources.get(clientId);
        if (dataSource == null) {
            final String message = String.format(
                    "Couldn't find data source for client \"%s\".", clientId);
            throw new IllegalStateException(message);
        }

        return dataSource;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return determineTargetDataSource().unwrap(iface);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return determineTargetDataSource().isWrapperFor(iface);
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return determineTargetDataSource().getLogWriter();
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {
        determineTargetDataSource().setLogWriter(out);
    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {
        determineTargetDataSource().setLoginTimeout(seconds);
    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return determineTargetDataSource().getLoginTimeout();
    }

    @Override
    public java.util.logging.Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return determineTargetDataSource().getParentLogger();
    }

}

Here is the class that holds the lookup key in a ThreadLocal:

package com.custsoft.client;

/**
 * Holds the client ID in the current thread. It is
 * generally placed there by a REST filter that reads
 * it from a "client" HTTP header.
 *
 * Created by eric on 8/25/15.
 */
public class ClientXrefHolder {

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

    public static String getClientXref() {
        return CLIENT_XREF_HOLDER.get();
    }

    public static void setClientXref(final String clientXref) {
        CLIENT_XREF_HOLDER.set(clientXref);
    }
}

persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0"
             xmlns="http://java.sun.com/xml/ns/persistence"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
             http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

    <persistence-unit name="customation" transaction-type="JTA">

        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <!-- Only used when transaction-type=JTA -->
        <jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/customation)</jta-data-source>

        <!-- Only used when transaction-type=RESOURCE_LOCAL -->
        <non-jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/customation)</non-jta-data-source>

        <class>com.custsoft.model.AccessToken</class>
        <class>com.custsoft.model.JpaModel</class>
        <class>com.custsoft.model.Role</class>
        <class>com.custsoft.model.stats.Stat</class>
        <class>com.custsoft.model.stats.StatDefinition</class>
        <class>com.custsoft.model.User</class>
        <class>com.custsoft.model.UserProperty</class>

        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
        </properties>

    </persistence-unit>

</persistence>