springboot 动态切库

最新推荐文章于 2024-05-26 13:06:48 发布

糊口度日的小白

最新推荐文章于 2024-05-26 13:06:48 发布

阅读量614

点赞数

分类专栏： springboot 文章标签： spring boot java

本文链接：https://blog.csdn.net/qq_30641461/article/details/115130382

版权

springboot 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、根据注解进行切库

1.导入依赖

<?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>1.5.17.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.lym</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.29</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mapstruct</groupId>
            <!-- jdk8以下就使用mapstruct -->
            <artifactId>mapstruct-jdk8</artifactId>
            <version>1.2.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct-processor</artifactId>
            <version>1.2.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.编写配置文件

dynamic-routing:
  connections:
    - name: test
      url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&&characterEncoding=utf8&&zeroDateTimeBehavior=convertToNull
      driver: com.mysql.jdbc.Driver
      username: root
      password: root
      maxActive: 500
      minIdle: 5
      maxWait: 60000
    - name: test2
      url: jdbc:mysql://127.0.0.1:3306/test2?useSSL=false&&characterEncoding=utf8&&zeroDateTimeBehavior=convertToNull
      driver: com.mysql.jdbc.Driver
      username: root
      password: root
      maxActive: 500
      minIdle: 5
      maxWait: 60000
    - name: test3
      url: jdbc:mysql://127.0.0.1:3306/test3?useSSL=false&&characterEncoding=utf8&&zeroDateTimeBehavior=convertToNull
      driver: com.mysql.jdbc.Driver
      username: root
      password: root
      maxActive: 500
      minIdle: 5
      maxWait: 60000
spring:
  mvc:
    async:
      request-timeout: 20000

  tomcat:
    max-active: 20
    max-wait: 60000
    time-between-eviction-runs-millis: 60000
    min-evictable-idle-time-millis: 30000
    min-idle: 3
    validation-query: select 'x'
    test-while-idle: true
    testOnBorrow: false
    testOnReturn: false
    connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    
  dbcp2:
    pool-prepared-statements: true #表示在数据库连接的每个connection中可以存储几个该statement对象

3、定义注解

   @Target({ElementType.TYPE,ElementType.METHOD})
	@Retention(RetentionPolicy.RUNTIME)
	@Documented
	public @interface DatasourceChange {
    	String value () default "test";
	}

4、获取配置文件中的多个数据源配置信息

  /**
 * @Author liyueming
 * @CreateTime 2021/3/5
 * @Description  获取配置文件中的多个数据源配置信息
 *   这是方法一 https://www.jb51.net/article/147300.htm
 *   方法二 ：https://blog.csdn.net/xp541130126/article/details/81739760
 */
   @ConfigurationProperties(prefix = "dynamic-routing")
    public class DataSourceConfig {
    private List<DataSourceObject> connections = new ArrayList<>();
    public List<DataSourceObject> getConnections() {
        return connections;
    }
    public void setConnections(List<DataSourceObject> connections) {
        this.connections = connections;
    }
}

5.注册多个数据源到 spring容器中

@Configuration
@EnableConfigurationProperties(DataSourceConfig.class)
public class DataSourcesMapConfig {
    private static final Logger logger = LoggerFactory.getLogger(DataSourcesMapConfig.class);

    @Autowired
	private DataSourceConfig connectionsConfig;
	/**
	 * 注册多个数据源到 spring容器中
	 * @return
	 */
    @Bean
    public Map<Object, Object> createDataSources() {
    	Map<Object,Object> dataSources = new HashMap<>();
    	for(DataSourceObject connection:connectionsConfig.getConnections()) {
    		Map<String,String> map = new HashMap<String,String>();
    		map.put("url", connection.getUrl());
    		map.put("username", connection.getUsername());
    		map.put("password", connection.getPassword());
    		map.put("maxActive", connection.getMaxActive());
			map.put("minIdle", connection.getMinIdle());
			map.put("maxWait", connection.getMaxWait());
//			map.put("defaultAutoCommit", "false");
//			map.put("filters","stat,wall");
//			map.put("connectionProperties","druid.stat.mergeSql=true;druid.stat.slowSqlMillis=2000");
//			map.put("useGlobalDataSourceStat","true");
            logger.info("connection.getName:"+connection.getName());
    		DataSource dataSource;
			try {
				dataSource = DruidDataSourceFactory.createDataSource(map);
				dataSources.put(connection.getName(),dataSource);
			} catch (Exception e) {
				
			}
    		
    	}
        return dataSources;
    }
}

6. 动态路由配置，将数据源添加到路由数据源的目标数据源中，并设置默认数据源

@Configuration
public class DynamicRoutingConfig {

@Autowired
private DataSourcesMapConfig dataSourcesMapConfig;

private Map<Object,Object> map = null;

@Bean
public DynamicRoutingDataSource dynamicRoutingDataSource() {
    this.map  = dataSourcesMapConfig.createDataSources();
    DynamicRoutingDataSource dynamicRoutingDataSource =new DynamicRoutingDataSource();
    dynamicRoutingDataSource.setTargetDataSources(map);
    dynamicRoutingDataSource.setDefaultTargetDataSource(map.get("test"));
    return dynamicRoutingDataSource;
}

public Map<Object, Object> getMap() {
    return this.map;
}

}

7.可以不用设置此处

package com.lym.demo.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;

import javax.sql.DataSource;

/**

@Author liyueming
@CreateTime 2021/3/16
@Description
*/
@Configuration
@EnableConfigurationProperties(DataSourceConfig.class)
public class DataSourceProperty {
private static final Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);

@Autowired
private DynamicRoutingConfig dynamicRoutingConfig;

/**
- 必须将动态数据源添加到sqlSessionFactory中
- @return
- @throws Exception
  /
  @Bean
  public SqlSessionFactory sqlSessionFactory() throws Exception {
  DataSource dataSource = dynamicRoutingConfig.dynamicRoutingDataSource();
  SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
  factoryBean.setDataSource(dataSource);
  // 设置自定义的事务处理器。解决分库切换及事务问题。
  // factoryBean.setTransactionFactory(new MultiDataSourceTransactionFactory());
  //资源路径解析器支持通配符
  ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
  factoryBean.setMapperLocations(resolver.getResources("classpath:/mapper/.xml"));
  return factoryBean.getObject();
  }
@Bean
public SqlSessionTemplate sqlSessionTemplate() throws Exception {
SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory());
logger.info(“获取sqlSessionTemplate对象”);
return template;
}
}

8.设置本地线程变量如果不懂threadlocal的可以先去查阅一些threadlocal的相关资料

package com.lym.demo.threadlocal;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @Author liyueming
 * @CreateTime 2021/3/5
 * @Description
 */
public class DataSourceContextHolder {
    public static final ThreadLocal<String> contextHolder=new ThreadLocal<>();
    private static final Logger logger = LoggerFactory.getLogger(DataSourceContextHolder.class);

    // 设置数据源名
    public static void setDB(String dbType) {
        contextHolder.set(dbType);
        logger.info("数据源名字:"+dbType);
    }

    // 获取数据源名
    public static String getDB() {
        if (contextHolder.get() == null) {
            return "default";
        }
        return (contextHolder.get());
    }

    // 清除数据源名
    public static void clearDB() {
        contextHolder.remove();
    }
}

9.动态切换数据源的主要类

package com.lym.demo.config;
import com.lym.demo.threadlocal.DataSourceContextHolder;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 *动态切换数据源的主要类 （获取数据源的连接）
 */
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
		 return DataSourceContextHolder.getDB();
    }

}

10.定义切面

package com.lym.demo.aspect;

import com.lym.demo.annotion.DatasourceChange;
import com.lym.demo.config.DataSourceConfig;
import com.lym.demo.dto.DataSourceObject;
import com.lym.demo.threadlocal.DataSourceContextHolder;
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.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.MissingServletRequestParameterException;

import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Stack;

/**
 * @Author liyueming
 * @CreateTime 2021/3/5
 * @Description 更改数据源的切面
 */
@Component
@Aspect
public class DataSourceAspect {
    private static final Logger logger = LoggerFactory.getLogger(DataSourceAspect.class);

    @Autowired
    DataSourceConfig dataSourceConfig;
    @Before("@annotation(com.lym.demo.annotion.DatasourceChange)")
    public void beforeDynaminSource(JoinPoint point) throws Exception {
        String beforeDBString = DataSourceContextHolder.getDB();
        Class<?> className = point.getTarget().getClass();
        String methodName = point.getSignature().getName();
        Class<?>[] argClass = ((MethodSignature) point.getSignature()).getParameterTypes();
        Method method = className.getMethod(methodName, argClass);
        if (method.isAnnotationPresent(DatasourceChange.class)) {
            for (Annotation anno : method.getDeclaredAnnotations()) {
                if (anno.annotationType().equals(DatasourceChange.class)) {
                    String annoValue = ((DatasourceChange) anno).value();
                    List<DataSourceObject> connections = dataSourceConfig.getConnections();
                    boolean flag = false;
                    // connections.stream().anyMatch((e) -> busycode.equals(e))
                    for (DataSourceObject connection : connections) {
                        flag = connection.getName().equals(annoValue);
                        if (flag) {
                            break;
                        }
                    }
                    if (!flag) {
                        //throw new CheckRequestParamException(ServiceResultTool.newFailServiceResultAddMessage("无法匹配数据源连接名称"));
                        throw new MissingServletRequestParameterException("无法匹配数据源连接名称","miss");
                    }
                    DataSourceContextHolder.setDB(annoValue);
                    logger.info("Service执行开始切换数据源{}->"+annoValue, 60, "*");
                }
            }
        }
    }

    @After("@annotation(com.lym.demo.annotion.DatasourceChange)")
    public void afterDynaminSource(JoinPoint point) {
        //切换为入参DB
        DataSourceContextHolder.setDB("test");

        logger.info("Service执行结束切换数据源{默认主库}->test", 60, "*");

        //DataSourceContextHolder.clearDB();
    }
}

后续步骤（mapper，controller自己写吧 ）

在这里插入图片描述

二、手动切库的话

只要设置该地方就行
 contextHolder.set(dbType); 当你在执行完的时候别忘了再回到主库

三、动态加载配置中心的mysql密码

1.获取配置中心密码并更新数据源

import cn.picclife.authen.common.config.datasourceconfig.DataSourceMapBean;
import cn.picclife.authen.common.config.datasourceconfig.DynamicRoutingDataSource;
import cn.picclife.authen.common.utils.SpringApplicationUtil;
import com.alibaba.druid.pool.DruidDataSource;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

/**
 * @Author liyueming
 * @CreateTime 2021/3/30
 * @Description  将远程配置中心的参数重新注入到 数据源
 */
@Aspect
@Component
public class ConfigCenterDataSourceAspect {
    private static final Logger logger = LoggerFactory.getLogger(ConfigCenterDataSourceAspect.class);
   /* @Autowired
    DataSourceProperty dataSourceProperty;*/
    @Autowired
    DynamicRoutingDataSource dynamicRoutingDataSource;
    //配置中心的数据库密码密码
    @Value("${mobile.password}")
    private String password ;
    @Autowired
    private SpringApplicationUtil springApplicationUtil;
    @Before("@annotation(cn.picclife.authen.common.annotation.ConfigCenterDataSource)")
    public void beforeDynaminSource(JoinPoint point) throws Exception {

        logger.info("===========配置中心密码+======>"+password);
       //TODO 读取配置中心的数据源
        //通过改注解 来判断是否需要动态获取配置中心的密码
        DataSourceMapBean dataSources=(DataSourceMapBean)springApplicationUtil.getBean("dataSource");
           logger.info("dataSources:"+dataSources.getHashMap().size());
        if(dataSources!=null&&dataSources.getHashMap().size()>0&&dataSources.getHashMap().get("default")!=null){
            DruidDataSource dataSource1=(DruidDataSource)dataSources.getHashMap().get("default");
            //判断远程中心密码是否和本地一致 不一致的话重新注入bean
            if(!StringUtils.isEmpty(password)&&!dataSource1.getPassword().equals(password)){
                //dataSource1.close();
                dataSource1.setPassword(password);
                //重点在于此处 数据源重新加载之后需要 重启
                dataSource1.restart();        
                dataSources.getHashMap().put("default",dataSource1);
                DynamicRoutingDataSource dynamicRoutingDataSource=(DynamicRoutingDataSource)springApplicationUtil.getBean("dynamicRoutingDataSource");
				//更新bean容器中的数据源信息
                dynamicRoutingDataSource.updateDataSource(dataSources.getHashMap());  
            }
        }
    }

}

2.定义一个map容器存放数据源信息

package cn.picclife.authen.common.config.datasourceconfig;

import lombok.Data;

import java.util.HashMap;

/**
 * @Author liyueming
 * @CreateTime 2021/4/2
 * @Description
 */
@Data
public class DataSourceMapBean {
    private HashMap hashMap;
}

3. 修改上面的第五步

如下：

	package cn.picclife.authen.common.config.datasourceconfig;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

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

@Configuration
@EnableConfigurationProperties(DataSourceConfig.class)
public class DataSourcesMapConfig {
    private static final Logger logger = LoggerFactory.getLogger(DataSourcesMapConfig.class);

	@Value("${mobile.password}")
	private String password ;

	@Value("${mobile.type}")
	private String mobileType;
    @Autowired
	private DataSourceConfig connectionsConfig;
	/**
	 * 注册多个数据源到 spring容器中
	 * @return
	 */
    @Bean("dataSource")
    public DataSourceMapBean createDataSources() {
    	//TODO 读取配置中心的数据源
		DataSourceMapBean dataSourceMapBean=new DataSourceMapBean();
		logger.info("数据库 配置中心密码：====>"+password);
    	Map<Object,Object> dataSources = new HashMap<>();
    	for(DataSourceObject connection:connectionsConfig.getConnections()) {
    		Map<String,String> map = new HashMap<String,String>();
    		map.put("url", connection.getUrl());
    		map.put("username", connection.getUsername());
    		map.put("password", connection.getPassword());
    		map.put("maxActive", connection.getMaxActive());
			map.put("minIdle", connection.getMinIdle());
			map.put("maxWait", connection.getMaxWait());
            logger.info("connection.getName:"+connection.getName());
    		DataSource dataSource;
			try {
				dataSource = DruidDataSourceFactory.createDataSource(map);
				dataSources.put(connection.getName(),dataSource);
			} catch (Exception e) {
				
			}
    		finally {
				try {
					//从配置中心读取的数据源
					DruidDataSource dataSource1=(DruidDataSource)dataSources.get("default");
					dataSource1.setPassword(password);
					dataSources.put("default",dataSource1);
					dataSourceMapBean.setHashMap((HashMap) dataSources);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
    	}
        return  dataSourceMapBean;
    }


}

糊口度日的小白

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
springboot 动态切库

一、根据注解进行切库1.导入依赖<?xml version="1.0" encoding="UTF-8"?>4.0.0org.springframework.bootspring-boot-starter-parent1.5.17.RELEASE com.lymdemo0.0.1-SNAPSHOTdemoDemo project for Spring Boot<java.version>1.8</java.version>org.
复制链接

扫一扫