? 背景? ? ??
????????最近有一個需求,有兩個庫需要做同步數據,一個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 {@Autowiredprivate DeDuplication1Mapper deDuplication1Mapper;@Autowiredprivate 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);//默認查詢數據庫bank1dynamicDataSource.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 {@Overrideprotected 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 {@Autowiredprivate 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("批量插入成功");}
}