This article explores scenarios where multiple data sources are required and demonstrates how to configure them using Spring Boot.

The configuration approach primarily references another blog post: https://blog.csdn.net/u012060033/article/details/123759694

For a concise overview, readers may refer to: https://www.cnblogs.com/chen-msg/p/7485701.html

I. Scenarios for Using Multiple Data Sources

Common reasons for employing multiple data sources include:

1. Data collection
2. Database sharding
3. Other use cases

This article focuses on database sharding. Typically, "sharding" refers to two primary goals:

1. Supporting multi-tenancy
2. Horizontal data partitioning to enhance throughput
3. Read-write separation

Implementing Multi-Tenancy

In certain conditions, sharding can be used for multi-tenancy, particularly when avoiding resource waste and reducing maintenance overhead is desired.

However, this practice is not generally recommended. When clients have minimal data volume, sharding provides little value. If data volumes are significant, clients should pay more, making shared infrastructure unnecessary.

Most large enterprises prefer self-managed infrastructure due to concerns over data security rather than relying on third-party cloud services.

Read-Write Separation

While databases offer built-in read-write separasion capabilities, custom implementations may still be needed in rare cases.

This article does not delve into whether multi-data source usage is advisable but instead assumes it's required.

II. Configuring Multiple Data Sources

2.1 Key Challenges in Multi-Data Source Setup

• Managing component switching with in systems like OCRMs that interact with data sources
• Ensuring transaction consistency across multiple data sources (if interrelated)
• Minimizing costs associated with data source switching

The topic of transaction consistency is extensive and thus omitted here.

The core concern is minimizing switching overhead.

MyBatis or MyBatis-Plus provide easier integration compared to custom frameworks.

2.2 Standard Implementation Approach

Spring supports multi-data source configurations natively, simplifying implementation. Designers should careful choose their approach.

Implementation steps:

1. Define data sources
2. Create related beans: data source, JdbcTemplate, transaction managers, etc.

Further modifications are based on these foundational elements.

Detailed explanation available at: https://www.cnblogs.com/chen-msg/p/7485701.html

III. Example Implementation

Example follows the guide from: https://www.cnblogs.com/chen-msg/p/7485701.html

Environment: Windows 11, MySQL 8.0.x, Spring Boot 2.6.7

3.1 application.properties

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.autoCommit=false
spring.datasource.hikari.connectionTimeout=180000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=1800000
spring.datasource.hikari.minimumIdle=3
spring.datasource.hikari.maximumPoolSize=6
spring.datasource.hikari.connection-test-query=select 1

# Multi-source demonstration
spring.datasource.school.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.school.jdbcUrl=jdbc:mysql://localhost:7799/<strong>spring</strong>?rewriteBatchedStatements=true&autoReconnect=true&allowMultiQueries=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.school.username=spring
spring.datasource.school.password=123

spring.datasource.factory.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.factory.jdbcUrl=jdbc:mysql://localhost:7799/<strong>factory</strong>?rewriteBatchedStatements=true&autoReconnect=true&allowMultiQueries=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.factory.username=spring
spring.datasource.factory.password=123

3.2 DataSourceConfig.java - Data Source Configuration

package com.example.multids.config;

import javax.sql.DataSource;

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 org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;


@Configuration
public class DataSourceConfig {
    @Bean(name = "factoryDataSource")
    @Qualifier("factoryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.factory")
    public DataSource factoryDataSource()
    {
        System.out.println("-------------------- factoryDataSource init ---------------------");
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "schoolDataSource")
    @Qualifier("schoolDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.school")
    public DataSource schoolDataSource()
    {
        System.out.println("-------------------- schoolDataSource init ---------------------");
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "schoolJdbcTemplate")
    @Qualifier("schoolJdbcTemplate")
    public JdbcTemplate schoolJdbcTemplate(@Qualifier("schoolDataSource") DataSource dataSource)
    {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "factoryJdbcTemplate")
    @Qualifier("factoryJdbcTemplate")
    public JdbcTemplate factoryJdbcTemplate(@Qualifier("factoryDataSource") DataSource dataSource)
    {
        return new JdbcTemplate(dataSource);
    }
    
    /****** Transaction Manager Configuration ********/
    
    @Bean
    public PlatformTransactionManager schoolTransactionManager(@Qualifier("schoolDataSource")DataSource ds) {
     return new DataSourceTransactionManager(ds);
    }
     
    @Bean
    public PlatformTransactionManager factoryTransactionManager(@Qualifier("factoryDataSource")DataSource ds) {
     return new DataSourceTransactionManager(ds);
    }
}

3.3 SqlController - Test Controller

package com.example.multids.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(value = "/multids")
public class SqlController {

    @Qualifier("schoolJdbcTemplate")
    @Autowired
    private JdbcTemplate jdbcTp;

    @Qualifier("factoryJdbcTemplate")
    @Autowired
    private JdbcTemplate factoryJdbcTp;

    @RequestMapping(value = "getAll", method = { RequestMethod.GET, RequestMethod.POST })
    public Object getAll() {
        String sql = "select * from student limit 2";
        List<Map<String, Object>> studentList = jdbcTp.queryForList(sql);
        String inventorySql = "select * from inventory limit 2";
        List<Map<String, Object>> inventoryList = factoryJdbcTp.queryForList(inventorySql);
        Map<String, Object> result = new HashMap<>();
        result.put("student-spring-ds", studentList);
        result.put("inventory-factory-ds", inventoryList);
        return result;
    }
}

3.4 Test Results

Browser execution result: http://localhost:8081/multids/getAll

IV. Summary

Overall, the setup process remains relatively straightforward.