Sring Boot starter 实现 Mybatis-log-plugin 插件功能

由于mybatis合mybatis-plus的sql日志打印在控制台总是在一行显示，我们查看sql日志时，不能直观的查看，而且 Mybatis-log-plugin 插件 目前好像要收费使用了，所以试着玩一下。

首先，创建一个springboot工程，导入该需要的maven坐标。

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <version>2.3.2.RELEASE</version>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

编写一个自定义注解，用来开启插件功能。

package com.mybatis.sql.log.plugin.annotion;

import com.mybatis.sql.log.plugin.conf.MybatisInterceptorConfig;
import org.springframework.context.annotation.Import;
import org.springframework.stereotype.Component;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * <p>
 *
 * </p>
 *
 * @author wangbin
 * @date 2021/6/9 17:03
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
@Component
@Import({MybatisInterceptorConfig.class})
public @interface EnableSqlLog {
}

编写springboot配置类。

package com.mybatis.sql.log.plugin.conf;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

/**
 * 自定义mybatis拦截器
 *
 * @author zengsong
 * @version 1.0
 * @description
 * @date 2019/5/30 10:17
 **/
@Configuration
@EnableConfigurationProperties(MybatisPluginProperties.class)
@ConditionalOnProperty(value = "myplugin.enable", matchIfMissing = true)
@ComponentScan("com.mybatis.sql.log.plugin")
@Slf4j
public class MybatisInterceptorConfig
{

    @Bean
    public String myPrimaryInterceptor(SqlSessionFactory sqlSessionFactory) {
        sqlSessionFactory.getConfiguration().addInterceptor(new MybatisResultInterceptor());
        log.info("--------------------------myPrimaryInterceptor registered -------------------------");
        return "myPrimaryInterceptor";
    }

    @ConditionalOnBean(name = {"secondSqlSessionFactory"})
    @ConditionalOnProperty(value = "myplugin.second.enable", matchIfMissing = true)
    @Bean
    public String mySecondInterceptor(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        sqlSessionFactory.getConfiguration().addInterceptor(new MybatisResultInterceptor());
        log.info("--------------------------mySecondInterceptor registered -------------------------");
        return "mySecondInterceptor";
    }
}

编写mybatis拦截器。

package com.mybatis.sql.log.plugin.conf;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.druid.pool.DruidPooledPreparedStatement;
import com.alibaba.druid.proxy.jdbc.PreparedStatementProxy;
import com.mybatis.sql.log.plugin.conf.util.ApplicationUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.factory.annotation.Autowired;

import java.lang.reflect.InvocationTargetException;
import java.sql.PreparedStatement;

/**
 * <p>
 *
 * </p>
 *
 * @author wangbin
 * @date 2021/6/4 14:03
 */
@Slf4j
@Intercepts({@Signature(
        type = ParameterHandler.class,
        method = "setParameters",
        args = {PreparedStatement.class}
), @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}
), @Signature(
        type = Executor.class,
        method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}
)})
public class MybatisResultInterceptor implements Interceptor {

    @Autowired
    MybatisPluginProperties properties;

    public MybatisResultInterceptor(){
        log.info("--------------------------MybatisResultInterceptor init -------------------------");
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof Executor) {
            return this.processExecutor(invocation);
        } else {
            return invocation.getTarget() instanceof ParameterHandler ? this.processParameterHandler(invocation) : invocation.proceed();
        }
    }

    private Object processExecutor(Invocation invocation) throws Exception {
        MappedStatement mappedStatement = (MappedStatement)invocation.getArgs()[0];
        String sqlId = mappedStatement == null ? "" : mappedStatement.getId();

        try {
            long startMillis = System.currentTimeMillis();
            log.info("{} start at {}", sqlId, startMillis);
            Object object = invocation.proceed();
            long endMillis = System.currentTimeMillis();
            long timeUsed = endMillis - startMillis;
            log.info("{} end at {}, cost:{}ms", new Object[]{sqlId, endMillis, timeUsed});
            return object;
        } catch (Exception var11) {
            log.error("[" + sqlId + "] - error", var11);
            throw var11;
        }
    }

    private Object processParameterHandler(Invocation invocation) throws InvocationTargetException, IllegalAccessException {
        String sql = null;
        Object object = invocation.proceed();
        Object psObj = invocation.getArgs()[0];
        if (psObj instanceof DruidPooledPreparedStatement) {
            Object psObj2 = ((DruidPooledPreparedStatement)psObj).getStatement();
            if (psObj2 instanceof PreparedStatementProxy) {
                sql = ((PreparedStatementProxy)psObj2).getRawObject().toString();
            } else {
                sql = psObj2.toString();
            }
        } else if (psObj instanceof PreparedStatement) {
            sql = psObj.toString();
        } else {
            sql = String.valueOf(psObj);
        }

        int index = sql.indexOf(58);
        if (index > -1) {
            sql = sql.substring(index + 1);
        }
        try {
            setProperties();
            printLog(sql);
        }catch (Exception e){
            log.error("------SQL 格式化异常！！！ SQL:[{}]",sql);
            log.error("------SQL 格式化异常！！！ Exception:[{}]",e);
        }
        return object;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    private void printLog(String resource){
        boolean converCase = properties.isConvercase();;
        switch (properties.getLevel().toLowerCase()){
            case "info":
                log.info(SQLFormatterUtil.format(resource, converCase));
                break;
            case "debug":
                log.debug(SQLFormatterUtil.format(resource, converCase));
                break;
            case "warn":
                log.warn(SQLFormatterUtil.format(resource, converCase));
                break;
            case "error":
                log.error(SQLFormatterUtil.format(resource, converCase));
                break;
            default:
                log.info(SQLFormatterUtil.format(resource, converCase));
                break;
        }
    }

    private void setProperties(){
        if(ObjectUtil.isEmpty(properties)){
            properties = (MybatisPluginProperties) ApplicationUtils.getBean(MybatisPluginProperties.class);
        }
        if(StringUtils.isEmpty(properties.getLevel())){
            properties.setLevel("info");
        }
    }
}

拷贝sql格式化工具类。

package com.mybatis.sql.log.plugin.conf;

import java.util.HashSet;
import java.util.LinkedList;
import java.util.Set;
import java.util.StringTokenizer;

public class SQLFormatterUtil {


    private static final Set<String> BEGIN_CLAUSES = new HashSet<String>();  
    private static final Set<String> END_CLAUSES = new HashSet<String>();  
    private static final Set<String> LOGICAL = new HashSet<String>();  
    private static final Set<String> QUANTIFIERS = new HashSet<String>();  
    private static final Set<String> DML = new HashSet<String>();  
    private static final Set<String> MISC = new HashSet<String>();  
    public static final String WHITESPACE = " \n\r\f\t";  
    static {  
        BEGIN_CLAUSES.add( "left" );  
        BEGIN_CLAUSES.add( "right" );  
        BEGIN_CLAUSES.add( "inner" );  
        BEGIN_CLAUSES.add( "outer" );  
        BEGIN_CLAUSES.add( "group" );  
        BEGIN_CLAUSES.add( "order" );  

        END_CLAUSES.add( "where" );  
        END_CLAUSES.add( "set" );  
        END_CLAUSES.add( "having" );  
        END_CLAUSES.add( "join" );  
        END_CLAUSES.add( "from" );  
        END_CLAUSES.add( "by" );  
        END_CLAUSES.add( "join" );  
        END_CLAUSES.add( "into" );  
        END_CLAUSES.add( "union" );  

        LOGICAL.add( "and" );  
        LOGICAL.add( "or" );  
        LOGICAL.add( "when" );  
        LOGICAL.add( "else" );  
        LOGICAL.add( "end" );  

        QUANTIFIERS.add( "in" );  
        QUANTIFIERS.add( "all" );  
        QUANTIFIERS.add( "exists" );  
        QUANTIFIERS.add( "some" );  
        QUANTIFIERS.add( "any" );  

        DML.add( "insert" );  
        DML.add( "update" );  
        DML.add( "delete" );  

        MISC.add( "select" );  
        MISC.add( "on" );  
    }  

    static final String indentString = "    ";  
    static final String initial = "\n    ";

    private SQLFormatterUtil(){};
    public static String format(String source) {
        return new FormatProcess( source, false ).perform();
    }  
    public static String format(String source, boolean converCase) {
        return new FormatProcess( source , converCase ).perform();
    }

    private static class FormatProcess {  
        boolean beginLine = true;  
        boolean afterBeginBeforeEnd = false;  
        boolean afterByOrSetOrFromOrSelect = false;  
        boolean afterValues = false;  
        boolean afterOn = false;  
        boolean afterBetween = false;  
        boolean afterInsert = false;
        boolean ischildSelect = false;
        boolean converCase;
        int inFunction = 0;
        int parensSinceSelect = 0;  
        private LinkedList<Integer> parenCounts = new LinkedList<Integer>();  
        private LinkedList<Boolean> afterByOrFromOrSelects = new LinkedList<Boolean>();  

        int indent = 1;  

        StringBuilder result = new StringBuilder();  
        StringTokenizer tokens;  
        String lastToken;  
        String token;  
        String lcToken;  

        public FormatProcess(String sql, boolean converCase) {
            tokens = new StringTokenizer(  
                    sql,  
                    "()+*/-=<>'`\"[]," + WHITESPACE,  
                    true  
            );
            this.converCase = converCase;
        }  

        public String perform() {  

            result.append( initial );

            while ( tokens.hasMoreTokens() ) {  
                token = tokens.nextToken();  
                lcToken = token.toLowerCase();  

                if ( "'".equals( token ) ) {  
                    String t;  
                    do {  
                        t = tokens.nextToken();  
                        token += t;  
                    }  
                    while ( !"'".equals( t ) && tokens.hasMoreTokens() ); // cannot handle single quotes  
                }  
                else if ( "\"".equals( token ) ) {  
                    String t;  
                    do {  
                        t = tokens.nextToken();  
                        token += t;  
                    }  
                    while ( !"\"".equals( t ) );
                }

                if ( afterByOrSetOrFromOrSelect && ",".equals( token ) ) {
                    commaAfterByOrFromOrSelect();
                }  
                else if ( afterOn && ",".equals( token ) ) {
                    commaAfterOn();
                }  

                else if ( "(".equals( token ) ) {  
                    openParen();  
                }  
                else if ( ")".equals( token ) ) {  
                    closeParen();  
                }  

                else if ( BEGIN_CLAUSES.contains( lcToken ) ) {
                    converCase(lcToken);
                    beginNewClause();
                }  

                else if ( END_CLAUSES.contains( lcToken ) ) {
                    converCase(lcToken);
                    endNewClause();  
                }  

                else if ( "select".equals( lcToken ) ) {
                    converCase(lcToken);
                    select();  
                }  

                else if ( DML.contains( lcToken ) ) {
                    converCase(lcToken);
                    updateOrInsertOrDelete();  
                }  

                else if ( "values".equals( lcToken ) ) {
                    converCase(lcToken);
                    values();  
                }  

                else if ( "on".equals( lcToken ) ) {
                    converCase(lcToken);
                    on();  
                }  

                else if ( afterBetween && lcToken.equals( "and" ) ) {
                    converCase(lcToken);
                    misc();  
                    afterBetween = false;  
                }  

                else if ( LOGICAL.contains( lcToken ) ) {
                    converCase(lcToken);
                    logical();  
                }  

                else if ( isWhitespace( token ) ) {
                    white();
                }  

                else {
                    misc();
                }  

                if ( !isWhitespace( token ) ) {  
                    lastToken = lcToken;  
                }  

            }  
            return result.toString();  
        }

        private void converCase(String resource){
            if(converCase){
                token = resource.toUpperCase();
            }
        }

        private void commaAfterOn() {  
            out();  
            indent--;  
            newline();  
            afterOn = false;  
            afterByOrSetOrFromOrSelect = true;  
        }  

        private void commaAfterByOrFromOrSelect() {  
            out();  
            newline();  
        }  

        private void logical() {  
            if ( "end".equals( lcToken ) ) {  
                indent--;  
            }  
            newline();  
            out();  
            beginLine = false;  
        }  

        private void on() {  
            indent++;  
            afterOn = true;  
            newline();  
            out();  
            beginLine = false;  
        }  

        private void misc() {  
            out();  
            if ( "between".equals( lcToken ) ) {  
                afterBetween = true;  
            }  
            if ( afterInsert ) {  
                newline();  
                afterInsert = false;  
            }  
            else {  
                beginLine = false;  
                if ( "case".equals( lcToken ) ) {  
                    indent++;  
                }  
            }  
        }  

        private void white() {  
            if ( !beginLine ) {  
                result.append( " " );  
            }  
        }  

        private void updateOrInsertOrDelete() {  
            out();  
            indent++;  
            beginLine = false;  
            if ( "update".equals( lcToken ) ) {  
                newline();  
            }  
            if ( "insert".equals( lcToken ) ) {  
                afterInsert = true;  
            }  
        }  

        @SuppressWarnings( {"UnnecessaryBoxing"})  
        private void select() {
            if(!ischildSelect){
                newline();
            }
            out();
            indent++;  
            newline();  
            parenCounts.addLast( Integer.valueOf( parensSinceSelect ) );  
            afterByOrFromOrSelects.addLast( Boolean.valueOf( afterByOrSetOrFromOrSelect ) );  
            parensSinceSelect = 0;  
            afterByOrSetOrFromOrSelect = true;  
        }  

        private void out() {  
            result.append( token );  
        }  

        private void endNewClause() {  
            if ( !afterBeginBeforeEnd ) {  
                indent--;  
                if ( afterOn ) {  
                    indent--;  
                    afterOn = false;  
                }  
                newline();  
            }  
            out();  
            if ( !"union".equals( lcToken ) ) {  
                indent++;  
            }  
            newline();  
            afterBeginBeforeEnd = false;  
            afterByOrSetOrFromOrSelect = "by".equals( lcToken )  
                    || "set".equals( lcToken )  
                    || "from".equals( lcToken );  
        }  

        private void beginNewClause() {  
            if ( !afterBeginBeforeEnd ) {  
                if ( afterOn ) {  
                    indent--;  
                    afterOn = false;  
                }  
                indent--;  
                newline();  
            }  
            out();  
            beginLine = false;  
            afterBeginBeforeEnd = true;  
        }  

        private void values() {  
            indent--;  
            newline();  
            out();  
            indent++;  
            newline();  
            afterValues = true;  
        }  

        @SuppressWarnings( {"UnnecessaryUnboxing"})  
        private void closeParen() {
            ischildSelect = false;
            parensSinceSelect--;  
            if ( parensSinceSelect < 0 ) {  
                indent--;  
                parensSinceSelect = parenCounts.removeLast().intValue();  
                afterByOrSetOrFromOrSelect = afterByOrFromOrSelects.removeLast().booleanValue();  
            }  
            if ( inFunction > 0 ) {  
                inFunction--;  
                out();  
            }  
            else {  
                if ( !afterByOrSetOrFromOrSelect ) {  
                    indent--;  
                    newline();  
                }  
                out();  
            }  
            beginLine = false;  
        }  

        private void openParen() {
            ischildSelect = true;
            if ( isFunctionName( lastToken ) || inFunction > 0 ) {  
                inFunction++;  
            }  
            beginLine = false;  
            if ( inFunction > 0 ) {  
                out();  
            }  
            else {  
                out();  
                if ( !afterByOrSetOrFromOrSelect ) {  
                    indent++;  
                    newline();  
                    beginLine = true;  
                }  
            }  
            parensSinceSelect++;  
        }  

        private static boolean isFunctionName(String tok) {  
            final char begin = tok.charAt( 0 );  
            final boolean isIdentifier = Character.isJavaIdentifierStart( begin ) || '"' == begin;  
            return isIdentifier &&  
                    !LOGICAL.contains( tok ) &&  
                    !END_CLAUSES.contains( tok ) &&  
                    !QUANTIFIERS.contains( tok ) &&  
                    !DML.contains( tok ) &&  
                    !MISC.contains( tok );  
        }  

        private static boolean isWhitespace(String token) {  
            return WHITESPACE.indexOf( token ) >= 0;  
        }  

        private void newline() {  
            result.append( "\n" );  
            for ( int i = 0; i < indent; i++ ) {
                result.append( indentString );  
            }  
            beginLine = true;  
        }  
    }  

      public static void main(String[] args) {  
          String sql = "select\n" +
                  "    sprb.role_code,\n" +
                  "    ssmb.menu_code,\n" +
                  "    ssb.button_code,\n" +
                  "    ssb.button_name  \n" +
                  "from\n" +
                  "    slmp_perm_role_button sprb  \n" +
                  "INNER JOIN\n" +
                  "    slmp_sys_menu_button ssmb \n" +
                  "        ON ssmb.menu_code = sprb.menu_code \n" +
                  "        AND ssmb.button_code = sprb.button_code  \n" +
                  "INNER JOIN\n" +
                  "    slmp_sys_button ssb \n" +
                  "        ON ssb.button_code = ssmb.button_code  \n" +
                  "WHERE\n" +
                  "    sprb.role_code in (\n" +
                  "        SELECT\n" +
                  "            distinct spur.role_code \n" +
                  "        FROM\n" +
                  "            slmp_perm_user_role spur \n" +
                  "        WHERE\n" +
                  "            spur.user_name = 'renpenglin'\n" +
                  "    )  \n" +
                  "    AND sprb.menu_code = 'ORG_1'";


//        String a = new SQLFormatterUtil().format(sql);
          String a= SQLFormatterUtil.format(sql, true);
          System.out.println(a);
//          System.out.println(a.toUpperCase());
    }
}

自动配置属性类。

package com.mybatis.sql.log.plugin.conf;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * <p>
 *  mybatis插件自动配置类
 * </p>
 *
 * @author wangbin
 * @date 2021/6/7 11:18
 */
@ConfigurationProperties(prefix = "myplugin")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class MybatisPluginProperties {

    private boolean enable;
    private String level;
    private boolean convercase;

}

最后打包成jar就好了。

在maven中引入：

		<dependency>
			<groupId>com.mybatis.sql.log.plugin</groupId>
			<artifactId>mybatis-sql-log-plugin</artifactId>
			<version>0.0.1-RELEASE</version>
			<scope>system</scope>
			<systemPath>C:/Users/CSI-31/Desktop/mybatis-sql-log-plugin-0.0.1-RELEASE.jar</systemPath>
		</dependency>