springboot结合mybatis使用多数据源的方式

记录学习日常

于 2024-05-30 16:07:57 发布

阅读量1.1k

点赞数 9

分类专栏：笔记文章标签： spring boot mybatis java

本文链接：https://blog.csdn.net/qq_34958326/article/details/139257310

版权

笔记专栏收录该内容

45 篇文章 0 订阅

订阅专栏

背景

最近有一个需求，有两个库需要做同步数据，一个Doris库，一个mysql库，两边的表结构一致，这里不能使用navicat等工具提供的数据传输之类的功能，只能使用代码做同步，springboot配置多数据源的方式在这里有示例：CSDNhttps://mp.csdn.net/mp_blog/creation/editor/132598605

方式一：不同的库对应不同的mapper

项目结构示例

数据库bank1配置

package com.example.springbootmybatisdiffdatasource.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;

/**
 * bank1配置
 * @Author wzw
 * @Date 2024/5/27 19:29
 * @Version 1.0
 * @Description todo
 */
@Configuration
@MapperScan(basePackages = "com.example.springbootmybatisdiffdatasource.mapper.bank1",
                sqlSessionFactoryRef ="bank1SqlSessionFactory" )
public class Bank1Config {
    /**
     * 本地mysql的bank1库的数据配置
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.bank1")
    public DataSourceProperties bank1SourceProperties() {
        return new DataSourceProperties();
    }
    /**
     * 初始化数据源
     * @return
     */
    @Bean("bank1DataSource")
    public DataSource bank1DataSource() {
        return bank1SourceProperties().initializeDataSourceBuilder().build();
    }

    /**
     * 创建session工厂
     * @param dataSource
     * @return org.apache.ibatis.session.SqlSessionFactory
     * @author: wzw
     * @date: 2024/5/27 19:37
     */
    @Bean("bank1SqlSessionFactory")
    public SqlSessionFactory bank1SqlSessionFactory(@Qualifier("bank1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        //设置数据源
        sqlSessionFactoryBean.setDataSource(dataSource);
        //设置mapper扫描路径
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:/mapper/bank1/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
}

数据库bank2配置

package com.example.springbootmybatisdiffdatasource.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

/**
 * bank2配置
 * @Author wzw
 * @Date 2024/5/27 19:38
 * @Version 1.0
 * @Description todo
 */
@Configuration
@MapperScan(basePackages = "com.example.springbootmybatisdiffdatasource.mapper.bank2",
        sqlSessionFactoryRef ="bank2SqlSessionFactory" )
public class Bank2Config {
    /**
     * 本地mysql的bank1库的数据配置
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.bank2")
    public DataSourceProperties bank2SourceProperties() {
        return new DataSourceProperties();
    }
    /**
     * 初始化数据源
     * @return
     */
    @Bean("bank2DataSource")
    public DataSource bank2DataSource() {
        return bank2SourceProperties().initializeDataSourceBuilder().build();
    }

    /**
     * 创建session工厂
     * @param dataSource
     * @return org.apache.ibatis.session.SqlSessionFactory
     * @author: wzw
     * @date: 2024/5/27 19:37
     */
    @Bean("bank2SqlSessionFactory")
    public SqlSessionFactory bank2SqlSessionFactory(@Qualifier("bank2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        //设置数据源
        sqlSessionFactoryBean.setDataSource(dataSource);
        //设置mapper扫描路径
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:/mapper/bank2/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
}

配置依赖数据库配置：

spring.application.name=springboot-mybatis-diffdatasource
server.port=8888

spring.datasource.bank1.url=jdbc:mysql://localhost:3306/bank1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.bank1.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.bank1.username=root
spring.datasource.bank1.password=123456

spring.datasource.bank2.url=jdbc:mysql://localhost:3306/bank2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.bank2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.bank2.username=root
spring.datasource.bank2.password=123456

实体类

package com.example.springbootmybatisdiffdatasource.entity;

import lombok.Data;

import java.sql.Timestamp;

/**
 * 数据库实体映射
 * @Author wzw
 * @Date 2024/5/27 19:44
 * @Version 1.0
 * @Description todo
 */
@Data
public class DeDuplication {
    /**
     * 日志号
     */
    private String txNo;
    /**
     * 创建时间
     */
    private Timestamp createTime;

}

bank1查询接口

package com.example.springbootmybatisdiffdatasource.mapper.bank1;

import com.example.springbootmybatisdiffdatasource.entity.DeDuplication;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * @Author wzw
 * @Date 2024/5/27 19:43
 * @Version 1.0
 * @Description todo
 */
@Mapper
public interface DeDuplication1Mapper {
    /**
     * 查询所有数据
     * @param 
     * @return java.util.List<com.example.springbootmybatisdiffdatasource.entity.DeDuplication>
     * @author: wzw
     * @date: 2024/5/27 19:54
     */
    List<DeDuplication> selectAll();
}

bank2批量插入接口

package com.example.springbootmybatisdiffdatasource.mapper.bank2;

import com.example.springbootmybatisdiffdatasource.entity.DeDuplication;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @Author wzw
 * @Date 2024/5/27 19:43
 * @Version 1.0
 * @Description todo
 */
@Mapper
public interface DeDuplication2Mapper {

    /**
     * 批量插入
     * @param list
     * @return java.lang.Integer
     * @author: wzw
     * @date: 2024/5/27 19:57
     */
    Integer batchInsert(@Param("list") List<DeDuplication> list);
}

bank1库示例表的xml

<?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.springbootmybatisdiffdatasource.mapper.bank1.DeDuplication1Mapper">

	<resultMap id="base" type="com.example.springbootmybatisdiffdatasource.entity.DeDuplication">
		<result column="tx_no" property="txNo"/>
		<result column="create_time" property="createTime"/>
	</resultMap>

	<select id="selectAll" resultMap="base">
		select * from de_duplication
	</select>

</mapper>

bank2库示例表的xml

<?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.springbootmybatisdiffdatasource.mapper.bank2.DeDuplication2Mapper">

	<insert id="batchInsert">
		insert into de_duplication(tx_no,create_time)
		values
		<foreach collection="list" item="a" separator=",">
			(
			 #{a.txNo},
			 #{a.createTime}
			)
		</foreach>
	</insert>

</mapper>

测试

package com.example.springbootmybatisdiffdatasource.test;

import com.example.springbootmybatisdiffdatasource.entity.DeDuplication;
import com.example.springbootmybatisdiffdatasource.mapper.bank1.DeDuplication1Mapper;
import com.example.springbootmybatisdiffdatasource.mapper.bank2.DeDuplication2Mapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * 测试类
 * @Author wzw
 * @Date 2024/5/27 20:02
 * @Version 1.0
 * @Description todo
 */
@RestController
public class TestController {
    @Autowired
    private DeDuplication1Mapper deDuplication1Mapper;

    @Autowired
    private DeDuplication2Mapper deDuplication2Mapper;

    @GetMapping("test")
    public void test(){
        List<DeDuplication> deDuplications = deDuplication1Mapper.selectAll();
        System.out.printf("个数：%d%n",deDuplications.size());
        deDuplication2Mapper.batchInsert(deDuplications);
        System.out.println("批量插入成功");
    }
}

bank1中的数据

bank2中目前是空的

运行测试方法

刷新bank2

至此方式一解决了不同库数据同步问题，但是有个问题需要考虑，示例是写一张表，如果写入成百上千张表呢，mapper是不是要double，这里采用动态路由的方式切换数据源，如下

方式二：动态切换数据源（不同库使用同一个mapper）

项目结构示例

动态数据源配置

package com.example.springbootmybatisdiffdatasource.twoway.config;

import com.example.springbootmybatisdiffdatasource.twoway.constant.DataSourceEnum;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * 同时配置bank1和bank2
 *
 * @Author wzw
 * @Date 2024/5/28 10:28
 * @Version 1.0
 * @Description todo
 */
@Configuration
//扫描mapper，两个库共用一个mapper，因为表结构是一致的
@MapperScan(basePackages = "com.example.springbootmybatisdiffdatasource.twoway.mapper")
public class DoubleDataSourceConfig {
    /**
     * 本地mysql的bank1库的数据配置
     *
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.bank1")
    public DataSourceProperties bank1SourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * bank1初始化
     *
     * @return
     */
    @Bean("bank1DataSource")
    public DataSource bank1DataSource() {
        return bank1SourceProperties().initializeDataSourceBuilder().build();
    }

    /**
     * bank2库的数据配置
     *
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.bank2")
    public DataSourceProperties bank2SourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * bank2初始化
     *
     * @return
     */
    @Bean("bank2DataSource")
    public DataSource bank2DataSource() {
        return bank2SourceProperties().initializeDataSourceBuilder().build();
    }

    /**
     * 配置动态数据源
     *
     * @param bank1
     * @param bank2
     * @return com.example.springbootmybatisdiffdatasource.twoway.config.DynamicToggleDataSource
     * @author: wzw
     * @date: 2024/5/28 10:56
     */
    @Bean("dynamicToggleDataSource")
    public DynamicToggleDataSource dynamicToggleDataSource(@Qualifier("bank1DataSource") DataSource bank1,
                                                           @Qualifier("bank2DataSource") DataSource bank2) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceEnum.BANK1, bank1);
        targetDataSources.put(DataSourceEnum.BANK2, bank2);
        DynamicToggleDataSource dynamicDataSource = new DynamicToggleDataSource();
        //数据源集合
        dynamicDataSource.setTargetDataSources(targetDataSources);
        //默认查询数据库bank1
        dynamicDataSource.setDefaultTargetDataSource(bank1);
        return dynamicDataSource;
    }

    /**
     * 创建session工厂
     * @param dataSource
     * @return org.apache.ibatis.session.SqlSessionFactory
     * @author: wzw
     * @date: 2024/5/27 19:37
     */
    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicToggleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        //设置数据源
        sqlSessionFactoryBean.setDataSource(dataSource);
        //设置mapper扫描路径
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:/twoway/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
}

package com.example.springbootmybatisdiffdatasource.twoway.config;

import com.example.springbootmybatisdiffdatasource.twoway.constant.DataSourceEnum;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态切换数据源，使用
 * @Author wzw
 * @Date 2024/5/28 10:35
 * @Version 1.0
 * @Description todo
 */
public class DynamicToggleDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }

    public static class DataSourceContextHolder {

        private static final ThreadLocal<DataSourceEnum> contextHolder = new ThreadLocal<>();

        public static void setDataSourceType(DataSourceEnum type) {
            contextHolder.set(type);
        }

        public static DataSourceEnum getDataSourceType() {
            return contextHolder.get();
        }

        public static void clearDataSourceType() {
            contextHolder.remove();
        }
    }
}

数据源枚举

package com.example.springbootmybatisdiffdatasource.twoway.constant;

import lombok.AllArgsConstructor;

/**
 * 数据库枚举
 * @Author wzw
 * @Date 2024/5/28 10:39
 * @Version 1.0
 * @Description todo
 */
@AllArgsConstructor
public enum DataSourceEnum {
    BANK1("mysql_bank1"),
    BANK2("mysql_bank2");

    private String dataSourceName;


    public String getDataSourceName() {
        return dataSourceName;
    }

    public void setDataSourceName(String dataSourceName) {
        this.dataSourceName = dataSourceName;
    }
}

表mapper

package com.example.springbootmybatisdiffdatasource.twoway.mapper;

import com.example.springbootmybatisdiffdatasource.entity.DeDuplication;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @Author wzw
 * @Date 2024/5/27 19:43
 * @Version 1.0
 * @Description todo
 */
@Mapper
public interface DeDuplicationMapper {
    /**
     * 查询所有数据
     * @param 
     * @return java.util.List<com.example.springbootmybatisdiffdatasource.entity.DeDuplication>
     * @author: wzw
     * @date: 2024/5/27 19:54
     */
    List<DeDuplication> selectAll();

    /**
     * 批量插入
     * @param list
     * @return java.lang.Integer
     * @author: wzw
     * @date: 2024/5/27 19:57
     */
    Integer batchInsert(@Param("list") List<DeDuplication> list);
}

xml

<?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.springbootmybatisdiffdatasource.twoway.mapper.DeDuplicationMapper">

	<resultMap id="base" type="com.example.springbootmybatisdiffdatasource.entity.DeDuplication">
		<result column="tx_no" property="txNo"/>
		<result column="create_time" property="createTime"/>
	</resultMap>

	<select id="selectAll" resultMap="base">
		select * from de_duplication
	</select>

	<insert id="batchInsert">
		insert into de_duplication(tx_no,create_time)
		values
		<foreach collection="list" item="a" separator=",">
			(
			#{a.txNo},
			#{a.createTime}
			)
		</foreach>
	</insert>

</mapper>

测试

package com.example.springbootmybatisdiffdatasource.test;

import com.example.springbootmybatisdiffdatasource.entity.DeDuplication;
import com.example.springbootmybatisdiffdatasource.twoway.config.DynamicToggleDataSource;
import com.example.springbootmybatisdiffdatasource.twoway.constant.DataSourceEnum;
import com.example.springbootmybatisdiffdatasource.twoway.mapper.DeDuplicationMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

/**
 * 测试类
 * @Author wzw
 * @Date 2024/5/27 20:02
 * @Version 1.0
 * @Description todo
 */
@RestController
public class TestController {
    @Autowired
    private DeDuplicationMapper deDuplicationMapper;

    @GetMapping("test")
    public void test(){
        List<DeDuplication> deDuplications = deDuplicationMapper.selectAll();
        System.out.printf("个数：%d%n",deDuplications.size());
        //  切换数据源
        DynamicToggleDataSource.DataSourceContextHolder.setDataSourceType(DataSourceEnum.BANK2);
        deDuplicationMapper.batchInsert(deDuplications);
        System.out.println("批量插入成功");
    }
}

测试结果跟上面一致，但是还会有一个问题：动态切换数据源在表很多的前提下还是会增大系统开销，浪费系统资源

方式三：使用SqlSessionTemplate指向不同的数据源（也是使用同一个mapper）

项目结构示例

动态数据源配置

package com.example.springbootmybatisdiffdatasource.threeway.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * bank1配置
 * @Author wzw
 * @Date 2024/5/27 19:29
 * @Version 1.0
 * @Description todo
 */
@Configuration
@MapperScan(basePackages = "com.example.springbootmybatisdiffdatasource.threeway.mapper",
                sqlSessionTemplateRef ="bank1SqlSessionTemplate" )
public class Bank1Config {
    /**
     * 本地mysql的bank1库的数据配置
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.bank1")
    public DataSourceProperties bank1SourceProperties() {
        return new DataSourceProperties();
    }
    /**
     * 初始化数据源
     * @return
     */
    @Bean("bank1DataSource")
    public DataSource bank1DataSource() {
        return bank1SourceProperties().initializeDataSourceBuilder().build();
    }

    /**
     * 创建session工厂
     * @param dataSource
     * @return org.apache.ibatis.session.SqlSessionFactory
     * @author: wzw
     * @date: 2024/5/27 19:37
     */
    @Bean("bank1SqlSessionFactory")
    public SqlSessionFactory bank1SqlSessionFactory(@Qualifier("bank1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        //设置数据源
        sqlSessionFactoryBean.setDataSource(dataSource);
        //设置mapper扫描路径
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:/threeway/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * bank1的事务管理器
     * @param dataSource
     * @return org.springframework.transaction.PlatformTransactionManager
     * @author: wzw
     * @date: 2024/5/28 11:55
     */
    @Bean(name = "bank1TransactionManager")
    public PlatformTransactionManager bank1TransactionManager(@Qualifier("bank1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * bank1的sql模板
     * @param sqlSessionFactory
     * @return org.mybatis.spring.SqlSessionTemplate
     * @author: wzw
     * @date: 2024/5/28 11:55
     */
    @Bean(name = "bank1SqlSessionTemplate")
    public SqlSessionTemplate bank1SqlSessionTemplate(@Qualifier("bank1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.example.springbootmybatisdiffdatasource.threeway.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * bank2配置
 * @Author wzw
 * @Date 2024/5/27 19:38
 * @Version 1.0
 * @Description todo
 */
@Configuration
@MapperScan(basePackages = "com.example.springbootmybatisdiffdatasource.threeway.mapper",
        sqlSessionTemplateRef ="bank2SqlSessionTemplate" )
public class Bank2Config {
    /**
     * 本地mysql的bank2库的数据配置
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.bank2")
    public DataSourceProperties bank2SourceProperties() {
        return new DataSourceProperties();
    }
    /**
     * 初始化数据源
     * @return
     */
    @Bean("bank2DataSource")
    public DataSource bank2DataSource() {
        return bank2SourceProperties().initializeDataSourceBuilder().build();
    }

    /**
     * 创建session工厂
     * @param dataSource
     * @return org.apache.ibatis.session.SqlSessionFactory
     * @author: wzw
     * @date: 2024/5/27 19:37
     */
    @Bean("bank2SqlSessionFactory")
    public SqlSessionFactory bank2SqlSessionFactory(@Qualifier("bank2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        //设置数据源
        sqlSessionFactoryBean.setDataSource(dataSource);
        //设置mapper扫描路径
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:/threeway/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * bank2的事务管理器
     * @param dataSource
     * @return org.springframework.transaction.PlatformTransactionManager
     * @author: wzw
     * @date: 2024/5/28 11:55
     */
    @Bean(name = "bank2TransactionManager")
    public PlatformTransactionManager bank2TransactionManager(@Qualifier("bank2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * bank1的sql模板
     * @param sqlSessionFactory
     * @return org.mybatis.spring.SqlSessionTemplate
     * @author: wzw
     * @date: 2024/5/28 11:55
     */
    @Bean(name = "bank2SqlSessionTemplate")
    public SqlSessionTemplate bank2SqlSessionTemplate(@Qualifier("bank2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

表mapper、xml和上面一样

测试

package com.example.springbootmybatisdiffdatasource.test;

import com.example.springbootmybatisdiffdatasource.entity.DeDuplication;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

/**
 * 测试类
 * @Author wzw
 * @Date 2024/5/27 20:02
 * @Version 1.0
 * @Description todo
 */
@RestController
public class TestController {
    @Autowired
    @Qualifier("bank1SqlSessionTemplate")
    private SqlSessionTemplate bank1Template;

    @Autowired
    @Qualifier("bank2SqlSessionTemplate")
    private SqlSessionTemplate bank2Template;

    @GetMapping("test")
    public void test(){
        List<DeDuplication> deDuplications =
                bank1Template.selectList("com.example.springbootmybatisdiffdatasource.threeway.mapper.DeDuplicationMapper.selectAll");
        System.out.printf("个数：%d%n",deDuplications.size());
        bank2Template.insert("com.example.springbootmybatisdiffdatasource.threeway.mapper.DeDuplicationMapper.batchInsert",deDuplications);
        System.out.println("批量插入成功");
    }
}

记录学习日常

关注

9
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
springboot结合mybatis使用多数据源的方式

至此方式一解决了不同库数据同步问题，但是有个问题需要考虑，示例是写一张表，如果写入成百上千张表呢，mapper是不是要double，这里采用动态路由的方式切换数据源，如下。测试结果跟上面一致，但是还会有一个问题：动态切换数据源在表很多的前提下还是会增大系统开销，浪费系统资源。bank2中目前是空的。
复制链接

扫一扫