SpringBoot 配置多数据源，集成Druid与SQL监控

记录下搭建SpringBoot集成druid配置多数据源的过程。

1.开发环境

jdk 1.8

mysql 5.1

2.项目目录结构

image-20210329154153896.png

3.pom依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.2.9.RELEASE</version>
</dependency>
<!--<dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version></version>
        </dependency>-->

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.10</version>
</dependency>

<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.59</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.30</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.19</version>
</dependency>

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.0.0</version>
</dependency>

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <!-- 去掉scope作用域，使用默认的compile，编译、测试、运行都有效的作用域 -->
    <!--<scope>slave</scope>-->
</dependency>

<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjrt</artifactId>
    <version>1.9.1</version>
</dependency>
<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.9.1</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.5</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.5</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.8.RELEASE</version>
</dependency>

4.application.yml配置多个jdbc连接

spring:
  datasource:
    druid:
      # 数据库一配置
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://172.16.1.61:3306/debt_data?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true
        username: lianxin
        password: LX@789023234
        #######################  druid配置  #########################
        # 下面为连接池的补充设置，应用到上面所有数据源中# 初始化大小，最小，最大
        ##################    连接池配置    ################
        #连接池建立时创建的初始化连接数
        initial-size: 5
        #连接池中最大的活跃连接数
        max-active: 20
        #连接池中最小的活跃连接数
        min-idle: 5
        # 配置获取连接等待超时的时间
        max-wait: 60000
        # 打开PSCache，并且指定每个连接上PSCache的大小
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        #spring.datasource.druid.max-open-prepared-statements= #和上面的等价
        validation-query: SELECT 1 FROM DUAL
        validation-query-timeout: 30000
        #是否在获得连接后检测其可用性
        test-on-borrow: false
        #是否在连接放回连接池后检测其可用性
        test-on-return: false
        #是否在连接空闲一段时间后检测其可用性
        test-while-idle: true
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙，log4j2为你自己使用的日志，如果是log4j就写log4j,我这里用的log4j2
        filters: stat,wall,log4j2
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://172.16.1.62:3306/debt_dev?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true
        username: lianxin
        password: LX@789023234
        #######################  druid配置  #########################
        # 下面为连接池的补充设置，应用到上面所有数据源中# 初始化大小，最小，最大
        ##################    连接池配置    ################
        #连接池建立时创建的初始化连接数
        initial-size: 5
        #连接池中最大的活跃连接数
        max-active: 20
        #连接池中最小的活跃连接数
        min-idle: 5
        # 配置获取连接等待超时的时间
        max-wait: 60000
        # 打开PSCache，并且指定每个连接上PSCache的大小
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        #spring.datasource.druid.max-open-prepared-statements= #和上面的等价
        validation-query: SELECT 1 FROM DUAL
        validation-query-timeout: 30000
        #是否在获得连接后检测其可用性
        test-on-borrow: false
        #是否在连接放回连接池后检测其可用性
        test-on-return: false
        #是否在连接空闲一段时间后检测其可用性
        test-while-idle: true
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙，log4j2为你自己使用的日志，如果是log4j就写log4j,我这里用的log4j2
        filters: stat,wall,log4j2

5.Druid 配置

package com.wangcp.demo01.config;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
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 java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
* druid配置
* @author wangcp
* @date 2021/03/29 13:46
**/
@Configuration
public class DruidConfig {

    /**
     * 配置Druid监控
     * 后台管理Servlet
     * @return
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // 这是配置的druid监控的登录密码
        Map<String,String> initParams = new HashMap<String, String>();
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","admin");
        //默认是允许所有访问
        initParams.put("allow","");
        //黑名单的IP
        initParams.put("deny","192.168.15.21");
        bean.setInitParameters(initParams);
        return bean;
    }

    /**
     * 配置web监控的filter
     *
     * @return
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String,String> initParams = new HashMap<String, String>();
        // 过滤掉需要监控的文件
        initParams.put("exclusions","/static/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

6.绑定配置的数据源

有几个数据源就需要对应添加几个配置类进行注入

  • master 数据源

    package com.wangcp.demo01.config;

import com.alibaba.druid.pool.DruidDataSource;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
* 绑定数据源
* @author wangcp
* @date 2021/03/29 14:02
**/
@Configuration
@MapperScan(basePackages = "com.wangcp.demo01.mapper.master",sqlSessionTemplateRef = "masterSqlSessionTemplate")
public class MasterDataSourceConfiguration {

    @Bean(name = "masterDataSource")
    @Primary   //配置默认数据源
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    public DataSource dataSource() {
        return  new DruidDataSource();
    }

    @Bean(name = "masterSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // 此处有坑，必须存在mapping/*.xml 文件，否则启动报错
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "masterTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "masterSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
            throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

  • slave 数据源

    package com.wangcp.demo01.config;

import com.alibaba.druid.pool.DruidDataSource;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
* slaver绑定数据源
* @author wangcp
* @date 2021/03/29 14:12
**/
@Configuration
@MapperScan(basePackages = "com.wangcp.demo01.mapper.slave", sqlSessionTemplateRef  = "slaveSqlSessionTemplate")
public class SlaverDataSourceConfiguration {

    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave")
    public DataSource dataSource() {
        return  new DruidDataSource();
    }

    @Bean(name = "slaveSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "slaveTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "slaveSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
            throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

7.配置SQL可视化监控

package com.wangcp.demo01.config;

import com.alibaba.druid.support.spring.stat.DruidStatInterceptor;
import org.springframework.aop.support.DefaultPointcutAdvisor;
import org.springframework.aop.support.JdkRegexpMethodPointcut;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Scope;

/**
* spring 监控配置
* @author wangcp
* @date 2021/03/29 14:47
**/
@Configuration
public class SpringDaoMethodAspect {

    @Bean
    public DruidStatInterceptor druidStatInterceptor() {
        DruidStatInterceptor dsInterceptor = new DruidStatInterceptor();
        return dsInterceptor;
    }

    @Bean
    @Scope("prototype")
    public JdkRegexpMethodPointcut druidStatPointcut() {
        JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
        pointcut.setPattern("com.wangcp.demo01.mapper.*");
        return pointcut;
    }

    @Bean
    public DefaultPointcutAdvisor druidStatAdvisor(DruidStatInterceptor druidStatInterceptor, JdkRegexpMethodPointcut druidStatPointcut) {
        DefaultPointcutAdvisor defaultPointAdvisor = new DefaultPointcutAdvisor();
        defaultPointAdvisor.setPointcut(druidStatPointcut);
        defaultPointAdvisor.setAdvice(druidStatInterceptor);
        return defaultPointAdvisor;
    }

}

8.springboot启动类

springboot启动类，固定写法没啥好解释的

package com.wangcp;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
* 主启动类
* @author wangcp
* @date 2021/03/29 14:00
**/
@SpringBootApplication
@MapperScan(basePackages = {"com.wangcp.demo01.mapper"})
public class MultipleMain {
    public static void main(String[] args) {
        SpringApplication.run(MultipleMain.class,args);
    }
}

9.编写测试类，进行效果验证

package com.wangcp.demo01.controller;

import com.wangcp.demo01.mapper.master.SCUserMapper;
import com.wangcp.demo01.mapper.slave.SCUserSlaveMapper;
import com.wangcp.entity.SCUser;
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.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
* 测试多数据源
* @author wangcp
* @date 2021/03/29 15:05
**/
@RestController
@Slf4j
public class TestController {

    // master 数据库
    @Autowired
    private SCUserMapper scUserMapper;
    // slave 数据库
    @Autowired
    private SCUserSlaveMapper scUserSlaveMapper;

    @GetMapping(value = "/masterTest")
    @ResponseBody
    public List<SCUser> masterTest(){
        return scUserMapper.queryUserByPid();
    }

    @GetMapping(value = "/slaveTest")
    @ResponseBody
    public List<SCUser> slaveTest(){
        return scUserSlaveMapper.queryUserByPid();
    }
}

10.最后展示下对应的sql监控页面

访问地址： http://localhost:8080/druid/spring.html

image-20210329155707017.png

补充

本次测试的两个数据源都为Mysql，但其实可以换成 Oracle 或其它数据源，除了驱动不同外，原理都是一样的。

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容