I just announced the new Spring 5 modules in REST With Spring:

>> CHECK OUT THE COURSE

1. Overview

In this tutorial we’ll implement a simple Spring configuration for a Spring Data JPA system with multiple databases.

Further reading:

Introduction to Spring Data JPA

Introduction to Spring Data JPA with Spring 4 - the Spring config, the DAO, manual and generated queries and transaction management.

Read more

Spring Persistence Tutorial

Persistence with Spring 4 Tutorial - setup for Hibernate, JPA, Spring Data JPA, the DAO layer and Transactions.

Read more

All Spring Data Guides

A quick guide to all the Spring Data articles here on Baeldung.

Read more

2. The Entities

First – let’s create two simple entities – each living in a separate database.

Here is the first entity “User“:

package org.baeldung.persistence.multiple.model.user;

@Entity
@Table(schema = "spring_jpa_user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    private String name;

    @Column(unique = true, nullable = false)
    private String email;

    private int age;
}

And the second entity – “Product“:

package org.baeldung.persistence.multiple.model.product;

@Entity
@Table(schema = "spring_jpa_product")
public class Product {

    @Id
    private int id;

    private String name;

    private double price;
}

As you can see, the two entities are also placed in independent packages – this will be important as we move into the configuration.

3. The JPA Repositories

Next – let’s take a look at our two JPA repositories – UserRepository:

package org.baeldung.persistence.multiple.dao.user;

public interface UserRepository
  extends JpaRepository<User, Integer> { }

And ProductRepository:

package org.baeldung.persistence.multiple.dao.product;

public interface ProductRepository
  extends JpaRepository<Product, Integer> { }

Note, again how we created these two repositories in different packages.

4. Configure JPA with Java

Next – let’s get to the actual Spring configuration. We’ll start by setting up 2 configuration classes – one for the User and the other for the Product.

In each one of this configuration classes, we’ll need to define the following:

  • User DataSource
  • User EntityManagerFactory (userEntityManager)
  • User TransactionManager (userTransactionManager)

Let’s start by looking the User configuration:

@Configuration
@PropertySource({ "classpath:persistence-multiple-db.properties" })
@EnableJpaRepositories(
    basePackages = "org.baeldung.persistence.multiple.dao.user", 
    entityManagerFactoryRef = "userEntityManager", 
    transactionManagerRef = "userTransactionManager"
)
public class UserConfig {
    @Autowired
    private Environment env;
    
    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean userEntityManager() {
        LocalContainerEntityManagerFactoryBean em
          = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(userDataSource());
        em.setPackagesToScan(
          new String[] { "org.baeldung.persistence.multiple.model.user" });

        HibernateJpaVendorAdapter vendorAdapter
          = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto",
          env.getProperty("hibernate.hbm2ddl.auto"));
        properties.put("hibernate.dialect",
          env.getProperty("hibernate.dialect"));
        em.setJpaPropertyMap(properties);

        return em;
    }

    @Primary
    @Bean
    public DataSource userDataSource() {
 
        DriverManagerDataSource dataSource
          = new DriverManagerDataSource();
        dataSource.setDriverClassName(
          env.getProperty("jdbc.driverClassName"));
        dataSource.setUrl(env.getProperty("user.jdbc.url"));
        dataSource.setUsername(env.getProperty("jdbc.user"));
        dataSource.setPassword(env.getProperty("jdbc.pass"));

        return dataSource;
    }

    @Primary
    @Bean
    public PlatformTransactionManager userTransactionManager() {
 
        JpaTransactionManager transactionManager
          = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
          userEntityManager().getObject());
        return transactionManager;
    }
}

Notice how we’re using the userTransactionManager as our Primary TransactionManager – by annotating the bean definition with @Primary. That’s helpful whenever we’re going to implicitly or explicitly inject the transaction manager without specifying which one by name.

Next, let’s discuss ProductConfig – where we define similar beans:

@Configuration
@PropertySource({ "classpath:persistence-multiple-db.properties" })
@EnableJpaRepositories(
    basePackages = "org.baeldung.persistence.multiple.dao.product", 
    entityManagerFactoryRef = "productEntityManager", 
    transactionManagerRef = "productTransactionManager"
)
public class ProductConfig {
    @Autowired
    private Environment env;

    @Bean
    public LocalContainerEntityManagerFactoryBean productEntityManager() {
        LocalContainerEntityManagerFactoryBean em
          = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(productDataSource());
        em.setPackagesToScan(
          new String[] { "org.baeldung.persistence.multiple.model.product" });

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto",
          env.getProperty("hibernate.hbm2ddl.auto"));
        properties.put("hibernate.dialect",
          env.getProperty("hibernate.dialect"));
        em.setJpaPropertyMap(properties);

        return em;
    }

    @Bean
    public DataSource productDataSource() {
 
        DriverManagerDataSource dataSource
          = new DriverManagerDataSource();
        dataSource.setDriverClassName(
          env.getProperty("jdbc.driverClassName"));
        dataSource.setUrl(env.getProperty("product.jdbc.url"));
        dataSource.setUsername(env.getProperty("jdbc.user"));
        dataSource.setPassword(env.getProperty("jdbc.pass"));

        return dataSource;
    }

    @Bean
    public PlatformTransactionManager productTransactionManager() {
 
        JpaTransactionManager transactionManager
          = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
          productEntityManager().getObject());
        return transactionManager;
    }
}

5. Simple Test

Finally – let’s test our configurations.

We will try a simple test by creating an instance of each entity and make sure it is created – as in the following example:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { UserConfig.class, ProductConfig.class })
@TransactionConfiguration
public class JPAMultipleDBTest {
 
    @Autowired
    private UserRepository userRepository;

    @Autowired
    private ProductRepository productRepository;

    @Test
    @Transactional("userTransactionManager")
    public void whenCreatingUser_thenCreated() {
        User user = new User();
        user.setName("John");
        user.setEmail("[email protected]");
        user.setAge(20);
        user = userRepository.save(user);

        assertNotNull(userRepository.findOne(user.getId()));
    }

    @Test
    @Transactional("userTransactionManager")
    public void whenCreatingUsersWithSameEmail_thenRollback() {
        User user1 = new User();
        user1.setName("John");
        user1.setEmail("[email protected]m");
        user1.setAge(20);
        user1 = userRepository.save(user1);
        assertNotNull(userRepository.findOne(user1.getId()));

        User user2 = new User();
        user2.setName("Tom");
        user2.setEmail("[email protected]");
        user2.setAge(10);
        try {
            user2 = userRepository.save(user2);
        } catch (DataIntegrityViolationException e) {
        }

        assertNull(userRepository.findOne(user2.getId()));
    }

    @Test
    @Transactional("productTransactionManager")
    public void whenCreatingProduct_thenCreated() {
        Product product = new Product();
        product.setName("Book");
        product.setId(2);
        product.setPrice(20);
        product = productRepository.save(product);

        assertNotNull(productRepository.findOne(product.getId()));
    }
}

6. Conclusion

This article was a practical overview of how to configure your Spring Data JPA project to use multiple databases.

The full implementation of this article can be found in the GitHub project – this is a Maven-based project, so it should be easy to import and run as it is.

I just announced the new Spring 5 modules in REST With Spring:

>> CHECK OUT THE LESSONS

  Subscribe  
newest oldest most voted
Notify of
W. Biller
Guest

I worked on a project with a similar layout. It was ok, but you had to manage the cross referenced entities by hand. Getting the ID, loading it from the other DB and so on. This was not so good.
After all I rather would span a layer above the databases, create synonyms for all tables of the databases and use that layer as source for JPA.

Eugen Paraschiv
Guest

Yes – doing that kind of low level work by hand doesn’t make a lot of sense – so it’s a good idea to go with something like this, where you don’t have to (if possible). Cheers,
Eugen.

Vern
Guest

How do you implement the scenario when entity Product resides on multiple databases?

Eugen Paraschiv
Guest

In that case, you’ll have to have both configurations include that package and then – whenever you’re using the client that’s going to talk to that particular database – you need to specify which one it is. For example – if you have 2 ProductRepository implementations, you’ll have to wire in and use the right one (via @Qualifier). If however you’re using the EntityManager directly, you similarly need to use the right one (@PersistenceContext or @Qualifier). Hope that helps. Cheers,
Eugen.

Parth Kachchhi
Guest

How can I mange DataSources at transaction level. Meaning I want to handle multiple data-sources for single repository(JpaRepository).
My requirement is that I want to perform read/write operations to database using different data sources.

Eugen Paraschiv
Guest

Last time I looked, it couldn’t find a good way to do this. What I did instead was a bit verbose, but quite clear – I had 2 different implementations, one for each. With a clean design, you can minimize the duplication almost entirely. Hope it helps. Cheers,
Eugen.

Hatem Jaber
Guest

Just ran across this article and found it to be very informative and easy to follow, thank you!

Arun Attupurath
Guest

I am getting “No qualifying bean of type” even when I have given the base package correctly. Can you give any ideas as to how this might occur ?

Eugen Paraschiv
Guest

Hey Arun – what exactly are you running when you’re seeing that particular exception? Cheers,
Eugen.