Using multiple datasources of mysql DB with Spring Boot and Spring Data

Spring Boot with Spring Data makes it easy to access a database through so-called Repositories. But what if you want to access multiple databases maybe even with different Database Management Systems?

In this post, we will show you how to configure multi data source with the MySQL using spring boot and spring data.

Project dependencies

task wrapper(type: Wrapper) {
    gradleVersion = '4.7'
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'


tasks.withType(JavaCompile) {
    options.encoding = 'UTF-8'
}

buildscript {
    ext {
        springBootVersion = '2.0.5.RELEASE'
    }
    repositories {
        mavenLocal()
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

repositories {
    mavenLocal()
    mavenCentral()
}


dependencies {
    compile('org.springframework.boot:spring-boot-starter-web') {
        exclude module: "spring-boot-starter-tomcat"
    }
    compile 'org.springframework.boot:spring-boot-starter-jetty'
    compile 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtime('mysql:mysql-connector-java')
    compileOnly('org.projectlombok:lombok')
    testCompile('org.springframework.boot:spring-boot-starter-test') {
        exclude(module: 'commons-logging')
    }
}

application.properties file

#mysql db configuration details
primary.mysql.url=jdbc:mysql://localhost:3306/test?useSSL=false
primary.mysql.username=root
primary.mysql.password=santosh
secondary.mysql.url=jdbc:mysql://localhost:3306/springbatch?useSSL=false
secondary.mysql.username=root
secondary.mysql.password=santosh
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
server.port=8081

Create AbstractMySQLConfig class for mysql DB

This class will have the details of database/datasource like url, username, and password.

package com.walking.techie.mysql.config;

import lombok.Data;
import org.springframework.context.annotation.Bean;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

import javax.sql.DataSource;

@Data
public abstract class AbstractMySQLConfig {

  // MySQL DB Properties
  private String url, username, password;

  public abstract DataSource dataSource();

  @Bean
  public JpaVendorAdapter jpaVendorAdapter() {
    HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
    adapter.setGenerateDdl(true);
    adapter.setShowSql(true);
    return adapter;
  }
}

Create configurable connection class for mysql DB

Here we will create two mysql configuration class, one configuration class will act as the primary data source and other will act as the secondary data source. Both class will extends AbstractMySQLConfig class.

package com.walking.techie.mysql.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
  basePackages = {"com.walking.techie.common.repository", "com.walking.techie.primary.repository"},
  entityManagerFactoryRef = "primaryEntityManager",
  transactionManagerRef = "primaryTransactionManager"
)
@ConfigurationProperties(prefix = "primary.mysql")
public class PrimaryMySQLConnection extends AbstractMySQLConfig {

  @Primary
  @Override
  @Bean(name = "primaryDataSource")
  public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setUrl(getUrl());
    dataSource.setUsername(getUsername());
    dataSource.setPassword(getPassword());
    return dataSource;
  }

  @Primary
  @Bean(name = "primaryEntityManager")
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(
      EntityManagerFactoryBuilder builder) {
    return builder
        .dataSource(dataSource())
        .persistenceUnit("test")
        .packages("com.walking.techie.model.primary")
        .build();
  }

  @Primary
  @Bean(name = "primaryTransactionManager")
  public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }
}

package com.walking.techie.mysql.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
  basePackages = {"com.walking.techie.secondary.repository"},
  entityManagerFactoryRef = "secondaryEntityManager",
  transactionManagerRef = "secondaryTransactionManager"
)
@ConfigurationProperties(prefix = "secondary.mysql")
public class SecondaryMySQLConnection extends AbstractMySQLConfig {

  @Override
  @Bean(name = "secondaryDataSource")
  public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setUrl(getUrl());
    dataSource.setUsername(getUsername());
    dataSource.setPassword(getPassword());
    return dataSource;
  }

  @Bean(name = "secondaryEntityManager")
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(
      EntityManagerFactoryBuilder builder) {
    return builder
        .dataSource(dataSource())
        .persistenceUnit("springbatch")
        .packages("com.walking.techie.model.secondary")
        .build();
  }

  @Bean(name = "secondaryTransactionManager")
  public PlatformTransactionManager transactionManager(
      @Qualifier("secondaryEntityManager") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }
}

Create repository

Here creating three repository, in that two repository will use the primary mysql connection, and other will use secondary mysql connection. I have created three packages to have all the repository in separate package to show the flexibility of the configurable connection.

package com.walking.techie.common.repository;
package com.walking.techie.common.repository;

import com.walking.techie.model.primary.User;
import org.springframework.data.jpa.repository.JpaRepository;

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

package com.walking.techie.primary.repository;

import com.walking.techie.model.primary.Employee;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {}

package com.walking.techie.secondary.repository;

import com.walking.techie.model.secondary.Product;
import org.springframework.data.jpa.repository.JpaRepository;

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

Java model class

package com.walking.techie.model.primary;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
@Data
public class User {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String name;
  private String email;
  private int age;
}

package com.walking.techie.model.primary;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
@Data
public class Employee {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String name;
  private String email;
  private int age;
}

package com.walking.techie.model.secondary;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
@Data
public class Product {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String name;
  private int price;
}

Controller

Here, create a rest controller.

package com.walking.techie.controller;

import com.walking.techie.handler.SaveTransaction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("save")
public class Transaction {

  @Autowired private SaveTransaction saveTransaction;

  @PostMapping
  public String save() {
    saveTransaction.save();
    return "success";
  }
}

Handler

Handler will have the logic to store the data into the mysql DB.

package com.walking.techie.handler;

public interface SaveTransaction {

  public void save();
}

package com.walking.techie.handler;

import com.walking.techie.common.repository.UserRepository;
import com.walking.techie.model.primary.Employee;
import com.walking.techie.model.primary.User;
import com.walking.techie.model.secondary.Product;
import com.walking.techie.primary.repository.EmployeeRepository;
import com.walking.techie.secondary.repository.ProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class SaveTransactionHandler implements SaveTransaction {

  @Autowired private UserRepository userRepository;
  @Autowired private ProductRepository productRepository;
  @Autowired private EmployeeRepository employeeRepository;

  @Override
  public void save() {
    // user details
    User user = new User();
    user.setName("John");
    user.setAge(28);
    user.setEmail("john@gmail.com");

    // product details
    Product product = new Product();
    product.setName("Insurance");
    product.setPrice(10000);

    // Employee details
    Employee employee = new Employee();
    employee.setName("Walking Techie");
    employee.setAge(26);
    employee.setEmail("walkingtechie@gmail.com");

    // save in DB
    userRepository.save(user);
    productRepository.save(product);
    employeeRepository.save(employee);
  }
}

Run Application

package com.walking.techie;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

  public static void main(String[] args) {
    SpringApplication.run(Application.class, args);
  }
}

Output

Run the web application. It will deploy on the embedded jetty server on port 8081. When you call the following url with post method. You will get the success as response, after that you can verify in the mysql DB.

localhost:8081/save

Recommended Posts:

• Spring Batch listener example
• Spring Batch Example in Spring boot - CSV File to MySql Database
• Spring Batch Hello world example using Spring boot
• Spring Boot - Remove _class field from MongoDB document
• Spring Boot Feature
• CSV Files with different delimiter to Mongo Database
• Using multiple data sources of MongoDB with Spring Boot and Spring Data
• Spring Batch Example in Spring boot - CSV File to Mongo Database
• Method hiding in Java
• Interface in Java 8