Spring JPA – Multiple Databases

1. Overview

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

Further reading:

Spring Data JPA – Derived Delete Methods

Learn how to define Spring Data deleteBy and removeBy methods

Read more →

Configuring a DataSource Programmatically in Spring Boot

Learn how to configure a Spring Boot DataSource programmatically, thereby side-stepping Spring Boot’s automatic DataSource configuration algorithm.

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 com.baeldung.multipledb.model.user;

@Entity
@Table(schema = "users")
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 com.baeldung.multipledb.model.product;

@Entity
@Table(schema = "products")
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.

Further reading:

Spring Data JPA – Derived Delete Methods

Learn how to define Spring Data deleteBy and removeBy methods

Read more →

Configuring a DataSource Programmatically in Spring Boot

Learn how to configure a Spring Boot DataSource programmatically, thereby side-stepping Spring Boot’s automatic DataSource configuration algorithm.

Read more →

3. The JPA Repositories

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

package com.baeldung.multipledb.dao.user;

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

And ProductRepository:

package com.baeldung.multipledb.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 interfaces for User:

  • DataSource

  • EntityManagerFactory (userEntityManager)

  • TransactionManager (userTransactionManager)

Let’s start by looking the User configuration:

@Configuration
@PropertySource({ "classpath:persistence-multiple-db.properties" })
@EnableJpaRepositories(
    basePackages = "com.baeldung.multipledb.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[] { "com.baeldung.multipledb.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 = "com.baeldung.multipledb.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[] { "com.baeldung.multipledb.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. Multiple Databases in Spring Boot

Spring Boot can simplify the configuration above.

By default, Spring Boot will instantiate its default DataSource with the configuration properties prefixed by spring.datasource.*:

spring.datasource.jdbcUrl = [url]
spring.datasource.username = [username]
spring.datasource.password = [password]

We now want to keep on using the same way to configure the second DataSource, but with a different property namespace:

spring.second-datasource.jdbcUrl = [url]
spring.second-datasource.username = [username]
spring.second-datasource.password = [password]

Because we want the Spring Boot autoconfiguration to pick up those different properties (and actually instantiate two different DataSources), we’ll define 2 configuration classes similar to the ones in the previous sections:

@Configuration
@PropertySource({"classpath:persistence-multiple-db-boot.properties"})
@EnableJpaRepositories(
  basePackages = "com.baeldung.multipledb.dao.user",
  entityManagerFactoryRef = "userEntityManager",
  transactionManagerRef = "userTransactionManager")
public class PersistenceUserAutoConfiguration {

    @Primary
    @Bean
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource userDataSource() {
        return DataSourceBuilder.create().build();
    }
    // userEntityManager bean

    // userTransactionManager bean
}
@Configuration
@PropertySource({"classpath:persistence-multiple-db-boot.properties"})
@EnableJpaRepositories(
  basePackages = "com.baeldung.multipledb.dao.product",
  entityManagerFactoryRef = "productEntityManager",
  transactionManagerRef = "productTransactionManager")
public class PersistenceProductAutoConfiguration {

    @Bean
    @ConfigurationProperties(prefix="spring.second-datasource")
    public DataSource productDataSource() {
        return DataSourceBuilder.create().build();
    }

    // productEntityManager bean

    // productTransactionManager bean
}

We have defined the data source properties inside persistence-multiple-db-boot.properties according to the Boot auto-configuration convention.

The interesting part is annotating the data source bean creation method with *@ConfigurationProperties*. We just need to specify the corresponding config prefix. Inside this method, we’re using a DataSourceBuilder, and Spring Boot will automatically take care of the rest.

But how do the configured properties actually get injected into the DataSource configuration?

When calling the build() method on the DataSourceBuilder, it’ll call its private bind() method:

public T build() {
    Class<? extends DataSource> type = getType();
    DataSource result = BeanUtils.instantiateClass(type);
    maybeGetDriverClassName();
    bind(result);
    return (T) result;
}

This private method performs much of the autoconfiguration magic, binding the resolved configuration to the actual DataSource instance:

private void bind(DataSource result) {
    ConfigurationPropertySource source = new MapConfigurationPropertySource(this.properties);
    ConfigurationPropertyNameAliases aliases = new ConfigurationPropertyNameAliases();
    aliases.addAliases("url", "jdbc-url");
    aliases.addAliases("username", "user");
    Binder binder = new Binder(source.withAliases(aliases));
    binder.bind(ConfigurationPropertyName.EMPTY, Bindable.ofInstance(result));
}

Although we don’t have to touch any of this code ourselves, it’s still useful to know what’s happening under the hood of the Spring Boot autoconfiguration.

Besides this, the Transaction Manager and Entity Manager beans configuration is the same as the standard Spring application.

7. 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.

Leave a Reply

Your email address will not be published.