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

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

什么是druid

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

为什么用druid

  1. 性能

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

  2. 自带监控功能

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

配置步骤:

  1. 基于干净的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>
    
  2. 配置数据库连接

    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叫什么其实并不重要,因为后面配置数据源,我们会自己读取配置。

  3. 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

  4. 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

© 2017, 冰冻鱼. 请尊重作者劳动成果,复制转载保留本站链接! 应用开发笔记