低调人的gravatar头像
低调人 2018-07-06 15:53:45
Spring Boot之使用Druid连接池以及SQL监控和spring监控

废话不多说!直接上代码

一、引入maven依赖包

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.cicoding.druid</groupId>
	<artifactId>springboot_druid</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>

	<name>springboot_druid</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.9.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
		</dependency>
	
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-tomcat</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.18</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.2.2</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

二、在application.properties配置文件中添加druid配置

##########################  mysql   ##########################
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

logging.level.com.cicoding.mapper=debug


##########################  mybatis   ##########################
mybatis.mapper-locations=classpath:mybatis/*.xml

##########################  druid配置   ##########################
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

# 初始化大小,最小,最大  
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间  
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒  
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间,单位是毫秒  
spring.datasource.minEvictableIdleTimeMillis=300000
# 校验SQL,Oracle配置 spring.datasource.validationQuery=SELECT 1 FROM DUAL,如果不配validationQuery项,则下面三项配置无用  
spring.datasource.validationQuery=SELECT 'x'
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache,并且指定每个连接上PSCache的大小  
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙  
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录  
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据  
spring.datasource.useGlobalDataSourceStat=true

 

三、配置druid连接池的webServlet

package com.cicoding.servlet;

import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

import com.alibaba.druid.support.http.StatViewServlet;
@WebServlet(urlPatterns = "/druid/*", 
    initParams={
            @WebInitParam(name="loginUsername",value="admin"),// 用户名
            @WebInitParam(name="loginPassword",value="admin"),// 密码
            @WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能
    })
public class DruidServlet extends StatViewServlet {

	private static final long serialVersionUID = 1L;
}

 

四、配置Druid连接池webFitler

package com.cicoding.filter;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

import com.alibaba.druid.support.http.WebStatFilter;

@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
initParams={
    @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
})
public class DruidFilter extends WebStatFilter {

}

 

五、扫描filter和servlet

package com.cicoding;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.context.annotation.ImportResource;

@SpringBootApplication
@ServletComponentScan
@ImportResource(locations = { "classpath:druid-bean.xml" })
public class DruidApplication {

	public static void main(String[] args) {
		SpringApplication.run(DruidApplication.class, args);
	}
}

 

六、不使用spring boot自动配置功能,手动初始化DataSource

描如果不使用代码手动初始化DataSource的话,监控界面的SQL监控会没有数据("是spring boot的bug??")

package com.cicoding.configuration;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration  
public class DruidConfigration {  
    @Value("${spring.datasource.url}")  
    private String dbUrl;  
    @Value("${spring.datasource.username}")  
    private String username;  
    @Value("${spring.datasource.password}")  
    private String password;  
    @Value("${spring.datasource.driver-class-name}")  
    private String driverClassName;  
    @Value("${spring.datasource.initialSize}")  
    private int initialSize;  
    @Value("${spring.datasource.minIdle}")  
    private int minIdle;  
    @Value("${spring.datasource.maxActive}")  
    private int maxActive;  
    @Value("${spring.datasource.maxWait}")  
    private int maxWait;  
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")  
    private int timeBetweenEvictionRunsMillis;  
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")  
    private int minEvictableIdleTimeMillis;  
    @Value("${spring.datasource.validationQuery}")  
    private String validationQuery;  
    @Value("${spring.datasource.testWhileIdle}")  
    private boolean testWhileIdle;  
    @Value("${spring.datasource.testOnBorrow}")  
    private boolean testOnBorrow;  
    @Value("${spring.datasource.testOnReturn}")  
    private boolean testOnReturn;  
    @Value("${spring.datasource.poolPreparedStatements}")  
    private boolean poolPreparedStatements;  
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")  
    private int maxPoolPreparedStatementPerConnectionSize;  
    @Value("${spring.datasource.filters}")  
    private String filters;  
    @Value("${spring.datasource.connectionProperties}")  
    private String connectionProperties;  
    @Value("${spring.datasource.useGlobalDataSourceStat}")  
    private boolean useGlobalDataSourceStat;  
  
    @Bean     //声明其为Bean实例  
    @Primary  //在同样的DataSource中,首先使用被标注的DataSource  
    public DataSource dataSource(){  
        DruidDataSource datasource = new DruidDataSource();  
        datasource.setUrl(this.dbUrl);  
        datasource.setUsername(username);  
        datasource.setPassword(password);  
        datasource.setDriverClassName(driverClassName);  
  
        //configuration  
        datasource.setInitialSize(initialSize);  
        datasource.setMinIdle(minIdle);  
        datasource.setMaxActive(maxActive);  
        datasource.setMaxWait(maxWait);  
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);  
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);  
        datasource.setValidationQuery(validationQuery);  
        datasource.setTestWhileIdle(testWhileIdle);  
        datasource.setTestOnBorrow(testOnBorrow);  
        datasource.setTestOnReturn(testOnReturn);  
        datasource.setPoolPreparedStatements(poolPreparedStatements);  
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);  
        datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);  
        try {  
            datasource.setFilters(filters);  
        } catch (SQLException e) {  
            System.err.println("druid configuration initialization filter: "+ e);  
        }  
        datasource.setConnectionProperties(connectionProperties);  
        return datasource;  
    }  
}  

八、spring监控之方法名正则匹配拦截配置druid-bean.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop.xsd">

	<!-- 配置_Druid和Spring关联监控配置 -->
	<bean id="druid-stat-interceptor"
		class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor"></bean>

	<!-- 方法名正则匹配拦截配置 -->
	<bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut"
		scope="prototype">
		<property name="patterns">
			<list>
				<value>com.cicoding.mapper.*</value>
			</list>
		</property>
	</bean>

	<aop:config proxy-target-class="true">
		<aop:advisor advice-ref="druid-stat-interceptor"
			pointcut-ref="druid-stat-pointcut" />
	</aop:config>

</beans>

 

 

九、引入配置xml

@SpringBootApplication
@ServletComponentScan
@ImportResource(locations = { "classpath:druid-bean.xml" })
public class DruidApplication {

	public static void main(String[] args) {
		SpringApplication.run(DruidApplication.class, args);
	}
}

十、controller配置

package com.cicoding.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.cicoding.bean.User;
import com.cicoding.mapper.UserMapper;

@Controller
@RequestMapping(value="/user")
public class UserController {

	@Autowired
	private UserMapper userMapper;
	
	@ResponseBody
	@RequestMapping(value="/get")
	public User get() {
		return userMapper.getOne(1);
	}
	
	@ResponseBody
	@RequestMapping(value="/list")
	public List<User> list() {
		return userMapper.list();
	}
}

十一、Mapper的dao接口和mapper xml配置

Mapper接口

package com.cicoding.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.cicoding.bean.User;
@Mapper
public interface UserMapper{
	List<User> list();
	User getOne(int id);
}

 

mapper xml配置

  • 因为application.properties配置了mybatis.mapper-locations=classpath:mybatis/*.xml
  • 放在mybatis目录下扫描
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cicoding.mapper.UserMapper">

	<resultMap type="com.cicoding.bean.User" id="userMap">
		<id column="id" property="id" />
		<result column="user_name" property="userName" />
		<result column="pass_word" property="passWord" />
		<result column="date" property="date" />
	</resultMap>

	<select id="list" resultMap="userMap">
		select * from users
	</select>

	<select id="getOne" parameterType="java.lang.Integer" resultMap="userMap">
		select * from users where id=#{id}
	</select>
</mapper>

十二、测试

Spring Boot之使用Druid连接池以及SQL监控和spring监控

Spring Boot之使用Druid连接池以及SQL监控和spring监控Spring Boot之使用Druid连接池以及SQL监控和spring监控

Spring Boot之使用Druid连接池以及SQL监控和spring监控

 


 




大功搞成!


打赏

已有3人打赏

人间蒸发的gravatar头像 已注销用户的gravatar头像 最代码官方的gravatar头像
最近浏览
johnkinna  LV1 2020年5月30日
已注销用户  LV34 2020年4月1日
一天一点爱恋  LV5 2020年3月13日
hui520 2019年12月24日
暂无贡献等级
2252536772  LV21 2019年9月10日
人间蒸发  LV23 2019年9月2日
安安an  LV17 2019年7月23日
fellowfun  LV12 2019年7月2日
8317073  LV12 2019年6月25日
jy34521  LV3 2019年6月18日
顶部 客服 微信二维码 底部
>扫描二维码关注最代码为好友扫描二维码关注最代码为好友