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]");
        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
Nicky Jah Yeh
Guest
Thanks for this post it helped me a lot. I have one question, I have User and Admin schemas so I created 2 configurations like you described. I have a Role class, it’s imported in the User configuration, can a class in the Admin package make a join with the Role in the User package? So in effect I’ll have a foreign key in the Admin schema referencing a primary key in the User schema (role table). I know this is possible on the database level, but can I achieve it using spring? The only thing that works for me… Read more »
Eugen Paraschiv
Guest

Well, it sounds like this is a surface problem pointing to a deeper domain modeling issue you have. The simple answer is – you can roll a separate client (think raw JDBC) and use that. But – higher level, I would recommend doing some domain work and maybe getting to the root of why the way you’re arranging your data doesn’t match the way you’re using that data.
Hope it helps. Cheers,
Eugen.

Sujan Deb
Guest

Can this support a distributed transaction? If yes then how?

Eugen Paraschiv
Guest

Hey Sujan – that’s an interesting question here, but definitely outside the scope of this particular article. And of course the answer depends on the way you’re connecting to the DB and on the underlying DB technology as well.

Naresh P
Guest

Hi, I have been getting an exception while starting the server “Caused by: java.lang.IllegalStateException: Only one TransactionManagementConfigurer may exist” please help

Eugen Paraschiv
Guest

Well Naresh, I’d need to see code to be able to diagnose that. I’m assuming you’re not seeing that exception on the actual github project of this article – but in your own code right?
The way to get to he bottom of it is – have a simple project that reproduces the issue (but does nothing else – so it’s really minimal) and then post the question over on StackOverflow.
At that point, send me a link to it over email and I’ll have a look. Cheers,
Eugen.

Chad J Hamilton
Guest

very confused with how jpa datasources with repos work here. i have tables in 4 dbs. i setup 4 datasources hoping to get access to the entities mapped to those tables in each datasource. does not appear to work unless i map 1 datasource to 1 entity? do i need several datasources to get data from all of these entities/tables? is spring data jpa the best option for this? seems like it is…maybe im missing something. please advise.

Eugen Paraschiv
Guest

Hey Chad – my experience with a very custom data structure like you seem to have is, at least for that particular scenario (not necessarily for your entire app) – don’t use JPA. Use something like jOOQ or Querydsl, or even JDBC – which is going to give you a lot more flexibility than trying to bend JPA to that underlying structure.
Hope that helps. Cheers,
Eugen.

Vinoth
Guest

Hi Eugen, I am trying to deal with multiple databases so i just took your above program as base model and tried to implement that. But the problem here is am getting some errors they are (No qualifying bean of type [javax.sql.DataSource] found for dependency) , (Could not autowire field: private javax.sql.DataSource ) , (Exception encountered during context initialization – cancelling refresh attempt ) . I doubted that i may did some mistakes is gradle.build file or application.properties file. Can you please clear me . thanks in advance

Eugen Paraschiv
Guest

Hey Vinoth,
I’d be happy to help, but I’ll need actual code to reproduce the error – unfortunately this isn’t the kind of problem that you can solve just from a high level description.
Hope that makes sense. Cheers,
Eugen.