跳至主要內容

SpringBoot与数据库编程

wangdx大约 19 分钟

Druid 基本配置

数据库连接池

  • 在项目开发中为了提高 SQL 数据库的处理性能，最佳的做法是采用数据库连接池的形式管理并限制项目中的全部数据库连接，而后通过 DataSource 接口实例来获取连接池中的 Connection 接口实例，并完成相应的 JDBC 程序开发

数据库连接池与服务监控

  • 数据库连接池可以在运行的 WEB 容器中配置，也可以基于组件的方式通过程序实现配置，考虑到项目简化部署的要求，现代的开发一般都会通过一些开源的数据库连接池组件实现连接池的配置，而随着项目开发要求的不断提升，数据库连接池除了要提供有高性能的处理性能之外还应该具有相应的监控功能
1、
// https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter
implementation group: 'com.alibaba', name: 'druid-spring-boot-starter', version: '1.2.21'
// https://mvnrepository.com/artifact/com.mysql/mysql-connector-j
implementation group: 'com.mysql', name: 'mysql-connector-j', version: '8.3.0'
// https://mvnrepository.com/artifact/org.springframework/spring-jdbc
implementation group: 'org.springframework', name: 'spring-jdbc', version: '6.1.4'

implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.25'
implementation group: 'com.alibaba', name: 'druid-spring-boot-starter', version: '1.2.6'

implementation group: 'org.springframework', name: 'spring-jdbc', version: '5.3.7'


2、
ext.versions = [    // 定义所有要使用的版本号
        springboot                          : '2.4.3', // SpringBoot版本号
        junit                               : '5.7.1', // 配置JUnit测试工具的版本编号
        junitPlatformLauncher               : '1.7.1',  // JUnit测试工具运行平台版本编号
        lombok                              : '1.18.18', // Lombok插件对应的版本号
        fastjson                            : '1.2.75', // FastJSON组件对应的版本号
        jackson                             : '2.12.2', // 配置Jackson相关依赖库
        itextpdf                            : '5.5.13.2', // PDF文件生成的依赖库
        easypoi                             : '4.3.0', // 生成Excel处理的依赖库
        hibernateValidator                  : '6.2.0.Final', // JSR303验证库
        prometheus                          : '1.6.5', // Prometheus监控数据版本
        shedlock                            : '4.23.0', // ShedLock组件
        springDataRedis                     : '2.4.5', // SpringDataRedis版本
        commonsPool2                        : '2.9.0', // 连接池版本
        jaxwsRi                             : '2.3.3', // JDK-WS依赖
        cxf                                 : '3.4.3', // WEBService开发框架版本
        mysql                               : '8.0.25', // MySQL驱动的版本
        druid                               : '1.2.6', // Druid版本
        springJdbc                          : '5.3.7', // SpringJDBC版本
]
ext.libraries = [   // 定义所有的依赖库
        // 以下的配置为SpringBoot项目所需要的核心依赖
        'spring-boot-gradle-plugin': "org.springframework.boot:spring-boot-gradle-plugin:${versions.springboot}",
        // 以下的配置为与项目用例测试有关的依赖
        'junit-jupiter-api': "org.junit.jupiter:junit-jupiter-api:${versions.junit}",
        'junit-vintage-engine': "org.junit.vintage:junit-vintage-engine:${versions.junit}",
        'junit-jupiter-engine': "org.junit.jupiter:junit-jupiter-engine:${versions.junit}",
        'junit-platform-launcher': "org.junit.platform:junit-platform-launcher:${versions.junitPlatformLauncher}",
        'junit-bom': "org.junit:junit-bom:${versions.junit}",
        // 以下的配置为Lombok组件有关的依赖
        'lombok': "org.projectlombok:lombok:${versions.lombok}",
        // 以下的配置为FastJSON组件有关的依赖
        'fastjson': "com.alibaba:fastjson:${versions.fastjson}",
        // 以下的配置为Jackson将输出转换为XML有关的依赖
        'jackson-dataformat-xml': "com.fasterxml.jackson.dataformat:jackson-dataformat-xml:${versions.jackson}",
        'jackson-databind': "com.fasterxml.jackson.core:jackson-databind:${versions.jackson}",
        'jackson-annotations': "com.fasterxml.jackson.core:jackson-annotations:${versions.jackson}",
        // 以下的配置为ITextPDF输出的有关依赖配置
        'itextpdf': "com.itextpdf:itextpdf:${versions.itextpdf}",
        // 以下的配置为生成Excel文件有关的依赖配置
        'easypoi-spring-boot-starter': "cn.afterturn:easypoi-spring-boot-starter:${versions.easypoi}",
        // 以下的配置为HibernateValidator实现的JSR303验证标准依赖
        'hibernate-validator': "org.hibernate.validator:hibernate-validator:${versions.hibernateValidator}",
        // 以下的配置为Prometheus监控数据操作
        'micrometer-registry-prometheus': "io.micrometer:micrometer-registry-prometheus:${versions.prometheus}",
        // 以下的配置为ShedLock分布式任务调度组件
        'shedlock-spring': "net.javacrumbs.shedlock:shedlock-spring:${versions.shedlock}",
        'shedlock-provider-redis-spring': "net.javacrumbs.shedlock:shedlock-provider-redis-spring:${versions.shedlock}",
        // 以下的配置为Redis缓存组件
        'spring-boot-starter-data-redis': "org.springframework.boot:spring-boot-starter-data-redis:${versions.springDataRedis}",
        'commons-pool2': "org.apache.commons:commons-pool2:${versions.commonsPool2}",
        // 以下的配置为WebService开发所需要的依赖：
        'jaxws-ri': "com.sun.xml.ws:jaxws-ri:${versions.jaxwsRi}",
        'cxf-spring-boot-starter-jaxws': "org.apache.cxf:cxf-spring-boot-starter-jaxws:${versions.cxf}",
        'cxf-rt-transports-http': "org.apache.cxf:cxf-rt-transports-http:${versions.cxf}",
        // 以下的配置为数据库开发所需要的依赖：
        'mysql-connector-java': "mysql:mysql-connector-java:${versions.mysql}",
        'druid-spring-boot-starter': "com.alibaba:druid-spring-boot-starter:${versions.druid}",
        'spring-jdbc': "org.springframework:spring-jdbc:${versions.springJdbc}",
]

3、


project('microboot-database') { // 子模块
    dependencies { // 配置子模块依赖
        compile(project(':microboot-common')) // 引入其他子模块
        compile('org.springframework.boot:spring-boot-starter-actuator')
        compile(libraries.'mysql-connector-java')
        compile(libraries.'druid-spring-boot-starter')
        compile(libraries.'spring-jdbc')
    }
}


4、
DROP DATABASE IF EXISTS yootk ;
CREATE DATABASE yootk CHARACTER SET UTF8 ;
USE yootk ;
CREATE TABLE member (
   mid          		VARCHAR(50),
   name         		VARCHAR(10) ,
   age          		INT ,
   salary       		DOUBLE,
   birthday     		DATE,
   content      		TEXT,
   isdel        		INT DEFAULT 0,
   CONSTRAINT pk_mid PRIMARY KEY(mid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO member(mid, name, age, salary, birthday, content) VALUES
	 ('muyan', '沐言科技', 18, 5999.99 , '2006-09-19', 'www.yootk.com') ;
INSERT INTO member(mid, name, age, salary, birthday, content) VALUES
	 ('yootk', '沐言优拓', 38, 8787.66, '1999-08-13', 'www.yootk.com') ;
INSERT INTO member(mid, name, age, salary, birthday, content) VALUES
	 ('edu', '李兴华编程训练营', 22, 6723.12, '2004-08-13', 'edu.yootk.com') ;

5、
server:
  port: 80

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource # 配置数据源的类型
    driver-class-name: com.mysql.cj.jdbc.Driver # MySQL8之后的驱动
    url: jdbc:mysql://localhost:3306/yootk # 数据库的JDBC连接地址
    username: root # 用户名
    password: mysqladmin # 密码
    druid: # 定义数据库连接池所需要的相关配置项
      initial-size: 5 # 初始化连接池大小
      min-idle: 10 # 最小维持的连接池大小
      max-active: 50 # 最大可提供的连接数量
      max-wait: 60000 # 最长等待时间
      time-between-eviction-runs-millis: 6000 # 关闭空闲连接间隔（毫秒）
      min-evictable-idle-time-millis: 30000 # 连接最小的生存时间
      validation-query: SELECT 1 FROM dual # 数据库的状态检测
      test-while-idle: true # 判断连接是否可用
      test-on-borrow: false # 在获得连接之前是否要进行测试
      test-on-return: false # 归还连接前是否需要进行测试
      pool-prepared-statements: false # 不缓存PSTMT
      max-pool-prepared-statement-per-connection-size: 20 # 配置PSTMT缓存个数

6、
package com.yootk.test;

import com.yootk.StartSpringBootDatabaseApplication;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import org.springframework.test.context.web.WebAppConfiguration;

import javax.sql.DataSource;
import java.sql.SQLException;

@ExtendWith(SpringExtension.class) // 使用JUnit5测试工具
@WebAppConfiguration // 启动WEB运行环境
@SpringBootTest(classes = StartSpringBootDatabaseApplication.class) // 配置程序启动类
public class TestDruidDataSource { // 编写测试类
    @Autowired
    private DataSource dataSource;
    @Test
    public void testDruid() {    // 进行响应测试
        try {
            System.out.println(this.dataSource.getConnection());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

基于 Bean 配置 Druid

自定义 Druid 数据源配置

  • 项目中引入了 druid-spring-boot-starter 模块可以直接基于 application.yml 配置 Druic 环境属性，但是这样一来就只能够通过 DruidDataSourceAutoConfiqure 自动装配类来管理 DataSource 接口实例，但是这样的配置方式对于 application.yml 配置文件存在有很大的限制，同时在一个 Spring 容器中也需要强制性的提供有一个名称 dataSource 的 Bean 实例，最终会降低程序配置的灵活性。
  • 有些开发者为了避免在 Druid 配置上存在的各种强制性的限制要求，所以会直接在项目中中引入原始的“druid”依赖库(取消 druid-spring-boot-starter 依赖)，而后采用手工的方式进行 DataSource 对象的 Bean 注册
1、
// https://mvnrepository.com/artifact/com.alibaba/druid
implementation group: 'com.alibaba', name: 'druid', version: '1.2.6'


2、
'druid': "com.alibaba:druid:${versions.druid}"

3、
project('microboot-database') { // 子模块
    dependencies { // 配置子模块依赖
        compile(project(':microboot-common')) // 引入其他子模块
        compile('org.springframework.boot:spring-boot-starter-actuator')
        compile(libraries.'mysql-connector-java')
        // compile(libraries.'druid-spring-boot-starter') // 删除掉此依赖库配置
        compile(libraries.'druid') // 添加原始依赖
        compile(libraries.'spring-jdbc')
    }
}

4、
spring:
  yootk: # 一个自定义配置项，这个名称就是数据库的名称
    datasource:
      type: com.alibaba.druid.pool.DruidDataSource # 配置数据源的类型
      driver-class-name: com.mysql.cj.jdbc.Driver # MySQL8之后的驱动
      url: jdbc:mysql://localhost:3306/yootk # 数据库的JDBC连接地址
      username: root # 用户名
      password: mysqladmin # 密码
      druid: # 定义数据库连接池所需要的相关配置项
        initial-size: 5 # 初始化连接池大小
        min-idle: 10 # 最小维持的连接池大小
        max-active: 50 # 最大可提供的连接数量
        max-wait: 60000 # 最长等待时间
        time-between-eviction-runs-millis: 6000 # 关闭空闲连接间隔（毫秒）
        min-evictable-idle-time-millis: 30000 # 连接最小的生存时间
        validation-query: SELECT 1 FROM dual # 数据库的状态检测
        test-while-idle: true # 判断连接是否可用
        test-on-borrow: false # 在获得连接之前是否要进行测试
        test-on-return: false # 归还连接前是否需要进行测试
        pool-prepared-statements: false # 不缓存PSTMT
        max-pool-prepared-statement-per-connection-size: 20 # 配置PSTMT缓存个数

5、
package com.yootk.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DruidDataSourceConfiguration { // 自定义的Druid配置类
    @Bean("yootkDruidDataSource") // 手工设置Bean名称
    public DruidDataSource getYootkDruidDataSource(
            @Value("${spring.yootk.database.driver-class-name}")
            String driverClassName, // 数据库驱动程序
            @Value("${spring.yootk.database.url}")
            String url, // 数据库连接地址
            @Value("${spring.yootk.database.username}")
            String username, // 数据库的用户名
            @Value("${spring.yootk.database.password}")
            String password, // 数据库的用户名
            @Value("${spring.yootk.database.druid.initial-size}")
            int initialSize, // 初始化连接数
            @Value("${spring.yootk.database.druid.min-idle}")
            int minIdle, // 最小维持连接数
            @Value("${spring.yootk.database.druid.max-active}")
            int maxActive, // 最大连接数
            @Value("${spring.yootk.database.druid.max-wait}")
            long maxWait, // 最长等待时间
            @Value("${spring.yootk.database.druid.time-between-eviction-runs-millis}")
            long timeBetweenEvictionRunsMillis, // 关闭空闲连接间隔
            @Value("${spring.yootk.database.druid.min-evictable-idle-time-millis}")
            long minEvictableIdleTimeMillis, // 最小存活时间
            @Value("${spring.yootk.database.druid.validation-query}")
            String validationQuery, // 验证查询
            @Value("${spring.yootk.database.druid.test-while-idle}")
            boolean testWhileIdle, // 测试空闲连接是否可用
            @Value("${spring.yootk.database.druid.test-on-borrow}")
            boolean testOnBorrow, // 测试后返回连接
            @Value("${spring.yootk.database.druid.test-on-return}")
            boolean testOnReturn, // 测试后归还
            @Value("${spring.yootk.database.druid.pool-prepared-statements}")
            boolean poolPreparedStatements, // 是否缓存PSTMT
            @Value("${spring.yootk.database.druid.max-pool-prepared-statement-per-connection-size}")
            int maxPoolPreparedStatementPerConnectionSize // PSTMT缓存个数
    ) {
        DruidDataSource dataSource = new DruidDataSource(); // 实例化DataSource子类对象
        dataSource.setDriverClassName(driverClassName); // 数据库驱动程序
        dataSource.setUrl(url); // 数据库的连接地址
        dataSource.setUsername(username); // 数据库用户名
        dataSource.setPassword(password); // 数据库密码
        dataSource.setInitialSize(initialSize); // 连接池初始化大小
        dataSource.setMinIdle(minIdle); // 最小维持的连接数量
        dataSource.setMaxActive(maxActive); // 最大的连接数量
        dataSource.setMaxWait(maxWait); // 最大等待时间
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); // 检查的间隔时间
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); // 存活时间
        dataSource.setValidationQuery(validationQuery); // 验证SQL
        dataSource.setTestWhileIdle(testWhileIdle); // 测试连接是否可用
        dataSource.setTestOnBorrow(testOnBorrow); // 获取时检测
        dataSource.setTestOnReturn(testOnReturn); // 归还时检测
        dataSource.setPoolPreparedStatements(poolPreparedStatements); // 是否缓存PSTMT
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); // 缓存个数
        return dataSource;
    }
}


6、
package com.yootk.test;

import com.yootk.StartSpringBootDatabaseApplication;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import org.springframework.test.context.web.WebAppConfiguration;

import javax.sql.DataSource;
import java.sql.SQLException;

@ExtendWith(SpringExtension.class) // 使用JUnit5测试工具
@WebAppConfiguration // 启动WEB运行环境
@SpringBootTest(classes = StartSpringBootDatabaseApplication.class) // 配置程序启动类
public class TestDruidDataSource { // 编写测试类
    @Autowired
    @Qualifier("yootkDruidDataSource") // 设置要注入的Bean名称
    private DataSource dataSource;
    @Test
    public void testDruid() {    // 进行响应测试
        try {
            System.out.println(this.dataSource.getConnection());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

Druid 监控界面

1、
package com.yootk.config;

import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DruidMonitorConfiguration { // Druid监控配置
    @Bean("druidStatViewServlet")
    public ServletRegistrationBean<StatViewServlet> getDruidStatViewServlet() {
        ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(
                new StatViewServlet(), "/druid/*"); // 程序的访问路径
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_ALLOW, "127.0.0.1"); // 白名单
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_DENY, ""); // 黑名单
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_USERNAME, "muyan"); // 用户名
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_PASSWORD, "yootk");// 密码
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_RESET_ENABLE, "true"); // 允许重置
        return registrationBean;
    }
}

http://localhost/druid/index.html

WEB 访问监控

1、
package com.yootk.action;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/message/*")
public class MessageAction {
    @RequestMapping("echo")
    public Object echo(String msg) {
        List<String> list = new ArrayList<>();
        for (int x = 0; x < 10; x++) {
            list.add("【ECHO】" + msg);
        }
        return list;
    }
}


2、
package com.yootk.config;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
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 org.springframework.context.annotation.DependsOn;

@Configuration
public class DruidMonitorConfiguration { // Druid监控配置
    @Bean("druidStatViewServlet")
    public ServletRegistrationBean<StatViewServlet> getDruidStatViewServlet() {
        ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(
                new StatViewServlet(), "/druid/*"); // 程序的访问路径
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_ALLOW, "127.0.0.1"); // 白名单
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_DENY, ""); // 黑名单
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_USERNAME, "muyan"); // 用户名
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_PASSWORD, "yootk");// 密码
        registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_RESET_ENABLE, "true"); // 允许重置
        return registrationBean;
    }
    @Bean
    @DependsOn("webStatFilter")
    public FilterRegistrationBean<WebStatFilter> getDruidWebStatFilter(
            WebStatFilter webStatFilter
    ) {
        FilterRegistrationBean<WebStatFilter> registrationBean = new FilterRegistrationBean<>(webStatFilter);
        registrationBean.addUrlPatterns("/*"); // 对所有的路径都进行监控配置
        registrationBean.addInitParameter(WebStatFilter.PARAM_NAME_EXCLUSIONS,
                "*.js,*.gif,*.jpg,*.bmp,*.css,*.ico,/druid/*"); // 路径排除
        return registrationBean;
    }
    @Bean("webStatFilter")
    public WebStatFilter getWebStatFilter() {   // 获取WEB状态过滤
        WebStatFilter statFilter = new WebStatFilter();
        statFilter.setSessionStatEnable(true); // 对Session状态进行监控
        return statFilter;
    }
}

SQL 监控

1、
spring:
  yootk: # 一个自定义配置项，这个名称就是数据库的名称
    datasource:
      type: com.alibaba.druid.pool.DruidDataSource # 配置数据源的类型
      driver-class-name: com.mysql.cj.jdbc.Driver # MySQL8之后的驱动
      url: jdbc:mysql://localhost:3306/yootk # 数据库的JDBC连接地址
      username: root # 用户名
      password: mysqladmin # 密码
      druid: # 定义数据库连接池所需要的相关配置项
        initial-size: 5 # 初始化连接池大小
        min-idle: 10 # 最小维持的连接池大小
        max-active: 50 # 最大可提供的连接数量
        max-wait: 60000 # 最长等待时间
        time-between-eviction-runs-millis: 6000 # 关闭空闲连接间隔（毫秒）
        min-evictable-idle-time-millis: 30000 # 连接最小的生存时间
        validation-query: SELECT 1 FROM dual # 数据库的状态检测
        test-while-idle: true # 判断连接是否可用
        test-on-borrow: false # 在获得连接之前是否要进行测试
        test-on-return: false # 归还连接前是否需要进行测试
        pool-prepared-statements: false # 不缓存PSTMT
        max-pool-prepared-statement-per-connection-size: 20 # 配置PSTMT缓存个数
        stat: # 监控配置
          merge-sql: true # 统计相同的SQL命令
          log-slow-sql: true # 记录慢SQL
          slow-sql-millis: 2000 # 慢SQL执行时间标准

2、
package com.yootk.config;

import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.context.annotation.DependsOn;

@Configuration
public class DruidMonitorConfiguration { // Druid监控配置
    @Bean("druidStatViewServlet")
    public ServletRegistrationBean<StatViewServlet> getDruidStatViewServlet() {
        ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(
                new StatViewServlet(), "/druid/*"); // 程序的访问路径
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_ALLOW, "127.0.0.1"); // 白名单
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_DENY, ""); // 黑名单
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_USERNAME, "muyan"); // 用户名
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_PASSWORD, "yootk");// 密码
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_RESET_ENABLE, "true"); // 允许重置
        return registrationBean;
    }
    @Bean
    @DependsOn("webStatFilter")
    public FilterRegistrationBean<WebStatFilter> getDruidWebStatFilter(
            WebStatFilter webStatFilter
    ) {
        FilterRegistrationBean<WebStatFilter> registrationBean = new FilterRegistrationBean<>(webStatFilter);
        registrationBean.addUrlPatterns("/*"); // 对所有的路径都进行监控配置
        registrationBean.addInitParameter(WebStatFilter.PARAM_NAME_EXCLUSIONS,
                "*.js,*.gif,*.jpg,*.bmp,*.css,*.ico,/druid/*"); // 路径排除
        return registrationBean;
    }
    @Bean("webStatFilter")
    public WebStatFilter getWebStatFilter() {   // 获取WEB状态过滤
        WebStatFilter statFilter = new WebStatFilter();
        statFilter.setSessionStatEnable(true); // 对Session状态进行监控
        return statFilter;
    }
    @Bean("sqlStatFilter")
    public StatFilter getSQLStatFilter(
            @Value("${spring.yootk.datasource.druid.stat.merge-sql}")
                    boolean mergeSql,
            @Value("${spring.yootk.datasource.druid.stat.log-slow-sql}")
                    boolean logSlowSql,
            @Value("${spring.yootk.datasource.druid.stat.slow-sql-millis}")
                    long slowSqlMillis
    ) { // 定义关于SQL监控的处理部分
        StatFilter filter = new StatFilter();
        filter.setMergeSql(mergeSql); // 是否需要合并统计
        filter.setLogSlowSql(logSlowSql); // 慢SQL记录
        filter.setSlowSqlMillis(slowSqlMillis); // 慢SQL执行时间
        return filter;
    }
}


3、
package com.yootk.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.List;

@Configuration
public class DruidDataSourceConfiguration { // 自定义的Druid配置类
    @Bean("yootkDruidDataSource") // 手工设置Bean名称
    public DruidDataSource getYootkDruidDataSource(
            @Value("${spring.yootk.datasource.driver-class-name}")
            String driverClassName, // 数据库驱动程序
            @Value("${spring.yootk.datasource.url}")
            String url, // 数据库连接地址
            @Value("${spring.yootk.datasource.username}")
            String username, // 数据库的用户名
            @Value("${spring.yootk.datasource.password}")
            String password, // 数据库的用户名
            @Value("${spring.yootk.datasource.druid.initial-size}")
            int initialSize, // 初始化连接数
            @Value("${spring.yootk.datasource.druid.min-idle}")
            int minIdle, // 最小维持连接数
            @Value("${spring.yootk.datasource.druid.max-active}")
            int maxActive, // 最大连接数
            @Value("${spring.yootk.datasource.druid.max-wait}")
            long maxWait, // 最长等待时间
            @Value("${spring.yootk.datasource.druid.time-between-eviction-runs-millis}")
            long timeBetweenEvictionRunsMillis, // 关闭空闲连接间隔
            @Value("${spring.yootk.datasource.druid.min-evictable-idle-time-millis}")
            long minEvictableIdleTimeMillis, // 最小存活时间
            @Value("${spring.yootk.datasource.druid.validation-query}")
            String validationQuery, // 验证查询
            @Value("${spring.yootk.datasource.druid.test-while-idle}")
            boolean testWhileIdle, // 测试空闲连接是否可用
            @Value("${spring.yootk.datasource.druid.test-on-borrow}")
            boolean testOnBorrow, // 测试后返回连接
            @Value("${spring.yootk.datasource.druid.test-on-return}")
            boolean testOnReturn, // 测试后归还
            @Value("${spring.yootk.datasource.druid.pool-prepared-statements}")
            boolean poolPreparedStatements, // 是否缓存PSTMT
            @Value("${spring.yootk.datasource.druid.max-pool-prepared-statement-per-connection-size}")
            int maxPoolPreparedStatementPerConnectionSize, // PSTMT缓存个数
            @Autowired StatFilter sqlStatFilter // 注入SQL监控
    ) {
        DruidDataSource dataSource = new DruidDataSource(); // 实例化DataSource子类对象
        dataSource.setDriverClassName(driverClassName); // 数据库驱动程序
        dataSource.setUrl(url); // 数据库的连接地址
        dataSource.setUsername(username); // 数据库用户名
        dataSource.setPassword(password); // 数据库密码
        dataSource.setInitialSize(initialSize); // 连接池初始化大小
        dataSource.setMinIdle(minIdle); // 最小维持的连接数量
        dataSource.setMaxActive(maxActive); // 最大的连接数量
        dataSource.setMaxWait(maxWait); // 最大等待时间
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); // 检查的间隔时间
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); // 存活时间
        dataSource.setValidationQuery(validationQuery); // 验证SQL
        dataSource.setTestWhileIdle(testWhileIdle); // 测试连接是否可用
        dataSource.setTestOnBorrow(testOnBorrow); // 获取时检测
        dataSource.setTestOnReturn(testOnReturn); // 归还时检测
        dataSource.setPoolPreparedStatements(poolPreparedStatements); // 是否缓存PSTMT
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); // 缓存个数
        // 定义所有可能存在的监控项集合
        List<Filter> filterList = new ArrayList<>();
        filterList.add(sqlStatFilter); // 配置监控项
        dataSource.setProxyFilters(filterList); // 与DataSource整合
        return dataSource;
    }
}


4、
package com.yootk.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class SpringJdbcConfiguration {
    @Bean
    public JdbcTemplate getJdbcTemplate(DataSource dataSource) {
        JdbcTemplate template = new JdbcTemplate();
        template.setDataSource(dataSource);
        return template;
    }
}


5、
package com.yootk.vo;

import lombok.Data;

import java.util.Date;

@Data
public class Member {
    private String mid;
    private String name;
    private Integer age;
    private Double salary;
    private Date birthday;
    private String content;
}


6、
package com.yootk.action;

import com.yootk.common.action.abs.AbstractBaseAction;
import com.yootk.vo.Member;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@RestController
@RequestMapping("/member/*")
public class MemberAction extends AbstractBaseAction {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @RequestMapping("list")
    public Object list() {
        String sql = "SELECT mid,name,age,salary,birthday,content FROM member";
        List<Member> allMembers = this.jdbcTemplate.query(sql,
                new RowMapper<Member>() {
                    @Override
                    public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Member member = new Member();
                        member.setMid(rs.getString(1));
                        member.setName(rs.getString(2));
                        member.setAge(rs.getInt(3));
                        member.setSalary(rs.getDouble(4));
                        member.setBirthday(rs.getDate(5));
                        member.setContent(rs.getString(6));
                        return member;
                    }
                });
        return allMembers;
    }
}

SQL 防火墙

1、
package com.yootk.config;

import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.context.annotation.DependsOn;

@Configuration
public class DruidMonitorConfiguration { // Druid监控配置
    @Bean("druidStatViewServlet")
    public ServletRegistrationBean<StatViewServlet> getDruidStatViewServlet() {
        ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(
                new StatViewServlet(), "/druid/*"); // 程序的访问路径
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_ALLOW, "127.0.0.1"); // 白名单
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_DENY, ""); // 黑名单
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_USERNAME, "muyan"); // 用户名
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_PASSWORD, "yootk");// 密码
        registrationBean.addInitParameter(
                StatViewServlet.PARAM_NAME_RESET_ENABLE, "true"); // 允许重置
        return registrationBean;
    }
    @Bean
    @DependsOn("webStatFilter")
    public FilterRegistrationBean<WebStatFilter> getDruidWebStatFilter(
            WebStatFilter webStatFilter
    ) {
        FilterRegistrationBean<WebStatFilter> registrationBean = new FilterRegistrationBean<>(webStatFilter);
        registrationBean.addUrlPatterns("/*"); // 对所有的路径都进行监控配置
        registrationBean.addInitParameter(WebStatFilter.PARAM_NAME_EXCLUSIONS,
                "*.js,*.gif,*.jpg,*.bmp,*.css,*.ico,/druid/*"); // 路径排除
        return registrationBean;
    }
    @Bean("webStatFilter")
    public WebStatFilter getWebStatFilter() {   // 获取WEB状态过滤
        WebStatFilter statFilter = new WebStatFilter();
        statFilter.setSessionStatEnable(true); // 对Session状态进行监控
        return statFilter;
    }
    @Bean("sqlStatFilter")
    public StatFilter getSQLStatFilter(
            @Value("${spring.yootk.datasource.druid.stat.merge-sql}")
                    boolean mergeSql,
            @Value("${spring.yootk.datasource.druid.stat.log-slow-sql}")
                    boolean logSlowSql,
            @Value("${spring.yootk.datasource.druid.stat.slow-sql-millis}")
                    long slowSqlMillis
    ) { // 定义关于SQL监控的处理部分
        StatFilter filter = new StatFilter();
        filter.setMergeSql(mergeSql); // 是否需要合并统计
        filter.setLogSlowSql(logSlowSql); // 慢SQL记录
        filter.setSlowSqlMillis(slowSqlMillis); // 慢SQL执行时间
        return filter;
    }
    @Bean("sqlWallConfig")
    public WallConfig getSQLWallConfig() { // 获取防火墙
        WallConfig wc = new WallConfig(); // 配置防火墙
        wc.setMultiStatementAllow(true); // 允许进行多个Statatement操作（批处理）
        wc.setDeleteAllow(false); // 不允许执行删除
        return wc;
    }
    @Bean("sqlWallFilter")
    public WallFilter getSQLWallFilter(WallConfig wallConfig) { // 注入防火墙配置项
        WallFilter filter = new WallFilter();
        filter.setConfig(wallConfig);
        return filter;
    }
}


2、
package com.yootk.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.List;

@Configuration
public class DruidDataSourceConfiguration { // 自定义的Druid配置类
    @Bean("yootkDruidDataSource") // 手工设置Bean名称
    public DruidDataSource getYootkDruidDataSource(
            @Value("${spring.yootk.datasource.driver-class-name}")
            String driverClassName, // 数据库驱动程序
            @Value("${spring.yootk.datasource.url}")
            String url, // 数据库连接地址
            @Value("${spring.yootk.datasource.username}")
            String username, // 数据库的用户名
            @Value("${spring.yootk.datasource.password}")
            String password, // 数据库的用户名
            @Value("${spring.yootk.datasource.druid.initial-size}")
            int initialSize, // 初始化连接数
            @Value("${spring.yootk.datasource.druid.min-idle}")
            int minIdle, // 最小维持连接数
            @Value("${spring.yootk.datasource.druid.max-active}")
            int maxActive, // 最大连接数
            @Value("${spring.yootk.datasource.druid.max-wait}")
            long maxWait, // 最长等待时间
            @Value("${spring.yootk.datasource.druid.time-between-eviction-runs-millis}")
            long timeBetweenEvictionRunsMillis, // 关闭空闲连接间隔
            @Value("${spring.yootk.datasource.druid.min-evictable-idle-time-millis}")
            long minEvictableIdleTimeMillis, // 最小存活时间
            @Value("${spring.yootk.datasource.druid.validation-query}")
            String validationQuery, // 验证查询
            @Value("${spring.yootk.datasource.druid.test-while-idle}")
            boolean testWhileIdle, // 测试空闲连接是否可用
            @Value("${spring.yootk.datasource.druid.test-on-borrow}")
            boolean testOnBorrow, // 测试后返回连接
            @Value("${spring.yootk.datasource.druid.test-on-return}")
            boolean testOnReturn, // 测试后归还
            @Value("${spring.yootk.datasource.druid.pool-prepared-statements}")
            boolean poolPreparedStatements, // 是否缓存PSTMT
            @Value("${spring.yootk.datasource.druid.max-pool-prepared-statement-per-connection-size}")
            int maxPoolPreparedStatementPerConnectionSize, // PSTMT缓存个数
            @Autowired StatFilter sqlStatFilter, // 注入SQL监控
            @Autowired WallFilter sqlWallFilter // 注入SQL防火墙
            ) {
        DruidDataSource dataSource = new DruidDataSource(); // 实例化DataSource子类对象
        dataSource.setDriverClassName(driverClassName); // 数据库驱动程序
        dataSource.setUrl(url); // 数据库的连接地址
        dataSource.setUsername(username); // 数据库用户名
        dataSource.setPassword(password); // 数据库密码
        dataSource.setInitialSize(initialSize); // 连接池初始化大小
        dataSource.setMinIdle(minIdle); // 最小维持的连接数量
        dataSource.setMaxActive(maxActive); // 最大的连接数量
        dataSource.setMaxWait(maxWait); // 最大等待时间
        dataSource.setTimeBetweenEvictionRunsMillis(
                timeBetweenEvictionRunsMillis); // 检查的间隔时间
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); // 存活时间
        dataSource.setValidationQuery(validationQuery); // 验证SQL
        dataSource.setTestWhileIdle(testWhileIdle); // 测试连接是否可用
        dataSource.setTestOnBorrow(testOnBorrow); // 获取时检测
        dataSource.setTestOnReturn(testOnReturn); // 归还时检测
        dataSource.setPoolPreparedStatements(poolPreparedStatements); // 是否缓存PSTMT
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(
                maxPoolPreparedStatementPerConnectionSize); // 缓存个数
        // 定义所有可能存在的监控项集合
        List<Filter> filterList = new ArrayList<>();
        filterList.add(sqlStatFilter); // 配置监控项
        filterList.add(sqlWallFilter); // SQL防火墙
        dataSource.setProxyFilters(filterList); // 与DataSource整合
        return dataSource;
    }
}


3、
package com.yootk.action;

import com.yootk.common.action.abs.AbstractBaseAction;
import com.yootk.vo.Member;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@RestController
@RequestMapping("/member/*")
public class MemberAction extends AbstractBaseAction {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @RequestMapping("list")
    public Object list() {
        String sql = "SELECT mid,name,age,salary,birthday,content FROM member";
        List<Member> allMembers = this.jdbcTemplate.query(sql,
                new RowMapper<Member>() {
                    @Override
                    public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Member member = new Member();
                        member.setMid(rs.getString(1));
                        member.setName(rs.getString(2));
                        member.setAge(rs.getInt(3));
                        member.setSalary(rs.getDouble(4));
                        member.setBirthday(rs.getDate(5));
                        member.setContent(rs.getString(6));
                        return member;
                    }
                });
        return allMembers;
    }
    @RequestMapping("add")
    public Object add(Member member) {
        String sql = "INSERT INTO member(mid, name, age, salary, birthday, content) " +
                " VALUES (?, ?, ?, ?, ?, ?)";
        return this.jdbcTemplate.update(sql, member.getMid(), member.getName(), member.getAge(),
                member.getSalary(), member.getBirthday(), member.getContent());
    }
    @RequestMapping("delete")
    public Object delete() {
        String sql = "DELETE FROM member";
        return this.jdbcTemplate.update(sql);
    }
}


4、
localhost/member/add?mid=lixinghua&name=爆可爱的小李老师&age=16&birthday=2005-08-13&salary=800.0&content=www.yootk.com

5、
localhost/member/delete

Spring 监控

1、
package com.yootk.service;

public interface IMessageService {
    public String echo(String msg);
}


2、
package com.yootk.service.impl;

import com.yootk.service.IMessageService;
import org.springframework.stereotype.Service;

@Service
public class MessageServiceImpl implements IMessageService {
    @Override
    public String echo(String msg) {
        return "【ECHO】" + msg;
    }
}


3、
package com.yootk.action;

import com.yootk.service.IMessageService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/message/*")
public class MessageAction {
    @Autowired
    private IMessageService messageService;
    @RequestMapping("echo")
    public Object echo(String msg) {
        List<String> list = new ArrayList<>();
        for (int x = 0; x < 10; x++) {
            list.add(this.messageService.echo(msg));
        }
        return list;
    }
}


4、
// https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-aop
implementation group: 'org.springframework.boot', name: 'spring-boot-starter-aop', version: '2.5.0'


5、
project('microboot-database') { // 子模块
    dependencies { // 配置子模块依赖
        compile(project(':microboot-common')) // 引入其他子模块
        compile('org.springframework.boot:spring-boot-starter-actuator')
        compile(libraries.'mysql-connector-java')
        // compile(libraries.'druid-spring-boot-starter') // 删除掉此依赖库配置
        compile(libraries.'druid') // 添加原始依赖
        compile(libraries.'spring-jdbc')
        compile('org.springframework.boot:spring-boot-starter-aop')
    }
}

6、
package com.yootk.config;

import com.alibaba.druid.support.spring.stat.DruidStatInterceptor;
import org.springframework.aop.support.DefaultPointcutAdvisor;
import org.springframework.aop.support.JdkRegexpMethodPointcut;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Scope;

@Configuration
public class DruidSpringConfig { // Spring监控配置类
    @Bean("druidStatInterceptor")
    public DruidStatInterceptor getDruidStatInterceptor() {
        DruidStatInterceptor interceptor = new DruidStatInterceptor(); // 创建Druid拦截器
        return interceptor;
    }
    @Bean("druidSpringStatPointcut")
    @Scope("prototype")
    public JdkRegexpMethodPointcut getDruidSpringStatPointcut() {   // 获取切面
        JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
        pointcut.setPatterns("com.yootk.service.*", "com.yootk.action.*", "com.yootk.dao.*");
        return pointcut;
    }
    @Bean("druidSpringStatAdvisor")
    public DefaultPointcutAdvisor getDruidSpringStatAdvisor(
            DruidStatInterceptor druidStatInterceptor,
            JdkRegexpMethodPointcut jdkRegexpMethodPointcut
    ) {
        DefaultPointcutAdvisor defaultPointcutAdvisor = new DefaultPointcutAdvisor();
        defaultPointcutAdvisor.setPointcut(jdkRegexpMethodPointcut);
        defaultPointcutAdvisor.setAdvice(druidStatInterceptor);
        return defaultPointcutAdvisor;
    }
}


7、
localhost/message/echo?msg=沐言科技：www.yootk.com

Druid 日志记录

项目性能优化

  • 为了便于重要监控信息的记录(例如:慢 SQL 监控)，在 Druid 工具中可以直接整合日志组件并将错误的信息保存在日志文件之中，这样程序开发人员就可以依据这些错误日志进行应用程序的性能优化

在 Druid 之中可以融合各个常用的日志组件，例如:Log4j、Slf4j、Logback，为了操作的统一性，本次将直接使用 Logback 实现日志的记录

1、
package com.yootk.config;

import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DruidLogConfig {
    @Bean("logFilter")
    public Slf4jLogFilter getLogFilter() {
        Slf4jLogFilter logFilter = new Slf4jLogFilter();
        logFilter.setDataSourceLogEnabled(true); // 启用数据库的日志
        logFilter.setStatementExecutableSqlLogEnable(true); // 记录执行日志
        return logFilter;
    }
}


2、
package com.yootk.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.List;

@Configuration
public class DruidDataSourceConfiguration { // 自定义的Druid配置类
    @Bean("yootkDruidDataSource") // 手工设置Bean名称
    public DruidDataSource getYootkDruidDataSource(
            @Value("${spring.yootk.datasource.driver-class-name}")
            String driverClassName, // 数据库驱动程序
            @Value("${spring.yootk.datasource.url}")
            String url, // 数据库连接地址
            @Value("${spring.yootk.datasource.username}")
            String username, // 数据库的用户名
            @Value("${spring.yootk.datasource.password}")
            String password, // 数据库的用户名
            @Value("${spring.yootk.datasource.druid.initial-size}")
            int initialSize, // 初始化连接数
            @Value("${spring.yootk.datasource.druid.min-idle}")
            int minIdle, // 最小维持连接数
            @Value("${spring.yootk.datasource.druid.max-active}")
            int maxActive, // 最大连接数
            @Value("${spring.yootk.datasource.druid.max-wait}")
            long maxWait, // 最长等待时间
            @Value("${spring.yootk.datasource.druid.time-between-eviction-runs-millis}")
            long timeBetweenEvictionRunsMillis, // 关闭空闲连接间隔
            @Value("${spring.yootk.datasource.druid.min-evictable-idle-time-millis}")
            long minEvictableIdleTimeMillis, // 最小存活时间
            @Value("${spring.yootk.datasource.druid.validation-query}")
            String validationQuery, // 验证查询
            @Value("${spring.yootk.datasource.druid.test-while-idle}")
            boolean testWhileIdle, // 测试空闲连接是否可用
            @Value("${spring.yootk.datasource.druid.test-on-borrow}")
            boolean testOnBorrow, // 测试后返回连接
            @Value("${spring.yootk.datasource.druid.test-on-return}")
            boolean testOnReturn, // 测试后归还
            @Value("${spring.yootk.datasource.druid.pool-prepared-statements}")
            boolean poolPreparedStatements, // 是否缓存PSTMT
            @Value("${spring.yootk.datasource.druid.max-pool-prepared-statement-per-connection-size}")
            int maxPoolPreparedStatementPerConnectionSize, // PSTMT缓存个数
            @Autowired StatFilter sqlStatFilter, // 注入SQL监控
            @Autowired WallFilter sqlWallFilter, // 注入SQL防火墙
            @Autowired Slf4jLogFilter logFilter // 日志记录
            ) {
        DruidDataSource dataSource = new DruidDataSource(); // 实例化DataSource子类对象
        dataSource.setDriverClassName(driverClassName); // 数据库驱动程序
        dataSource.setUrl(url); // 数据库的连接地址
        dataSource.setUsername(username); // 数据库用户名
        dataSource.setPassword(password); // 数据库密码
        dataSource.setInitialSize(initialSize); // 连接池初始化大小
        dataSource.setMinIdle(minIdle); // 最小维持的连接数量
        dataSource.setMaxActive(maxActive); // 最大的连接数量
        dataSource.setMaxWait(maxWait); // 最大等待时间
        dataSource.setTimeBetweenEvictionRunsMillis(
                timeBetweenEvictionRunsMillis); // 检查的间隔时间
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); // 存活时间
        dataSource.setValidationQuery(validationQuery); // 验证SQL
        dataSource.setTestWhileIdle(testWhileIdle); // 测试连接是否可用
        dataSource.setTestOnBorrow(testOnBorrow); // 获取时检测
        dataSource.setTestOnReturn(testOnReturn); // 归还时检测
        dataSource.setPoolPreparedStatements(poolPreparedStatements); // 是否缓存PSTMT
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(
                maxPoolPreparedStatementPerConnectionSize); // 缓存个数
        // 定义所有可能存在的监控项集合
        List<Filter> filterList = new ArrayList<>();
        filterList.add(sqlStatFilter); // 配置监控项
        filterList.add(sqlWallFilter); // SQL防火墙
        filterList.add(logFilter); // 日志记录
        dataSource.setProxyFilters(filterList); // 与DataSource整合
        return dataSource;
    }
}

demo
上次编辑于:
MIT Licensed | Copyright © 2024-present WDX