Springboot實現多數據源切換詳情
1. 實現效果
1.1 controller
最終實現效果,在接口上標記上 @Router 注解用來標記當前接口需要根據參數中的某個字段進行數據的切換
@RestController
@RequestMapping("/user")
public class UserController {
@Resource
private UserMapper userMapper;
@GetMapping("/save")
@Router(routingFiled = "id")
@Transactional
public void saveUser(@RequestParam("id") String id){
User user = new User();
user.setAge("123");
user.setId(Long.valueOf(id));
user.setName("zs");
//設置表的后綴名稱,在mybatis執(zhí)行sql時可以獲取
user.setTableSuffix(MultiDataSourceHolder.getTableIndex());
userMapper.insert(user);
}
@GetMapping("/get")
@Router(routingFiled = "id")
public User getUser(@RequestParam("id") String id){
return userMapper.selectByPrimaryKey(Long.valueOf(id),MultiDataSourceHolder.getTableIndex());
}
}1.2 mybatis.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.zhj.multiple.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.zhj.multiple.entity.User">
<!--@mbg.generated-->
<id column="id" jdbcType="BIGINT" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="age" jdbcType="VARCHAR" property="age" />
</resultMap>
<sql id="Base_Column_List">
<!--@mbg.generated-->
id, `name`, age
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
<!--@mbg.generated-->
select
<include refid="Base_Column_List" />
<!--通過${}獲取出表名,為什么不是用#呢?因為表名是在內部進行計算,不用擔心sql注入的問題,而且$進行獲取是直接將表名進行拼接上,不會使用預處理-->
from user${tableName}
where id = #{id,jdbcType=BIGINT}
</select>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.zhj.multiple.entity.User" useGeneratedKeys="true">
<!--@mbg.generated-->
insert into user${tableSuffix} (id ,`name`, age)
values (#{id},#{name,jdbcType=VARCHAR}, #{age,jdbcType=VARCHAR})
</insert>
</mapper>1.3 application.yml
#showSql
logging:
level:
com:
zhj:
mapper : debug
server:
port: 8081
# 配置路由分庫分表的策略
datasource:
stragegy:
dataSourceNum: 2 #庫的個數
tableNum: 2 #表的個數
routingFiled: 'userId' #根據哪個字段來進行分庫分表
tableSuffixStyle: '%04d' #表的索引值 4位補齊 例如:_0003
tableSuffixConnect: '_' #表的連接風格 order_
routingStategy: 'ROUTING_DS_TABLE_STATEGY' #表的策略,啟動時會根據表的策略進行驗證
#配置數據源
multiple:
data0:
user: root
password: root
url: jdbc:mysql://192.168.60.46:3306/multiple-0?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
driver: com.mysql.jdbc.Driver
data1:
user: root
password: root
url: jdbc:mysql://192.168.60.46:3306/multiple-1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
driver: com.mysql.jdbc.Driver1.4 啟動類
@SpringBootApplication
@EnableAspectJAutoProxy
@MapperScan("com.zhj.multiple.mapper")
@EnableTransactionManagement
public class MultipleApplication {
public static void main(String[] args) {
SpringApplication.run(MultipleApplication.class, args);
}
}2. 注解
2.1 @Router
/**
* 路由注解
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface Router {
/**
* 路由字段
*
* @return 默認路由字段是參數中的哪一個
*/
String routingFiled() default MultipleConstant.DEFAULT_ROUTING_FIELD;
}3. 分庫策略
3.1 MultipleConstant
目前有三種分庫的策略:
- 多庫多表
- 多庫單表
- 單庫多表
@Data
public class MultipleConstant {
/**
* 多庫多表策略
*/
public static final String ROUTING_DS_TABLE_STATEGY = "ROUTING_DS_TABLE_STATEGY";
/**
* 多庫單表策略
*/
public static final String ROUTGING_DS_STATEGY = "ROUTGING_DS_STATEGY";
/**
* 單庫多表策略
*/
public static final String ROUTGIN_TABLE_STATEGY = "ROUTGIN_TABLE_STATEGY";
/**
* 默認的路由字段
*/
public static final String DEFAULT_ROUTING_FIELD = "accountId";
}3.2 IRoutingInterface
路由的頂級接口,用于定義一些通用的方法
public interface IRoutingInterface {
/**
* 根據字段key計算出數據庫
* @param routingFiled
* @return
*/
String calDataSourceKey(String routingFiled);
/**
* 獲取路由字段的hashCode
* @param routingFiled
* @return
*/
Integer getRoutingFileHashCode(String routingFiled);
/**
* 計算出表名
* @param routingFiled
* @return
*/
String calTableKey(String routingFiled);
/**
* 計算出表的前綴
* @param tableIndex
* @return
*/
String getFormatTableSuffix(Integer tableIndex);
}3.3 AbstractRouting
@EnableConfigurationProperties({MultipleStategyProperties.class})
@Data
@Slf4j
public abstract class AbstractRouting implements IRoutingInterface, InitializingBean {
@Resource
private MultipleStategyProperties multipleStategyProperties;
@Override
public Integer getRoutingFileHashCode(String routingFiled){
return Math.abs(routingFiled.hashCode());
}
/**
* 獲取表的后綴
* @param tableIndex 表索引
*/
@Override
public String getFormatTableSuffix(Integer tableIndex){
//獲取連接符
String tableSuffixConnect = multipleStategyProperties.getTableSuffixConnect();
//根據配置風格格式化表后綴名稱
String format = String.format(multipleStategyProperties.getTableSuffixStyle(), tableIndex);
return tableSuffixConnect + format;
}
/**
* 工程啟動時,檢驗配置的數據源是否跟策略相似,實現了 InitializingBean 初始化后會執(zhí)行當前方法
*/
@Override
public void afterPropertiesSet(){
switch (multipleStategyProperties.getRoutingStategy()) {
case MultipleConstant.ROUTING_DS_TABLE_STATEGY:
checkRoutingDsTableStategyConfig();
break;
case MultipleConstant.ROUTGING_DS_STATEGY:
checkRoutingDsStategyConfig();
break;
default:
checkRoutingTableStategyConfig();
break;
}
}
/**
* 檢查多庫 多表配置
*/
private void checkRoutingDsTableStategyConfig() {
if(multipleStategyProperties.getTableNum()<=1 ||multipleStategyProperties.getDataSourceNum()<=1){
log.error("你的配置項routingStategy:{}是多庫多表配置,數據庫個數>1," +
"每一個庫中表的個數必須>1,您的配置:數據庫個數:{},表的個數:{}",multipleStategyProperties.getRoutingStategy(),
multipleStategyProperties.getDataSourceNum(),multipleStategyProperties.getTableNum());
throw new RuntimeException();
}
}
/**
* 檢查多庫一表的路由配置項
*/
private void checkRoutingDsStategyConfig() {
if(multipleStategyProperties.getTableNum()!=1 ||multipleStategyProperties.getDataSourceNum()<=1){
log.error("你的配置項routingStategy:{}是多庫一表配置,數據庫個數>1," +
"每一個庫中表的個數必須=1,您的配置:數據庫個數:{},表的個數:{}",multipleStategyProperties.getRoutingStategy(),
multipleStategyProperties.getDataSourceNum(),multipleStategyProperties.getTableNum());
throw new RuntimeException();
}
}
/**
* 檢查一庫多表的路由配置項
*/
private void checkRoutingTableStategyConfig() {
if(multipleStategyProperties.getTableNum()<=1 ||multipleStategyProperties.getDataSourceNum()!=1){
log.error("你的配置項routingStategy:{}是一庫多表配置,數據庫個數=1," +
"每一個庫中表的個數必須>1,您的配置:數據庫個數:{},表的個數:{}",multipleStategyProperties.getRoutingStategy(),
multipleStategyProperties.getDataSourceNum(),multipleStategyProperties.getTableNum());
throw new RuntimeException();
}
}
}3.4 RoutingDsAndTbStrategy
目前實現了一個多庫多表的策略進行配置,其余兩個分庫算法可以自行實現
@Slf4j
public class RoutingDsAndTbStrategy extends AbstractRouting {
/**
* 確定數據源的key
* @param routingFiled
* @return
*/
@Override
public String calDataSourceKey(String routingFiled) {
//計算hash值
Integer routingFileHashCode = getRoutingFileHashCode(routingFiled);
//定位數據源
int dsIndex = routingFileHashCode % getMultipleStategyProperties().getDataSourceNum();
String dataSourceKey = getMultipleStategyProperties().getDataSourceKeysMapping().get(dsIndex);
//將數據源key放入持有器當中
MultiDataSourceHolder.setDataSourceHolder(dataSourceKey);
log.info("根據路由字段:{},值:{},計算出數據庫索引值:{},數據源key的值:{}",getMultipleStategyProperties().getRoutingFiled(),routingFiled,dsIndex,dataSourceKey);
return dataSourceKey;
}
/**
* 計算表的key
* @param routingFiled
* @return
*/
@Override
public String calTableKey(String routingFiled) {
//獲取到當前key的hash
Integer routingFileHashCode = getRoutingFileHashCode(routingFiled);
//通過hash值取模,獲取到對應的索引值
int tbIndex = routingFileHashCode % getMultipleStategyProperties().getTableNum();
//獲取表后綴
String formatTableSuffix = getFormatTableSuffix(tbIndex);
//將表名設置到上下文中,方便后續(xù)同線程獲取到對應的表名
MultiDataSourceHolder.setTableIndexHolder(formatTableSuffix);
return formatTableSuffix;
}
}3.5 RoutingDsStrategy
多庫單表:
public class RoutingDsStrategy extends AbstractRouting {
@Override
public String calDataSourceKey(String routingFiled) {
return null;
}
@Override
public String calTableKey(String routingFiled) {
return null;
}
}3.6 RoutingTbStrategy
單庫多表策略:
public class RoutingTbStrategy extends AbstractRouting {
@Override
public String calDataSourceKey(String routingFiled) {
return null;
}
@Override
public String calTableKey(String routingFiled) {
return null;
}
}4. 配置類
以下兩個配置:
MultipleStategyProperties:用于配置數據庫策略,有多少庫,多少表,以及表名
4.1 MultipleStategyProperties
@ConfigurationProperties(prefix = "datasource.stragegy")
@Data
public class MultipleStategyProperties {
/**
* 默認是一個數據庫 默認一個
*/
private Integer dataSourceNum = 1;
/**
* 每一個庫對應表的個數 默認是一個
*/
private Integer tableNum = 1;
/**
* 路由字段 必須在配置文件中配置(不配置會拋出異常)
*/
private String routingFiled;
/**
* 數據庫的映射關系
*/
private Map<Integer,String> dataSourceKeysMapping;
/**
* 表的后綴連接風格 比如order_
*/
private String tableSuffixConnect="_";
/**
* 表的索引值 格式化為四位 不足左補零 1->0001 然后在根據tableSuffixConnect屬性拼接成
* 成一個完整的表名 比如 order表 所以為1 那么數據庫表明為 order_0001
*/
private String tableSuffixStyle= "%04d";
/**
* 默認的多庫多表策略
*/
private String routingStategy = MultipleConstant.ROUTING_DS_TABLE_STATEGY;
}4.2 MultipleStategyProperties
@Configuration
public class MultipleDataSourceStrategyConfig {
/**
* 當配置文件里面包含某個配置,并且值是多少時生效
*
* @return routing interface
* @since 1.0.0
*/
@Bean
@ConditionalOnProperty(prefix = "datasource.stragegy",name = "routingStategy",havingValue = "ROUTING_DS_TABLE_STATEGY")
public IRoutingInterface routingDsAndTbStrategy(){
return new RoutingDsAndTbStrategy();
}
/**
* Routing ds strategy
*
* @return the routing interface
* @since 1.0.0
*/
@Bean
@ConditionalOnProperty(prefix = "datasource.stragegy",name = "routingStategy",havingValue = "ROUTGING_DS_STATEGY")
public IRoutingInterface routingDsStrategy(){
return new RoutingDsStrategy();
}
/**
* Routing tb strategy
*
* @return the routing interface
* @since 1.0.0
*/
@Bean
@ConditionalOnProperty(prefix = "datasource.stragegy",name = "routingStategy",havingValue = "ROUTGIN_TABLE_STATEGY")
public IRoutingInterface routingTbStrategy(){
return new RoutingTbStrategy();
}
}4.3 MultipleDataSourceStrategyConfig
根據對應的配置創(chuàng)建不同的分庫策略
@Configuration
public class MultipleDataSourceStrategyConfig {
/**
* 當配置文件里面包含某個配置,并且值是多少時生效
*
* @return routing interface
* @since 1.0.0
*/
@Bean
@ConditionalOnProperty(prefix = "datasource.stragegy",name = "routingStategy",havingValue = "ROUTING_DS_TABLE_STATEGY")
public IRoutingInterface routingDsAndTbStrategy(){
return new RoutingDsAndTbStrategy();
}
/**
* Routing ds strategy
*
* @return the routing interface
* @since 1.0.0
*/
@Bean
@ConditionalOnProperty(prefix = "datasource.stragegy",name = "routingStategy",havingValue = "ROUTGING_DS_STATEGY")
public IRoutingInterface routingDsStrategy(){
return new RoutingDsStrategy();
}
/**
* Routing tb strategy
*
* @return the routing interface
* @since 1.0.0
*/
@Bean
@ConditionalOnProperty(prefix = "datasource.stragegy",name = "routingStategy",havingValue = "ROUTGIN_TABLE_STATEGY")
public IRoutingInterface routingTbStrategy(){
return new RoutingTbStrategy();
}
}4.4 MultipleDataSourceConfig
多數據源自動裝配類,其中創(chuàng)建了多個數據源,通過 spring提供的 AbstractRoutingDataSource 類進行數據源的切換
@Configuration
//開啟數據源以及數據分庫策略配置
@EnableConfigurationProperties({MultipleDataSourceProperties.class, MultipleStategyProperties.class})
public class MultipleDataSourceConfig {
@Resource
private MultipleDataSourceProperties multipleDataSourceProperties;
@Resource
private MultipleStategyProperties multipleStategyProperties;
/**
* 配置數據源
* @return
*/
@Bean("data0")
public DataSource dataSource0(){
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername(multipleDataSourceProperties.getData0().getUser());
druidDataSource.setPassword(multipleDataSourceProperties.getData0().getPassword());
druidDataSource.setUrl(multipleDataSourceProperties.getData0().getUrl());
druidDataSource.setDriverClassName(multipleDataSourceProperties.getData0().getDriver());
return druidDataSource;
}
@Bean("data1")
public DataSource dataSource1(){
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername(multipleDataSourceProperties.getData1().getUser());
druidDataSource.setPassword(multipleDataSourceProperties.getData1().getPassword());
druidDataSource.setUrl(multipleDataSourceProperties.getData1().getUrl());
druidDataSource.setDriverClassName(multipleDataSourceProperties.getData1().getDriver());
return druidDataSource;
}
/**
* 設置多數據源
* @param data0
* @param data1
* @return
*/
@Bean
public MultiDataSource multiDataSource(DataSource data0,DataSource data1){
//將多個數據與數據源關聯起來
MultiDataSource multiDataSource = new MultiDataSource();
HashMap<Object, Object> multiMap = new HashMap<>();
multiMap.put("data0",data0);
multiMap.put("data1",data1);
//設置目標數據源
multiDataSource.setTargetDataSources(multiMap);
//設置默認的數據源
multiDataSource.setDefaultTargetDataSource(data0);
//設置數據源名稱的映射
Map<Integer, String> multiMappings = new HashMap<>();
multiMappings.put(0,"data0");
multiMappings.put(1,"data1");
multipleStategyProperties.setDataSourceKeysMapping(multiMappings);
return multiDataSource;
}
/**
* 將多數據源設置進mybatis的工廠類中
* @param multiDataSource
* @return
*/
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("multiDataSource") MultiDataSource multiDataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(multiDataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mybatis/mappers/*.xml"));
sqlSessionFactoryBean.setTypeAliasesPackage("com.zhj.multiple.entity");
return sqlSessionFactoryBean.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
/**
* 將多數據源設置到事務管理器中
*
* @param multiDataSource
* @return
*/
@Bean
public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("multiDataSource") MultiDataSource multiDataSource){
return new DataSourceTransactionManager(multiDataSource);
}
}4.5 MultiDataSource
覆寫 AbstractRoutingDataSource.determineCurrentLookupKey() 的方法,在mybatis中通過 Datasource.getConnection() 會調用 determineCurrentLookupKey() 獲取到對應的數據源,然后通過數據源獲取到連接,其中內部維護了一個 Map 來保存數據源的映射關系
public class MultiDataSource extends AbstractRoutingDataSource {
/**
* 獲取到指定的數據源
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
return MultiDataSourceHolder.getDataSourceKey();
}
}5. 全局上下文
用于保存數據庫、表名,方便后續(xù)使用
5.1 MultiDataSourceHolder
@Data
public class MultiDataSourceHolder {
/**
* 存儲數據源
*/
private static final ThreadLocal<String> dataSourceHolder = new ThreadLocal<>();
/**
* 存儲表的索引
*/
private static final ThreadLocal<String> tableIndexHolder = new ThreadLocal<>();
/**
* Get data source key
*
* @return the string
* @since 1.0.0
*/
public static String getDataSourceKey(){
return dataSourceHolder.get();
}
/**
* Get table index
*
* @return the string
* @since 1.0.0
*/
public static String getTableIndex(){
return tableIndexHolder.get();
}
/**
* Clear data source key
*
* @since 1.0.0
*/
public static void clearDataSourceKey(){
dataSourceHolder.remove();
}
/**
* Clear table index
*
* @since 1.0.0
*/
public static void clearTableIndex(){
tableIndexHolder.remove();
}
/**
* Set data source holder
*
* @param key key
* @since 1.0.0
*/
public static void setDataSourceHolder(String key){
dataSourceHolder.set(key);
}
/**
* Set table index holder
*
* @param key key
* @since 1.0.0
*/
public static void setTableIndexHolder(String key){
tableIndexHolder.set(key);
}
}6. 切面
6.1 RoutingAspect
通過路由切面進行 @Router 注解的處理,提前將數據庫的key以及表名的后綴獲取出來進行存儲
@Aspect
@Component
@Slf4j
public class RoutingAspect {
@Resource
private IRoutingInterface iRoutingInterface;
@Pointcut("@annotation(com.zhj.multiple.annotations.Router)")
public void pointCut(){};
@Before("pointCut()")
public void before(JoinPoint joinPoint) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
long beginTime = System.currentTimeMillis();
//獲取方法調用名稱
Method method = getInvokeMethod(joinPoint);
//獲取方法指定的注解
Router router = method.getAnnotation(Router.class);
//獲取指定的路由key
String routingFiled = router.routingFiled();
if(Objects.nonNull(router)){
boolean havingRoutingField = false;
//獲取到http請求
HttpServletRequest requestAttributes = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
//優(yōu)先獲取@ReqeustParam注解中的路由字段
String routingFieldValue = requestAttributes.getParameter(routingFiled);
if(!StringUtils.isEmpty(routingFieldValue)){
//計算數據庫key
String dbKey = iRoutingInterface.calDataSourceKey(routingFieldValue);
//計算表索引
String tableIndex = iRoutingInterface.calTableKey(routingFieldValue);
log.info("選擇的dbkey是:{},tableKey是:{}",dbKey,tableIndex);
}else {
//獲取方法入參
Object[] args = joinPoint.getArgs();
if(args != null && args.length > 0) {
for(int index = 0; index < args.length; index++) {
//找到參數當中路由字段的值
routingFieldValue = BeanUtils.getProperty(args[index],routingFiled);
if(!StringUtils.isEmpty(routingFieldValue)) {
//計算數據庫key
String dbKey = iRoutingInterface.calDataSourceKey(routingFieldValue);
//計算表索引
String tableIndex = iRoutingInterface.calTableKey(routingFieldValue);
log.info("選擇的dbkey是:{},tableKey是:{}",dbKey,tableIndex);
havingRoutingField = true;
break;
}
}
//判斷入參中沒有路由字段
if(!havingRoutingField) {
log.warn("入參{}中沒有包含路由字段:{}",args,routingFiled);
throw new RuntimeException();
}
}
}
}
}
private Method getInvokeMethod(JoinPoint joinPoint) {
Signature signature = joinPoint.getSignature();
MethodSignature methodSignature = (MethodSignature)signature;
Method targetMethod = methodSignature.getMethod();
return targetMethod;
}
/**
* 清除線程緩存
* @param joinPoint
*/
@After("pointCut()")
public void methodAfter(JoinPoint joinPoint){
MultiDataSourceHolder.clearDataSourceKey();
MultiDataSourceHolder.clearTableIndex();
}
}到此這篇關于Springboot實現多數據源切換詳情的文章就介紹到這了,更多相關Springboot多數據源切換內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
Java?Stream實現多字段分組groupingBy操作詳解
Stream是Java8的一個新特性,主要用戶集合數據的處理,如排序、過濾、去重等等功能,本文就來講講如何利用Stream實現比較優(yōu)雅的按多字段進行分組groupingBy吧2023-06-06
Java中使用StackWalker和Stream API進行堆棧遍歷
StackWalking API是添加到Java中最酷的(并且對大多數開發(fā)人員來說完全不切實際,一般不會用,除非深層跟蹤調優(yōu))的功能之一。在這篇簡短的文章中,我們將看到它是什么以及使用它有多么容易,很快的認識它2018-09-09
利用java和sqlserver建立簡易圖書管理系統(tǒng)的完整步驟
圖書館管理系統(tǒng)是圖書館管理工作中不可缺少的部分,它對于圖書館的管理者和使用者都非常重要,下面這篇文章主要給大家介紹了關于利用java和sqlserver建立簡易圖書管理系統(tǒng)的完整步驟,需要的朋友可以參考下2022-06-06

