SpringBoot DynamicDataSource 继承AbstractRoutingDataSource动态设置数据源 DruidDataSource

太过爱你忘了你带给我的痛 2023-10-04 12:08 57阅读 0赞

### SpringBoot 继承AbstractRoutingDataSource动态设置数据源 ###

**一、默认数据源**

Yml配置文件：

server:
      port: 8887
    
    spring:
      application:
        name: zhx-moon
    
      datasource:
        username: 用户名
        password: 数据库密码
        url: jdbc:postgresql://192.168.31.51:5432/数据库名称
        driverClassName: org.postgresql.Driver #com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        # 下面为连接池的补充设置，应用到上面所有数据源中
        # 初始化大小，最小，最大
        initial-size: 5
        min-idle: 5
        max-active: 20
        # 配置获取连接等待超时的时间
        max-wait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        time-between-eviction-runs-millis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        min-evictable-idle-time-millis: 300000
        validation-query: select version()
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        pool-prepared-statements: true
        #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，wall用于防火墙
        max-pool-prepared-statement-per-connection-size: 20
        filters: stat,wall
        use-global-data-source-stat: true
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    
    #mybatis配置
    mybatis:
      mapper-locations: classpath:/mapper/*/*.xml  #修改为对应的mapper文件路径
      #配置别名
      type-aliases-package: com.demo.moon.model
      #驼峰命名
      configuration:
        map-underscore-to-camel-case: true
    
    #打印sql
    logging:
      level:
        com.demo.moon: debug

默认初始化数据库：

package com.atguigu.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.*;
    import org.springframework.stereotype.Component;
    
    import javax.sql.DataSource;
    import java.sql.SQLException;
    
    /**
     * DRUID 数据库连接池类
     * @author  
     * @date 2020/11/19 22:19
     * @version V1.0.0
     */
    @Configuration
    @Slf4j
    @PropertySource("classpath:application.yml")
    public class DruidConfig {
        
    
        private static final String DB_PREFIX = "spring.datasource";
    
    	/**
         * 数据源动态初始化的管理类
         * @return
         */
        @Bean(name = "dynamicDataSource")
        @Primary
        @DependsOn({
        "springUtils", "defaultDataSource"})
        public DynamicDataSource dataSource() {
        
            DynamicDataSource dynamicDataSource = new DynamicDataSource();
            dynamicDataSource.setTargetDataSources(DynamicDataSource.dataSourcesMap);
            return dynamicDataSource;
        }
    
        @Component
        @ConfigurationProperties(prefix = DB_PREFIX)
        static class IDataSourceProperties {
        
            private String url;
            private String username;
            private String password;
            private String driverClassName;
            private int initialSize;
            private int minIdle;
            private int maxActive;
            private int maxWait;
            private int timeBetweenEvictionRunsMillis;
            private int minEvictableIdleTimeMillis;
            private String validationQuery;
            private boolean testWhileIdle;
            private boolean testOnBorrow;
            private boolean testOnReturn;
            private boolean poolPreparedStatements;
            private int maxPoolPreparedStatementPerConnectionSize;
            private String filters;
            private String connectionProperties;
    
            @Bean     //声明其为Bean实例
            //@Primary  //在同样的DataSource中，首先使用被标注的DataSource
            public DataSource defaultDataSource() {
        
                DruidDataSource datasource = new DruidDataSource();
                datasource.setUrl(url);
                datasource.setUsername(username);
                datasource.setPassword("passwd");
                datasource.setDriverClassName(driverClassName);
    
                //configuration
                datasource.setInitialSize(initialSize);
                datasource.setMinIdle(minIdle);
                datasource.setMaxActive(maxActive);
                datasource.setMaxWait(maxWait);
                datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
                datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
                datasource.setValidationQuery(validationQuery);
                datasource.setTestWhileIdle(testWhileIdle);
                datasource.setTestOnBorrow(testOnBorrow);
                datasource.setTestOnReturn(testOnReturn);
                datasource.setPoolPreparedStatements(poolPreparedStatements);
                datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
                try {
        
                    datasource.setFilters(filters);
                } catch (SQLException e) {
        
                    log.error("druid configuration initialization filter: " , e);
                }
                datasource.setConnectionProperties(connectionProperties);
                try {
        
                    datasource.init();
                } catch (SQLException throwables) {
        
                    throwables.printStackTrace();
                }
                return datasource;
            }
    
            public String getUrl() {
        
                return url;
            }
    
            public void setUrl(String url) {
        
                this.url = url;
            }
    
            public String getUsername() {
        
                return username;
            }
    
            public void setUsername(String username) {
        
                this.username = username;
            }
    
            public String getPassword() {
        
                return password;
            }
    
            public void setPassword(String password) {
        
                this.password = password;
            }
    
            public String getDriverClassName() {
        
                return driverClassName;
            }
    
            public void setDriverClassName(String driverClassName) {
        
                this.driverClassName = driverClassName;
            }
    
            public int getInitialSize() {
        
                return initialSize;
            }
    
            public void setInitialSize(int initialSize) {
        
                this.initialSize = initialSize;
            }
    
            public int getMinIdle() {
        
                return minIdle;
            }
    
            public void setMinIdle(int minIdle) {
        
                this.minIdle = minIdle;
            }
    
            public int getMaxActive() {
        
                return maxActive;
            }
    
            public void setMaxActive(int maxActive) {
        
                this.maxActive = maxActive;
            }
    
            public int getMaxWait() {
        
                return maxWait;
            }
    
            public void setMaxWait(int maxWait) {
        
                this.maxWait = maxWait;
            }
    
            public int getTimeBetweenEvictionRunsMillis() {
        
                return timeBetweenEvictionRunsMillis;
            }
    
            public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        
                this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
            }
    
            public int getMinEvictableIdleTimeMillis() {
        
                return minEvictableIdleTimeMillis;
            }
    
            public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        
                this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
            }
    
            public String getValidationQuery() {
        
                return validationQuery;
            }
    
            public void setValidationQuery(String validationQuery) {
        
                this.validationQuery = validationQuery;
            }
    
            public boolean isTestWhileIdle() {
        
                return testWhileIdle;
            }
    
            public void setTestWhileIdle(boolean testWhileIdle) {
        
                this.testWhileIdle = testWhileIdle;
            }
    
            public boolean isTestOnBorrow() {
        
                return testOnBorrow;
            }
    
            public void setTestOnBorrow(boolean testOnBorrow) {
        
                this.testOnBorrow = testOnBorrow;
            }
    
            public boolean isTestOnReturn() {
        
                return testOnReturn;
            }
    
            public void setTestOnReturn(boolean testOnReturn) {
        
                this.testOnReturn = testOnReturn;
            }
    
            public boolean isPoolPreparedStatements() {
        
                return poolPreparedStatements;
            }
    
            public void setPoolPreparedStatements(boolean poolPreparedStatements) {
        
                this.poolPreparedStatements = poolPreparedStatements;
            }
    
            public int getMaxPoolPreparedStatementPerConnectionSize() {
        
                return maxPoolPreparedStatementPerConnectionSize;
            }
    
            public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
        
                this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
            }
    
            public String getFilters() {
        
                return filters;
            }
    
            public void setFilters(String filters) {
        
                this.filters = filters;
            }
    
            public String getConnectionProperties() {
        
                return connectionProperties;
            }
    
            public void setConnectionProperties(String connectionProperties) {
        
                this.connectionProperties = connectionProperties;
            }
    
            @Bean
            public ServletRegistrationBean<StatViewServlet> druidServlet() {
        // 主要实现web监控的配置处理
                ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(
                        new StatViewServlet(), "/druid/*");//表示进行druid监控的配置处理操作
                servletRegistrationBean.addInitParameter("allow", "127.0.0.1,192.168.0.12");//白名单
                servletRegistrationBean.addInitParameter("deny", "129.168.1.12");//黑名单
                servletRegistrationBean.addInitParameter("loginUsername", "root");//用户名
                servletRegistrationBean.addInitParameter("loginPassword", "123456");//密码
                servletRegistrationBean.addInitParameter("resetEnable", "false");//是否可以重置数据源
                return servletRegistrationBean;
    
            }
    
            @Bean    //监控
            public FilterRegistrationBean<WebStatFilter> filterRegistrationBean(){
        
                FilterRegistrationBean<WebStatFilter> filterRegistrationBean=new FilterRegistrationBean<>();
                filterRegistrationBean.setFilter(new WebStatFilter());
                filterRegistrationBean.addUrlPatterns("/*");//所有请求进行监控处理
                filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.css,/druid/*");//排除
                return filterRegistrationBean;
            }
        }
    }

**二、数据源管理类DynamicDataSource**

获取指定名称Bean的工具类：

package com.atguigu.util;
    
    import lombok.Getter;
    import org.springframework.beans.BeansException;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.ApplicationContextAware;
    import org.springframework.stereotype.Component;
    
    /**
     * Description: Spring工具类
     *
     * @Author: 
     * @Date: 2021-04-27 23:51
     * @version: V1.0.0
     */
    @Component
    public final class SpringUtils implements ApplicationContextAware {
        
    
        @Getter
        private static ApplicationContext applicationContext;
    
        @Override
        public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        
            if (SpringUtils.applicationContext == null) {
        
                SpringUtils.applicationContext = applicationContext;
            }
        }
    
        public static Object getBean(String name) {
        
            return SpringUtils.applicationContext.getBean(name);
        }
    }

管理类：

package com.atguigu.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    import com.atguigu.util.SpringUtils;
    import java.util.Map;
    import java.util.concurrent.ConcurrentHashMap;
    
    /**
     * Description: 动态数据源
     *
     * @Author: zhx & moon hongxu_1234@163.com
     * @Date: 2021-04-27 23:49
     * @version: V1.0.0
     */
    public class DynamicDataSource extends AbstractRoutingDataSource {
        
    
        /**
         * 本地线程变量，保存数据源标识，避免多线程操作数据源时互相干扰
         */
        private static final ThreadLocal<String> dataSourceKey = ThreadLocal.withInitial(() -> "defaultDataSource");
    
        /**
         * 设置默认数据源
         */
        public static Map<Object, Object> dataSourcesMap = new ConcurrentHashMap<>(10);
    
        static {
        
            dataSourcesMap.put("defaultDataSource", SpringUtils.getBean("defaultDataSource"));
        }
    
        /**
         * 获取需要使用的DataSource的key值，根据key从resolvedDataSources这个map里取出对应的DataSource
         * @return
         */
        @Override
        protected Object determineCurrentLookupKey() {
        
            return DynamicDataSource.dataSourceKey.get();
        }
    
        /**
         * 设置动态数据源
         * @param dataSource
         */
        public static void setDataSource(String dataSource) {
        
            DynamicDataSource.dataSourceKey.set(dataSource);
            DynamicDataSource dynamicDataSource = (DynamicDataSource) SpringUtils.getBean("dynamicDataSource");
            dynamicDataSource.afterPropertiesSet();
        }
    
        /**
         * 恢复默认数据源
         */
        public static void clear() {
        
            DynamicDataSource.dataSourceKey.remove();
        }
    
        /**
         * 销毁旧的数据源
         */
        public static void close() {
        
    
            DynamicDataSource dynamicDataSource = (DynamicDataSource) SpringUtils.getBean("dynamicDataSource");
            DruidDataSource lastDruidDataSource = (DruidDataSource)dynamicDataSource.determineTargetDataSource();
            lastDruidDataSource.close();
        }
    }

**三、关于使用**

保持单一数据源，接口直接切换

package com.atguigu.controller;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.atguigu.config.DynamicDataSource;
    import com.atguigu.entity.UserEntity;
    import com.atguigu.mapper.UserMapper;
    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.ResponseBody;
    import org.springframework.web.bind.annotation.RestController;
    
    /**
     * @author Administrator
     */
    @RestController
    public class HelloController {
        
    
        @Autowired
        UserMapper userMapper;
    
        @GetMapping("/dataSource")
        public void dataSource(int type){
        
            //销毁上一个数据源
            DynamicDataSource.close();
            if(type==0){
        
                DruidDataSource druidDataSource = new DruidDataSource();
                druidDataSource.setUrl("jdbc:postgresql://192.168.31.51:5432/database1");
                druidDataSource.setUsername("postgres");
                druidDataSource.setPassword("passwd");
                DynamicDataSource.clear();
                DynamicDataSource.dataSourcesMap.put("defaultDataSource", druidDataSource);
                DynamicDataSource.setDataSource("defaultDataSource");
                UserEntity userEntity = userMapper.selectUser();
                System.out.println(userEntity.toString());
            }else {
        
                DruidDataSource druidDataSource = new DruidDataSource();
                druidDataSource.setUrl("jdbc:postgresql://192.168.31.51:5432/database2");
                druidDataSource.setUsername("postgres");
                druidDataSource.setPassword("passwd");
                DynamicDataSource.clear();
                DynamicDataSource.dataSourcesMap.put("defaultDataSource", druidDataSource);
                DynamicDataSource.setDataSource("defaultDataSource");
                UserEntity userEntity = userMapper.selectUser();
                System.out.println(userEntity.toString());
            }
        }
    }

多数据源实时切换，可通过切面实现，进行数据源切换管理

请自行实现