springboot 使用 druid 实现 sql监控

druid是什么

druid是阿里巴巴开源的数据库连接池，自称是Java语言中最好的数据库连接池，提供强大的监控和扩展功能。

 

为什么用druid

1. 性能

   官方数据Benchmark_aliyun,druid在响应时间上优于其他几个线程池。非官方的测试数据可能差距没这么明显，但仍然高于其他几个线程池。

2. 自带监控功能

   自带监控，可帮助开发者找出慢查询，查看并发数等。

文章的最后提供了源码下载地址

 

部分截图：

 

部分代码：

1. 添加依赖

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

 

2. mybatis、druid 相关配置

一些配置类

package com.hl.config;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

@Configuration
@MapperScan("com.hl.dal.mapper")
public class MyBatisConfig {
	@Value("${spring.datasource.url}")
	private String dbUrl;
	@Value("${spring.datasource.username}")
	private String userName;
	@Value("${spring.datasource.password}")
	private String password;
	@Value("${spring.datasource.driverClassName}")
	private String driverClassName;

	private static String MYBATIS_CONFIG = "mybatis_config.xml";
	private static String MAPPER_PATH = "/mapper/*.xml";

	@Bean
	public SqlSessionFactoryBean createSqlSessionFactoryBean() throws IOException {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		// 设置mybatis configuration 扫描路径
		sqlSessionFactoryBean.setConfigLocation(new ClassPathResource(MYBATIS_CONFIG));
		// 添加mapper 扫描路径
		PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();
		String packageSearchPath = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX + MAPPER_PATH;
		sqlSessionFactoryBean.setMapperLocations(pathMatchingResourcePatternResolver.getResources(packageSearchPath));
		// 设置datasource
		sqlSessionFactoryBean.setDataSource(dataSource());
		return sqlSessionFactoryBean;
	}

	private DataSource dataSource() {
		Map<String, Object> properties = new HashMap<>();
		properties.put(DruidDataSourceFactory.PROP_DRIVERCLASSNAME, driverClassName);
		properties.put(DruidDataSourceFactory.PROP_URL, dbUrl);
		properties.put(DruidDataSourceFactory.PROP_USERNAME, userName);
		properties.put(DruidDataSourceFactory.PROP_PASSWORD, password);
		// 添加统计、SQL注入、日志过滤器
		properties.put(DruidDataSourceFactory.PROP_FILTERS, "stat,wall,log4j2");
		// sql合并，慢查询定义为5s
		properties.put(DruidDataSourceFactory.PROP_CONNECTIONPROPERTIES, "druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");
		try {
			return DruidDataSourceFactory.createDataSource(properties);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}

 

package com.hl.web.servlet;

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;

import java.util.HashMap;
import java.util.Map;

@Configuration
public class ServletConfiguration {
	@Bean
	public ServletRegistrationBean druidStatViewServletBean() {
		// 后台的路径
		ServletRegistrationBean statViewServletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
		Map<String, String> params = new HashMap<>();
		// 账号密码，是否允许重置数据
		params.put("loginUsername", "admin");
		params.put("loginPassword", "admin");
		params.put("resetEnable", "true");
		statViewServletRegistrationBean.setInitParameters(params);
		return statViewServletRegistrationBean;
	}
}

 

package com.hl.web.filter;

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

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Configuration
public class FilterConfiguration {
	@Bean
	public FilterRegistrationBean druidStatFilterBean() {
		FilterRegistrationBean druidStatFilterBean = new FilterRegistrationBean(new WebStatFilter());
		List<String> urlPattern = new ArrayList<>();
		urlPattern.add("/*");
		druidStatFilterBean.setUrlPatterns(urlPattern);
		Map<String, String> initParams = new HashMap<>();
		initParams.put("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
		druidStatFilterBean.setInitParameters(initParams);
		return druidStatFilterBean;
	}
}

 

Controller：

package com.hl.web.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.hl.dal.mapper.UserMapper;
import com.hl.dal.po.User;

@RestController
@RequestMapping("/user")
public class IndexController {
	@Autowired
	private UserMapper UserMapper;

	@RequestMapping()
	public List<User> getList() {
		return UserMapper.getAll();
	}

	@RequestMapping("/get/{id}")
	public Object index(@PathVariable Long id) {
		User configPO = UserMapper.getByPrimaryKey(id);
		return configPO;
	}

	@RequestMapping("/delete/{id}")
	public String delete(@PathVariable Long id) {
		if (UserMapper.deleteByPrimaryKey(id) > 0) {
			return "删除成功";
		};
		return "删除失败";
	}

}

 

Mapper：

package com.hl.dal.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import com.hl.dal.po.User;

@Mapper
public interface UserMapper {

	User getByPrimaryKey(Long id);

	List<User> getAll();

	int insert(User record);

	int deleteByPrimaryKey(Long id);

	int updateByPrimaryKey(User record);

}

 

<?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.hl.dal.mapper.UserMapper">
	<resultMap id="BaseResultMap" type="com.hl.dal.po.User">
		<id column="id" jdbcType="BIGINT" property="id" />
		<result column="is_deleted" jdbcType="CHAR" property="isDeleted" />
		<result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate" />
		<result column="gmt_modified" jdbcType="TIMESTAMP" property="gmtModified" />
		<result column="user_name" jdbcType="VARCHAR" property="userName" />
		<result column="user_age" jdbcType="TINYINT" property="userAge" />
		<result column="nick_name" jdbcType="VARCHAR" property="nickName" />
	</resultMap>


	<sql id="Base_Column_List">
		id, is_deleted, gmt_create, gmt_modified, user_name,
		user_age, nick_name
	</sql>

	<select id="getByPrimaryKey" parameterType="java.lang.Long"
		resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from users
		where id = #{id,jdbcType=BIGINT}
	</select>
	<select id="getAll" parameterType="java.lang.Long" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from users
	</select>
	<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
		delete from users
		where id = #{id,jdbcType=BIGINT}
	</delete>

	<insert id="insert" parameterType="com.hl.dal.po.User">
		<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
			SELECT
			LAST_INSERT_ID()
		</selectKey>
		insert into users (is_deleted, gmt_create, gmt_modified,user_name, user_age, nick_name
		)
		values (#{isDeleted,jdbcType=CHAR},
		#{gmtCreate,jdbcType=TIMESTAMP},
		#{gmtModified,jdbcType=TIMESTAMP},
		#{userName,jdbcType=VARCHAR}, #{userAge,jdbcType=TINYINT},
		#{nickName,jdbcType=VARCHAR}
		)
	</insert>

	<update id="updateByPrimaryKey" parameterType="com.hl.dal.po.User">
		update users
		set
		is_deleted = #{isDeleted,jdbcType=CHAR},
		gmt_create =
		#{gmtCreate,jdbcType=TIMESTAMP},
		gmt_modified =
		#{gmtModified,jdbcType=TIMESTAMP},
		user_name =
		#{userName,jdbcType=VARCHAR},
		user_age = #{userAge,jdbcType=TINYINT},
		nick_name = #{nickName,jdbcType=VARCHAR}
		where id =
		#{id,jdbcType=BIGINT}
	</update>

</mapper>

 

附上源码：http://download.csdn.net/download/qq_28125445/10263324