Spring Boot, MyBatis, MultiDataSource

In many projects, the need arises to manage multiple databases efficiently. Typically, this involves setting up a central database for fast access and an archive database for long-term storage. In this article, we’ll dive into configuring and utilizing the Spring framework alongside MyBatis to establish connections with multiple databases.

Setting Up the Project

To begin, let’s create a new project. Navigate to Spring Initializr and select the following dependencies:

• MyBatis Framework
• H2 Database

The list of dependencies is as follows:

dependencies {
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:3.0.3'
    runtimeOnly 'com.h2database:h2'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testImplementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter-test:3.0.3'
    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

Configuring Data Sources

In this example, we will utilize two separate in-memory H2 databases. Let’s configure the first data source. It will provide operational data and be named the “central” database. We will initialize it using the @ConfigurationProperties annotation. In the @MapperScan annotation, we will specify the basePackages parameter, which denotes the package where mapper files for this data source should be located.

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@MapperScan(
      basePackages = "**.springmybatismultidatasource.repositories.central",
      sqlSessionFactoryRef = "centralSqlSessionFactory"
)
public class CentralDataSourceConfiguration {

   @Bean("centralDataSource")
   @ConfigurationProperties(prefix = "datasource.central-database")
   public DataSource dataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean("centralSqlSessionFactory")
   public SqlSessionFactory sqlSessionFactory(@Autowired @Qualifier("centralDataSource") DataSource dataSource) throws Exception {
      SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
      sessionFactory.setDataSource(dataSource);
      return sessionFactory.getObject();
   }
}

In the application.yml, we’ll add the following lines:

datasource:
 central-database:
   jdbcUrl: jdbc:h2:mem:central;INIT=RUNSCRIPT FROM 'classpath:/sql/central-db-init-script.sql'
   driverClassName: org.h2.Driver

We’ll initialize the central database using the script central-db-init-script.sql, where we’ll create a table and populate it with data. It’s worth noting that this example is solely for demonstration purposes. In real projects, only database connections are typically utilized.

Adding Another Data Source

Following a similar approach, let’s configure another data source named “archive” for historical data storage.

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;

@Configuration
@MapperScan(
  basePackages = "**.springmybatismultidatasource.repositories.archive",
  sqlSessionFactoryRef = "archiveSqlSessionFactory"
)
public class ArchiveDataSourceConfiguration {

 @Bean("archiveDataSource")
 @ConfigurationProperties(prefix = "datasource.archive-database")
 public DataSource dataSource() {
  return DataSourceBuilder.create().build();
 }

 @Bean("archiveSqlSessionFactory")
 public SqlSessionFactory sqlSessionFactory(@Autowired @Qualifier("archiveDataSource") DataSource dataSource) throws Exception {
  SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
  sessionFactory.setDataSource(dataSource);
  return sessionFactory.getObject();
 }
}

Here’s the final version of the application.yml configuration file that encompasses both data sources:

datasource:
  archive-database:
    jdbcUrl: jdbc:h2:mem:archive;INIT=RUNSCRIPT FROM 'classpath:/sql/archive-db-init-script.sql'
    driverClassName: org.h2.Driver
  central-database:
    jdbcUrl: jdbc:h2:mem:central;INIT=RUNSCRIPT FROM 'classpath:/sql/central-db-init-script.sql'
    driverClassName: org.h2.Driver

Creating Mappers

We’ll need mappers to retrieve data from each database. These mappers should be placed in packages specified in the @MapperScan annotation.

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;

@Mapper
public interface CentralRepository {
   @Select("SELECT * FROM payments")
   List<Payment> getPayments();
}

Following the same pattern, let’s define the second mapper. Afterwards, simply autowire the mapper class onto your components, and each one should connect to a different database.

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;

@Mapper
public interface ArchiveRepository {
 @Select("SELECT * FROM payments_archive")
 List<Payment> getPayments();
}

Writing Tests

Finally, we’ll write a test to ensure data retrieval from both databases is successful.

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotEquals;

@SpringBootTest
class SpringMybatisMultiDatasourceApplicationTests {
 @Autowired
 ArchiveRepository archiveRepository;
 @Autowired
 CentralRepository centralRepository;

 @Test
 void testMultiDatasourcePayments() {
  // Act: Retrieve payments from both repositories
  List<Payment> archivePayments = archiveRepository.getPayments();
  List<Payment> centralPayments = centralRepository.getPayments();

  // Assert: Verify the results
  assertFalse(archivePayments.isEmpty(), "Archive payments should not be empty");
  assertFalse(centralPayments.isEmpty(), "Central payments should not be empty");
  assertNotEquals(archivePayments, centralPayments, "Payments from archive and central repositories should not be equal");
 }
}

Conclusion

In this article, we’ve demonstrated how to configure and utilize multiple data sources with Spring and MyBatis. You can find the complete example on GitHub. With this setup, handling multiple databases in your projects becomes seamless and efficient.