springboot2.1.0、DruidDataSource多数据源简单使用

一）背景

由于项目中有用到多数据源方式，所以把该功能单独摘取出来，便于理解。

 

二）实现步骤

1、先在pom.xml中引入DruidDataSource相关的jar，引入的是1.1.17版本

备注：引用了oracle的jar。引入mybatis的jar是因为和druid有依赖关系，也可以结合mybatis使用。

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.oysept.springboot</groupId>
    <artifactId>oysept-springboot-druiddatasource</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>oysept-springboot-druiddatasource</name>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.0.RELEASE</version>
        <relativePath/>
    </parent>

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

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
		
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
		
        <!-- mybatis start -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>
        <!-- mybatis end -->
        
        <!-- oracle start -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        <!-- oracle end -->
        
        <!-- druid jar -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>
	</dependencies>

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

 

2、在application.properties中配置oracle数据源（只配置基本必须项）

备注：配置了一个主数据源和两个从数据源，好区分测试。

          由于该功能只是测试，所有数据源都配置成了一致，在调用时可区分，该项目是运行使用过的。

server.port=8080

spring.datasource.druid.primary.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.druid.primary.url=jdbc:oracle:thin:@localhost:1521/oysept
spring.datasource.druid.primary.username=oysept
spring.datasource.druid.primary.password=oysept

spring.datasource.druid.one.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.druid.one.url=jdbc:oracle:thin:@localhost:1521/oysept
spring.datasource.druid.one.username=oysept
spring.datasource.druid.one.password=oysept

spring.datasource.druid.two.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.druid.two.url=jdbc:oracle:thin:@localhost:1521/oysept
spring.datasource.druid.two.username=oysept
spring.datasource.druid.two.password=oysept

数据源扩展属性(如名称一致时，会直接覆盖)，可先不配置，等项目运行时，再逐渐添加：

#连接池配置
spring.datasource.druid.initial-size=
spring.datasource.druid.max-active=
spring.datasource.druid.min-idle=
spring.datasource.druid.max-wait=
spring.datasource.druid.pool-prepared-statements=
spring.datasource.druid.max-pool-prepared-statement-per-connection-size= 
spring.datasource.druid.max-open-prepared-statements= #和上面的等价
spring.datasource.druid.validation-query=
spring.datasource.druid.validation-query-timeout=
spring.datasource.druid.test-on-borrow=
spring.datasource.druid.test-on-return=
spring.datasource.druid.test-while-idle=
spring.datasource.druid.time-between-eviction-runs-millis=
spring.datasource.druid.min-evictable-idle-time-millis=
spring.datasource.druid.max-evictable-idle-time-millis=
spring.datasource.druid.filters= #配置多个英文逗号分隔

#监控配置
# WebStatFilter配置，说明请参考Druid Wiki，配置_配置WebStatFilter
spring.datasource.druid.web-stat-filter.enabled= #是否启用StatFilter默认值false
spring.datasource.druid.web-stat-filter.url-pattern=
spring.datasource.druid.web-stat-filter.exclusions=
spring.datasource.druid.web-stat-filter.session-stat-enable=
spring.datasource.druid.web-stat-filter.session-stat-max-count=
spring.datasource.druid.web-stat-filter.principal-session-name=
spring.datasource.druid.web-stat-filter.principal-cookie-name=
spring.datasource.druid.web-stat-filter.profile-enable=

# StatViewServlet配置，说明请参考Druid Wiki，配置_StatViewServlet配置
spring.datasource.druid.stat-view-servlet.enabled=
#是否启用StatViewServlet（监控页面）默认值为false（考虑到安全问题默认并未启动，如需启用建议设置密码或白名单以保障安全）
spring.datasource.druid.stat-view-servlet.url-pattern=
spring.datasource.druid.stat-view-servlet.reset-enable=
spring.datasource.druid.stat-view-servlet.login-username=
spring.datasource.druid.stat-view-servlet.login-password=
spring.datasource.druid.stat-view-servlet.allow=
spring.datasource.druid.stat-view-servlet.deny=

# Spring监控配置，说明请参考Druid Github Wiki，配置_Druid和Spring关联监控配置
spring.datasource.druid.aop-patterns=
# Spring监控AOP切入点，如x.y.z.service.*,配置多个英文逗号分隔

 

3、新增主数据源和从数据源配置类

说明：@ConfigurationProperties : 根据application.properties文件中prefix前缀的属性名称,批量注入属性值.。

           继承DruidDataSource类只是为了重写几个基本配置。

主数据源：

package com.oysept.springboot.config;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;

import com.alibaba.druid.pool.DruidDataSource;

@ConfigurationProperties("spring.datasource.druid.primary")
public class PrimaryDataSourceWrapper extends DruidDataSource implements InitializingBean {
    
    private static final long serialVersionUID = 1L;
	
    @Autowired
    private DataSourceProperties basicProperties;
	
    @Override
    public void afterPropertiesSet() throws Exception {
        // 如果未找到前缀“spring.datasource.druid”JDBC属性，将使用“Spring.DataSource”前缀JDBC属性。
        if (super.getUsername() == null) {
            super.setUsername(basicProperties.determineUsername());
        }
        if (super.getPassword() == null) {
            super.setPassword(basicProperties.determinePassword());
        }
        if (super.getUrl() == null) {
            super.setUrl(basicProperties.determineUrl());
        }
        if (super.getDriverClassName() == null) {
            super.setDriverClassName(basicProperties.getDriverClassName());
        }
    }
}

 

第一个从数据源：

package com.oysept.springboot.config;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;

import com.alibaba.druid.pool.DruidDataSource;

@ConfigurationProperties(prefix = "spring.datasource.druid.one")
public class OneBackDataSourceWrapper extends DruidDataSource implements InitializingBean {

    private static final long serialVersionUID = 1L;
	
    @Autowired
    private DataSourceProperties basicProperties;
	
    @Override
    public void afterPropertiesSet() throws Exception {
        // 如果未找到前缀“spring.datasource.druid”JDBC属性，将使用“Spring.DataSource”前缀JDBC属性。
        if (super.getUsername() == null) {
            super.setUsername(basicProperties.determineUsername());
        }
        if (super.getPassword() == null) {
            super.setPassword(basicProperties.determinePassword());
        }
        if (super.getUrl() == null) {
            super.setUrl(basicProperties.determineUrl());
        }
        if (super.getDriverClassName() == null) {
            super.setDriverClassName(basicProperties.getDriverClassName());
        }
    }
}

 

第二个从数据源：

package com.oysept.springboot.config;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;

import com.alibaba.druid.pool.DruidDataSource;

@ConfigurationProperties(prefix = "spring.datasource.druid.two")
public class TwoBackDataSourceWrapper extends DruidDataSource implements InitializingBean {

    private static final long serialVersionUID = 1L;
	
    @Autowired
    private DataSourceProperties basicProperties;
	
    @Override
    public void afterPropertiesSet() throws Exception {
        // 如果未找到前缀“spring.datasource.druid”JDBC属性，将使用“Spring.DataSource”前缀JDBC属性。
        if (super.getUsername() == null) {
            super.setUsername(basicProperties.determineUsername());
        }
        if (super.getPassword() == null) {
            super.setPassword(basicProperties.determinePassword());
        }
        if (super.getUrl() == null) {
            super.setUrl(basicProperties.determineUrl());
        }
        if (super.getDriverClassName() == null) {
            super.setDriverClassName(basicProperties.getDriverClassName());
        }
    }
}

 

4、新增数据源构建类，该构建类主要是把数据源注入成Bean，交由spring管理

备注：该篇是用手动方式创建数据源，用第一种默认创建方式构建比较简单。

package com.oysept.springboot.config;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.pool.DruidDataSource;

/**
 * druid配置构建类
 * 备注: 如数据源的属性有不一致的情况,可把该类中的Bean单独创建一个类
 * @author ouyangjun
 */
@Configuration
public class DruidBuilder {
	
    /**
     * 实例化
     * @return
     */
    public static DruidBuilder create() {
        return new DruidBuilder();
    }
	
    /**
     * 主数据源: 如果在使用时,不特别指定Bean的名称,默认是使用主数据源操作.
     * @Primary: 自动装配时当出现多个Bean时,被注解为@Primary的Bean将作为首选者,否则将抛出异常 .
     * @ConfigurationProperties : 根据配置文件中prefix前缀的属性名称,批量注入属性值.
     * @return
     */
    @Primary
    @Bean(name = "primaryDataSource")
    public DataSource primaryDataSource(){
        // 第一种默认创建方式
        //DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
		
        // 第二种手动创建方式
        DruidDataSource druidDataSource = DruidBuilder.create().buildPromaryDataSource();
        System.out.println("==>primaryDataSource,druidDataSource: " + druidDataSource);
        return druidDataSource;
    }
	
    /**
     * 创建主数据源
     * @return
     */
    public DruidDataSource buildPromaryDataSource() {
        return new PrimaryDataSourceWrapper();
    }
	
    /**
     * 第一个从数据源
     * @return
     */
    @Bean(name = "oneBackDataSource")
    public DataSource oneBackDataSource(){
        // 第一种默认创建方式
        //DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
		
        // 第二种手动创建方式
        DruidDataSource druidDataSource = DruidBuilder.create().buildOneBackDataSource();
        System.out.println("==>oneBackDataSource,druidDataSource: " + druidDataSource);
        return druidDataSource;
    }
	
    /**
     * 创建第一个从数据源
     * @return
     */
    public DruidDataSource buildOneBackDataSource() {
        return new OneBackDataSourceWrapper();
    }
	
    /**
     * 第二个从数据源
     * @return
     */
    @Bean(name = "twoBackDataSource")
    public DataSource twoBackDataSource(){
        // 第一种默认创建方式
        //DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
		
        // 第二种手动创建方式
        DruidDataSource druidDataSource = DruidBuilder.create().buildTwoBackDataSource();
        System.out.println("==>twoBackDataSource,druidDataSource: " + druidDataSource);
        return druidDataSource;
    }
	
    /**
     * 创建第二个从数据源
     * @return
     */
    public DruidDataSource buildTwoBackDataSource() {
        return new TwoBackDataSourceWrapper();
    }
}

 

5、新增druid页面监控servlet配置类，该类主要是界面化监控数据源的使用情况

备注：配置该servlet，在springboot项目启动之后，可直接在浏览器通过地址访问，查看数据源使用情况。

package com.oysept.springboot.config;

import java.util.HashMap;
import java.util.Map;

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 com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

/**
 * druid页面监控,添加该配置类,springboot启动之后可通过访问地址进行查看
 * 访问地址: http://localhost:8080/druid
 * 账号: admin
 * 密码: admin
 * @author ouyangjun
 */
@Configuration
public class DruidServletConfiguration {

    /**
     * 添加druid页面监控servlet
     * @return
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean registrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // 白名单
        Map<String,String> initParameters = new HashMap<>(16);
        // 禁用HTML页面上的“REST ALL”功能
        initParameters.put("resetEnable","false");
        // IP白名单（没有配置或者为空，则允许所有访问）
        initParameters.put("/druid/*","");
        // ip黑名单
        initParameters.put("deny","");
        // 监控页面登录用户名
        initParameters.put("loginUsername","admin");
        // 监控页面登录用户密码
        initParameters.put("loginPassword", "admin");
        
        registrationBean.setInitParameters(initParameters);
        return registrationBean;
    }
	
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        // 过滤规则
        filterRegistrationBean.addUrlPatterns("/*");
        // 忽略过滤格式
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
        //filterRegistrationBean.addInitParameter("profileEnable", "true");
        //filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");
        //filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");
        //filterRegistrationBean.addInitParameter("DruidWebStatFilter", "/*");
        return filterRegistrationBean;
    }
}

 

6、新增Controller测试类，该类只有一个数据源连接测试接口，可到druid界面查看

package com.oysept.springboot.controller;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * springboot druiddatasource案例
 * @author ouyangjun
 */
@RestController
@RequestMapping(value="/druiddatasource")
public class DruidController {

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;
	
    @Autowired
    @Qualifier("oneBackDataSource")
    private DataSource oneBackDataSource;
	
    @Autowired
    @Qualifier("twoBackDataSource")
    private DataSource twoBackDataSource;
	
    /**
     * 启动之后,访问该接口,然后在druid界面查看数据源使用相关信息
     * 访问地址: http://localhost:8080/druiddatasource/index
     * @return
     */
    @RequestMapping(value="/index")
    public String index() {
        try {
            Connection primaryConnection = primaryDataSource.getConnection();
            System.out.println("==>primaryConnection: " + primaryConnection);
			
            Connection oneBackConnection = oneBackDataSource.getConnection();
            System.out.println("==>oneBackConnection: " + oneBackConnection);
			
            Connection twoBackConnection = twoBackDataSource.getConnection();
            System.out.println("==>twoBackConnection: " + twoBackConnection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "Hello oysept druiddatasource!";
    }
}

 

7、新增springboot启动类

package com.oysept.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * springboot启动类
 * @author ouyangjun
 */
@SpringBootApplication
public class DruidApplication {

    public static void main(String[] args) {
        SpringApplication.run(DruidApplication.class, args);
    }
}

 

8、项目结构图

 

9、项目启动之后使用步骤

第一步：先启动DruidApplication类。

第二步：访问Controller类中接口：http://localhost:8080/druiddatasource/index，可多次访问。

第三步：访问druid监控地址：http://localhost:8080/druid，输入账号密码admin查看数据源。

 

识别二维码关注个人微信公众号


 本章完结，待续，欢迎转载！
 
本文说明：该文章属于原创，如需转载，请标明文章转载来源！

版权声明：本文为p812438109原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。