关于数据库连接池
早期数据库访问(也没多少人上网)
1装载数据库驱动程序;
2通过 jdbc 建立数据库连接;
3访问数据库, 执行 sql 语句;
4断开数据库连接.
对于并发量大的网站, 会导致以下问题:
每一次 web 请求都要建立一次数据库连接, 在同样的步骤下重复占用系统资源
不能控制被创建的连接对象数, 系统资源会被毫无顾及的分配出去
如连接过多, 也可能导致内存泄漏, 服务器崩溃.
数据库连接池
连接池的作用是为了提高性能, 将已经创建好的连接保存在池中, 当有请求来时, 直接使用已经创建好的连接对 Server 端进行访问.
这样省略 (复用) 了创建连接和销毁连接的过程(TCP 连接建立时的三次握手和销毁时的四次握手), 从而在性能上得到了提高.
嚣张的 Druid(GitHub 的 Wiki 上自称是 Java 语言最好的数据库连接池)
集成 Druid
在上次 Thymeleaf 基础上进行集成
https://www.cnblogs.com/noneplus/p/11528129.html
引入 pom
- <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>druid</artifactId>
- <version>1.1.20</version>
- </dependency>
- YAML
- spring:
- datasource:
- # 数据源基本配置
- username: noneplus
- password: Noneplus564925080!
- driver-class-name: com.MySQL.cj.jdbc.Driver
- url: jdbc:MySQL://47.103.6.247:3306/user?serverTimezone=UTC
- type: com.alibaba.druid.pool.DruidDataSource
- # Druid 数据源配置
- initialSize: 5
- minIdle: 5
- maxActive: 20
- maxWait: 60000
- timeBetweenEvictionRunsMillis: 60000
- minEvictableIdleTimeMillis: 300000
- validationQuery: SELECT 1 FROM DUAL
- testWhileIdle: true
- testOnBorrow: false
- testOnReturn: false
- poolPreparedStatements: true
- # 配置监控统计拦截的 filters, 去掉后监控界面 sql 无法统计,'wall'用于防火墙
- filters: stat,wall,log4j
- maxPoolPreparedStatementPerConnectionSize: 20
- useGlobalDataSourceStat: true
- connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
- thymeleaf:
- cache: false
- pagehelper:
- helperDialect: MySQL
- reasonable: true
- supportMethodsArguments: true
- pageSizeZero: false #pageSize=0
- DruidConfig
- package zkrun.top.Web.config;
- import com.alibaba.druid.pool.DruidDataSource;
- import com.alibaba.druid.support.http.StatViewServlet;
- import com.alibaba.druid.support.http.WebStatFilter;
- import org.springframework.boot.context.properties.ConfigurationProperties;
- import org.springframework.boot.Web.servlet.FilterRegistrationBean;
- import org.springframework.boot.Web.servlet.ServletRegistrationBean;
- import org.springframework.context.annotation.Bean;
- import org.springframework.context.annotation.Configuration;
- import javax.sql.DataSource;
- import java.util.Arrays;
- import java.util.HashMap;
- import java.util.Map;
- @Configuration
- public class DruidConfig {
- // 加载 application.YAML 中的 Druid 配置
- @ConfigurationProperties(prefix = "spring.datasource")
- @Bean
- public DataSource druid(){
- return new DruidDataSource();
- }
- // 配置 Druid 的监控
- //1, 配置一个管理后台的 Servlet
- @Bean
- public ServletRegistrationBean statViewServlet(){
- ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
- Map<String,String> initParams = new HashMap<>();
- initParams.put("loginUsername","hq");
- initParams.put("loginPassword","564925080");
- initParams.put("allow","");// 默认就是允许所有访问
- initParams.put("deny","192.168.15.21");
- bean.setInitParameters(initParams);
- return bean;
- }
- //2, 配置一个 Web 监控的 filter
- @Bean
- public FilterRegistrationBean webStatFilter(){
- FilterRegistrationBean bean = new FilterRegistrationBean();
- bean.setFilter(new WebStatFilter());
- Map<String,String> initParams = new HashMap<>();
- initParams.put("exclusions","*.js,*.CSS,/druid/*");
- bean.setInitParameters(initParams);
- bean.setUrlPatterns(Arrays.asList("/*"));
- return bean;
- }
- }
- log4j
- <dependency>
- <groupId>log4j</groupId>
- <artifactId>log4j</artifactId>
- <version>1.2.17</version>
- </dependency>
测试
访问:
http://localhost:8080/druid/
实时监控:
数据源配置文件密码加密显示
找到 jar 包, 在终端打开:
生成密码
java -cp druid-1.0.16.jar com.alibaba.druid.filter.config.ConfigTools yourPassword
其中 druid-1.0.16.jar 换成正在使用对应的版本, yourPassword 改成数据库连接的密码.
生成 privateKey 和 publicKey 以及 password
- privateKey:MIIBVAIBADANBgkqhkiG9w0BAQEFAASCAT4wggE6AgEAAkEAgiX0+n2diIixWASA642qrI8hTo2xg1LItfeDFwGEg9G1wvlarF69gq+wDBpPhY63DDbry8d3I/vNZMaG0bGxwwIDAQABAkAQfiDiUWLSN4hJwCBaOVSkk2rEGp6hJq
- 96p8f79VxGm/FneqyzLgiIQvvIutsOWSSQses+C/cDoXYEgfddieSpAiEAuq1lcFuBPh7MWZwlbIi0GM4+/1tNQed+W9Lm3qGJYk0CIQCyepaXbrY8Vk8pUZMOKr74kFFBfG5WqbAJuNYym1PMTwIgZttPfx8METxLADzm6exQvzI0x+J4qQ1yNAn
- 6p0R+PCkCIFPZ8EI5C3kRe6OH5bT0NECHXQWNNjGVC1Wl90xTWW1VAiEAnKdXLI45e1e8jgpCQ4TG90PmDlKlrcxUnBiHUrOhyew=
- publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIIl9Pp9nYiIsVgEgOuNqqyPIU6NsYNSyLX3gxcBhIPRtcL5WqxevYKvsAwaT4WOtww268vHdyP7zWTGhtGxscMCAwEAAQ==
- password:MEMMpYHaOUFVuaR37bMbUmGW76WVSLAD7pnFLrbup5H4Q6sZvWMDsYAcnZvAL2hY2Man1rc6SCJMYwrse1xPKw==
YAML 配置
修改 password, 添加公钥.
- spring:
- datasource:
- # 数据源基本配置
- username: noneplus
- password: MEMMpYHaOUFVuaR37bMbUmGW76WVSLAD7pnFLrbup5H4Q6sZvWMDsYAcnZvAL2hY2Man1rc6SCJMYwrse1xPKw== # 1. 配置生成的 password
- driver-class-name: com.MySQL.cj.jdbc.Driver
- url: jdbc:MySQL://47.103.6.247:3306/user?serverTimezone=UTC
- type: com.alibaba.druid.pool.DruidDataSource
- # Druid 数据源配置
- initialSize: 5
- minIdle: 5
- maxActive: 20
- maxWait: 60000
- timeBetweenEvictionRunsMillis: 60000
- minEvictableIdleTimeMillis: 300000
- validationQuery: SELECT 1 FROM DUAL
- testWhileIdle: true
- testOnBorrow: false
- testOnReturn: false
- poolPreparedStatements: true
- # 配置监控统计拦截的 filters, 去掉后监控界面 sql 无法统计,'wall'用于防火墙
- filters: stat,wall,log4j,config # 3. 添加 config
- maxPoolPreparedStatementPerConnectionSize: 20
- useGlobalDataSourceStat: true
- # 2. 开启加密, 配置公钥
- connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500;config.decrypt=true;config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIIl9Pp9nYiIsVgEgOuNqqyPIU6NsYNSyLX3gxcBhIPRtcL5WqxevYKvsAwaT4WOtww268vHdyP7zWTGhtGxscMCAwEAAQ==
- thymeleaf:
- cache: false
- pagehelper:
- helperDialect: MySQL
- reasonable: true
- supportMethodsArguments: true
- pageSizeZero: false #pageSize=0
代码参考
https://github.com/HCJ-shadow/SpringBootPlus
来源: https://www.cnblogs.com/noneplus/p/11532065.html