SpringBoot-使用MyBatis注册多个SqlSessionFactory进行多数据源管理

数据源配置 - DruidDataSource

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    w1:
      url: jdbc:postgresql://localhost:55432/study
      username: user
      password: pw
    r1:
      url: jdbc:postgresql://localhost:55433/study
      username: user
      password: pw

多数据源配置 - SqlSessionFactory

用MapperScan中不同的basePackages创建不同的SqlSessionFactory

@Configuration
@MapperScan(basePackages = "org.jxch.study.studyspringcloud.mapper.w", sqlSessionFactoryRef = "wSqlSessionFactory")
public class WMyBatisConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.w1")
    public DataSource dsw1() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public SqlSessionFactory wSqlSessionFactory() throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dsw1());
        // 指定对应的mapper.xml文件
        // sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/w/*.xml"));
        return sessionFactory.getObject();
    }

    @Bean
    public DataSourceTransactionManager wTransactionManager(){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dsw1());
        return dataSourceTransactionManager;
    }

    @Bean
    public TransactionTemplate wTransactionTemplate(){
        return new TransactionTemplate(wTransactionManager());
    }

}

@Configuration
@MapperScan(basePackages = "org.jxch.study.studyspringcloud.mapper.r", sqlSessionFactoryRef = "rSqlSessionFactory")
public class RMyBatisConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.r1")
    public DataSource dsr1() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    public SqlSessionFactory rSqlSessionFactory() throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dsr1());
        // 指定对应的mapper.xml文件
        // sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/r/*.xml"));
        return sessionFactory.getObject();
    }

    @Bean
    public DataSourceTransactionManager rTransactionManager(){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dsr1());
        return dataSourceTransactionManager;
    }

    @Bean
    public TransactionTemplate rTransactionTemplate(){
        return new TransactionTemplate(rTransactionManager());
    }

}

Dao - Mybatis

@Data
@Accessors(chain = true)
@TableName("r")
public class R {
    @TableId
    private Integer id;
    private String name;
}

@Data
@Accessors(chain = true)
@TableName("w")
public class W {
    @TableId
    private Integer id;
    private String name;
}

Mapper - MapperScan(basePackages)

不同的数据源放到不同的package路径

package org.jxch.study.studyspringcloud.mapper.r;
@Mapper
public interface RMapper {
    @Select("select * from r")
    List<R> selectAll();
}

package org.jxch.study.studyspringcloud.mapper.w;
@Mapper
public interface WMapper {
    @Select("select * from w")
    List<W> selectAll();
}

切换数据源 - 无侵入

public interface SelectService {
    String selectWAll();
    String selectRAll();
}

@Service
@RequiredArgsConstructor
public class SelectServiceImpl implements SelectService {
    private final RMapper rMapper;
    private final WMapper wMapper;

    @Override
    public String selectWAll() {
        return JSON.toJSONString(wMapper.selectAll());
    }

    @Override
    public String selectRAll() {
        return JSON.toJSONString(rMapper.selectAll());
    }

}

测试 - SpringBootTest

@Slf4j
@SpringBootTest
class SelectServiceImplTest {
    @Autowired
    private SelectService selectService;

    @Test
    void selectWAll() {
        log.info(selectService.selectWAll());
    }

    @Test
    void selectRAll() {
        log.info(selectService.selectRAll());
    }

}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.0.6</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>org.jxch.study</groupId>
    <artifactId>study-springcloud</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>study-springcloud</name>
    <description>study-springcloud</description>
    <properties>
        <java.version>20</java.version>
        <spring-cloud.version>2022.0.2</spring-cloud.version>
        <spring-boot.version>3.0.6</spring-boot.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring-boot.version}</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.14.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.18</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.28</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.23</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>2.0.33</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring-boot.version}</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

库表结构 - sql

create table public.w (
  id integer primary key not null,
  name character varying(100)
);

create table public.r (
  id integer primary key not null,
  name character varying(100)
);

数据库部署 - docker-compose

version: '3.8'
services:
  db_w1:
    image: postgres
    restart: unless-stopped
    ports:
      - "55432:5432"
    volumes:
      - ./data/db_w1:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=pw
      - POSTGRES_USER=user
      - POSTGRES_DB=study
  db_r1:
    image: postgres
    restart: unless-stopped
    ports:
      - "55433:5432"
    volumes:
      - ./data/db_r1:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=pw
      - POSTGRES_USER=user
      - POSTGRES_DB=study