1. 前言
最近做项目碰到了一个需要连4个不同数据库的需求,其中db1、db2表结构都不相同;另外两个数据库same_db_private、same_db_public表结构完全相同,一个对内一个对外,只是从物理上隔离了数据而已。
所以打算通过静态配置包路径的方式来实现db1、db2的操作,并且通过扩展Spring的AbstractRoutingDataSource的方式来实现same_db_private、same_db_public的动态切换数据源。
2. 数据准备
-
db1创建表并准备数据
create table goods_info ( id bigint auto_increment comment '主键' primary key, name varchar(50) not null )collate=utf8mb4_bin; insert into db1.goods_info (id, name) values (1, '商品1'), (2, '商品2'), (3, '商品3');
-
db1创建表并准备数据
create table user_info ( id bigint auto_increment comment '主键' primary key, name varchar(50) not null )collate=utf8mb4_bin; insert into db2.user_info (id, name) values (1, '用户1'), (2, '用户2'), (3, '用户3');
-
same_db_private创建表并准备数据
create table brand_info ( id bigint auto_increment comment '主键' primary key, name varchar(50) not null )collate=utf8mb4_bin; insert into brand_info (id, name) values (1, '内部品牌1');
-
same_db_public创建表并准备数据
create table brand_info ( id bigint auto_increment comment '主键' primary key, name varchar(50) not null )collate=utf8mb4_bin; insert into brand_info (id, name) values (1, '外部品牌1');
3. 代码细节
3.1 工程目录
3.2 配置文件
spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db1.username=root
spring.datasource.db1.password=xxxxxx
spring.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1
spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db2.username=root
spring.datasource.db2.password=xxxxxx
spring.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/db2
spring.datasource.same-db-private.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.same-db-private.username=root
spring.datasource.same-db-private.password=xxxxxx
spring.datasource.same-db-private.jdbc-url=jdbc:mysql://localhost:3306/same_db_private
spring.datasource.same-db-public.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.same-db-public.username=root
spring.datasource.same-db-public.password=xxxxxx
spring.datasource.same-db-public.jdbc-url=jdbc:mysql://localhost:3306/same_db_public
3.3 静态配置数据源
在config中创建db1和db2的静态数据源配置
package com.aresbf.multi.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.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.aresbf.multi.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSource1 {
/**
* 配置db1数据库
*
* @return
*/
@Bean(name = "db1Datasource")
@ConfigurationProperties(prefix = "spring.datasource.db1")
public DataSource testDatasource() {
return DataSourceBuilder.create().build();
}
/**
* 创建SqlSessionFactory
*
* @param dataSource
* @return
* @throws Exception
*/
@Bean(name = "db1SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("db1Datasource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return bean.getObject();
}
/**
* 配置事务管理
*
* @param dataSource
* @return
*/
@Bean(name = "db1TransactionManager")
public DataSourceTransactionManager testTransactionManager(
@Qualifier("db1Datasource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "db1SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db1SqlSessionFactory")
SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
package com.aresbf.multi.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.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.aresbf.multi.mapper.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSource2 {
/**
* 配置db2数据库
*
* @return
*/
@Bean(name = "db2Datasource")
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource testDatasource() {
return DataSourceBuilder.create().build();
}
/**
* 创建SqlSessionFactory
*
* @param dataSource
* @return
* @throws Exception
*/
@Bean(name = "db2SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("db2Datasource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return bean.getObject();
}
/**
* 配置事务管理
*
* @param dataSource
* @return
*/
@Bean(name = "db2TransactionManager")
public DataSourceTransactionManager testTransactionManager(
@Qualifier("db2Datasource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "db2SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db2SqlSessionFactory")
SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
3.4 动态切换数据源
-
创建动态数据源配置
package com.aresbf.multi.config; import com.aresbf.multi.dynamicdatasource.DataSourceRouting; import lombok.extern.slf4j.Slf4j; 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.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.datasource.DataSourceTransactionManager; import javax.sql.DataSource; /** * @description:sameDb动态数据源 * @author: aresbf * @createDate: 2022/10/21 */ @Configuration @Slf4j @MapperScan(basePackages = "com.aresbf.multi.mapper.same_db", sqlSessionTemplateRef = "sameDbDynamicSqlSessionTemplate") public class SameDbDynamicDataSourceConfig { /** * 自定义动态datasource * * @param sameDbPrivateDataSource 对内datasource * @param sameDbPublicDataSource 对外datasource * @return DataSource */ @Bean(name = "sameDbDynamicDataSource") @Primary public DataSource dataSource(@Qualifier("sameDbPrivateDataSource") DataSource sameDbPrivateDataSource, @Qualifier("sameDbPublicDataSource") DataSource sameDbPublicDataSource) { DataSourceRouting routingDataSource = new DataSourceRouting(); routingDataSource.initDatasource(sameDbPrivateDataSource, sameDbPublicDataSource); return routingDataSource; } /** * 对内datasource * * @return DataSource */ @Bean(name = "sameDbPrivateDataSource") @ConfigurationProperties(prefix = "spring.datasource.same-db-private") public DataSource sameDbPrivateDataSource() { return DataSourceBuilder.create().build(); } /** * 对外datasource * * @return DataSource */ @Bean(name = "sameDbPublicDataSource") @ConfigurationProperties(prefix = "spring.datasource.same-db-public") public DataSource sameDbPublicDataSource() { return DataSourceBuilder.create().build(); } /** * 自定义SqlSessionFactory * * @param dataSource 自定义datasource * @return SqlSessionFactory * @throws Exception */ @Bean(name = "sameDbDynamicSqlSessionFactory") public SqlSessionFactory customSqlSessionFactory(@Qualifier("sameDbDynamicDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); //实现数据库下划线字段到POJO类驼峰形式的自动映射 bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true); return bean.getObject(); } /** * 自定义DataSourceTransactionManager * * @param dataSource 自定义datasource * @return DataSourceTransactionManager */ @Bean(name = "sameDbDynamicTransactionManager") @Primary public DataSourceTransactionManager customTransactionManager(@Qualifier("sameDbDynamicDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } /** * 自定义SqlSessionTemplate * * @param sqlSessionFactory 自定义SqlSessionFactory * @return SqlSessionTemplate */ @Bean(name = "sameDbDynamicSqlSessionTemplate") @Primary public SqlSessionTemplate customSqlSessionTemplate(@Qualifier("sameDbDynamicSqlSessionFactory") SqlSessionFactory sqlSessionFactory) { return new SqlSessionTemplate(sqlSessionFactory); } }
-
创建区分动态数据源枚举项
package com.aresbf.multi.dynamicdatasource; import lombok.Getter; /** * @description: 动态数据源枚举 * @author: aresbf * @createDate: 2022/10/26 */ public enum DynamicDataSourceEnum { /** * 对内系统数据库 */ SAME_DB_PRIVATE("SAME_DB_PRIVATE", "对内系统数据库"), /** * 对外系统数据库 */ SAME_DB_PUBLIC("SAME_DB_PUBLIC", "对外系统数据库"), ; /** * 数据源code */ @Getter private final String dataSource; /** * 描述 */ private final String description; /** * 构造器 * * @param dataSource 数据源标识 * @param description 描述 */ DynamicDataSourceEnum(String dataSource, String description) { this.dataSource = dataSource; this.description = description; } }
-
动态数据源切换上下文
package com.aresbf.multi.dynamicdatasource; import lombok.extern.slf4j.Slf4j; /** * @description: 动态数据源切换用上下文 * @author: aresbf * @createDate: 2022/10/26 */ @Slf4j public class DataSourceContextHolder { /** * 用于存在数据源切换标识 */ private static ThreadLocal<DynamicDataSourceEnum> datasourceContext = new ThreadLocal<>(); /** * 切换数据源 * * @param dynamicDataSourceEnum 要切换的数据源标识 */ public static void switchDataSource(DynamicDataSourceEnum dynamicDataSourceEnum) { log.debug("switchDataSource: {}", dynamicDataSourceEnum.getDataSource()); datasourceContext.set(dynamicDataSourceEnum); } /** * 获取当前的数据源标识 * * @return */ public static DynamicDataSourceEnum getDataSource() { return datasourceContext.get(); } /** * 清理上下文中的数据源标识 */ public static void clear() { datasourceContext.remove(); } }
-
动态路由
package com.aresbf.multi.dynamicdatasource; import lombok.extern.slf4j.Slf4j; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; /** * @description:动态数据源路由 * @author: aresbf * @createDate: 2022/10/26 */ @Slf4j public class DataSourceRouting extends AbstractRoutingDataSource { /** * 决定使用哪个数据源标识 * * @return 数据源标识 */ @Override protected Object determineCurrentLookupKey() { DynamicDataSourceEnum dynamicDataSourceEnum = DataSourceContextHolder.getDataSource(); //如果没有设置数据源标识,默认使用对内数据源标识 if (dynamicDataSourceEnum == null) { dynamicDataSourceEnum = DynamicDataSourceEnum.SAME_DB_PRIVATE; } log.debug("use{}", dynamicDataSourceEnum.getDataSource()); return dynamicDataSourceEnum; } /** * 初始化数据源列表 * * @param sameDbPrivate 对内数据源 * @param sameDbPublic 对外数据源 */ public void initDatasource(DataSource sameDbPrivate, DataSource sameDbPublic) { Map<Object, Object> dataSourceMap = new HashMap<>(); dataSourceMap.put(DynamicDataSourceEnum.SAME_DB_PRIVATE, sameDbPrivate); dataSourceMap.put(DynamicDataSourceEnum.SAME_DB_PUBLIC, sameDbPublic); this.setTargetDataSources(dataSourceMap); this.setDefaultTargetDataSource(sameDbPrivate); } }
-
自定义动态数据源注解
package com.aresbf.multi.dynamicdatasource; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * @description:自定义动态数据源注解 * @author: aresbf * @createDate: 2022/10/26 */ @Target({ElementType.TYPE, ElementType.METHOD}) @Retention(RetentionPolicy.RUNTIME) public @interface SameDbDynamicDataSource { /** * 数据源标识 * * @return DynamicDataSourceEnum */ DynamicDataSourceEnum name() default DynamicDataSourceEnum.SAME_DB_PRIVATE; }
-
动态数据源拦截文章来源:https://www.toymoban.com/news/detail-700221.html
package com.aresbf.multi.dynamicdatasource; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.annotation.After; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.aspectj.lang.annotation.Pointcut; import org.aspectj.lang.reflect.MethodSignature; import org.springframework.stereotype.Component; import java.lang.reflect.Method; /** * @description:动态数据源拦截 * @author: aresbf * @createDate: 2022/10/26 */ @Aspect @Component public class HandleDatasourceAspect { /** * 所有标识sameDbDynamicDataSource注解的类和方法 */ @Pointcut("@annotation(com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource)||@within(com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource)") public void pointcut() { } /** * 方法执行前 * * @param joinPoint 拦截点 */ @Before("pointcut()") public void beforeExecute(JoinPoint joinPoint) { Method method = ((MethodSignature) joinPoint.getSignature()).getMethod(); SameDbDynamicDataSource annotation = method.getAnnotation(SameDbDynamicDataSource.class); if (null == annotation) { annotation = joinPoint.getTarget().getClass().getAnnotation(SameDbDynamicDataSource.class); } if (null != annotation) { // 切换数据源 DataSourceContextHolder.switchDataSource(annotation.name()); } } /** * 方法执行后 */ @After("pointcut()") public void afterExecute() { DataSourceContextHolder.clear(); } }
3.5 编写测试类
通过SameDbDynamicDataSource动态指定需要访问的数据源,避免相同的mapper代码重复写文章来源地址https://www.toymoban.com/news/detail-700221.html
package com.aresbf.multi.controller;
import com.aresbf.multi.dynamicdatasource.DynamicDataSourceEnum;
import com.aresbf.multi.dynamicdatasource.SameDbDynamicDataSource;
import com.aresbf.multi.entity.BrandInfoPO;
import com.aresbf.multi.entity.GoodsInfoPO;
import com.aresbf.multi.entity.UserInfoPO;
import com.aresbf.multi.service.BrandInfoService;
import com.aresbf.multi.service.GoodsInfoService;
import com.aresbf.multi.service.UserInfoService;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.List;
/**
* @description: 测试
* @author: aresbf
* @createDate: 2022/10/24
*/
@RestController
@RequestMapping
@Slf4j
public class TestController {
@Autowired
private GoodsInfoService goodsInfoService;
@Autowired
private UserInfoService userInfoService;
@Autowired
private BrandInfoService brandInfoService;
/**
* 查内部
*
* @return ok
*/
@GetMapping("/queryPrivate")
@SameDbDynamicDataSource(name = DynamicDataSourceEnum.SAME_DB_PRIVATE)
public String queryPrivate() throws JsonProcessingException {
List<GoodsInfoPO> goodsInfoPOS = goodsInfoService.queryAll();
List<UserInfoPO> userInfoPOS = userInfoService.queryAll();
List<BrandInfoPO> brandInfoPOS = brandInfoService.queryAll();
HashMap<String, List> resultMap = new HashMap<>();
resultMap.put("goodsInfo", goodsInfoPOS);
resultMap.put("userInfo", userInfoPOS);
resultMap.put("brandInfo", brandInfoPOS);
ObjectMapper objectMapper = new ObjectMapper();
return objectMapper.writeValueAsString(resultMap);
}
/**
* 查外部
*
* @return ok
*/
@GetMapping("/queryPublic")
@SameDbDynamicDataSource(name = DynamicDataSourceEnum.SAME_DB_PUBLIC)
public String queryPublic() throws JsonProcessingException {
List<GoodsInfoPO> goodsInfoPOS = goodsInfoService.queryAll();
List<UserInfoPO> userInfoPOS = userInfoService.queryAll();
List<BrandInfoPO> brandInfoPOS = brandInfoService.queryAll();
HashMap<String, List> resultMap = new HashMap<>();
resultMap.put("goodsInfo", goodsInfoPOS);
resultMap.put("userInfo", userInfoPOS);
resultMap.put("brandInfo", brandInfoPOS);
ObjectMapper objectMapper = new ObjectMapper();
return objectMapper.writeValueAsString(resultMap);
}
}
3.6 测试结果
3.6.1 请求http://localhost:8080/queryPublic
{"userInfo":[{"id":1,"name":"用户1"},{"id":2,"name":"用户2"},{"id":3,"name":"用户3"}],"brandInfo":[{"id":1,"name":"外部品牌1"}],"goodsInfo":[{"id":1,"name":"商品1"},{"id":2,"name":"商品2"},{"id":3,"name":"商品3"}]}
3.6.2 请求http://localhost:8080/queryPrivate
{"userInfo":[{"id":1,"name":"用户1"},{"id":2,"name":"用户2"},{"id":3,"name":"用户3"}],"brandInfo":[{"id":1,"name":"内部品牌1"}],"goodsInfo":[{"id":1,"name":"商品1"},{"id":2,"name":"商品2"},{"id":3,"name":"商品3"}]}
到了这里,关于springboot+mybatis实现多数据源的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!