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

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

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

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

即自动优先级tomcat>HikariCP>DBCP>DBCP2（想要使用后面的连接池需要指定类型type：如：join-settlement.type=mons.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

首先：目录结构如下

pom.xml如下

1 <?xml version="1.0" encoding="UTF-8"?> 2 <project xmlns="/POM/4.0.0" xmlns:xsi="/2001/XMLSchema-instance" 3xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.0.xsd"> 4<modelVersion>4.0.0</modelVersion> 5 6<groupId>join</groupId> 7<artifactId>spring-boot</artifactId> 8<version>0.0.1-SNAPSHOT</version> 9<packaging>war</packaging> 10 11<name>spring-boot</name> 12<description>spring-boot</description> 13 14<parent> 15 <groupId>org.springframework.boot</groupId> 16 <artifactId>spring-boot-starter-parent</artifactId> 17 <version>2.0.4.RELEASE</version> 18 <relativePath /> <!-- lookup parent from repository --> 19</parent> 20 21<properties> 22 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> 23 <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> 24 <java.version>1.8</java.version> 25</properties> 26 27<dependencies> 28 <dependency> 29 <groupId>org.springframework.boot</groupId> 30 <artifactId>spring-boot-starter-web</artifactId> 31 </dependency> 32 33 <!-- tomcat自带jdbc连接池 --> 34 <dependency> 35 <groupId>org.springframework.boot</groupId> 36 <artifactId>spring-boot-starter-tomcat</artifactId> 37 <!-- <scope>provided</scope> --> 38 </dependency> 39 40 <dependency> 41 <groupId>org.mybatis.spring.boot</groupId> 42 <artifactId>mybatis-spring-boot-starter</artifactId> 43 <version>1.3.2</version> 44 </dependency> 45 46 <dependency> 47 <groupId>mysql</groupId> 48 <artifactId>mysql-connector-java</artifactId> 49 <scope>runtime</scope> 50 </dependency> 51 52 <dependency> 53 <groupId>org.springframework.boot</groupId> 54 <artifactId>spring-boot-starter-jdbc</artifactId> 55 </dependency> 5657 <!-- dbcp2数据库连接池 --> 58 <dependency> 59 <groupId>mons</groupId> 60 <artifactId>commons-dbcp2</artifactId> 61 </dependency> 62 63 <!-- dbcp数据库连接池 --> 64 <dependency> 65 <groupId>commons-dbcp</groupId> 66 <artifactId>commons-dbcp</artifactId> 67 <version>1.4</version> 68 </dependency> 69 70 <!-- druid数据库连接池 --> 71 <dependency> 72 <groupId>com.alibaba</groupId> 73 <artifactId>druid</artifactId> 74 <version>1.1.10</version> 75 </dependency> 76 77 <dependency> 78 <groupId>org.springframework.boot</groupId> 79 <artifactId>spring-boot-starter-test</artifactId> 80 <scope>test</scope> 81 </dependency> 82 <dependency> 83 <groupId>org.springframework.boot</groupId> 84 <artifactId>spring-boot-configuration-processor</artifactId> 85 <optional>false</optional> 86 </dependency> 87</dependencies> 88 89<build> 90 <finalName>spring-boot</finalName> 91 <plugins> 92 <plugin> 93 <groupId>org.springframework.boot</groupId> 94 <artifactId>spring-boot-maven-plugin</artifactId> 95 <executions> 96 <execution> 97<goals> 98 <goal>repackage</goal> 99</goals>100 </execution>101 </executions>102 </plugin>103 </plugins>104</build>105 106 </project>

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

1 package join.config; 2 3 import org.springframework.beans.factory.annotation.Value; 4 import org.springframework.context.annotation.PropertySource; 5 import org.ponent; 6 7 @Component 8 @PropertySource("file:/var/opt/spring-boot-test/config.properties") 9 public class ConfigProperties { 10 11@Value("${spring.datasource.jmuv3.jdbc-url}") 12private String jmuv3Url; 13 14@Value("${spring.datasource.jmuv3.driver-class-name}") 15private String jmuv3DriverClassName; 16 17@Value("${spring.datasource.jmuv3.username}") 18private String jmuv3Username; 19 20@Value("${spring.datasource.jmuv3.password}") 21private String jmuv3Password; 22 23@Value("${join-settlement.jdbc-url}") 24private String ccjoinSettlementUrl; 25 26@Value("${join-settlement.driver-class-name}") 27private String ccjoinSettlementDriverClassName; 28 29@Value("${join-settlement.username}") 30private String ccjoinSettlementUsername; 31 32@Value("${join-settlement.password}") 33private String ccjoinSettlementPassword; 34 35@Value("${join-settlement.type}") 36private String ccjoinSettlementType; 37 38@Value("${spring.datasource.jmu-mp.jdbc-url}") 39private String jmuMpUrl; 40 41@Value("${spring.datasource.jmu-mp.driver-class-name}") 42private String jmuMpDriverClassName; 43 44@Value("${spring.datasource.jmu-mp.username}") 45private String jmuMpUsername; 46 47@Value("${spring.datasource.jmu-mp.password}") 48private String jmuMpPassword; 49 50/** 51* jmuv3Url的取得 52* 53* @return String jmuv3Url 54*/ 55public String getJmuv3Url() { 56 return jmuv3Url; 57} 58 59/** 60* jmuv3Url的设定 61* 62* @param jmuv3Url 63* jmuv3Url 64*/ 65public void setJmuv3Url(String jmuv3Url) { 66 this.jmuv3Url = jmuv3Url; 67} 68 69/** 70* jmuv3DriverClassName的取得 71* 72* @return String jmuv3DriverClassName 73*/ 74public String getJmuv3DriverClassName() { 75 return jmuv3DriverClassName; 76} 77 78/** 79* jmuv3DriverClassName的设定 80* 81* @param jmuv3DriverClassName 82* jmuv3DriverClassName 83*/ 84public void setJmuv3DriverClassName(String jmuv3DriverClassName) { 85 this.jmuv3DriverClassName = jmuv3DriverClassName; 86} 87 88/** 89* jmuv3Username的取得 90* 91* @return String jmuv3Username 92*/ 93public String getJmuv3Username() { 94 return jmuv3Username; 95} 96 97/** 98* jmuv3Username的设定 99* 100* @param jmuv3Username101* jmuv3Username102*/103public void setJmuv3Username(String jmuv3Username) {104 this.jmuv3Username = jmuv3Username;105}106 107/**108* jmuv3Password的取得109* 110* @return String jmuv3Password111*/112public String getJmuv3Password() {113 return jmuv3Password;114}115 116/**117* jmuv3Password的设定118* 119* @param jmuv3Password120* jmuv3Password121*/122public void setJmuv3Password(String jmuv3Password) {123 this.jmuv3Password = jmuv3Password;124}125 126/**127* ccjoinSettlementUrl的取得128* 129* @return String ccjoinSettlementUrl130*/131public String getCcjoinSettlementUrl() {132 return ccjoinSettlementUrl;133}134 135/**136* ccjoinSettlementUrl的设定137* 138* @param ccjoinSettlementUrl139* ccjoinSettlementUrl140*/141public void setCcjoinSettlementUrl(String ccjoinSettlementUrl) {142 joinSettlementUrl = ccjoinSettlementUrl;143}144 145/**146* ccjoinSettlementDriverClassName的取得147* 148* @return String ccjoinSettlementDriverClassName149*/150public String getCcjoinSettlementDriverClassName() {151 return ccjoinSettlementDriverClassName;152}153 154/**155* ccjoinSettlementDriverClassName的设定156* 157* @param ccjoinSettlementDriverClassName158* ccjoinSettlementDriverClassName159*/160public void setCcjoinSettlementDriverClassName(String ccjoinSettlementDriverClassName) {161 joinSettlementDriverClassName = ccjoinSettlementDriverClassName;162}163 164/**165* ccjoinSettlementUsername的取得166* 167* @return String ccjoinSettlementUsername168*/169public String getCcjoinSettlementUsername() {170 return ccjoinSettlementUsername;171}172 173/**174* ccjoinSettlementUsername的设定175* 176* @param ccjoinSettlementUsername177* ccjoinSettlementUsername178*/179public void setCcjoinSettlementUsername(String ccjoinSettlementUsername) {180 joinSettlementUsername = ccjoinSettlementUsername;181}182 183/**184* ccjoinSettlementPassword的取得185* 186* @return String ccjoinSettlementPassword187*/188public String getCcjoinSettlementPassword() {189 return ccjoinSettlementPassword;190}191 192/**193* ccjoinSettlementPassword的设定194* 195* @param ccjoinSettlementPassword196* ccjoinSettlementPassword197*/198public void setCcjoinSettlementPassword(String ccjoinSettlementPassword) {199 joinSettlementPassword = ccjoinSettlementPassword;200}201 202/**203* ccjoinSettlementType的取得204* 205* @return String ccjoinSettlementType206*/207public String getCcjoinSettlementType() {208 return ccjoinSettlementType;209}210 211/**212* ccjoinSettlementType的设定213* 214* @param ccjoinSettlementType215* ccjoinSettlementType216*/217public void setCcjoinSettlementType(String ccjoinSettlementType) {218 joinSettlementType = ccjoinSettlementType;219}220 221/**222* jmuMpUrl的取得223* 224* @return String jmuMpUrl225*/226public String getJmuMpUrl() {227 return jmuMpUrl;228}229 230/**231* jmuMpUrl的设定232* 233* @param jmuMpUrl234* jmuMpUrl235*/236public void setJmuMpUrl(String jmuMpUrl) {237 this.jmuMpUrl = jmuMpUrl;238}239 240/**241* jmuMpDriverClassName的取得242* 243* @return String jmuMpDriverClassName244*/245public String getJmuMpDriverClassName() {246 return jmuMpDriverClassName;247}248 249/**250* jmuMpDriverClassName的设定251* 252* @param jmuMpDriverClassName253* jmuMpDriverClassName254*/255public void setJmuMpDriverClassName(String jmuMpDriverClassName) {256 this.jmuMpDriverClassName = jmuMpDriverClassName;257}258 259/**260* jmuMpUsername的取得261* 262* @return String jmuMpUsername263*/264public String getJmuMpUsername() {265 return jmuMpUsername;266}267 268/**269* jmuMpUsername的设定270* 271* @param jmuMpUsername272* jmuMpUsername273*/274public void setJmuMpUsername(String jmuMpUsername) {275 this.jmuMpUsername = jmuMpUsername;276}277 278/**279* jmuMpPassword的取得280* 281* @return String jmuMpPassword282*/283public String getJmuMpPassword() {284 return jmuMpPassword;285}286 287/**288* jmuMpPassword的设定289* 290* @param jmuMpPassword291* jmuMpPassword292*/293public void setJmuMpPassword(String jmuMpPassword) {294 this.jmuMpPassword = jmuMpPassword;295}296 297 }

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

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

1 package join.config; 2 3 import javax.sql.DataSource; 4 5 import org.apache.ibatis.session.SqlSessionFactory; 6 import org.mybatis.spring.SqlSessionFactoryBean; 7 import org.mybatis.spring.annotation.MapperScan; 8 import org.springframework.beans.factory.annotation.Autowired; 9 import org.springframework.beans.factory.annotation.Qualifier;10 import org.springframework.boot.context.properties.ConfigurationProperties;11 import org.springframework.boot.jdbc.DataSourceBuilder;12 import org.springframework.context.annotation.Bean;13 import org.springframework.context.annotation.Configuration;14 import org.springframework.context.annotation.Primary;15 import org.springframework.core.env.Environment;16 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;17 import org.springframework.jdbc.datasource.DataSourceTransactionManager;18 19 @Configuration20 @MapperScan(basePackages = "joinSettlement", sqlSessionFactoryRef = "sqlSessionFactoryCcjoinSettlement")21 public class DataSourceConfigCcjoinSettlement {22 23@Autowired24private Environment env;25 26@Bean(name = "ccjoinSettlement")27@ConfigurationProperties(prefix = "join-settlement")28public DataSource dataSourceCcjoinSettlement() {29 // 使用DBCP2数据源(在配置文件配置所使用的数据源类型)30 return DataSourceBuilder.create().build();31}32 33@Bean(name = "sqlSessionFactoryCcjoinSettlement")34public SqlSessionFactory createSqlSessionFactory(@Qualifier("ccjoinSettlement") DataSource ds) throws Exception {35 36 SqlSessionFactoryBean fb = new SqlSessionFactoryBean();37 fb.setDataSource(ds);38 39 fb.setTypeAliasesPackage(env.getProperty("spring.mybatis.typeAliasPackage"));40 fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("spring.joinSettlement")));41 42 return fb.getObject();43}44 45@Bean(name = "transactionManagerCcjoinSettlement")46@Primary47public DataSourceTransactionManager transactionManager(@Qualifier("ccjoinSettlement") DataSource ds) throws Exception {48 return new DataSourceTransactionManager(ds);49}50 }

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

1 package join.config; 2 3 import javax.sql.DataSource; 4 5 import org.apache.ibatis.session.SqlSessionFactory; 6 import org.mybatis.spring.SqlSessionFactoryBean; 7 import org.mybatis.spring.annotation.MapperScan; 8 import org.springframework.beans.factory.annotation.Autowired; 9 import org.springframework.beans.factory.annotation.Qualifier;10 import org.springframework.boot.context.properties.ConfigurationProperties;11 import org.springframework.context.annotation.Bean;12 import org.springframework.context.annotation.Configuration;13 import org.springframework.context.annotation.Primary;14 import org.springframework.core.env.Environment;15 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;16 import org.springframework.jdbc.datasource.DataSourceTransactionManager;17 18 import com.alibaba.druid.pool.DruidDataSource;19 20 @Configuration21 @MapperScan(basePackages = "join.dao.jmuMp",sqlSessionFactoryRef="sqlSessionFactoryJmuMp" )22 public class DataSourceConfigJmuMp {23 24@Autowired25private ConfigProperties configProperties;26 27@Autowired28private Environment env;29 30@Bean(name = "jmuMp")31@ConfigurationProperties(prefix = "spring.datasource.jmu-mp")32public DataSource dataSourceJmuMp() {33 34 // 使用Druid连接池35 DruidDataSource ds = new DruidDataSource();36 ds.setUrl(configProperties.getJmuMpUrl());37 ds.setUsername(configProperties.getJmuMpUsername());38 ds.setPassword(configProperties.getJmuMpPassword());39 40 return ds;41}42 43@Bean(name = "sqlSessionFactoryJmuMp")44public SqlSessionFactory createSqlSessionFactory(@Qualifier("jmuMp") DataSource ds) throws Exception {45 46 SqlSessionFactoryBean fb = new SqlSessionFactoryBean();47 fb.setDataSource(ds);48 49 fb.setTypeAliasesPackage(env.getProperty("spring.mybatis.typeAliasPackage"));50 fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("spring.mybatis.mapper.jmuMp")));51 52 return fb.getObject();53}54 55@Bean(name = "transactionManagerJmuMp")56@Primary57public DataSourceTransactionManager transactionManager(@Qualifier("jmuMp") DataSource ds) throws Exception {58 return new DataSourceTransactionManager(ds);59}60 }

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

1 package join.config; 2 3 import javax.sql.DataSource; 4 5 import org.apache.ibatis.session.SqlSessionFactory; 6 import org.mybatis.spring.SqlSessionFactoryBean; 7 import org.mybatis.spring.annotation.MapperScan; 8 import org.springframework.beans.factory.annotation.Autowired; 9 import org.springframework.beans.factory.annotation.Qualifier;10 import org.springframework.boot.context.properties.ConfigurationProperties;11 import org.springframework.boot.jdbc.DataSourceBuilder;12 import org.springframework.context.annotation.Bean;13 import org.springframework.context.annotation.Configuration;14 import org.springframework.context.annotation.Primary;15 import org.springframework.core.env.Environment;16 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;17 import org.springframework.jdbc.datasource.DataSourceTransactionManager;18 19 @Configuration20 @MapperScan(basePackages = "join.dao.jmuv3", sqlSessionFactoryRef = "sqlSessionFactoryJmuv3")21 public class DataSourceConfigJmuv3 {22 23@Autowired24private Environment env;25 26@Bean(name = "jmuv3")27@Primary28@ConfigurationProperties(prefix = "spring.datasource.jmuv3")29public DataSource dataSourceJmuv3() {30 // 使用JDBC数据源31 return DataSourceBuilder.create().build();32}33 34@Bean(name = "sqlSessionFactoryJmuv3")35@Primary36public SqlSessionFactory createSqlSessionFactory(@Qualifier("jmuv3") DataSource ds) throws Exception {37 38 SqlSessionFactoryBean fb = new SqlSessionFactoryBean();39 fb.setDataSource(ds);40 41 fb.setTypeAliasesPackage(env.getProperty("spring.mybatis.typeAliasPackage"));42 fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("spring.mybatis.mapper.jmuv3")));43 44 return fb.getObject();45}46 47@Bean(name = "transactionManagerJmuv3")48@Primary49public DataSourceTransactionManager transactionManager(@Qualifier("jmuv3") DataSource ds) throws Exception {50 return new DataSourceTransactionManager(ds);51}52 }

Application.java

1 package join; 2 3 import org.springframework.boot.SpringApplication; 4 import org.springframework.boot.autoconfigure.SpringBootApplication; 5 import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; 6 import org.springframework.boot.builder.SpringApplicationBuilder; 7 import org.springframework.boot.web.servlet.support.SpringBootServletInitializer; 8 9 @SpringBootApplication(exclude = DataSourceAutoConfiguration.class)10 public class Application extends SpringBootServletInitializer {11 12public static void main(String[] args) {13 SpringApplication.run(Application.class, args);14}15 16@Override17protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {18 return builder.sources(Application.class);19}20 }

application.yml配置文件

1 server: 2 port: 20001 3 servlet: 4path: /spring-boot 5 6 spring: 7 mybatis: 8typeAliasPackage: join.entity 9mapper:10 jmuv3: classpath:mybatis/mysql/jmuv3/*.xml11 ccjoinSettlement: classpath:mybatis/mysql/ccjoinSettlement/*.xml 12 jmuMp: classpath:mybatis/mysql/jmuMp/*.xml13 14

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

1 # 数据源1配置 2 spring.datasource.jmuv3.jdbc-url=jdbc:mysql://172.16.1.23:3306/jmuv3?useUnicode=true&characterEncoding=utf8 3 spring.datasource.jmuv3.driver-class-name=com.mysql.jdbc.Driver 4 spring.datasource.jmuv3.username=root 5 spring.datasource.jmuv3.password= 6 #数据源1的连接池（使用默认即tomcat自带的JDBC连接池） 7 8 # 数据源2配置 9 join-settlement.jdbc-url=jdbc:mysql://172.16.1.23:3306/ccjoin_settlement?useUnicode=true&characterEncoding=utf810 join-settlement.driver-class-name=com.mysql.jdbc.Driver11 join-settlement.username=root12 join-settlement.password=13 #数据源2的连接池配置（使用DBCP2连接池，也是springboot支持的连接池之一）14 join-settlement.type=mons.dbcp2.BasicDataSource15 16 # 数据源3配置 17 spring.datasource.jmu-mp.jdbc-url=jdbc:mysql://172.16.1.23:3306/jmu_mp?useUnicode=true&characterEncoding=utf818 spring.datasource.jmu-mp.driver-class-name=com.mysql.jdbc.Driver19 spring.datasource.jmu-mp.username=root20 spring.datasource.jmu-mp.password=21 #数据源3的连接池配置（使用第三方数据源，Druid）22 23 24 #连接池配置25 spring.datasource.max-wait-millis=10000 26 spring.datasource.min-idle=5 27 spring.datasource.initial-size=5 28 spring.datasource.validation-query=SELECT x 29 spring.datasource.connection-properties=characterEncoding=utf8

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