Multiple Databases With Spring Data Repositories

The Spring Data project keeps making it easier to do database access in Spring applications, and one of the neatest improvements of recent times is that by defining an interface which extends JpaRepository and referencing a JPA entity, an implementation will automatically be injected with all the usual CRUD methods: findAll(), findOne(id), save(entity), delete(id), etc.

Recently I was working on a project, where I had taken full advantage of this, and for which I needed to add domain objects from an additional database. Unfortunately, as soon as I added references to entities in a different database I started experiencing troubles. For instance:

Not an managed type: class com.sctrcd.multidsdemo.domain.bar.Bar

… which was being caused by my repository being injected with the entityManager and transactionManager for the other database. Here I walk through how I resolved the problems and got things working.

After naming my beans to ensure that I would not be referencing the wrong one, I started seeing:

No bean named 'entityManagerFactory' is defined.

… because the repository implementation defaults to a by-name search for a bean called “entityManagerFactory”.

I was struggling to find any documentation of how to do it right, so to help me work through the steps of such a configuration, I created a minimal demo project at GitHub, containing two entities with those traditional names: Foo and Bar.

https://github.com/gratiartis/multids-demo

Here I shall explain the configuration that I ended up with in the hope that readers might understand that it can be done, and that it’s actually quite easy and requires very little code, if you know how!

First of all, we set up two JPA entities, Foo and Bar:

@Entity public class Foo { /* Constructors, fields and accessors/mutators */ }
@Entity public class Bar { /* Constructors, fields and accessors/mutators */ }

Associated with these we create two repositories: FooRepository and BarRepository. Thanks to the awesomeness of Spring Data, we can get ourselves some pretty full-featured repositories purely by defining interfaces which extend JpaRepository:

public interface FooRepository extends JpaRepository<Foo, Long> {}
public interface BarRepository extends JpaRepository<Bar, Long> {}

We need to ensure that each of these maps to a table in its own database. To achieve this, we will need two separate entity managers, each of which has a different datasource. However, in a Spring Java config @Configuration class, we can only have one @EnableJpaRepositories annotation and each such annotation can only reference one EntityManagerFactory. To achieve this, we create two separate @Configuration classes: FooConfig and BarConfig.

Each of these @Configuration classes defines a DataSource based on an embedded HSQL database. The following is the BarConfig. FooConfig is identical except for some different names and package paths.

Each configuration should define a DataSource, EntityManager, EntityManagerFactory and PlatformTransactionManager. You need to make sure that @Entity beans for different data sources are in different packages. We then need to put the correct references in the @EnableJpaRepositories annotation for each @Configuration class.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "barEntityManagerFactory", 
        transactionManagerRef = "barTransactionManager",
        basePackages = {"com.sctrcd.multidsdemo.integration.repositories.bar"})
public class BarConfig {
    // ...
}

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "barEntityManagerFactory", 
        transactionManagerRef = "barTransactionManager",
        basePackages = { "com.sctrcd.multidsdemo.integration.repositories.bar" })
public class BarConfig {
    // ...
}

As you can see, each of these @EnableJpaRepositories annotations defines a specific named EntityManagerFactory and PlatformTransactionManager. They also specify which repositories should be wired up with those beans. In the example, I have put the repositories in database-specific packages. It is also possible to define each individual repository by name, by adding includeFilters to the annotation, but by segregating the repositories by database, I believe that things should end up more readable.

At this point you should have a working application using Spring Data repositories to manage entities in two separate databases. Feel free to grab the project from the link above and run the tests to see this happening. And please do let me know if you can spot any good opportunities for improvement.

Update  

Since writing the post above, I have had the opportunity to implement a multiple datasource solution in a Spring Boot application. As a few people asked about it, here’s a follow-up post describing what needs to be done to implement multiple data sources in a Spring Boot application.