Spring Boot with Spring Data JPA provides quick and easy way to configure single datasource and accessing database using repository. But have you wondered if you have to get data from multiple databases or even different DBMS?

Lucky for us spring provides a solution for this also.

Add additional data source configuration in your application.properties

prd.datasource.jdbcUrl=jdbc:mysql://localhost:3306/productdb
prd.datasource.username=root
prd.datasource.password=root

usr.datasource.jdbcUrl=jdbc:mysql://localhost:3306/userdb
usr.datasource.username=root
usr.datasource.password=root

Create Java package for each data source

(For this example we will use two database User and Product.)

Creating Database configs

LocalContainerEntityManagerFactoryBean

FactoryBean that creates a JPA EntityManagerFactory according to JPA's standard container bootstrap contract.

HibernateJpaVendorAdapter

Exposes Hibernate's persistence provider and Hibernate's Session as extended EntityManager interface, and adapts AbstractJpaVendorAdapter's common configuration settings.

HikariConfig

HikariConfig is the configuration class used to initialize a data source. It comes with four well-known, must-use parameters: username, password, jdbcUrl, and dataSourceClassName.

PlatformTransactionManager

The PlatformTransactionManager helps the template to create, commit or roll back transactions. When using Spring Boot, an appropriate bean of type PlatformTransactionManager will be automatically registered, so we just need to simply inject it.

User Database Config

@Configuration
@EnableJpaRepositories(
        basePackages = "com.shail.multidb.usr",
        entityManagerFactoryRef = "userEntityManager",
        transactionManagerRef = "userTransactionManager"
)
public class UserConfig {

    @Autowired
    private Environment env;

    @Bean
    public LocalContainerEntityManagerFactoryBean userEntityManager() {
        final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(userDataSource());
        em.setPackagesToScan("com.shail.multidb.usr");

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

        return em;
    }

    @Bean
    public DataSource userDataSource() {
        HikariConfig config = new HikariConfig();
        config.setDriverClassName(Preconditions.checkNotNull(env.getProperty("mysql.datasource.driver_class_name")));
        config.setJdbcUrl(env.getProperty("usr.datasource.jdbcUrl"));
        config.setUsername(env.getProperty("usr.datasource.username"));
        config.setPassword(env.getProperty("usr.datasource.password"));
        config.setMaximumPoolSize(Integer.parseInt(env.getProperty("hikariCP.datasource.maximum_pool_size")));
        config.setMaxLifetime(Long.parseLong(env.getProperty("hikariCP.datasource.max_lifetime")));
        config.setPoolName(env.getProperty("hikariCP.datasource.pool_name"));
        config.setConnectionTimeout(Long.parseLong(env.getProperty("hikariCP.datasource.connection_timeout")));
        config.setMinimumIdle(Integer.parseInt(env.getProperty("hikariCP.datasource.minimum_idle")));
        config.setIdleTimeout(Long.parseLong(env.getProperty("hikariCP.datasource.idle_timeout")));

        return new HikariDataSource(config);
    }

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

Product Database config

@Configuration
@EnableJpaRepositories(
        basePackages = "com.shail.multidb.prd",
        entityManagerFactoryRef = "productEntityManager",
        transactionManagerRef = "productTransactionManager"
)
public class ProductConfig {

    @Autowired
    private Environment env;

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean productEntityManager() {
        final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(productDataSource());
        em.setPackagesToScan("com.shail.multidb.prd");

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

        return em;
    }

    @Primary
    @Bean
    public DataSource productDataSource() {
        HikariConfig config = new HikariConfig();
        config.setDriverClassName(Preconditions.checkNotNull(env.getProperty("mysql.datasource.driver_class_name")));
        config.setJdbcUrl(env.getProperty("prd.datasource.jdbcUrl"));
        config.setUsername(env.getProperty("prd.datasource.username"));
        config.setPassword(env.getProperty("prd.datasource.password"));
        config.setMaximumPoolSize(Integer.parseInt(env.getProperty("hikariCP.datasource.maximum_pool_size")));
        config.setMaxLifetime(Long.parseLong(env.getProperty("hikariCP.datasource.max_lifetime")));
        config.setPoolName(env.getProperty("hikariCP.datasource.pool_name"));
        config.setConnectionTimeout(Long.parseLong(env.getProperty("hikariCP.datasource.connection_timeout")));
        config.setMinimumIdle(Integer.parseInt(env.getProperty("hikariCP.datasource.minimum_idle")));
        config.setIdleTimeout(Long.parseLong(env.getProperty("hikariCP.datasource.idle_timeout")));

        return new HikariDataSource(config);
    }

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

Creating Entity and Repo

ProductEntity.java

@Entity
@Table(name = "product")
public class Product {

    @Id
    private Integer id;
    private String name;
    private String price;
    @Column(length = 5000)
    private String description;
    private String weight;
    private String brand;
    private String onForSale;
    private String status;
    //Getter & Setters & Constructor
}

ProductRepo.java

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

UserEntity.java

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String name;
    @Column(unique = true, nullable = false)
    private String email;
    private int age;

   //Getter & Setters & Constructor
}

UserRepo.java

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

Testing Implementation

To test our implementation create a simple rest controller with two methods; one for saving and other for fetching data.

TestController.java

@RestController
public class TestController {

    @Autowired
    ProductRepo prdRepo;

    @Autowired
    UserRepo usrRepo;

    @Autowired
    private SecretKeyConstant keyConstant;

    @PostMapping(value = "/save")
    public String saveData() {
        prdRepo.save(new Product(1, "Nokia Lumia 300", "450.0", "Mobile", "56.0", "Nokia", "Y", "Y"));
        usrRepo.save(new User(1, "John", "john@mail.com", 45));;
        return "success";
    }

    @GetMapping(value = "/fetch")
    public String fetchData() throws JsonProcessingException {
        String prds = new ObjectMapper().writeValueAsString(prdRepo.findAll());
        String usrs = new ObjectMapper().writeValueAsString(usrRepo.findAll());
        return "Products:  " + prds + "\nUsers:  " + usrs;
    }
}

Summary

In this example, we learned to configure two MySQL databases in spring boot. Using the same approach if needed we can also configure databases created in different DBMS.

As always you can find the source code for the example @ Github