springboot数据库连接池的使用策略,在springboot官网中表示:
Production database connections can also be auto-configured using a
pooling DataSource. Here’s the algorithm for choosing a specific implementation:
We prefer the Tomcat pooling DataSource for its performance and concurrency, so if that is available we always choose it.
If HikariCP is available we will use it.
If Commons DBCP is available we will use it, but we don’t recommend it in production.
Lastly, if Commons DBCP2 is available we will use it.
If you use the spring-boot-starter-jdbc or spring-boot-starter-data-jpa ‘starter
POMs’ you will automatically get a dependency to tomcat-jdbc.
springboot会优先使用tomcat连接池,因为其性能和并发性很好,如果可用的话,将会优先使用。tomcat连接池,请查看: http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html
如果HikariCP可用,会选择使用 http://brettwooldridge.github.io/HikariCP/。
如果DBCP可用,会选择使用,但是不推荐在生产环境使用它。
最后,如果使用DBCP2,会选择使用
下面我列举一下部分关于springboot数据库连接池的使用例子,仅供参考和选择
首先介绍如何查看springboot的数据库连接池:
1 @SpringBootApplication 2 public class QianyidemoApplication implements CommandLineRunner { 3 @Autowired 4 DataSource dataSource; 5 public static void main(String[] args) { 6 SpringApplication.run(QianyidemoApplication.class, args); 7 } 8 @Override 9 public void run(String... args) throws Exception { 10 System.out.println("DATASOURCE = " + dataSource); 11 } 12 }
Console控制台打印出:
DATASOURCE = org.apache.tomcat.jdbc.pool.DataSource@5063621b
可以看到默认是采用的是tomcat.jdbc数据库连接池
springboot数据库连接池:HikariCP数据连接池,
首先加入依赖
1 <dependency> 2 <groupId>com.zaxxer</groupId> 3 <artifactId>HikariCP</artifactId> 4 </dependency>
排除tomcat数据库连接池
1 <dependency> 2 <groupId>org.springframework.boot</groupId> 3 <artifactId>spring-boot-starter-data-jpa</artifactId> 4 <exclusions> 5 <exclusion> 6 <groupId>org.apache.tomcat</groupId> 7 <artifactId>tomcat-jdbc</artifactId> 8 </exclusion> 9 </exclusions> 10 </dependency>
springboot数据库连接池dbcp,dbcp2
#数据源配置 spring.datasource.url=jdbc:mysql://localhost:3306/ssb_test spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.username=root spring.datasource.password=root #连接池配置 spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource spring.datasource.dbcp2.max-wait-millis=10000 spring.datasource.dbcp2.min-idle=5 spring.datasource.dbcp2.initial-size=5 spring.datasource.dbcp2.validation-query=SELECT x spring.datasource.dbcp2.connection-properties=characterEncoding=utf8 spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource:指定使用那个连接池,默认使用tomcate-jdbc连接池。
测试数据源代码
@RunWith(SpringRunner.class) @SpringBootTest public class DataSourceTests { @Autowired ApplicationContext applicationContext; @Autowired DataSourceProperties dataSourceProperties; @Test public void testDataSource() throws Exception { // 获取配置的数据源 DataSource dataSource = applicationContext.getBean(DataSource.class); // 查看配置数据源信息 System.out.println(dataSource); System.out.println(dataSource.getClass().getName()); System.out.println(dataSourceProperties); } }
springboot数据库连接池: c3p0:
引入的maven依赖:
<dependency> <groupId>c3p0</groupId> <artifactId>c3p0</artifactId> <version>0.9.1.2</version> </dependency>
c3p0的配置信息,写到application.properties配置文件中
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/ssb_test c3p0.user=${username} c3p0.password=${password} c3p0.driverClass=com.mysql.jdbc.Driver c3p0.minPoolSize=2 c3p0.maxPoolSize=10 c3p0.maxIdleTime=1800000 c3p0.acquireIncrement=3 c3p0.maxStatements=1000 c3p0.initialPoolSize=3 c3p0.idleConnectionTestPeriod=60 c3p0.acquireRetryAttempts=30 c3p0.acquireRetryDelay=1000 c3p0.breakAfterAcquireFailure=false c3p0.testConnectionOnCheckout=false
springboot配置c3p0数据源datasource:
@Configuration public class DatasourceConfiguration { @Bean(name = "dataSource") @Qualifier(value = "dataSource") @Primary @ConfigurationProperties(prefix = "c3p0") public DataSource dataSource() { return DataSourceBuilder.create().type(com.mchange.v2.c3p0.ComboPooledDataSource.class).build(); } }
springboot数据库连接池:Druid
pom
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.25</version> </dependency>
#驱动配置信息 spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.url = jdbc:mysql://localhost:3306/ssb_test spring.datasource.username = root spring.datasource.password = 123456 spring.datasource.driverClassName = com.mysql.jdbc.Driver #连接池的配置信息 spring.datasource.initialSize=5 spring.datasource.minIdle=5 spring.datasource.maxActive=20 spring.datasource.maxWait=60000 spring.datasource.timeBetweenEvictionRunsMillis=60000 spring.datasource.minEvictableIdleTimeMillis=300000 spring.datasource.validationQuery=SELECT 1 FROM DUAL spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false spring.datasource.poolPreparedStatements=true spring.datasource.maxPoolPreparedStatementPerConnectionSize=20 spring.datasource.filters=stat,wall,log4j spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 DruidDBConfig类被@Configuration标注,用作配置信息; DataSource对象被@Bean声明,为Spring容器所管理, @Primary表示这里定义的DataSource将覆盖其他来源的DataSource。 #初始化大小,最小,最大 spring.datasource.initialSize=5 spring.datasource.minIdle=5 spring.datasource.maxActive=20 #配置获取连接等待超时的时间 spring.datasource.maxWait=60000 #配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 spring.datasource.timeBetweenEvictionRunsMillis=60000 #配置一个连接在池中最小生存的时间,单位是毫秒 spring.datasource.minEvictableIdleTimeMillis=300000 spring.datasource.validationQuery=SELECT 1 FROM DUAL spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false #打开PSCache,并且指定每个连接上PSCache的大小 spring.datasource.poolPreparedStatements=true spring.datasource.maxPoolPreparedStatementPerConnectionSize=20 #配置监控统计拦截的filters,去掉后监控界面sql无法统计,\'wall\'用于防火墙 spring.datasource.filters=stat,wall,log4j #通过connectProperties属性来打开mergeSql功能;慢SQL记录 spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 #合并多个DruidDataSource的监控数据 spring.datasource.useGlobalDataSourceStat=true #需要注意的是:spring.datasource.type旧的spring boot版本是不能识别的。
以上列举dbcp/dbcp2,druid,c3p0,jdbc,hikari五种springboot连接池方式,以供参考