SpringBoot+druid多数据源-优快云博客

本文介绍在Spring Boot项目中如何配置多个数据源，使用Druid连接池进行数据库连接管理和监控。通过示例展示了如何在配置文件中定义不同数据库的参数，以及如何在代码中创建和使用对应的DataSource和SqlSessionFactory。

开始

项目开发中存在不止一个数据库的情况，需要用到多数据源配置，进行相关业务开发。

实现方式

使用方式与单个druid配置相同

pom

 <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.14</version>
</dependency>

配置文件

application配置

#第一个数据配置
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai

# 连接池指定 springboot版本默认使用HikariCP 此处要替换成Druid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

## 初始化连接池的连接数量 大小，最小，最大
spring.datasource.druid.initial-size=1
spring.datasource.druid.min-idle=1
spring.datasource.druid.max-active=20
## 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query=SELECT 'x'
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.filter.stat.log-slow-sql=true
# 是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.filters=stat,wall
spring.datasource.druid.test-while-idle=true
#Spring监控，对内部各接口调用的监控
spring.datasource.druid.aop-patterns=com.felix.project.controller.*,com.felix.project.mapper.*,com.felix.project.service.*

# 第二个数据库配置
spring.datasource.felix.username=root
spring.datasource.felix.password=root
spring.datasource.felix.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.felix.url=jdbc:mysql://localhost:3306/test1?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
# 连接池指定 springboot版本默认使用HikariCP 此处要替换成Druid
spring.datasource.felix.type=com.alibaba.druid.pool.DruidDataSource
## 初始化连接池的连接数量 大小，最小，最大
spring.datasource.felix.druid.initial-size=1
spring.datasource.felix.druid.min-idle=1
spring.datasource.felix.druid.max-active=20
## 配置获取连接等待超时的时间
spring.datasource.felix.druid.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.felix.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.felix.druid.min-evictable-idle-time-millis=300000
spring.datasource.felix.druid.validation-query=SELECT 'x'
spring.datasource.felix.druid.test-on-borrow=false
spring.datasource.felix.druid.test-on-return=false
spring.datasource.felix.druid.filter.stat.log-slow-sql=true
# 是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
spring.datasource.felix.druid.pool-prepared-statements=false
spring.datasource.felix.druid.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.felix.druid.filters=stat,wall
spring.datasource.felix.druid.test-while-idle=true
#Spring监控，对内部各接口调用的监控
spring.datasource.felix.druid.aop-patterns=com.felix.project.controller.*,com.felix.project.mapper.*,com.felix.project.service.*


mybatis.mapper-locations=classpath:mapper/*.xml,classpath:mapper2/*.xml
mybatis.type-aliases-package=com.felix.project.model
mapper.not-empty=false
mapper.identity=MYSQL

yml 配置文件相类似

第一个config类

新建两个druid配置,采用手动配置的方式，避免druid不能监控到SQL
第一个配置文件

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@MapperScan(basePackages ="com.felix.project.mapper", sqlSessionFactoryRef = "masterSqlSessionFactory")
public class DruidConfig {

    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;
    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;
    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;
    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;
    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.druid.validation-query}")
    private String validationQuery;
    @Value("${spring.datasource.druid.test-while-idle}")
    private boolean testWhileIdle;
    @Value("${spring.datasource.druid.test-on-borrow}")
    private boolean testOnBorrow;
    @Value("${spring.datasource.druid.test-on-return}")
    private boolean testOnReturn;
    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.druid.filters}")
    private String filters;
    @Value("${spring.datasource.druid.aop-patterns}")
    private String[] aopPatterns;
    /**
     * 创建数据源
     *
     * @return DataSource
     */
    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        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);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return datasource;
    }

    // 创建该数据源的事务管理
    @Primary
    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager primaryTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(masterDataSource());
    }

    // 创建Mybatis的连接会话工厂实例
    @Primary
    @Bean(name = "masterSqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("masterDataSource") DataSource primaryDataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(primaryDataSource);  // 设置数据源bean
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml"));  // 设置mapper文件路径
        return sessionFactory.getObject();
    }
}

第二config类

package com.felix.project.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@MapperScan(basePackages ="com.felix.project.mapper2",sqlSessionFactoryRef = "secondSqlSessionFactory")
public class DruidFelicConfig {

    @Value("${spring.datasource.felix.url}")
    private String url;
    @Value("${spring.datasource.felix.username}")
    private String username;
    @Value("${spring.datasource.felix.password}")
    private String password;
    @Value("${spring.datasource.felix.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.felix.druid.initial-size}")
    private int initialSize;
    @Value("${spring.datasource.felix.druid.min-idle}")
    private int minIdle;
    @Value("${spring.datasource.felix.druid.max-active}")
    private int maxActive;
    @Value("${spring.datasource.felix.druid.max-wait}")
    private int maxWait;
    @Value("${spring.datasource.felix.druid.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.felix.druid.min-evictable-idle-time-millis}")
    private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.felix.druid.validation-query}")
    private String validationQuery;
    @Value("${spring.datasource.felix.druid.test-while-idle}")
    private boolean testWhileIdle;
    @Value("${spring.datasource.felix.druid.test-on-borrow}")
    private boolean testOnBorrow;
    @Value("${spring.datasource.felix.druid.test-on-return}")
    private boolean testOnReturn;
    @Value("${spring.datasource.felix.druid.pool-prepared-statements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.felix.druid.max-pool-prepared-statement-per-connection-size}")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.felix.druid.filters}")
    private String filters;
    @Value("${spring.datasource.felix.druid.aop-patterns}")
    private String[] aopPatterns;

    /**
     *  DataSource
     * @return
     */
    @Bean(name = "secondDataSource")
    public DataSource secondDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        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);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return datasource;
    }

    // 创建该数据源的事务管理
    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager secondTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(secondDataSource());
    }

    // 创建Mybatis的连接会话工厂实例
    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource primaryDataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(primaryDataSource);  // 设置数据源bean
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml"));  // 设置mapper文件路径
        return sessionFactory.getObject();
    }
}

两配置文件基本相同，主要区别在与主配置需要加上@Primary注解

监控

新建一个DruidMoniterConfig配置项用于开启druid监控

package com.felix.project.config;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
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;

@Configuration
public class DruidMoniterConfig {
    @Bean
    @ConditionalOnMissingBean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean =
                new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //白名单
        // servletRegistrationBean.addInitParameter("allow","192.168.6.195");
        //IP黑名单(存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
        //  servletRegistrationBean.addInitParameter("deny","192.168.6.73");
        //用于登陆的账号密码
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "admin");
        //是否能重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    @ConditionalOnMissingBean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        //添加过滤规则.
        filterRegistrationBean.addUrlPatterns("/*");
        //添加不需要忽略的格式信息.
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

}