springboot 数据库连接池

时间:2024-03-07 16:50:53

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连接池方式,以供参考