Springboot-数据访问-使用Druid数据源

Springboot-数据访问-使用Druid数据源

使用Druid数据源

1、druid官方github地址

https://github.com/alibaba/druid

整合第三方技术的两种方式
• 自定义
• 找starter

2、自定义方式
1、创建数据源
引入druid数据源

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>

实例：
//druid数据源，监控页面

package com.zm.admin.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.sql.SQLException;
import java.util.Arrays;

@Configuration
public class MyDataSourceConfig {
    //默认的自动配置是判断容器中没有才会配置@ConditionalOnMissingBean(DataSource.class)
    @ConfigurationProperties("spring.datasource") //绑定配置文件中的spring.datasource下的信息
    @Bean
    public DataSource dataSource() throws SQLException {
        DruidDataSource druidDataSource=new DruidDataSource();
        //加入监控功能
        druidDataSource.setFilters("stat,wall");  //也可以在配置文件里面进行配置
        return druidDataSource;
    }
    //配置druid的监控页功能；开启页面监控功能
    @Bean
    public ServletRegistrationBean statViewServlet(){
        StatViewServlet statViewServlet=new StatViewServlet();
        ServletRegistrationBean<StatViewServlet> registrationBean=new ServletRegistrationBean<StatViewServlet>(statViewServlet,"/druid/*");

        //设置登录的用户名和密码
        registrationBean.addInitParameter("loginUsername","admin");
        registrationBean.addInitParameter("loginPassword","123456");

        return registrationBean;
    }
    //WebStatFilter 用于采集web-jdbc关联监控的数据
    @Bean
    public FilterRegistrationBean webStatFilter(){
        WebStatFilter webStatFilter=new WebStatFilter();
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean=new FilterRegistrationBean<WebStatFilter>(webStatFilter);
        filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
        filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

//设置sql访问，发送请求

package com.zm.admin.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
public class DruidJiankongPageController {
    @Autowired
    JdbcTemplate jdbcTemplate;
    @ResponseBody
    @GetMapping("/sql")
    public String queryFromDb(){
        Long along=jdbcTemplate.queryForObject("select count(*) from student",Long.class);
        return along.toString();
    }
}

2、StatViewServlet
StatViewServlet的用途包括：
• 提供监控信息展示的html页面
• 提供监控信息的JSON API

<servlet>
    <servlet-name>DruidStatView</servlet-name>
    <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>DruidStatView</servlet-name>
    <url-pattern>/druid/*</url-pattern>
</servlet-mapping>

3、StatFilter
用于统计监控信息；如SQL监控、URI监控
需要给数据源中配置如下属性；可以允许多个filter，多个用，分割；如：

系统中所有filter：
别名 Filter类名
default com.alibaba.druid.filter.stat.StatFilter
stat com.alibaba.druid.filter.stat.StatFilter
mergeStat com.alibaba.druid.filter.stat.MergeStatFilter
encoding com.alibaba.druid.filter.encoding.EncodingConvertFilter
log4j com.alibaba.druid.filter.logging.Log4jFilter
log4j2 com.alibaba.druid.filter.logging.Log4j2Filter
slf4j com.alibaba.druid.filter.logging.Slf4jLogFilter
commonlogging com.alibaba.druid.filter.logging.CommonsLogFilter

慢SQL记录配置

<bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">
    <property name="slowSqlMillis" value="10000" />
    <property name="logSlowSql" value="true" />
</bean>

使用 slowSqlMillis 定义慢SQL的时长

3、使用官方starter方式

1、引入druid-starter

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.17</version>
</dependency>

2、分析自动配置
• 扩展配置项 spring.datasource.druid
• DruidSpringAopConfiguration.class, 监控SpringBean的；配置项：spring.datasource.druid.aop-patterns
• DruidStatViewServletConfiguration.class, 监控页的配置：spring.datasource.druid.stat-view-servlet；默认开启
• DruidWebStatFilterConfiguration.class, web监控配置；spring.datasource.druid.web-stat-filter；默认开启
• DruidFilterConfiguration.class}) 所有Druid自己filter的配置
private static final String FILTER_STAT_PREFIX = “spring.datasource.druid.filter.stat”;
private static final String FILTER_CONFIG_PREFIX = “spring.datasource.druid.filter.config”;
private static final String FILTER_ENCODING_PREFIX = “spring.datasource.druid.filter.encoding”;
private static final String FILTER_SLF4J_PREFIX = “spring.datasource.druid.filter.slf4j”;
private static final String FILTER_LOG4J_PREFIX = “spring.datasource.druid.filter.log4j”;
private static final String FILTER_LOG4J2_PREFIX = “spring.datasource.druid.filter.log4j2”;
private static final String FILTER_COMMONS_LOG_PREFIX = “spring.datasource.druid.filter.commons-log”;
private static final String FILTER_WALL_PREFIX = “spring.datasource.druid.filter.wall”;

3、配置示例

在配置文件中进行配置
实例如下在yaml文件里进行配置：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springboot_test
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

    druid:
      aop-patterns: com.zm.admin.*  #监控SpringBean
      filters: stat,wall,slf4j  #底层开启功能，stat（sql监控），wall（防火墙）;stat对web进行监控，wall对防火墙进行监控

      stat-view-servlet:  #配置监控页功能
        enabled: true
        login-username: admin
        login-password: 123
        reset-enable: true  #允许重置

      web-stat-filter:  #监控web
        enabled: true
        url-pattern: /*
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'

      filter:
        stat:  #对上面filters里面的stat的详细配置
          enabled: true
          slow-sql-millis: 1000 #慢查询，只要是时间超过1000ms的查询都是慢查询
          log-slow-sql: true #记录下慢查询的sql
        wall:  #对上面filters里面的wall的详细配置
          enabled: true
          config:
            drop-table-allow: false #不允许删表，所有的删表操作都会被删除

SpringBoot配置示例

https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

配置项列表
https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8