Implementation method for multi-data source with Spring boot (Mybatis and Spring Data JPA)

I will organize the implementation method when using multi-data source with Spring boot.

For Mybatis

Since Autoconfig is used, DB connection information is described in application.properties.

application.properties


#Primary DB
spring.datasource.url=jdbc:mysql://localhost:3306/primary_db?useSSL=false
spring.datasource.username=demo
spring.datasource.password=demo
spring.datasource.driverClassName=com.mysql.jdbc.Driver

#Secondary DB
secondary.datasource.url=jdbc:mysql://localhost:3306/secondary_db?useSSL=false
secondary.datasource.username=demo
secondary.datasource.password=demo
secondary.datasource.driverClassName=com.mysql.jdbc.Driver

Create the main class of Spring boot as usual.

DemoApplication


@SpringBootApplication
public class DemoApplication {
	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args).close();
	}
}

Define the primary dataSource and sqlSession. -Basepackage of repository class -SQLMap storage folder

PrimaryDbConfig


@Configuration
@MapperScan(basePackages = PrimaryDbConfig.BASE_PACKAGES
            , sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryDbConfig {
	public static final String BASE_PACKAGES = "com.example.demo.primary";
	public static final String MAPPER_XML_PATH = "classpath:com/example/demo/primary/*.xml";

	@Primary
	@Bean(name = "primaryDataSource")
	@ConfigurationProperties(prefix = "spring.datasource")
	public DataSource dataSource() {
		return new DataSource();
	}

	@Primary
	@Bean(name = "primarySqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("primaryDataSource") DataSource primaryDataSource)
			throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(primaryDataSource);
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_XML_PATH));
		return bean.getObject();
	}

	@Bean(name = "primarySqlSessionTemplate")
	public SqlSessionTemplate sqlSessionTemplate(
			@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

Define a secondary dataSource and sqlSession as above.

SecondaryDbConfig


@Configuration
@MapperScan(basePackages = SecondaryDbConfig.BASE_PACKAGES
            , sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class SecondaryDbConfig {
	public static final String BASE_PACKAGES = "com.example.demo.secondary";
	public static final String MAPPER_XML_PATH = "classpath:com/example/demo/secondary/*.xml";
	
	@Bean(name = "secondaryDataSource")
	@ConfigurationProperties(prefix = "secondary.datasource")
	public DataSource dataSource() {
		return new DataSource();
	}

	@Bean(name = "secondarySqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("secondaryDataSource") DataSource secondaryDataSource)
			throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(secondaryDataSource);
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_XML_PATH));
		return bean.getObject();
	}

	@Bean(name = "secondarySqlSessionTemplate")
	public SqlSessionTemplate sqlSessionTemplate(
			@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

Create each repository class.

PrimaryRepository


@Mapper
public interface PrimaryRepository {
	public long selectCountFromPrimary();
}

SecondaryRepository


@Mapper
public interface SecondaryRepository {
	public long selectCountFromSecondary();
}

Define the primary SqlMap in the following resource folder.

com
 |-example
     |-demo
         |-primary
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        
<mapper namespace="com.example.demo.primary.PrimaryRepository">

    <select id="selectCountFromPrimary" resultType="long">
        select count(*) from emp;
    </select>
</mapper>

Define a secondary SqlMap in the following resource folder.

com
 |-example
     |-demo
         |-secondary
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        
<mapper namespace="com.example.demo.secondary.SecondaryRepository">

    <select id="selectCountFromSecondary" resultType="long">
        select count(*) from emp2;
    </select>
</mapper>

Call the above repository and execute SQL.

	@Autowired
    private PrimaryRepository primaryRepository;
	@Autowired
    private SecondaryRepository secondaryRepository;

    ...
    long countPrimary = primaryRepository.selectCountFromPrimary();
    long countSecondary = secondaryRepository.selectCountFromSecondary();

For Spring Data JPA

As with Mybatis, describe the DB connection information in application.properties.

application.properties


#Primary DB
spring.datasource.url=jdbc:mysql://localhost:3306/primary_db?useSSL=false
spring.datasource.username=demo
spring.datasource.password=demo
spring.datasource.driverClassName=com.mysql.jdbc.Driver

#Secondary DB
secondary.datasource.url=jdbc:mysql://localhost:3306/secondary_db?useSSL=false
secondary.datasource.username=demo
secondary.datasource.password=demo
secondary.datasource.driverClassName=com.mysql.jdbc.Driver

spring.jpa.database=default

Create the main class of Spring boot as usual.

DemoApplication


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

Define the primary dataSource, entityManager and transactionManager. -Basepackage of repository class -Domain class basepackage

PrimaryDbConfig


@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory"
                       , transactionManagerRef = "transactionManager"
                       , basePackages = { "com.example.demo.primary" })
public class PrimaryDbConfig {

	@Primary
	@Bean(name = "dataSource")
	@ConfigurationProperties(prefix = "spring.datasource")
	public DataSource dataSource() {
		return DataSourceBuilder.create().build();
	}

	@Primary
	@Bean(name = "entityManagerFactory")
	public LocalContainerEntityManagerFactoryBean entityManagerFactory(
			EntityManagerFactoryBuilder builder,
			@Qualifier("dataSource") DataSource dataSource) {
		return builder.dataSource(dataSource).packages("com.example.demo.primary.domain").build();
	}

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

Define the secondary dataSource, entityManager and transactionManager as above.

SecondaryDbConfig


@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "secondaryEntityManagerFactory"
                       , transactionManagerRef = "secondaryTransactionManager"
                       , basePackages = {"com.example.demo.secondary" })
public class SecondaryDbConfig {

	@Bean(name = "secondaryDataSource")
	@ConfigurationProperties(prefix = "secondary.datasource")
	public DataSource dataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "secondaryEntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
			EntityManagerFactoryBuilder builder,
			@Qualifier("secondaryDataSource") DataSource dataSource) {
		return builder.dataSource(dataSource).packages("com.example.demo.secondary.domain").build();
	}

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

Create each repository class and domain class.

PrimaryRepository


@Repository
public interface PrimaryRepository extends CrudRepository<Emp, String> {
	public long count();	
}

SecondaryRepository


@Repository
public interface SecondaryRepository extends CrudRepository<Emp2, String> {
	public long count();
}

EmpDomain


@Entity
@Table(name="emp")
public class Emp {
	
	@Id
	@Column(name="emp_id")
	private String empId;

	@Column(name="dept_id")
	private String deptId;

	...
}

EmpDomain2


@Entity
@Table(name="emp2")
public class Emp2 {
	
	@Id
	@Column(name="emp_id")
	private String empId;

	@Column(name="dept_id")
	private String deptId;

	...

Call the repository class with the same feeling as Mybatis.

	@Autowired
    private PrimaryRepository primaryRepository;
	@Autowired
    private SecondaryRepository secondaryRepository;

    ...
    long countPrimary = primaryRepository.selectCountFromPrimary();
    long countSecondary = secondaryRepository.selectCountFromSecondary();

Recommended Posts

Implementation method for multi-data source with Spring boot (Mybatis and Spring Data JPA)
Until data acquisition with Spring Boot + MyBatis + PostgreSQL
Compatibility of Spring JDBC and MyBatis with Spring Data JDBC (provisional)
Spring Boot Introductory Guide I tried [Accessing Data with JPA]
Implement REST API with Spring Boot and JPA (Application Layer)
Implement REST API with Spring Boot and JPA (Infrastructure layer)
Connect to database with spring boot + spring jpa and CRUD operation
Implement REST API with Spring Boot and JPA (domain layer)
See the behavior of entity update with Spring Boot + Spring Data JPA
Creating REST APIs with Spring JPA Data with REST and Lombok incredibly easy.
Spring with Kotorin --2 RestController and Data Class
HTTPS with Spring Boot and Let's Encrypt
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
Periodically update DB with Spring Batch and MyBatis
Spring Data JPA Entity cross-reference and its notes
Create CRUD apps with Spring Boot 2 + Thymeleaf + MyBatis
Sample code for DB control by declarative transaction in Spring Boot + Spring Data JPA
Sort by Spring Data JPA (with compound key sort)
Creating a common repository with Spring Data JPA
Spring Boot + Spring Data JPA About multiple table joins
Check the behavior of getOne, findById, and query methods in Spring Boot + Spring Data JPA
Form and process file and String data at the same time with Spring Boot + Java
Plans to support JDK 11 for Eclipse and Spring Boot
Settings for connecting to MySQL with Spring Boot + Spring JDBC
Testing JPA entities and repositories using Spring Boot @DataJpaTest
Try using DI container with Laravel and Spring Boot
A memorandum when trying Spring Data JPA with STS
Spring Security usage memo: Cooperation with Spring MVC and Boot
Spring Boot with Spring Security Filter settings and addictive points
I tried to get started with Spring Data JPA
Until the use of Spring Data and JPA Part 2
Until the use of Spring Data and JPA Part 1
Attempt to SSR Vue.js with Spring Boot and GraalJS
Connect Spring Boot and Angular type-safely with OpenAPI Generator
Download with Spring Boot
[Spring Data JPA] Can And condition be used in the automatically implemented method of delete?
How to use the same Mapper class in multiple data sources with Spring Boot + MyBatis
Spring Boot Mybatis SQL Snake Case Column and Camel Case Mapping
Change the injection target for each environment with Spring Boot 2
Until INSERT and SELECT to Postgres with Spring boot and thymeleaf
Domain Driven Development with Java and Spring Boot ~ Layers and Modules ~
8 things to insert into DB using Spring Boot and JPA
[Java] Sample project for developing web applications with Spring Boot
Spring + MyBatis connection setting method
Generate barcode with Spring Boot
Hello World with Spring Boot
Implement GraphQL with Spring Boot
Get started with Spring boot
[spring] Let's use Spring Data JPA
Hello World with Spring Boot!
File upload with Spring Boot
Spring Boot starting with copy
Spring Boot starting with Docker
Hello World with Spring Boot
Set cookies with Spring Boot
Use Spring JDBC with Spring Boot
Add module with Spring Boot
Getting Started with Spring Boot
Create microservices with Spring Boot
Send email with spring boot
Spring Boot for annotation learning