1 介绍

1.1数据库连接池介绍

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是在重新建立一个，通过数据库连接池能明显提高对数据库操作的性能。
在Java应用程序中，常用的数据库连接池有DBCP、C3P0、Proxool、Druid等

1.2 数据库连接池性能比较

参考：https://blog.csdn.net/u011389515/article/details/76578129

1.3 Druid介绍

Druid 是阿里开源的一个JDBC应用组件，提供了非常优秀的数据库监控和扩展功能。
网址：Druid：https://github.com/alibaba/druid/

2集成

Springboot 默认数据库连接池 hikari

2.1加入依赖

  1. <!--druid-->
  2. <dependency>
  3.   <groupId>com.alibaba</groupId>
  4.   <artifactId>druid-spring-boot-starter</artifactId>
  5.   <version>1.2.8</version>
  6. </dependency>

2.2 添加配置

修改配置文件，将原有的数据源配置替换成Druid数据源并配置数据源相关参数。

  1. spring:
  2. datasource:
  3. name: druidDataSource
  4. type: com.alibaba.druid.pool.DruidDataSource
  5. druid:
  6. # JDBC 配置
  7. driver-class-name: com.mysql.jdbc.Driver
  8. url: jdbc:mysql://localhost:3306/mybatis_plus_demo?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8
  9. username: root
  10. password: root
  12. filters: stat,wall
  13. #      filters: stat,wall,log4j,config
  14. #连接池配置
  15. max-active: 100
  16. initial-size: 1
  17. max-wait: 60000
  18. min-idle: 1
  19. time-between-eviction-runs-millis: 60000
  20. min-evictable-idle-time-millis: 300000
  21. validation-query: select 'x'
  22. test-while-idle: true
  23. test-on-borrow: false
  24. test-on-return: false
  25. pool-prepared-statements: true
  26. max-open-prepared-statements: 50
  27.       max-pool-prepared-statement-per-connection-size: 20

2.3 自定义属性配置

如果需要通过定制的配置文件对Druid进行自定义属性配置，添加配置类如下：
edu.xja.config 包下新建一个DruidDataSourceProperties 类

  1. /**
  2. * 数据源属性
  3. */
  4. @ConfigurationProperties(prefix = "spring.datasource.druid")
  5. public class DruidDataSourceProperties {
  7.     // jdbc
  8.     private String driverClassName;
  9.     private String url;
  10.     private String username;
  11.     private String password;
  12.     // jdbc connection pool
  13.     private int initialSize;
  14.     private int minIdle;
  15.     private int maxActive = 100;
  16.     private long maxWait;
  17.     private long timeBetweenEvictionRunsMillis;
  18.     private long minEvictableIdleTimeMillis;
  19.     private String validationQuery;
  20.     private boolean testWhileIdle;
  21.     private boolean testOnBorrow;
  22.     private boolean testOnReturn;
  23.     private boolean poolPreparedStatements;
  24.     private int maxPoolPreparedStatementPerConnectionSize;
  25.     // filter
  26.     private String filters;
  28.     public int getInitialSize() {
  29.         return initialSize;
  30.     }
  32.     public void setInitialSize(int initialSize) {
  33.         this.initialSize = initialSize;
  34.     }
  36.     public int getMinIdle() {
  37.         return minIdle;
  38.     }
  40.     public void setMinIdle(int minIdle) {
  41.         this.minIdle = minIdle;
  42.     }
  44.     public int getMaxActive() {
  45.         return maxActive;
  46.     }
  48.     public void setMaxActive(int maxActive) {
  49.         this.maxActive = maxActive;
  50.     }
  52.     public long getMaxWait() {
  53.         return maxWait;
  54.     }
  56.     public void setMaxWait(long maxWait) {
  57.         this.maxWait = maxWait;
  58.     }
  60.     public long getTimeBetweenEvictionRunsMillis() {
  61.         return timeBetweenEvictionRunsMillis;
  62.     }
  64.     public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
  65.         this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
  66.     }
  68.     public long getMinEvictableIdleTimeMillis() {
  69.         return minEvictableIdleTimeMillis;
  70.     }
  72.     public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {
  73.         this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
  74.     }
  76.     public String getValidationQuery() {
  77.         return validationQuery;
  78.     }
  80.     public void setValidationQuery(String validationQuery) {
  81.         this.validationQuery = validationQuery;
  82.     }
  84.     public boolean isTestWhileIdle() {
  85.         return testWhileIdle;
  86.     }
  88.     public void setTestWhileIdle(boolean testWhileIdle) {
  89.         this.testWhileIdle = testWhileIdle;
  90.     }
  92.     public boolean isTestOnBorrow() {
  93.         return testOnBorrow;
  94.     }
  96.     public void setTestOnBorrow(boolean testOnBorrow) {
  97.         this.testOnBorrow = testOnBorrow;
  98.     }
  100.     public boolean isTestOnReturn() {
  101.         return testOnReturn;
  102.     }
  104.     public void setTestOnReturn(boolean testOnReturn) {
  105.         this.testOnReturn = testOnReturn;
  106.     }
  108.     public boolean isPoolPreparedStatements() {
  109.         return poolPreparedStatements;
  110.     }
  112.     public void setPoolPreparedStatements(boolean poolPreparedStatements) {
  113.         this.poolPreparedStatements = poolPreparedStatements;
  114.     }
  116.     public int getMaxPoolPreparedStatementPerConnectionSize() {
  117.         return maxPoolPreparedStatementPerConnectionSize;
  118.     }
  120.     public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
  121.         this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
  122.     }
  124.     public String getFilters() {
  125.         return filters;
  126.     }
  128.     public void setFilters(String filters) {
  129.         this.filters = filters;
  130.     }
  132.     public String getDriverClassName() {
  133.         return driverClassName;
  134.     }
  136.     public void setDriverClassName(String driverClassName) {
  137.         this.driverClassName = driverClassName;
  138.     }
  140.     public String getUrl() {
  141.         return url;
  142.     }
  144.     public void setUrl(String url) {
  145.         this.url = url;
  146.     }
  148.     public String getUsername() {
  149.         return username;
  150.     }
  152.     public void setUsername(String username) {
  153.         this.username = username;
  154.     }
  156.     public String getPassword() {
  157.         return password;
  158.     }
  160.     public void setPassword(String password) {
  161.         this.password = password;
  162.     }
  164. }

2.4 config包下配置Servlet和Filter

Config包下新建一个DruidConfig配置类，主要是注入属性和配置连接池相关的配置，如黑白名单、监控管理后台登录账户密码等

  1. /**
  2.  * Druid数据源配置
  3.  */
  4. @Configuration
  5. @EnableConfigurationProperties({DruidDataSourceProperties.class})
  6. public class DruidConfig {
  7.     @Autowired
  8.     private DruidDataSourceProperties properties;
  10.     @Bean
  11.     @ConditionalOnMissingBean
  12.     public DataSource druidDataSource() {
  13.         DruidDataSource druidDataSource = new DruidDataSource();
  14.         druidDataSource.setDriverClassName(properties.getDriverClassName());
  15.         druidDataSource.setUrl(properties.getUrl());
  16.         druidDataSource.setUsername(properties.getUsername());
  17.         druidDataSource.setPassword(properties.getPassword());
  18.         druidDataSource.setInitialSize(properties.getInitialSize());
  19.         druidDataSource.setMinIdle(properties.getMinIdle());
  20.         druidDataSource.setMaxActive(properties.getMaxActive());
  21.         druidDataSource.setMaxWait(properties.getMaxWait());
  22.         druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
  23.         druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
  24.         druidDataSource.setValidationQuery(properties.getValidationQuery());
  25.         druidDataSource.setTestWhileIdle(properties.isTestWhileIdle());
  26.         druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());
  27.         druidDataSource.setTestOnReturn(properties.isTestOnReturn());
  28.         druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());
  29.         druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(properties.getMaxPoolPreparedStatementPerConnectionSize());
  31.         try {
  32.             druidDataSource.setFilters(properties.getFilters());
  33.             druidDataSource.init();
  34.         } catch (SQLException e) {
  35.             e.printStackTrace();
  36.         }
  38.         return druidDataSource;
  39.     }
  41.     /**
  42.      * 注册Servlet信息， 配置监控视图
  43.      *
  44.      * @return
  45.      */
  46.     @Bean
  47.     @ConditionalOnMissingBean
  48.     public ServletRegistrationBean<Servlet> druidServlet() {
  49.         ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<Servlet>(new StatViewServlet(), "/druid/*");
  51.         //白名单：
  52. //        servletRegistrationBean.addInitParameter("allow","127.0.0.1,139.196.87.48");
  53.         //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
  54.         servletRegistrationBean.addInitParameter("deny","192.168.1.119");
  55.         //登录查看信息的账号密码, 用于登录Druid监控后台
  56.         servletRegistrationBean.addInitParameter("loginUsername", "admin");
  57.         servletRegistrationBean.addInitParameter("loginPassword", "admin");
  58.         //是否能够重置数据.
  59.         servletRegistrationBean.addInitParameter("resetEnable", "true");
  60.         return servletRegistrationBean;
  62.     }
  64.     /**
  65.      * 注册Filter信息, 监控拦截器
  66.      *
  67.      * @return
  68.      */
  69.     @Bean
  70.     @ConditionalOnMissingBean
  71.     public FilterRegistrationBean<Filter> filterRegistrationBean() {
  72.         FilterRegistrationBean<Filter> filterRegistrationBean = new FilterRegistrationBean<Filter>();
  73.         filterRegistrationBean.setFilter(new WebStatFilter());
  74.         filterRegistrationBean.addUrlPatterns("/*");
  75.         filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
  76.         return filterRegistrationBean;
  77.     }
  78. }

3 查看监控

3.1 登录页面

http://localhost:9999/druid/login.html
image.png

3.2监控首页

image.png