前一段时间看了一篇打印sql语句的分享,如下:
http://www.jfinal.com/share/324
该文章中使用额外的jar包进行sql语句打印,
<dependency> <groupId>com.googlecode.log4jdbc</groupId> <artifactId>log4jdbc</artifactId> <version>1.2</version> </dependency>
但是因为日志框架的问题无法使用,谁知柳暗花明又一村,让我发现了druid,druid能做的事情更多,除了打印sql外还能监控,统计,文档地址如下:
https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98
本文整理了druid的sql语句打印,监控统计功能的配置
sql语句打印
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中配置日志过滤,我的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>
2.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/*,所以将JFinal
<filter-mapping> <filter-name>jfinal</filter-name> <url-pattern>/api/*</url-pattern> </filter-mapping>
即可
