druid 手动指定数据源_Springboot2.1.9+druid+mybatits（不重启项目动态添加mysql和oracle数据源）和数据监控...

最新推荐文章于 2024-04-25 21:10:41 发布

weixin_39614262

最新推荐文章于 2024-04-25 21:10:41 发布

阅读量271

点赞数

文章标签： druid 手动指定数据源 druid监控页面 jsp 动态添加一行数据

一、前言

最近公司来了一个项目，在理需求和编写接口文档的时候有一个比较有意思的需求：项目启动时不加载数据库，然后管理页面需要提供一个接口，用来配置数据库，配置成功后才连接数据库。于是我做了以下的尝试。

二、技术预研

1.我首先想到的是：不配置url

2019-11-22 16:16:27.268 ERROR 90476 --- [           main] o.s.b.d.LoggingFailureAnalysisReporter   : 

***************************
APPLICATION FAILED TO START
***************************

Description:

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.

Reason: Failed to determine a suitable driver class

这里表示如果需要spring容器管理dataSource就必须在配置文件中配置一个数据源，但是与我的需求不符合，失败。

2.然后我就想如果我排除spring自动配置数据源

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class,
                                  DataSourceAutoConfiguration.class})

但是这里也存在一个问题，通过自动装配实现的mapper无法被识别和实例化，需要自己全部接管所有的sqlBeanFactory，这简直是一个灾难，所以这个宣告失败。

3.使用sqlite数据库当做默认数据库，让springboot帮我自动装配，然后调用接口的时候把这个bean从容器中移除

sqlite是一个文件，可以放在项目中，不需要开启服务，连接就能用，很方便，这个宣告成功，实现代码如下。

三、实现思路

在启动方法处获取spring上下文
注入到SpringContextUtil中
通过@Bean注入MyDynamicDataSource配置到容器中，并设置默认的数据源
重写AbstractRoutingDataSource类的determineCurrentLookupKey方法，实现数据源的切换
ThreadLocal 保证当前线程安全的前提下设置当前线程的数据源

四、代码实现

配置文件配置

server.port=8081
server.tomcat.uri-encoding=utf-8
#指定连接地址和数据库
spring.datasource.url=jdbc:sqlite:classpath:static/defaultDataBase/default.db
spring.datasource.driver-class-name=org.sqlite.JDBC
#指定数据源为druid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource 

# 如果没有进行第三步3的配置，下面的属性是不会生效的
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=6000
spring.datasource.timeBetweenEvictionRunsMillis=6000
spring.datasource.minEvictableIdleTimeMillis=30000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
#filters=stat,wall,log4j
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.useGlobalDataSourceStat=true
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

# 指定全局配置文件的位置
mybatis.config-location=classpath:mybatis-config.xml
# 指定sql映射文件的位置
mybatis.mapper-locations=classpath:mappers/*.xml

2.pom引入

<?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>
    <groupId>com.infosec</groupId>
    <artifactId>sra</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>SRA</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.9.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.51</version>
        </dependency>

        <!--aop-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

        <!-- swagger -->
        <!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger2 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>com.gizwits</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.7.0</version>
        </dependency>

        <!-- 阿里连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <!--mappers-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--ojdbc7驱动 -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.1.0.2.2</version>
            <scope>system</scope>
            <systemPath>${pom.basedir}/src/main/resources/static/lib/ojdbc7-12.1.0.2.jar
            </systemPath>
        </dependency>
        <!--sqlite驱动-->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.23.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

这里有一个问题如果druid版本太低会出现以下的异常，解决办法是提高版本

Caused by: java.lang.NoClassDefFoundError: org/springframework/boot/autoconfigure/jdbc/metadata/DataSourcePoolMetadataProvider
 at java.lang.Class.getDeclaredMethods0(Native Method) ~[na:1.8.0_201]
 at java.lang.Class.privateGetDeclaredMethods(Class.java:2701) ~[na:1.8.0_201]
 at java.lang.Class.getDeclaredMethods(Class.java:1975) ~[na:1.8.0_201]

druid配置类

package com.infosec.ra.configuration;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.infosec.ra.common.MyDynamicDataSource;
import com.infosec.ra.filter.SrmFilter;
import lombok.extern.slf4j.Slf4j;
import org.mybatis.spring.SqlSessionFactoryBean;
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.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;

import java.util.HashMap;

import java.util.Map;

@Slf4j
@Configuration
public class DruidConfig {


    /**
     * @return
     * @Bean 防止数据监控报错，无法查看数据源
     * @ConfigurationProperties 会把配置文件的参数自动赋值到dataSource里。
     * @Primary 用于标识默认使用的 DataSource Bean
     */
    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    @Primary
    public DataSource masterDataSource() {
        log.info("创建masterDataSource");
        //DruidDataSource druidDataSource = new DruidDataSource();
        DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
        return druidDataSource;
    }

    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        MyDynamicDataSource myDynamicDataSource = new MyDynamicDataSource();
        // 配置多数据源
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource());
        myDynamicDataSource.setTargetDataSources(targetDataSources);
        return myDynamicDataSource;

    }



    /**
     * 配置 SqlSessionFactoryBean
     */
    @Bean(value = "sqlSessionFactoryBeanTest")
    @ConfigurationProperties(prefix = "mappers")
    public SqlSessionFactoryBean sqlSessionFactoryBean() {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        // 配置数据源，此处配置为关键配置，如果没有将 dynamicDataSource 作为数据源则不能实现切换
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        return sqlSessionFactoryBean;

    }



    /**
     * 注入 DataSourceTransactionManager 用于事务管理
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }



    @Bean
    public ServletRegistrationBean statViewServlet() {
        //创建servlet注册实体
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //设置ip白名单
        servletRegistrationBean.addInitParameter("allow", "");
        //设置ip黑名单
        servletRegistrationBean.addInitParameter("deny", "");
        //设置控制台管理用户__登录用户名和密码
        servletRegistrationBean.addInitParameter("loginUsername", "druid");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");

        //是否可以重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;

    }

    /**
     * druid过滤器
     *
     * @return
     */
    @Bean
    public FilterRegistrationBean druidFilter() {

        //创建过滤器
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        //设置过滤器过滤路径
        filterRegistrationBean.addUrlPatterns("/*");
        //忽略过滤的形式
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

}

启动类

我这里是在windows开发，然后正式环境实在linux上的，所以为了避免频繁修改配置文件，我通过System类获取操作系统类型，判断是开发还是正式环境，自动切换配置文件。

package com.infosec.ra;

import com.infosec.ra.util.DataSourceUtil;
import com.infosec.ra.util.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;
import org.springframework.context.ConfigurableApplicationContext;

import java.util.Map;

@Slf4j
@SpringBootApplication
public class SraApplication extends SpringBootServletInitializer {

    //环境变量OS对应操作系统
    private static final String OS = "OS";
    //项目所处操作系统环境为windows
    private static final String WINDOWS = "Windows";
    //项目为开发环境
    private static final String DEVELOP = "develop";
    //项目为测试环境
    private static final String PRODUCT = "product";

    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
        return builder.sources(SraApplication.class).profiles(getProfile());
    }

    private static String getProfile(){
        //获取系统环境变量
        Map<String, String> env = System.getenv();
        //获取操作系统类型，支持windows和linux
        String profile = (env != null && env.get(OS).contains(WINDOWS) ? DEVELOP : PRODUCT);
        return profile;
    }

    public static void main(String[] args) {
        ConfigurableApplicationContext applicationContext = new SpringApplicationBuilder().sources(SraApplication.class).profiles(getProfile()).run(args);
        log.info("SpringBoot启动成功");
        SpringContextUtil.setApplicationContext(applicationContext);
        DataSourceUtil.initDataSource();
    }

}

5.数据源工具类

package com.infosec.ra.util;




import com.alibaba.druid.pool.DruidDataSource;
import com.infosec.ra.common.MyDynamicDataSource;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.LoggerFactory;
import java.util.HashMap;
import java.util.Map;

/**
 * 初始化和添加数据源
 */
@Slf4j
public class DataSourceUtil {



    public static final Map<Object, Object> dataSourceMap = new HashMap<>();



    public static void initDataSource() {
        //获取masterDataSource
        DruidDataSource masterDataSource = (DruidDataSource) SpringContextUtil.getBean("masterDataSource");
        addDataSource("master", masterDataSource);
        //初始化其它数据源
        //initOthersDataSource();

        //刷新数据源
        flushDataSource();

    }



    public static void flushDataSource() {

        //获取spring管理的dynamicDataSource
        MyDynamicDataSource myDynamicDataSource = (MyDynamicDataSource) SpringContextUtil.getBean("dynamicDataSource");
        //将数据源设置到 targetDataSources
        myDynamicDataSource.setTargetDataSources(dataSourceMap);
        //将 targetDataSources 中的连接信息放入 resolvedDataSources 管理
        myDynamicDataSource.afterPropertiesSet();

    }



    public static void addDataSource(String key, DruidDataSource masterDataSource) {
        dataSourceMap.put(key, masterDataSource);
    }



    private static void initOthersDataSource() {
        //在此处可以查询出所有的数据源（例如，配置文件，数据库）然后添加
        String key = "slave";
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("123456");
        druidDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://10.100.61.160:3306/db1");
        //添加数据源到map
        addDataSource(key, druidDataSource);
    }

}

spring上下文工具类

package com.infosec.ra.util;

import org.springframework.context.ApplicationContext;



public class SpringContextUtil {
    private static ApplicationContext applicationContext;



    //获取上下文
    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }



    //设置上下文

    public static void setApplicationContext(ApplicationContext applicationContext) {
        SpringContextUtil.applicationContext = applicationContext;
    }



    //通过名字获取上下文中的bean
    public static Object getBean(String name) {
        return applicationContext.getBean(name);
    }



    //通过类型获取上下文中的bean
    public static Object getBean(Class<?> requiredType) {
        return applicationContext.getBean(requiredType);
    }

}

动态数据源MyDynamicDataSource

package com.infosec.ra.common;


import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.util.StringUtils;


@Slf4j
public class MyDynamicDataSource extends AbstractRoutingDataSource {

    @Override
    public Object determineCurrentLookupKey() {

        //获取当前线程的数据源，如果不存在使用master数据源
        String datasource = DBContextHolder.getDataSource();
        if (StringUtils.isEmpty(datasource)) {
            datasource = "master";

        }
        logger.info("datasource=" + datasource);
        return datasource;

    }

}

8.数据源切换类

package com.infosec.ra.common;


import com.infosec.ra.util.DataSourceUtil;

public class DBContextHolder {

    // 对当前线程的操作-线程安全的
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    // 调用此方法，切换数据源
    public static void setDataSource(String dataSource) {
        if (DataSourceUtil.dataSourceMap.containsKey(dataSource)) {
            contextHolder.set(dataSource);
        } else {
            throw new RuntimeException("数据源:" + dataSource + "不存在");
        }
    }

    // 获取数据源
    public static String getDataSource() {
        return contextHolder.get();

    }

    // 删除数据源
    public static void clearDataSource() {
        contextHolder.remove();
    }

}

9.mapper类

package com.infosec.ra.mapper;

import com.infosec.ra.entity.Role;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface RoleMapper {
    @Select("select * from ROLE")
    List<Role> getRoles();
}

10.controller类

package com.infosec.ra.controller;

import com.alibaba.druid.pool.DruidDataSource;
import com.infosec.ra.common.DBContextHolder;
import com.infosec.ra.entity.Role;
import com.infosec.ra.mapper.RoleMapper;
import com.infosec.ra.util.DataSourceUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

@RestController
public class DataSourceController {

    @Autowired
    private RoleMapper roleMapper;

    @GetMapping("/role")
    public List<Role> getRoles(){
        //double random = Math.random();
        /*if((random*10)%2==0){
            DBContextHolder.setDataSource("master");
        }else{
            DBContextHolder.setDataSource("slave2");
        }*/
        DBContextHolder.setDataSource("master");
        return roleMapper.getRoles();
    }

    @GetMapping("/roles")
    public List<Role> getRoless(){
        DBContextHolder.setDataSource("slave2");
        return roleMapper.getRoles();
    }

    @PostMapping("/test")
    public String addDataSource(String token,String userName,String password,String driver,String url,String key){
        Map<String, String> map = new HashMap<>();
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(userName);
        druidDataSource.setPassword(password);
        druidDataSource.setDriverClassName(driver);
        druidDataSource.setUrl(url);
        //添加数据源到map
         DataSourceUtil.addDataSource(key, druidDataSource);
        // 刷新
        DataSourceUtil.flushDataSource();
        map.put("msg", "数据源数量:" + DataSourceUtil.dataSourceMap.size());

        return "添加成功："+DataSourceUtil.dataSourceMap.size();
    }
}

五、结果展示

刚启动，前端没有配置数据源，所以看不到数据源

2.通过swagger调用接口，配置数据源

再加一个mybatits数据库

userName: root
pwd: 123456
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://10.100.61.160:3306/db1
key: master

再次查看数据源,可以在数据源中找到两个，一个是mysql，一个是oracle

查询结果

#mysql
[
  {
    "id": 1,
    "name": "test"
  }
]

#oracle
[
  {
    "id": 1,
    "name": "安全管理员"
  },
  {
    "id": 2,
    "name": "系统管理员"
  },
  {
    "id": 3,
    "name": "日志管理员"
  }
]

这里需要先执行查询方法，然后在数据源中才能看到刚刚添加的数据源，如果碰到问题，欢迎提问！

自我推荐

这个是我的微信公众号，欢迎扫码关注，谢谢！