jfinal-配置druid进行监控及打印sql语句

背景介绍

前一段时间看了一篇打印sql语句的分享,如下:Jfinal中使用日志框架输出完整sql语句信息

该文章中使用额外的jar包进行sql语句打印,

<dependency>
    <groupId>com.googlecode.log4jdbc</groupId>
    <artifactId>log4jdbc</artifactId>
    <version>1.2</version>
</dependency>

但是因为日志框架的问题无法使用,谁知柳暗花明又一村,让我发现了druid,druid能做的事情更多,除了打印sql外还能监控,统计.druid常见问题

本文整理了druid的sql语句打印,监控统计功能的配置

sql语句打印

filter配置

druid使用了拦截过滤器模式,只要配置相应Filter的即可,代码如下

/**
 * @return 配置完善的DruidPlugin
 */
public static DruidPlugin createDruidPlugin() {
   // 从配置文件中读取数据库url,username,password
   String name = Const.devProfile.getName();
   String url = prop.get(name + ".db.url");
   String username = prop.get(name + ".db.username");
   String password = prop.get(name + ".db.password").trim();
   DruidPlugin dp = new DruidPlugin(url, username, password);
   // 添加数据库插件
   // 1.统计信息插件
   StatFilter statFilter = new StatFilter();
   statFilter.setMergeSql(true);
   statFilter.setLogSlowSql(true);
   // 慢查询目前设置为1s,随着优化一步步进行慢慢更改
   statFilter.setSlowSqlMillis(Duration.ofMillis(1000).toMillis());
   dp.addFilter(statFilter);
   // 2.日志插件
   // 保存DruidDataSource的监控记录,设置打印日志周期,默认使用DruidDataSourceStatLoggerImpl
   // DruidPlugin未暴露setTimeBetweenLogStatsMillis(),只能使用properties方法设置
   dp.setConnectionProperties("druid.timeBetweenLogStatsMillis="
         + Duration.ofHours(24).toMillis());
   Slf4jLogFilter slf4jLogFilter = new Slf4jLogFilter();
   slf4jLogFilter.setDataSourceLogEnabled(false);
   slf4jLogFilter.setConnectionLogEnabled(false);
   slf4jLogFilter.setConnectionLogErrorEnabled(true);
   slf4jLogFilter.setStatementLogEnabled(false);
   slf4jLogFilter.setStatementLogErrorEnabled(true);
   slf4jLogFilter.setResultSetLogEnabled(false);
   slf4jLogFilter.setResultSetLogErrorEnabled(true);
   slf4jLogFilter.setConnectionConnectBeforeLogEnabled(false);
   slf4jLogFilter.setConnectionConnectAfterLogEnabled(false);
   slf4jLogFilter.setConnectionCommitAfterLogEnabled(false);
   slf4jLogFilter.setConnectionRollbackAfterLogEnabled(true);
   slf4jLogFilter.setConnectionCloseAfterLogEnabled(false);
   slf4jLogFilter.setStatementCreateAfterLogEnabled(false);
   slf4jLogFilter.setStatementPrepareAfterLogEnabled(false);
   slf4jLogFilter.setStatementPrepareCallAfterLogEnabled(false);
   slf4jLogFilter.setStatementExecuteAfterLogEnabled(false);
   slf4jLogFilter.setStatementExecuteQueryAfterLogEnabled(false);
   slf4jLogFilter.setStatementExecuteUpdateAfterLogEnabled(false);
   slf4jLogFilter.setStatementExecuteBatchAfterLogEnabled(false);
   slf4jLogFilter.setStatementCloseAfterLogEnabled(false);
   slf4jLogFilter.setStatementParameterSetLogEnabled(false);
   slf4jLogFilter.setResultSetNextAfterLogEnabled(false);
   slf4jLogFilter.setResultSetOpenAfterLogEnabled(false);
   slf4jLogFilter.setResultSetCloseAfterLogEnabled(false);
   if (Const.devProfile == Const.DevProfile.LOCAL_DEV
         || Const.devProfile == Const.DevProfile.LOCAL_TEST) {
      slf4jLogFilter.setStatementExecutableSqlLogEnable(true);
   }
   dp.addFilter(slf4jLogFilter);
   // 3.防注入插件
   WallFilter wall = new WallFilter();
   wall.setDbType("mysql");
   dp.addFilter(wall);
   return dp;
}

配置了3个Filter,就是配置Slf4jLogFilter时需要注意哪些需要打印,哪些不需要

log4j2 配置

此外,还要在log4j2中配置日志过滤,我的Logger配置如下

<Loggers>
    <!--打印无用日志较多的设置level为info-->
    <!--<Logger name="net.sf.ehcache" level="WARN"/>-->
    <Logger name="org.apache.shiro.web.servlet.SimpleCookie" level="info"/>
    <Logger name="org.apache.shiro" level="info"/>

    <!--设置druid日志level为debug-->
    <Logger name="druid" level="debug" additivity="false">
        <AppenderRef ref="sql"/>
        <AppenderRef ref="Console"/>
    </Logger>

    <Root level="debug">
        <AppenderRef ref="Console"/>
        <AppenderRef ref="debug"/>
        <AppenderRef ref="info"/>
        <AppenderRef ref="warn"/>
        <AppenderRef ref="error"/>
    </Root>
</Loggers>

druid的监控功能

druid的监控功能是通过配置servlet和filter实现的,配置如下:

<!--配置Druid的WebStatFilter-->
<filter>
    <filter-name>DruidWebStatFilter</filter-name>
    <filter-class>com.app.vocalist.filter.druid.MyWebStatFilter</filter-class>
    <init-param>
        <param-name>exclusions</param-name>
        <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>
    </init-param>
    <init-param>
        <!--开启session监控-->
        <param-name>sessionStatEnable</param-name>
        <param-value>true</param-value>
    </init-param>
    <init-param>
        <!--最大监控session数-->
        <param-name>sessionStatMaxCount</param-name>
        <param-value>2000</param-value>
    </init-param>
    <init-param>
        <!--principal在session中的属性名-->
        <param-name>principalSessionName</param-name>
        <param-value>user</param-value>
    </init-param>
    <init-param>
        <!--单条url分析-->
        <param-name>profileEnable</param-name>
        <param-value>true</param-value>
    </init-param>
</filter>
<!--Druid内置监控配置-->
<servlet>
    <servlet-name>DruidStatView</servlet-name>
    <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
    <init-param>
        <!-- 用户名 -->
        <param-name>loginUsername</param-name>
        <param-value>root</param-value>
    </init-param>
    <init-param>
        <!-- 密码 -->
        <param-name>loginPassword</param-name>
        <param-value>root</param-value>
    </init-param>
</servlet>
<servlet-mapping>
    <servlet-name>DruidStatView</servlet-name>
    <url-pattern>/druid/*</url-pattern>
</servlet-mapping>

注意:

• com.app.vocalist.filter.druid.MyWebStatFilter是我写的继承com.alibaba.druid.support.http.WebStatFilter的一个类,重写了getPrincipal(httpRequest)
• jfinal框架是一个Filter,处理request后不会将其交给后续的servlet,而是直接返回(其实是使用了boolean[] isHandled = {false}作为标志,但是在ActionHandler中会将其设置为true,doFilter()中判断为true就直接返回了)
• 可以在jfinal中设置com.jfinal.plugin.druid.DruidStatViewHandler进行处理,我所有的url格式为/api/*,所以将配置<filter-mapping>即可

<filter-mapping>
    <filter-name>jfinal</filter-name>
    <url-pattern>/api/*</url-pattern>
</filter-mapping>

参考

• Jfinal中使用日志框架输出完整sql语句信息
• druid常见问题