springboot 多数据源（三种数据库连接池--JDBC，dbcp2，Druid）

时间：2024-01-21 21:50:22

本文使用的是springboot2.0（在配置数据源时和springboot1.X略有区别）

首先：springboot默认支持的连接池有dbcp,dbcp2, tomcat, hikari四种连接池

  1. 由于Tomcat数据源连接池的性能和并发，在tomcat可用时，我们总是优先使用它。
  2. 如果HikariCP可用，我们将使用它。
  3. 如果Commons DBCP可用，我们将使用它，但在生产环境不推荐使用它。
  4. 最后，如果Commons DBCP2可用，我们将使用它

　　即自动优先级tomcat>HikariCP>DBCP>DBCP2（想要使用后面的连接池需要指定类型type：如：spring.datasource.ccjoin-settlement.type=org.apache.commons.dbcp2.BasicDataSource）

本例使用的是用多数据源，不同连接池：

需要的包：

spring-boot-starter-web(包含自带的tomcat)、mybatis-spring-boot-starter

springboot数据库连接：
有两种方法与数据库建立连接，一种是集成Mybatis，另一种用JdbcTemplate
用JdbcTemplate需要的包：
mysql-connector-java、spring-boot-starter-jdbc
集成mybatis需要的包：
mysql-connector-java、spring-boot-starter-jdbc、mybatis-spring-boot-starter

首先：目录结构如下

springboot 多数据源（三种数据库连接池--JDBC，dbcp2，Druid）

pom.xml如下

 <?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.jmu.ccjoin</groupId>

     <artifactId>spring-boot</artifactId>

     <version>0.0.1-SNAPSHOT</version>

     <packaging>war</packaging>

     <name>spring-boot</name>

     <description>spring-boot</description>

     <parent>

         <groupId>org.springframework.boot</groupId>

         <artifactId>spring-boot-starter-parent</artifactId>

         <version>2.0.4.RELEASE</version>

         <relativePath /> <!-- lookup parent from repository -->

     </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-web</artifactId>

         </dependency>

         <!-- tomcat自带jdbc连接池 -->

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-tomcat</artifactId>

             <!-- <scope>provided</scope> -->

         </dependency>

         <dependency>

             <groupId>org.mybatis.spring.boot</groupId>

             <artifactId>mybatis-spring-boot-starter</artifactId>

             <version>1.3.2</version>

         </dependency>

         <dependency>

             <groupId>mysql</groupId>

             <artifactId>mysql-connector-java</artifactId>

             <scope>runtime</scope>

         </dependency>

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-jdbc</artifactId>

         </dependency>

         <!-- dbcp2数据库连接池 -->

         <dependency>

             <groupId>org.apache.commons</groupId>

             <artifactId>commons-dbcp2</artifactId>

         </dependency>

         <!-- dbcp数据库连接池 -->

         <dependency>

             <groupId>commons-dbcp</groupId>

             <artifactId>commons-dbcp</artifactId>

             <version>1.4</version>

         </dependency>

         <!-- druid数据库连接池 -->

         <dependency>

             <groupId>com.alibaba</groupId>

             <artifactId>druid</artifactId>

             <version>1.1.10</version>

         </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-configuration-processor</artifactId>

             <optional>false</optional>

         </dependency>

     </dependencies>

     <build>

         <finalName>spring-boot</finalName>

         <plugins>

             <plugin>

                 <groupId>org.springframework.boot</groupId>

                 <artifactId>spring-boot-maven-plugin</artifactId>

                 <executions>

                     <execution>

                         <goals>

                             <goal>repackage</goal>

                         </goals>

                     </execution>

                 </executions>

             </plugin>

         </plugins>

     </build>

 </project>

ConfigProperties.java(把配置文件封装到bean并注入spring容器)

 package com.jmu.ccjoin.config;

 import org.springframework.beans.factory.annotation.Value;

 import org.springframework.context.annotation.PropertySource;

 import org.springframework.stereotype.Component;

 @Component

 @PropertySource("file:/var/opt/spring-boot-test/config.properties")

 public class ConfigProperties {

     @Value("${spring.datasource.jmuv3.jdbc-url}")

     private String jmuv3Url;

     @Value("${spring.datasource.jmuv3.driver-class-name}")

     private String jmuv3DriverClassName;

     @Value("${spring.datasource.jmuv3.username}")

     private String jmuv3Username;

     @Value("${spring.datasource.jmuv3.password}")

     private String jmuv3Password;

     @Value("${spring.datasource.ccjoin-settlement.jdbc-url}")

     private String ccjoinSettlementUrl;

     @Value("${spring.datasource.ccjoin-settlement.driver-class-name}")

     private String ccjoinSettlementDriverClassName;

     @Value("${spring.datasource.ccjoin-settlement.username}")

     private String ccjoinSettlementUsername;

     @Value("${spring.datasource.ccjoin-settlement.password}")

     private String ccjoinSettlementPassword;

     @Value("${spring.datasource.ccjoin-settlement.type}")

     private String ccjoinSettlementType;

     @Value("${spring.datasource.jmu-mp.jdbc-url}")

     private String jmuMpUrl;

     @Value("${spring.datasource.jmu-mp.driver-class-name}")

     private String jmuMpDriverClassName;

     @Value("${spring.datasource.jmu-mp.username}")

     private String jmuMpUsername;

     @Value("${spring.datasource.jmu-mp.password}")

     private String jmuMpPassword;

     /**

      * jmuv3Url的取得

      *

      * @return String jmuv3Url

      */

     public String getJmuv3Url() {

         return jmuv3Url;

     }

     /**

      * jmuv3Url的设定

      *

      * @param jmuv3Url

      *            jmuv3Url

      */

     public void setJmuv3Url(String jmuv3Url) {

         this.jmuv3Url = jmuv3Url;

     }

     /**

      * jmuv3DriverClassName的取得

      *

      * @return String jmuv3DriverClassName

      */

     public String getJmuv3DriverClassName() {

         return jmuv3DriverClassName;

     }

     /**

      * jmuv3DriverClassName的设定

      *

      * @param jmuv3DriverClassName

      *            jmuv3DriverClassName

      */

     public void setJmuv3DriverClassName(String jmuv3DriverClassName) {

         this.jmuv3DriverClassName = jmuv3DriverClassName;

     }

     /**

      * jmuv3Username的取得

      *

      * @return String jmuv3Username

      */

     public String getJmuv3Username() {

         return jmuv3Username;

     }

     /**

      * jmuv3Username的设定

      *

      * @param jmuv3Username

      *            jmuv3Username

      */

     public void setJmuv3Username(String jmuv3Username) {

         this.jmuv3Username = jmuv3Username;

     }

     /**

      * jmuv3Password的取得

      *

      * @return String jmuv3Password

      */

     public String getJmuv3Password() {

         return jmuv3Password;

     }

     /**

      * jmuv3Password的设定

      *

      * @param jmuv3Password

      *            jmuv3Password

      */

     public void setJmuv3Password(String jmuv3Password) {

         this.jmuv3Password = jmuv3Password;

     }

     /**

      * ccjoinSettlementUrl的取得

      *

      * @return String ccjoinSettlementUrl

      */

     public String getCcjoinSettlementUrl() {

         return ccjoinSettlementUrl;

     }

     /**

      * ccjoinSettlementUrl的设定

      *

      * @param ccjoinSettlementUrl

      *            ccjoinSettlementUrl

      */

     public void setCcjoinSettlementUrl(String ccjoinSettlementUrl) {

         this.ccjoinSettlementUrl = ccjoinSettlementUrl;

     }

     /**

      * ccjoinSettlementDriverClassName的取得

      *

      * @return String ccjoinSettlementDriverClassName

      */

     public String getCcjoinSettlementDriverClassName() {

         return ccjoinSettlementDriverClassName;

     }

     /**

      * ccjoinSettlementDriverClassName的设定

      *

      * @param ccjoinSettlementDriverClassName

      *            ccjoinSettlementDriverClassName

      */

     public void setCcjoinSettlementDriverClassName(String ccjoinSettlementDriverClassName) {

         this.ccjoinSettlementDriverClassName = ccjoinSettlementDriverClassName;

     }

     /**

      * ccjoinSettlementUsername的取得

      *

      * @return String ccjoinSettlementUsername

      */

     public String getCcjoinSettlementUsername() {

         return ccjoinSettlementUsername;

     }

     /**

      * ccjoinSettlementUsername的设定

      *

      * @param ccjoinSettlementUsername

      *            ccjoinSettlementUsername

      */

     public void setCcjoinSettlementUsername(String ccjoinSettlementUsername) {

         this.ccjoinSettlementUsername = ccjoinSettlementUsername;

     }

     /**

      * ccjoinSettlementPassword的取得

      *

      * @return String ccjoinSettlementPassword

      */

     public String getCcjoinSettlementPassword() {

         return ccjoinSettlementPassword;

     }

     /**

      * ccjoinSettlementPassword的设定

      *

      * @param ccjoinSettlementPassword

      *            ccjoinSettlementPassword

      */

     public void setCcjoinSettlementPassword(String ccjoinSettlementPassword) {

         this.ccjoinSettlementPassword = ccjoinSettlementPassword;

     }

     /**

      * ccjoinSettlementType的取得

      *

      * @return String ccjoinSettlementType

      */

     public String getCcjoinSettlementType() {

         return ccjoinSettlementType;

     }

     /**

      * ccjoinSettlementType的设定

      *

      * @param ccjoinSettlementType

      *            ccjoinSettlementType

      */

     public void setCcjoinSettlementType(String ccjoinSettlementType) {

         this.ccjoinSettlementType = ccjoinSettlementType;

     }

     /**

      * jmuMpUrl的取得

      *

      * @return String jmuMpUrl

      */

     public String getJmuMpUrl() {

         return jmuMpUrl;

     }

     /**

      * jmuMpUrl的设定

      *

      * @param jmuMpUrl

      *            jmuMpUrl

      */

     public void setJmuMpUrl(String jmuMpUrl) {

         this.jmuMpUrl = jmuMpUrl;

     }

     /**

      * jmuMpDriverClassName的取得

      *

      * @return String jmuMpDriverClassName

      */

     public String getJmuMpDriverClassName() {

         return jmuMpDriverClassName;

     }

     /**

      * jmuMpDriverClassName的设定

      *

      * @param jmuMpDriverClassName

      *            jmuMpDriverClassName

      */

     public void setJmuMpDriverClassName(String jmuMpDriverClassName) {

         this.jmuMpDriverClassName = jmuMpDriverClassName;

     }

     /**

      * jmuMpUsername的取得

      *

      * @return String jmuMpUsername

      */

     public String getJmuMpUsername() {

         return jmuMpUsername;

     }

     /**

      * jmuMpUsername的设定

      *

      * @param jmuMpUsername

      *            jmuMpUsername

      */

     public void setJmuMpUsername(String jmuMpUsername) {

         this.jmuMpUsername = jmuMpUsername;

     }

     /**

      * jmuMpPassword的取得

      *

      * @return String jmuMpPassword

      */

     public String getJmuMpPassword() {

         return jmuMpPassword;

     }

     /**

      * jmuMpPassword的设定

      *

      * @param jmuMpPassword

      *            jmuMpPassword

      */

     public void setJmuMpPassword(String jmuMpPassword) {

         this.jmuMpPassword = jmuMpPassword;

     }

 }

DataSourceConfigCcjoinSettlement.java（使用DBCP2连接池，优先级较低，有tomcat自带JDBC连接池时需要指定数据源类型type）

注意 @ConfigurationProperties注解不支持驼峰式

 package com.jmu.ccjoin.config;

 import javax.sql.DataSource;

 import org.apache.ibatis.session.SqlSessionFactory;

 import org.mybatis.spring.SqlSessionFactoryBean;

 import org.mybatis.spring.annotation.MapperScan;

 import org.springframework.beans.factory.annotation.Autowired;

 import org.springframework.beans.factory.annotation.Qualifier;

 import org.springframework.boot.context.properties.ConfigurationProperties;

 import org.springframework.boot.jdbc.DataSourceBuilder;

 import org.springframework.context.annotation.Bean;

 import org.springframework.context.annotation.Configuration;

 import org.springframework.context.annotation.Primary;

 import org.springframework.core.env.Environment;

 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

 import org.springframework.jdbc.datasource.DataSourceTransactionManager;

 @Configuration

 @MapperScan(basePackages = "com.jmu.ccjoin.dao.ccjoinSettlement", sqlSessionFactoryRef = "sqlSessionFactoryCcjoinSettlement")

 public class DataSourceConfigCcjoinSettlement {

     @Autowired

     private Environment env;

     @Bean(name = "ccjoinSettlement")

     @ConfigurationProperties(prefix = "spring.datasource.ccjoin-settlement")

     public DataSource dataSourceCcjoinSettlement() {

         // 使用DBCP2数据源(在配置文件配置所使用的数据源类型)

         return DataSourceBuilder.create().build();

     }

     @Bean(name = "sqlSessionFactoryCcjoinSettlement")

     public SqlSessionFactory createSqlSessionFactory(@Qualifier("ccjoinSettlement") DataSource ds) throws Exception {

         SqlSessionFactoryBean fb = new SqlSessionFactoryBean();

         fb.setDataSource(ds);

         fb.setTypeAliasesPackage(env.getProperty("spring.mybatis.typeAliasPackage"));

         fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("spring.mybatis.mapper.ccjoinSettlement")));

         return fb.getObject();

     }

     @Bean(name = "transactionManagerCcjoinSettlement")

     @Primary

     public DataSourceTransactionManager transactionManager(@Qualifier("ccjoinSettlement") DataSource ds) throws Exception {

         return new DataSourceTransactionManager(ds);

     }

 }

DataSourceConfigJmuMp.java（使用第三方数据源阿里连接池Druid）

 package com.jmu.ccjoin.config;

 import javax.sql.DataSource;

 import org.apache.ibatis.session.SqlSessionFactory;

 import org.mybatis.spring.SqlSessionFactoryBean;

 import org.mybatis.spring.annotation.MapperScan;

 import org.springframework.beans.factory.annotation.Autowired;

 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.env.Environment;

 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

 import org.springframework.jdbc.datasource.DataSourceTransactionManager;

 import com.alibaba.druid.pool.DruidDataSource;

 @Configuration

 @MapperScan(basePackages = "com.jmu.ccjoin.dao.jmuMp",sqlSessionFactoryRef="sqlSessionFactoryJmuMp" )

 public class DataSourceConfigJmuMp {

     @Autowired

     private ConfigProperties configProperties;

     @Autowired

     private Environment env;

     @Bean(name = "jmuMp")

     @ConfigurationProperties(prefix = "spring.datasource.jmu-mp")

     public DataSource dataSourceJmuMp() {

         // 使用Druid连接池

         DruidDataSource ds = new DruidDataSource();

         ds.setUrl(configProperties.getJmuMpUrl());

         ds.setUsername(configProperties.getJmuMpUsername());

         ds.setPassword(configProperties.getJmuMpPassword());

         return ds;

     }

     @Bean(name = "sqlSessionFactoryJmuMp")

     public SqlSessionFactory createSqlSessionFactory(@Qualifier("jmuMp") DataSource ds) throws Exception {

         SqlSessionFactoryBean fb = new SqlSessionFactoryBean();

         fb.setDataSource(ds);

         fb.setTypeAliasesPackage(env.getProperty("spring.mybatis.typeAliasPackage"));

         fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("spring.mybatis.mapper.jmuMp")));

         return fb.getObject();

     }

     @Bean(name = "transactionManagerJmuMp")

     @Primary

     public DataSourceTransactionManager transactionManager(@Qualifier("jmuMp") DataSource ds) throws Exception {

         return new DataSourceTransactionManager(ds);

     }

 }

DataSourceConfigJmuv3.java（使用Tomcat自带的的jdbc连接池）

 package com.jmu.ccjoin.config;

 import javax.sql.DataSource;

 import org.apache.ibatis.session.SqlSessionFactory;

 import org.mybatis.spring.SqlSessionFactoryBean;

 import org.mybatis.spring.annotation.MapperScan;

 import org.springframework.beans.factory.annotation.Autowired;

 import org.springframework.beans.factory.annotation.Qualifier;

 import org.springframework.boot.context.properties.ConfigurationProperties;

 import org.springframework.boot.jdbc.DataSourceBuilder;

 import org.springframework.context.annotation.Bean;

 import org.springframework.context.annotation.Configuration;

 import org.springframework.context.annotation.Primary;

 import org.springframework.core.env.Environment;

 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

 import org.springframework.jdbc.datasource.DataSourceTransactionManager;

 @Configuration

 @MapperScan(basePackages = "com.jmu.ccjoin.dao.jmuv3", sqlSessionFactoryRef = "sqlSessionFactoryJmuv3")

 public class DataSourceConfigJmuv3 {

     @Autowired

     private Environment env;

     @Bean(name = "jmuv3")

     @Primary

     @ConfigurationProperties(prefix = "spring.datasource.jmuv3")

     public DataSource dataSourceJmuv3() {

         // 使用JDBC数据源

         return DataSourceBuilder.create().build();

     }

     @Bean(name = "sqlSessionFactoryJmuv3")

     @Primary

     public SqlSessionFactory createSqlSessionFactory(@Qualifier("jmuv3") DataSource ds) throws Exception {

         SqlSessionFactoryBean fb = new SqlSessionFactoryBean();

         fb.setDataSource(ds);

         fb.setTypeAliasesPackage(env.getProperty("spring.mybatis.typeAliasPackage"));

         fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("spring.mybatis.mapper.jmuv3")));

         return fb.getObject();

     }

     @Bean(name = "transactionManagerJmuv3")

     @Primary

     public DataSourceTransactionManager transactionManager(@Qualifier("jmuv3") DataSource ds) throws Exception {

         return new DataSourceTransactionManager(ds);

     }

 }

Application.java

 package com.jmu.ccjoin;

 import org.springframework.boot.SpringApplication;

 import org.springframework.boot.autoconfigure.SpringBootApplication;

 import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

 import org.springframework.boot.builder.SpringApplicationBuilder;

 import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;

 @SpringBootApplication(exclude = DataSourceAutoConfiguration.class)

 public class Application extends SpringBootServletInitializer {

     public static void main(String[] args) {

         SpringApplication.run(Application.class, args);

     }

     @Override

     protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {

         return builder.sources(Application.class);

     }

 }

application.yml配置文件

 server:

   port: 20001

   servlet:

     path: /spring-boot

 spring:

   mybatis:

     typeAliasPackage: com.jmu.ccjoin.entity

     mapper:

       jmuv3: classpath:mybatis/mysql/jmuv3/*.xml

       ccjoinSettlement: classpath:mybatis/mysql/ccjoinSettlement/*.xml

       jmuMp: classpath:mybatis/mysql/jmuMp/*.xml

外部配置文件（路径：D:\var\opt\spring-boot-test\config.properties）

 # 数据源1配置

 spring.datasource.jmuv3.jdbc-url=jdbc:mysql://172.16.1.23:3306/jmuv3?useUnicode=true&characterEncoding=utf8

 spring.datasource.jmuv3.driver-class-name=com.mysql.jdbc.Driver

 spring.datasource.jmuv3.username=root

 spring.datasource.jmuv3.password=123.com

 #数据源1的连接池（使用默认即tomcat自带的JDBC连接池）

 # 数据源2配置

 spring.datasource.ccjoin-settlement.jdbc-url=jdbc:mysql://172.16.1.23:3306/ccjoin_settlement?useUnicode=true&characterEncoding=utf8

 spring.datasource.ccjoin-settlement.driver-class-name=com.mysql.jdbc.Driver

 spring.datasource.ccjoin-settlement.username=root

 spring.datasource.ccjoin-settlement.password=123.com

 #数据源2的连接池配置（使用DBCP2连接池，也是springboot支持的连接池之一）

 spring.datasource.ccjoin-settlement.type=org.apache.commons.dbcp2.BasicDataSource

 # 数据源3配置

 spring.datasource.jmu-mp.jdbc-url=jdbc:mysql://172.16.1.23:3306/jmu_mp?useUnicode=true&characterEncoding=utf8

 spring.datasource.jmu-mp.driver-class-name=com.mysql.jdbc.Driver

 spring.datasource.jmu-mp.username=root

 spring.datasource.jmu-mp.password=123.com

 #数据源3的连接池配置（使用第三方数据源，Druid）

 #连接池配置

 spring.datasource.max-wait-millis=10000

 spring.datasource.min-idle=5

 spring.datasource.initial-size=5

 spring.datasource.validation-query=SELECT x

 spring.datasource.connection-properties=characterEncoding=utf8

注意配置文件中配置项后面的空格

相关文章