mybatis使用拦截器显示sql,使用druid配置连接信息

com.cpp.core.filter; import java.text.DateFormat; import java.util.Date; import java.util.List; import java.util.Locale; import java.util.Properties; import org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; import org.apache.ibatis.type.TypeHandlerRegistry; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.cpp.core.common.utils.SQLFormatter; @Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }), @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) }) public class MybatisInterceptor implements Interceptor { private static Logger logger = LoggerFactory.getLogger(MybatisInterceptor.class); private Properties properties; public Object intercept(Invocation invocation) throws Throwable { MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; Object parameter = null; if (invocation.getArgs().length > 1) { parameter = invocation.getArgs()[1]; } String sqlId = mappedStatement.getId(); BoundSql boundSql = mappedStatement.getBoundSql(parameter); Configuration configuration = mappedStatement.getConfiguration(); Object returnValue = null; long start = System.currentTimeMillis(); returnValue = invocation.proceed(); long end = System.currentTimeMillis(); long time = (end - start); if (time > 1) { String sql = getSql(configuration, boundSql, sqlId, time); logger.info("调用的java方法为：\n "+sql.split(":")[0]); logger.info("查询sql语句为：\n"+SQLFormatter.format(sql.split(":")[1]) +"\n"+"sql语句执行的时间："+time+"\n\n"); } return returnValue; } public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) { String sql = showSql(configuration, boundSql); StringBuilder str = new StringBuilder(100); str.append(sqlId); str.append(":"); str.append(sql); str.append(":"); str.append(time); str.append("ms"); return str.toString(); } private static String getParameterValue(Object obj) { String value = null; if (obj instanceof String) { value = "'" + obj.toString() + "'"; } else if (obj instanceof Date) { DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA); value = "'" + formatter.format(new Date()) + "'"; } else { if (obj != null) { value = obj.toString(); } else { value = ""; } } return value; } public static String showSql(Configuration configuration, BoundSql boundSql) { Object parameterObject = boundSql.getParameterObject(); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); String sql = boundSql.getSql().replaceAll("[\\s]+", " "); if (parameterMappings.size() > 0 && parameterObject != null) { TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { sql = sql.replaceFirst("\\?", getParameterValue(parameterObject)); } else { MetaObject metaObject = configuration.newMetaObject(parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { String propertyName = parameterMapping.getProperty(); if (metaObject.hasGetter(propertyName)) { Object obj = metaObject.getValue(propertyName); sql = sql.replaceFirst("\\?", getParameterValue(obj)); } else if (boundSql.hasAdditionalParameter(propertyName)) { Object obj = boundSql.getAdditionalParameter(propertyName); sql = sql.replaceFirst("\\?", getParameterValue(obj)); } } } } return sql; } public Object plugin(Object target) { return Plugin.wrap(target, this); } public void setProperties(Properties properties0) { this.properties = properties0; } }

2、在新建sql格式化工具，格式化sql语句

<code class="language-java hljs" style="background-color: #D6DBDF; border: 0; border-radius: 4px; color: #68615e; font-size: 90%; padding-top: 0.5em; padding-right: 0.5em; padding-bottom: 0.5em; padding-left: 0.5em; display: block; overflow-x: auto; background: #f1efee; -webkit-text-size-adjust: none;"><span class="hljs-keyword" style="color: #6666ea;">package</span> com.cpp.core.filter;
<span class="hljs-keyword" style="color: #6666ea;">import</span> java.util.Properties;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.executor.Executor;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.mapping.MappedStatement;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.plugin.Interceptor;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.plugin.Intercepts;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.plugin.Invocation;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.plugin.Plugin;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.plugin.Signature;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.session.ResultHandler;
<span class="hljs-keyword" style="color: #6666ea;">import</span> org.apache.ibatis.session.RowBounds;
<span class="hljs-annotation">@Intercepts</span>({
    <span class="hljs-annotation">@Signature</span>(type = Executor.class, method = <span class="hljs-string" style="color: #7b9726;">"update"</span>, args = { MappedStatement.class, Object.class }),
    <span class="hljs-annotation">@Signature</span>(type = Executor.class, method = <span class="hljs-string" style="color: #7b9726;">"query"</span>, args = { MappedStatement.class, Object.class,
            RowBounds.class, ResultHandler.class }) })
<span class="hljs-keyword" style="color: #6666ea;">public</span> <span class="hljs-class"><span class="hljs-keyword" style="color: #6666ea;">class</span> <span class="hljs-title" style="color: #3d97b8;">SqlStatementInterceptor</span> <span class="hljs-keyword" style="color: #6666ea;">implements</span> <span class="hljs-title" style="color: #3d97b8;">Interceptor</span></span>{
    <span class="hljs-keyword" style="color: #6666ea;">private</span> Properties properties;
    <span class="hljs-annotation">@Override</span>
    <span class="hljs-function" style="color: #407ee7;"><span class="hljs-keyword" style="color: #6666ea;">public</span> Object <span class="hljs-title" style="color: #3d97b8;">intercept</span><span class="hljs-params" style="color: #df5320;">(Invocation invocation)</span> <span class="hljs-keyword" style="color: #6666ea;">throws</span> Throwable </span>{
        System.out.println(<span class="hljs-string" style="color: #7b9726;">"test"</span>);
        <span class="hljs-keyword" style="color: #6666ea;">return</span> invocation.proceed();
    }
    <span class="hljs-annotation">@Override</span>
    <span class="hljs-function" style="color: #407ee7;"><span class="hljs-keyword" style="color: #6666ea;">public</span> Object <span class="hljs-title" style="color: #3d97b8;">plugin</span><span class="hljs-params" style="color: #df5320;">(Object target)</span> </span>{
        <span class="hljs-keyword" style="color: #6666ea;">return</span> Plugin.wrap(target, <span class="hljs-keyword" style="color: #6666ea;">this</span>);
    }
    <span class="hljs-annotation">@Override</span>
    <span class="hljs-function" style="color: #407ee7;"><span class="hljs-keyword" style="color: #6666ea;">public</span> <span class="hljs-keyword" style="color: #6666ea;">void</span> <span class="hljs-title" style="color: #3d97b8;">setProperties</span><span class="hljs-params" style="color: #df5320;">(Properties properties0)</span> </span>{
        <span class="hljs-keyword" style="color: #6666ea;">this</span>.properties = properties0;
    }
}
</code>

3、在spring的配置文件中添加

<code class="language-xml hljs" style="background-color: #D6DBDF; border: 0; border-radius: 4px; color: #68615e; font-size: 90%; padding-top: 0.5em; padding-right: 0.5em; padding-bottom: 0.5em; padding-left: 0.5em; display: block; overflow-x: auto; background: #f1efee; -webkit-text-size-adjust: none;"> <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">bean</span> <span class="hljs-attribute" style="color: #f22c40;">id</span>=<span class="hljs-value" style="color: #7b9726;">"sqlSessionFactory"</span> <span class="hljs-attribute" style="color: #f22c40;">class</span>=<span class="hljs-value" style="color: #7b9726;">"org.mybatis.spring.SqlSessionFactoryBean"</span>></span>
        <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">property</span> <span class="hljs-attribute" style="color: #f22c40;">name</span>=<span class="hljs-value" style="color: #7b9726;">"dataSource"</span> <span class="hljs-attribute" style="color: #f22c40;">ref</span>=<span class="hljs-value" style="color: #7b9726;">"dataSource"</span> /></span>
        <span class="hljs-comment" style="color: #766e6b;"><!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 --></span>
        <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">property</span> <span class="hljs-attribute" style="color: #f22c40;">name</span>=<span class="hljs-value" style="color: #7b9726;">"typeAliasesPackage"</span> <span class="hljs-attribute" style="color: #f22c40;">value</span>=<span class="hljs-value" style="color: #7b9726;">"com.cpp.core"</span> /></span>
        <span class="hljs-comment" style="color: #766e6b;"><!-- 显式指定Mapper文件位置 --></span>
        <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">property</span> <span class="hljs-attribute" style="color: #f22c40;">name</span>=<span class="hljs-value" style="color: #7b9726;">"mapperLocations"</span> <span class="hljs-attribute" style="color: #f22c40;">value</span>=<span class="hljs-value" style="color: #7b9726;">"classpath:/mybatis/*/*Mapper.xml"</span> /></span>
        <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">property</span> <span class="hljs-attribute" style="color: #f22c40;">name</span>=<span class="hljs-value" style="color: #7b9726;">"plugins"</span>></span>
            <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">array</span>></span>
                <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">ref</span> <span class="hljs-attribute" style="color: #f22c40;">bean</span>=<span class="hljs-value" style="color: #7b9726;">"paginationInterceptor"</span>/></span>
                <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">ref</span> <span class="hljs-attribute" style="color: #f22c40;">bean</span>=<span class="hljs-value" style="color: #7b9726;">"sqlStatementInterceptor"</span>/></span>
            <span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">array</span>></span>
        <span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">property</span>></span>
        <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">property</span> <span class="hljs-attribute" style="color: #f22c40;">name</span>=<span class="hljs-value" style="color: #7b9726;">"configurationProperties"</span>></span>
            <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">props</span>></span>
                <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">prop</span> <span class="hljs-attribute" style="color: #f22c40;">key</span>=<span class="hljs-value" style="color: #7b9726;">"dialect"</span>></span>mysql<span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">prop</span>></span>
            <span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">props</span>></span>
        <span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">property</span>></span>
    <span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">bean</span>></span>
</code>

配置插件

<code class="language-xml hljs" style="background-color: #D6DBDF; border: 0; border-radius: 4px; color: #68615e; font-size: 90%; padding-top: 0.5em; padding-right: 0.5em; padding-bottom: 0.5em; padding-left: 0.5em; display: block; overflow-x: auto; background: #f1efee; -webkit-text-size-adjust: none;"> <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">ref</span> <span class="hljs-attribute" style="color: #f22c40;">bean</span>=<span class="hljs-value" style="color: #7b9726;">"sqlStatementInterceptor"</span>/></span>
</code>

配置注入的内容

<code class="language-xml hljs" style="background-color: #D6DBDF; border: 0; border-radius: 4px; color: #68615e; font-size: 90%; padding-top: 0.5em; padding-right: 0.5em; padding-bottom: 0.5em; padding-left: 0.5em; display: block; overflow-x: auto; background: #f1efee; -webkit-text-size-adjust: none;"> <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">bean</span> <span class="hljs-attribute" style="color: #f22c40;">id</span>=<span class="hljs-value" style="color: #7b9726;">"sqlStatementInterceptor"</span> <span class="hljs-attribute" style="color: #f22c40;">class</span>=<span class="hljs-value" style="color: #7b9726;">"com.cpp.core.filter.MybatisInterceptor"</span>></span><span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">bean</span>></span>
</code>

2、如果使用的是Druid来连接的数据库

---

在web.xml文件中添加下面的信息

<code class="language-xml hljs" style="background-color: #D6DBDF; border: 0; border-radius: 4px; color: #68615e; font-size: 90%; padding-top: 0.5em; padding-right: 0.5em; padding-bottom: 0.5em; padding-left: 0.5em; display: block; overflow-x: auto; background: #f1efee; -webkit-text-size-adjust: none;"> <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">servlet</span>></span>
      <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">servlet-name</span>></span>DruidStatView<span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">servlet-name</span>></span>
      <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">servlet-class</span>></span>com.alibaba.druid.support.http.StatViewServlet<span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">servlet-class</span>></span>
  <span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">servlet</span>></span>
  <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">servlet-mapping</span>></span>
      <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">servlet-name</span>></span>DruidStatView<span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">servlet-name</span>></span>
      <span class="hljs-tag" style="color: #f22c40;"><<span class="hljs-title" style="color: #3d97b8;">url-pattern</span>></span>/druid/*<span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">url-pattern</span>></span>
  <span class="hljs-tag" style="color: #f22c40;"></<span class="hljs-title" style="color: #3d97b8;">servlet-mapping</span>></span>  
</code>

可以通过web的访问的形式进行访问页面：输入：
http://dev.eop.zhc360.com:8080/cpp-middleman-api/druid/sql.html
就可以查看到输出的sql语句了

mybatis使用拦截器显示sql,使用druid配置连接信息

标签：