Spring boot+mybatis+druid+SQL监控配置

之前写过一篇搭建Spring boot + mybatis + freemarker项目,Spring boot默认的数据库连接池是tomcat-jdbc，今天我们要用druid替换它。

什么是druid

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

为什么用druid

性能

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

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

配置步骤：

基于干净的spring boot web项目，添加mysql、mybatis、druid库:

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.31</version>
</dependency>

配置数据库连接

application.properties:

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=

这里key叫什么其实并不重要，因为后面配置数据源，我们会自己读取配置。

Mybatis、druid配置
新建MyBatisConfig.java:

package com.pocketdigi.config.database;

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.pocketdigi.dal.mapper")
public class MyBatisConfig {
    @Value("${spring.datasource.url}")
    String dbUrl;
    @Value("${spring.datasource.username}")
    String userName;
    @Value("${spring.datasource.password}")
    String password;

    @Value("${spring.datasource.driverClassName}")
    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;
    }

}

mybatis的mapper接口在com.pocketdigi.dal.mapper，mybatis_config.xml在resources目录下，xml mapper文件在resources/mapper/目录下。

mybatis_config.xml:

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!-- 所有映射器中配置的缓存的全局开关-->
        <setting name="cacheEnabled" value="false"/>
        <!-- 延迟加载的全局开关 配置二级缓存时将此属性关闭-->
        <setting name="lazyLoadingEnabled" value="false"/>
        <!-- 关联对象加载 配置二级缓存时将此属性关闭-->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!-- 是否允许单一语句返回多结果集-->
        <setting name="multipleResultSetsEnabled" value="true"/>
        <!-- 使用列标签代替列名-->
        <setting name="useColumnLabel" value="true"/>
        <!-- 允许 JDBC 支持自动生成主键，需要驱动兼容 -->
        <setting name="useGeneratedKeys" value="false"/>
        <!-- 指定 MyBatis 是否以及如何自动映射指定的列到字段或属性-->
        <setting name="autoMappingBehavior" value="PARTIAL"/>
        <!-- 配置默认的执行器-->
        <setting name="defaultExecutorType" value="SIMPLE"/>
        <!-- 设置超时时间，它决定驱动等待数据库响应的秒数-->
        <setting name="defaultStatementTimeout" value="30"/>
        <!-- 允许在嵌套语句中使用行分界 -->
        <setting name="safeRowBoundsEnabled" value="false"/>
        <!-- 是否开启自动驼峰命名规则映射 -->
        <setting name="mapUnderscoreToCamelCase" value="false"/>
        <!-- 利用本地缓存机制防止循环引用和加速重复嵌套查询 默认值为 SESSION，这种情况下会缓存一个会话中执行的所有查询-->
        <setting name="localCacheScope" value="SESSION"/>
        <!-- 当没有为参数提供特定的 JDBC 类型时，为空值指定 JDBC 类型 -->
        <setting name="jdbcTypeForNull" value="OTHER"/>
        <!-- 指定哪些对象的方法触发一次延迟加载-->
        <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
    </settings>

    <typeAliases>
        <package name="com.pocketdigi.dal.po"/>
    </typeAliases>
</configuration>

数据库对应的POJO类在com.pocketdigi.dal.po

druid监控后台配置

druid监控后台需要配置一个Filter和一个Servlet,将指定的路径转发到com.alibaba.druid.support.http.StatViewServlet

ServletConfiguration.java:

@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;
    }
}

FilterConfiguration.java

@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;
    }
}

配置完成，启动应用，打开http://localhost:8000/druid/ 用ServletConfiguration.java配置的账号密码登录，即可进入druid监控后台。

代码已传github https://github.com/pocketdigi/springboot-demo