Druid汇总
Java程序很大一部分要操作数据库,为了提高性能操作数据库的时候,又不得不使用数据库连接池。 Druid 是阿里巴巴开源平台上一个数据库连接池实现,结合了 C3P0、DBCP 等 DB 池的优点,同时加入了日志监控。 Druid 可以很好的监控 DB 池连接和 SQL 的执行情况,天生就是针对监控而生的 DB 连接池。 Druid已经在阿里巴巴部署了超过600个应用,经过一年多生产环境大规模部署的严苛考验。 Spring Boot 2.0 以上默认使用 Hikari 数据源,可以说 Hikari 与 Driud 都是当前 Java Web 上最优秀的数据源,我们来重点介绍 Spring Boot 如何集成 Druid 数据源,如何实现数据库监控。 Github地址:https://github.com/alibaba/druid/
com.alibaba.druid.pool.DruidDataSource 基本配置参数如下:
导入依赖:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.kuang</groupId>
<artifactId>springboot-04-data</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-04-data</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- 德鲁伊数据源-->
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<!--log4j 日志-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.yml
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://127.0.0.1:3306/qcda?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
# Springboot默认不注入这些属性,需要自己绑定
# druid数据源转有配置
initialSize: 10
minIdle: 10
maxActive: 1000
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置监控统计拦截器的filter,stat:监控统计,log4j:日志记录,wall:防御sql注入
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
server:
port: 8848
DruidConfig
package com.kuang.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 {
//你首先要做的是把yml文件跟这个controller绑起来,实际上就是把这个文件绑定到bean上
@ConfigurationProperties(prefix = "spring.datasource")
@Bean//这样的话就把yml跟这个controller绑定了
public DataSource druidDataSource(){
return new DruidDataSource();
}
//后台监控功能 == web.xml
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/**");
HashMap<String, String> initParameters = new HashMap<>();
//增加配置
initParameters.put( "loginUsername","admin");//登陆key 是固定的loginUsername loginPassword
initParameters.put( "loginPassword","123456");
//允许谁都可以访问
//initParameters.put("allow","");//allow后面参数为空那么代表所有人都可以访问 localhost只有本机可以访问
initParameters.put("allow","");
//禁止谁能访问
initParameters.put("名字随便起","这里面是不让他看的那个人的IP地址");
//后台需要有人登陆,账号密码配置
bean.setInitParameters(initParameters);//设置初始化参数 因为这个里面是map,所以我们new 出来
return bean;
}
// 配置 Druid web 监控 filter 过滤器
//配置 Druid 监控 之 web 监控的 filter
//WebStatFilter:用于配置Web和Druid数据源之间的管理关联监控统计
@Bean
public FilterRegistrationBean webStatFilter() {
FilterRegistrationBean bean = new FilterRegistrationBean();
//可以过滤哪些请求呢?
bean.setFilter(new WebStatFilter());
//exclusions:设置哪些请求进行过滤排除掉,从而不进行统计
Map<String, String> initParams = new HashMap<>();
//这些东西不进行统计
initParams.put("exclusions", "*.js,*.css,/druid/*,/jdbc/*");
bean.setInitParameters(initParams);
//"/*" 表示过滤所有请求
bean.setUrlPatterns( Arrays.asList("/*"));
return bean;
}
}
JDBCController
package com.kuang.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
@RestController
public class JDBCController {
@Autowired
JdbcTemplate jdbcTemplate;
//查询数据库的所有信息
@GetMapping("/userList")
public List<Map<String, Object>> userList(){
String sql = "select * from tb_user";
List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql);
return list_maps;
}
@GetMapping("/addUser")
public String addUser(){
String sql ="insert into user values (3,'小明','123456')";
jdbcTemplate.update(sql);
return "update-ok";
}
@GetMapping("/updateUser/{id}")
public String updateUser(@PathVariable("id") Integer id){
String sql ="update user set name = ?,password=? where id="+id;
//封装
Object[] object = new Object[2];
object[0]="小明2";
object[1] = "zzzz";
jdbcTemplate.update(sql,object);
return "updateUser-ok";
}
@GetMapping("/deleteUser/{id}")
public String deleteUser(@PathVariable("id") int id){
String sql ="delete from user where id=?";
jdbcTemplate.update(sql, id);
return "deleteUser-ok";
}
}
Springboot04DataApplication
package com.kuang;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Springboot04DataApplication {
public static void main(String[] args) {
SpringApplication.run(Springboot04DataApplication.class, args);
}
}
Springboot04DataApplicationTests
package com.kuang;
import com.alibaba.druid.pool.DruidDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
@SpringBootTest
class Springboot04DataApplicationTests {
//因为springboot的自动装配原理,所有不需要写一大堆的代码
@Autowired//注入数据源
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
//查看默认数据源
System.out.println(dataSource.getClass());//class com.zaxxer.hikari.HikariDataSource 默认数据源输出
//修改数据源后的输出 type: com.alibaba.druid.pool.DruidDataSource
//class com.alibaba.druid.pool.DruidDataSource
//获取连接
Connection connection = dataSource.getConnection();
System.out.println(connection);//HikariProxyConnection@1889457907 wrapping com.mysql.cj.jdbc.ConnectionImpl@698fee9a
//修改数据源后的输出 com.mysql.cj.jdbc.ConnectionImpl@71978f46
//关闭
DruidDataSource druidDataSource = (DruidDataSource) dataSource;
System.out.println("druidDataSource 数据源最大连接数:" + druidDataSource.getMaxActive());
System.out.println("druidDataSource 数据源初始化连接数:" + druidDataSource.getInitialSize());
// com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@14a049f9
// druidDataSource 数据源最大连接数:1000
// druidDataSource 数据源初始化连接数:10
connection.close();
}
}
【注意】要是需要源码,就在我的博客中自行下载,免费的!