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