MyBatis拦截器打印不带问号的完整sql语句方法

转载

mb5fdb12e4adbb2 2018-05-11 17:10:00

文章标签 MyBatis sql apache sql语句 java 文章分类 MySQL 数据库

1	`/* Preparing: SELECT * FROM tb_user WHERE id = ? AND user_name = ? <br> 目标是打印：SELECT * FROM tb_user WHERE id = 1000059081 AND user_name = '积极'<br>*/` `这部分代码只是拦截了查询和更新，如果想对其他语句进行拦截，在``@Intercepts``中添加对应方法即可`

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119	`<br>``package` `dao.Interceptor;` `import` `org.apache.commons.collections.CollectionUtils;` `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.;` `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` `java.text.DateFormat;` `import` `java.util.Date;` `import` `java.util.List;` `import` `java.util.Locale;` `import` `java.util.Properties;` `import` `java.util.regex.Matcher;` `@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` `}) })` `@SuppressWarnings``({``"unchecked"``,` `"rawtypes"``})` `public` `class` `MybatisInterceptor` `implements` `Interceptor {` `@Override` `public` `Object intercept(Invocation invocation)` `throws` `Throwable {` `try``{` `MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[``0``];` `// 获取xml中的一个select/update/insert/delete节点，主要描述的是一条SQL语句` `Object parameter =` `null``;` `// 获取参数，if语句成立，表示sql语句有参数，参数格式是map形式` `if` `(invocation.getArgs().length >` `1``) {` `parameter = invocation.getArgs()[``1``];` `System.out.println(``"parameter = "` `+ parameter);` `}` `String sqlId = mappedStatement.getId();` `// 获取到节点的id,即sql语句的id` `System.out.println(``"sqlId = "` `+ sqlId);` `BoundSql boundSql = mappedStatement.getBoundSql(parameter);` `// BoundSql就是封装myBatis最终产生的sql类` `Configuration configuration = mappedStatement.getConfiguration();` `// 获取节点的配置` `String sql = getSql(configuration, boundSql, sqlId);` `// 获取到最终的sql语句` `System.out.println(``"sql = "` `+ sql);` `//log.debug(sql);` `}``catch``(Exception e){` `// log.error(e.getMessage(), e);` `}` `return` `invocation.proceed();` `// 执行完上面的任务后，不改变原有的sql执行过程` `}` `// 封装了一下sql语句，使得结果返回完整xml路径下的sql语句节点id + sql语句` `public` `static` `String getSql(Configuration configuration, BoundSql boundSql,String sqlId) {` `String sql = showSql(configuration, boundSql);` `StringBuilder str =` `new` `StringBuilder(``100``);` `str.append(sqlId);` `str.append(``":"``);` `str.append(sql);` `return` `str.toString();` `}` `/<br> 如果参数是String，则添加单引号，如果是日期，则转换为时间格式器并加单引号；对参数是null和不是null的情况作了处理<br>　　/` `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]+"``,` `" "``);` `// sql语句中多个空格都用一个空格代替` `if` `(CollectionUtils.isNotEmpty(parameterMappings) && parameterObject !=` `null``) {` `TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();` `// 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换<br>　　　　　　　// 如果根据parameterObject.getClass(）可以找到对应的类型，则替换` `if` `(typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {` `sql = sql.replaceFirst(``"\\?"``, Matcher.quoteReplacement(getParameterValue(parameterObject)));` `}` `else` `{` `MetaObject metaObject = configuration.newMetaObject(parameterObject);``// MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作` `for` `(ParameterMapping parameterMapping : parameterMappings) {` `String propertyName = parameterMapping.getProperty();` `if` `(metaObject.hasGetter(propertyName)) {` `Object obj = metaObject.getValue(propertyName);` `sql = sql.replaceFirst(``"\\?"``, Matcher.quoteReplacement(getParameterValue(obj)));` `}` `else` `if` `(boundSql.hasAdditionalParameter(propertyName)) {` `Object obj = boundSql.getAdditionalParameter(propertyName);` `// 该分支是动态sql` `sql = sql.replaceFirst(``"\\?"``, Matcher.quoteReplacement(getParameterValue(obj)));` `}``else``{sql=sql.replaceFirst(``"\\?"``,``"缺失"``);}``//打印出缺失，提醒该参数缺失并防止错位` `}` `}` `}` `return` `sql;` `}` `@Override` `public` `Object plugin(Object target) {` `return` `Plugin.wrap(target,` `this``);` `}` `@Override` `public` `void` `setProperties(Properties properties) {` `}` `}`

　Mybatis配置文件如下：

MyBatis拦截器打印不带问号的完整sql语句方法_MyBatis

<?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>
　　<！--该路径是拦截器文件的路径 dao.Interceptor是拦截器文件的包名称>

<plugins>

<plugin interceptor="dao.Interceptor.MybatisInterceptor">

</plugin>

</plugins>


<span style="color: #0000ff"><</span><span style="color: #800000">environments </span><span style="color: #ff0000">default</span><span style="color: #0000ff">="development"</span><span style="color: #0000ff">></span></br>
    <span style="color: #0000ff"><</span><span style="color: #800000">environment </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="development"</span><span style="color: #0000ff">></span></br>
            <span style="color: #0000ff"><</span><span style="color: #800000">transactionManager </span><span style="color: #ff0000">type</span><span style="color: #0000ff">="JDBC"</span><span style="color: #0000ff">></span></br>
                <span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">=""</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">=""</span><span style="color: #0000ff">></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span></br>
            <span style="color: #0000ff"></</span><span style="color: #800000">transactionManager</span><span style="color: #0000ff">></span>
            <span style="color: #0000ff"><</span><span style="color: #800000">dataSource </span><span style="color: #ff0000">type</span><span style="color: #0000ff">="UNPOOLED"</span><span style="color: #0000ff">></span></br>
                <span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="driver"</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">="oracle.jdbc.driver.OracleDriver"</span><span style="color: #0000ff">></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span>></br>
                <span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="url"</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">="jdbc:oracle:thin:@xx.xxx.xxx.xxx:端口:oratest"</span><span style="color: #0000ff">></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span></br>
                <span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="username"</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">="用户名"</span><span style="color: #0000ff">></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span></br>
                <span style="color: #0000ff"><</span><span style="color: #800000">property </span><span style="color: #ff0000">name</span><span style="color: #0000ff">="password"</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">="密码"</span><span style="color: #0000ff">></</span><span style="color: #800000">property</span><span style="color: #0000ff">></span></br></br>
            <span style="color: #0000ff"></</span><span style="color: #800000">dataSource</span><span style="color: #0000ff">></span></br>
    <span style="color: #0000ff"></</span><span style="color: #800000">environment</span><span style="color: #0000ff">></span></br>
<span style="color: #0000ff"></</span><span style="color: #800000">environments</span><span style="color: #0000ff">></span></br>

<span style="color: #0000ff"><</span><span style="color: #800000">mappers</span><span style="color: #0000ff">></span>  <br><br>　　　　　　<mapper resource="mybatis/UserMapper.xml"/></pre>

    </mappers>
</configuration>