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
Thanks for this blog keep sharing your thoughts like this...
ReplyDeleteLeadership Training in Chennai
Leadership Online Training