数据库连接池

发表于 分类于

数据库连接池

概念：数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。能明显提高对数据库操作的性能。

优点：

1.节约资源

2.高效

数据库连接池的原理

连接池基本的思想是在系统初始化的时候，将数据库连接作为对象存储在内存中，当用户需要访问数据库时，并非建立一个新的连接，而是从连接池中取出一个已建立的空闲连接对象。

使用完毕后，用户也并非将连接关闭，而是将连接放回连接池中，以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时，还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等，也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。

常见的数据库连接池介绍

C3P0

主要特性：1、编码的简单易用

缺点：C3P0 本身存在BUG，容易造成死锁

Druid

主要特性：

1、 强大的监控特性，通过Druid提供的监控功能，可以清楚知道连接池和SQL

a.监控SQL的执行时间、ResultSet持有时间、返回行数、更新行数、错误次数、错误堆栈信息;

b. SQL执行的耗时区间分布。什么是耗时区间分布呢？比如说，某个SQL执行了1000次，其中01毫秒区间50次，110毫秒800次，10100毫秒100次，1001000毫秒30次，1~10秒15次，10秒以上5次。通过耗时区间分布，能够非常清楚知道SQL的执行耗时情况。

c. 监控连接池的物理连接创建和销毁次数、逻辑连接的申请和关闭次数、非空等待次数、PSCache命中率等。

2、便于扩展。Druid提供了Filter-Chain模式的扩展API，可以自己编写Filter拦截JDBC中的任何方法，可以在上面做任何事情，比如说性能监控、SQL审计、用户名密码加密、日志等等。

HikariCP

主要特性：快速，简单，可靠，稳定性高，据说是性能最好是一个高性能的JDBC连接池。Spring Boot将在2.0版本中把HikariCP作为其默认的JDBC连接池。配置方便，默认就是它了。

HikariCP(待测试)

(CP:Connect Pool)

依赖

maven仓库： https://mvnrepository.com/artifact/com.zaxxer/HikariCP 

1
2
3
4
5
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

它是 Spring Boot2.0 后默认的数据库连接池，更具体的说，由 spring-boot-starter-jdbc 模块引入。

配置数据源

配置文件方式

配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver # 数据源驱动
    type: com.zaxxer.hikari.HikariDataSource # 数据源类型
    # 数据库地址，编译类型为utf-8
    url: mysql://localhost:5588/database?useUnicode=true&characterEncoding=UTF8&allowMultiQueries=true
    username: root # 数据库用户名
    password: root # 数据库密码
    hikari:
      pool-name: MyHikariCP # 连接池名称
      minimum-idle: 5 # 最小空闲连接数量
      maximum-pool-size: 10 # 池中最大连接数，包括闲置和使用中的连接，默认是10
      idle-timeout: 600000 # 连接允许在池中闲置的最长时间，默认600000毫秒（10分钟）
      max-lifetime: 1800000 # 池中连接最长生命周期，值0表示无限生命周期，默认1800000毫秒即30分钟
      auto-commit: true # 此属性控制从池返回的连接的默认自动提交行为,默认值：true
      connection-test-query: SELECT 1
      connection-timeout: 30000 # 等待来自池的连接的最大毫秒数
      connection-init-sql: null # 该属性设置一个SQL语句，在将每个新连接创建后，将其添加到池中之前执行该语句。
      validation-timeout: 5000
      transactionIsolation: null # 控制从池返回的连接的默认事务隔离级别
      metric-registry:
      health-check-registry:
      initialization-fail-timeout:
      driver-class-name:
      jdbc-url:
      username:
      password:

配置Mybatis扫描mapper的路径

1
2
3
4
5
6
@MapperScan(basePackages = "com.zhaolq.service.*.dao", sqlSessionTemplateRef = "pgsqlSqlSessionTemplate")
public class ServiceApplication {
	public static void main(String[] args) 
		SpringApplication.run(ServiceApplication.class, args);
	}
}

配置类+配置文件方式

配置类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
@Configuration
// 配置Mybatis mapper的扫描路径
@MapperScan("com.zhaolq.service.*.dao")
public class DataSourceConfiguration {
    @Value("${jdbc.driver}")
    private String jdbcDriver;

    @Value("${jdbc.url}")
    private String jdbcUrl;

    @Value("${jdbc.username}")
    private String jdbcUserName;

    @Value("${jdbc.password}")
    private String jdbcPassWord;

    /**
     * Create data source combo pooled data source
     *
     * @return the combo pooled data source
     * @throws PropertyVetoException property veto exception
     */
    @Bean(name = "dataSource")
    public DataSource getHikariDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(jdbcUrl); // 数据源
        config.setUsername(jdbcUserName); // 用户名
        config.setPassword(jdbcPassWord); // 密码
        config.setDriverClassName(jdbcDriver);
        // 关闭连接后不自动commit
        config.setAutoCommit(false);

        // 保持最小的连接数
        config.setMinimumIdle(3);
        // 连接池中允许的最大连接数，默认是10
        config.setMaximumPoolSize(10);
        // 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），默认:10分钟
        config.setIdleTimeout(600000);
        // 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 默认:30秒
        config.setConnectionTimeout(30000);
        // 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），默认:30分钟 1800000ms，建议设置比数据库超时时长少60秒
        config.setMaxLifetime(1800000);
        config.setConnectionTestQuery("select 1");

        HikariDataSource ds = new HikariDataSource(config);
        return ds;
    }

}

druid-spring-boot-starter

使用Spring boot封装的Druid依赖（支持配置文件和配置类两种方式）。

依赖

1
2
3
4
5
6
<!-- Spring boot封装的Druid数据源依赖 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>

配置数据源

配置文件方式

配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource # 统一配置为 druid
    druid:
      driver-class-name: org.postgresql.Driver  # 对应驱动
      url: jdbc:postgresql://127.0.0.1:5432/testDB # 数据库地址
      username: test
      password: test
      initial-size: 10 #初始化时建立物理连接的个数。 取值：产品 360 项目统一为 10
      min-idle: 10 #最小连接池数量。 取值产品 360 项目统一为 10
      max-active: 30 #最大连接池数量。 取值：产品 360 项目统一为 30
      time-between-eviction-runs-millis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      min-evictable-idle-time-millis: 300000 # 连接保持空闲而不被驱逐的最小时间，配置连接在池中的最小生存时间
      validation-query: select '1' from dual #用来检测连接是否有效的sql
      test-while-idle: true #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
      test-on-borrow: false #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
      test-on-return: false #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能

      # 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
      # 打开 PSCache，并且指定每个连接上 PSCache 的大小
      pool-prepared-statements: true
      max-open-prepared-statements: 20
      max-pool-prepared-statement-per-connection-size: 20 #要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true

      filters: stat # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，配置了之后，在项目ip:port/druid，就可以查看监控数据
      # 设置只有通过登录认证才可以访问
      stat-view-servlet:
        url-pattern: /druid/*
        reset-enable: false
        login-username: admin
        login-password: admin
        enabled: true
      web-stat-filter: # 排除一些静态资源，以提高效率
        url-pattern: /*
        exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
        enabled: true

配置Mybatis扫描mapper的路径

1
2
3
4
5
6
@MapperScan(basePackages = "com.zhaolq.service.*.dao", sqlSessionTemplateRef = "pgsqlSqlSessionTemplate")
public class ServiceApplication {
	public static void main(String[] args) 
		SpringApplication.run(ServiceApplication.class, args);
	}
}

druid内置的监控页面：http://ip:port/server.servlet.context-path/druid/login.html，账号、密码见配置文件。

需要配置监控访问路径不拦截。

配置类+配置文件方式

配置文件

1
2
3
4
5
6
7
8
9
10
# 数据库配置
pgsql:
  driver-class-name: org.postgresql.Driver # 对应驱动
  rl: jdbc:postgresql://127.0.0.1:5432/testDB # 数据库地址
  username: test
  password: test
# druid登录认证的账号密码
druid:
  login-username: admin
  login-password: admin

配置类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
@Configuration
@MapperScan(basePackages = "com.zhaolq.service.*.dao", sqlSessionTemplateRef = "pgsqlSqlSessionTemplate")
@Slf4j
public class PgsqlDataSourceConfiguration {
    @Value("${pgsql.driver}")
    private String jdbcDriver;

    @Value("${pgsql.url}")
    private String jdbcUrl;

    @Value("${pgsql.username}")
    private String jdbcUserName;

    @Value("${pgsql.password}")
    private String jdbcPassWord;

    @Value("${druid.login-username}")
    private String loginUsername;

    @Value("${druid.login-password}")
    private String loginPassword;

    /**
     * sql
     *
     * @return javax.sql.DataSource
     * @throws SQLException
     */
    @Bean(name = "pgsqlDataSource")
    public DataSource createDataSource() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(jdbcDriver);
        dataSource.setUrl(jdbcUrl);
        dataSource.setUsername(jdbcUserName);
        dataSource.setPassword(jdbcPassWord);

        // 初始化时建立物理连接的个数。 取值：产品 360 项目统一为 10
        dataSource.setInitialSize(10);
        // 最小连接池数量。 取值产品 360 项目统一为 10
        dataSource.setMinIdle(10);
        // 最大连接池数量。 取值：产品 360 项目统一为 30
        dataSource.setMaxActive(30);
        // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        // 连接保持空闲而不被驱逐的最小时间，配置连接在池中的最小生存时间
        dataSource.setMinEvictableIdleTimeMillis(300000);

        // 用来检测连接是否有效的sql
        dataSource.setValidationQuery("select '1'");
        // 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        dataSource.setTestWhileIdle(true);
        // 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
        dataSource.setTestOnBorrow(false);
        // 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
        dataSource.setTestOnReturn(false);

        dataSource.setPoolPreparedStatements(false);
        dataSource.setMaxOpenPreparedStatements(10);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(10);

        // 配置监控统计拦截的filters，去掉后监控界面sql无法统计，配置了之后，在项目ip:port/druid,就可以查看监控数据
        dataSource.setFilters("stat");

        return dataSource;
    }

    /**
     * 配置，设置只有通过登录认证才可以访问
     *
     * @return ServletRegistrationBean ServletRegistrationBean
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),
                "/druid/*");
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        servletRegistrationBean.addInitParameter("loginUsername", loginUsername);
        servletRegistrationBean.addInitParameter("loginPassword", loginPassword);
        servletRegistrationBean.addInitParameter("ehabled", "true");
        return servletRegistrationBean;
    }

    /**
     * 排除一些静态资源，以提高效率
     *
     * @return FilterRegistrationBean FilterRegistrationBean
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*,/jdbc/*");
        filterRegistrationBean.addInitParameter("ehabled", "true");
        return filterRegistrationBean;
    }
}

注：

避免配置文件过长，除了数据库连接，其他的配置全写死在配置类中。

单数据源使用配置文件方式即可。如果是多数据源，就要用配置类方式。

druid

druid 和 druid-spring-boot-starter

1）druid-spring-boot-starter只是在druid基础上进行了一次封装，专门用来整合spring-boot项目，两者配置都相同；

2）使用druid，需要手动编写配置类并注入到bean中，并加载对应的配置参数。druid-spring-boot-starter 只需要在配置文件中配置，就可实现数据库连接池的配置。

配置方式的选择

1）两种都可用的情况下，druid-spring-boot-starter 更方便，不用编写配置类。

2）druid-spring-boot-starter 由于未入库，所以开源扫描不通过，可以挂靠到其它。因此应选择 druid 依赖。

依赖

1
2
3
4
5
6
<!-- 使用阿里的原生druid数据源依赖 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>

配置数据源(配置文件+配置类)

配置文件：只需要配置数据库连接相关参数，其他写死在配置类即可。

1
2
3
4
5
6
7
8
9
10
# 数据库配置
pgsql:
  driver-class-name: org.postgresql.Driver # 对应驱动
  rl: jdbc:postgresql://127.0.0.1:5432/testDB # 数据库地址
  username: test
  password: test
# druid登录认证的账号密码
druid:
  login-username: admin
  login-password: admin

配置类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
@Configuration
@MapperScan(basePackages = "com.zhaolq.service.*.dao", sqlSessionTemplateRef = "pgsqlSqlSessionTemplate")
@Slf4j
public class PgsqlDataSourceConfiguration {
    @Value("${pgsql.driver}")
    private String jdbcDriver;

    @Value("${pgsql.url}")
    private String jdbcUrl;

    @Value("${pgsql.username}")
    private String jdbcUserName;

    @Value("${pgsql.password}")
    private String jdbcPassWord;

    @Value("${druid.login-username}")
    private String loginUsername;

    @Value("${druid.login-password}")
    private String loginPassword;

    /**
     * sql
     *
     * @return javax.sql.DataSource
     * @throws SQLException
     */
    @Bean(name = "pgsqlDataSource")
    public DataSource createDataSource() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(jdbcDriver);
        dataSource.setUrl(jdbcUrl);
        dataSource.setUsername(jdbcUserName);
        dataSource.setPassword(jdbcPassWord);

        // 初始化时建立物理连接的个数。 取值：产品 360 项目统一为 10
        dataSource.setInitialSize(10);
        // 最小连接池数量。 取值产品 360 项目统一为 10
        dataSource.setMinIdle(10);
        // 最大连接池数量。 取值：产品 360 项目统一为 30
        dataSource.setMaxActive(30);
        // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        // 连接保持空闲而不被驱逐的最小时间，配置连接在池中的最小生存时间
        dataSource.setMinEvictableIdleTimeMillis(300000);

        // 用来检测连接是否有效的sql
        dataSource.setValidationQuery("select '1'");
        // 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        dataSource.setTestWhileIdle(true);
        // 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
        dataSource.setTestOnBorrow(false);
        // 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
        dataSource.setTestOnReturn(false);

        dataSource.setPoolPreparedStatements(false);
        dataSource.setMaxOpenPreparedStatements(10);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(10);

        // 配置监控统计拦截的filters，去掉后监控界面sql无法统计，配置了之后，在项目ip:port/druid,就可以查看监控数据
        dataSource.setFilters("stat");

        return dataSource;
    }

    /**
     * 配置，设置只有通过登录认证才可以访问
     *
     * @return ServletRegistrationBean ServletRegistrationBean
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),
                "/druid/*");
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        servletRegistrationBean.addInitParameter("loginUsername", loginUsername);
        servletRegistrationBean.addInitParameter("loginPassword", loginPassword);
        servletRegistrationBean.addInitParameter("ehabled", "true");
        return servletRegistrationBean;
    }

    /**
     * 排除一些静态资源，以提高效率
     *
     * @return FilterRegistrationBean FilterRegistrationBean
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*,/jdbc/*");
        filterRegistrationBean.addInitParameter("ehabled", "true");
        return filterRegistrationBean;
    }

    /**
     * pgsqlSqlSessionFactory
     *
     * @param dataSource dataSource
     * @return org.apache.ibatis.session.SqlSessionFactory
     * @throws IOException
     */
    @Bean(name = "pgsqlSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("pgsqlDataSource") DataSource dataSource) throws IOException {
        SqlSessionFactory sql = null;
        try {
            SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
            factoryBean.setDataSource(dataSource);
            factoryBean.setMapperLocations(
                    new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*/*.xml"));

            Properties properties = new Properties();
            properties.setProperty("helperDialect", "postgresql");
            properties.setProperty("offsetAsPageNum", "true");
            properties.setProperty("rowBoundsWithCount", "true");
            properties.setProperty("reasonable", "true");
            properties.setProperty("supportMethodsArguments", "true");
            properties.setProperty("params", "pageNum=pageNumKey;pageSize=pageSizeKey;");
            // 分页插件
            Interceptor interceptor = new PageInterceptor();
            interceptor.setProperties(properties);
            factoryBean.setPlugins(new Interceptor[]{interceptor});
            // 支持驼峰
            factoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);

            sql = factoryBean.getObject();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return sql;
    }

    /**
     * sql
     *
     * @param dataSource dataSource
     * @return org.springframework.jdbc.datasource.DataSourceTransactionManager
     */
    @Bean(name = "pgsqlTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("pgsqlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * sql
     *
     * @param sqlSessionFactory sqlSessionFactory
     * @return org.mybatis.spring.SqlSessionTemplate
     */
    @Bean(name = "pgsqlSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(
            @Qualifier("pgsqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

需要配置监控访问路径不拦截