java sql 打印格式化 的库 java格式化sql语句

第一步：创建一个SQL格式化工具类SQLFormatterUtil.java

package com.trackable.small.domain.common.util;

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

/**
 * 描述:SQL格式化工具
 *
 * @outhor Administrator
 * @create 2018-11-08 14:01
 */
public class SQLFormatterUtil {

  private static SQLFormatterUtil instance = null;

  public static SQLFormatterUtil getInstance() {
    if (instance == null) {
      instance = new SQLFormatterUtil();
    }
    return instance;
  }

  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    ";

  public String format(String source) {
    return new FormatProcess(source).perform();
  }

  private class FormatProcess {

    boolean beginLine = true;
    boolean afterBeginBeforeEnd = false;
    boolean afterByOrSetOrFromOrSelect = false;
    boolean afterValues = false;
    boolean afterOn = false;
    boolean afterBetween = false;
    boolean afterInsert = false;
    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) {
      tokens = new StringTokenizer(
          sql,
          "()+*/-=<>'`\"[]," + WHITESPACE,
          true
      );
    }

    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)) {
          beginNewClause();
        } else if (END_CLAUSES.contains(lcToken)) {
          endNewClause();
        } else if ("select".equals(lcToken)) {
          select();
        } else if (DML.contains(lcToken)) {
          updateOrInsertOrDelete();
        } else if ("values".equals(lcToken)) {
          values();
        } else if ("on".equals(lcToken)) {
          on();
        } else if (afterBetween && lcToken.equals("and")) {
          misc();
          afterBetween = false;
        } else if (LOGICAL.contains(lcToken)) {
          logical();
        } else if (isWhitespace(token)) {
          white();
        } else {
          misc();
        }

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

      }
      return result.toString();
    }

    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() {
      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() {
      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() {
      if (isFunctionName(lastToken) || inFunction > 0) {
        inFunction++;
      }
      beginLine = false;
      if (inFunction > 0) {
        out();
      } else {
        out();
        if (!afterByOrSetOrFromOrSelect) {
          indent++;
          newline();
          beginLine = true;
        }
      }
      parensSinceSelect++;
    }

    private 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 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;
    }
  }

}

第二步：创建一个sql拦截器SqlStatementInterceptor.java

import org.apache.ibatis.cache.CacheKey;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

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;

/**
 * 描述:数据库操作性能拦截器,记录耗时
 *
 * @outhor Administrator
 * @create 2018-11-07 13:41
 */

@Intercepts(value = {
    @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, CacheKey.class, BoundSql.class}),
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class,
        RowBounds.class, ResultHandler.class})})
public class SqlStatementInterceptor implements Interceptor {

  private static Logger logger = LoggerFactory.getLogger(SqlStatementInterceptor.class);

  private Properties properties;

  public Object intercept(Invocation invocation) throws Throwable {
    Object returnValue;
    long start = System.currentTimeMillis();
    returnValue = invocation.proceed();
    long end = System.currentTimeMillis();
    long time = end - start;
    try {
      final Object[] args = invocation.getArgs();
      //获取原始的ms
      MappedStatement ms = (MappedStatement) args[0];
      Object parameter = null;
      //获取参数，if语句成立，表示sql语句有参数，参数格式是map形式
      if (invocation.getArgs().length > 1) {
        parameter = invocation.getArgs()[1];
      }
      logger.debug("查询参数: " + parameter);
      String sqlId = ms.getId();// 获取到节点的id,即sql语句的id
      BoundSql boundSql = ms.getBoundSql(parameter);  // BoundSql就是封装myBatis最终产生的sql类
      Configuration configuration = ms.getConfiguration();  // 获取节点的配置
      String sql = getSql(configuration, boundSql, sqlId, time); // 获取到最终的sql语句
    } catch (Exception e) {
      logger.error("拦截sql处理出错" + e.getMessage());
    }
    return returnValue;
  }

  // 封装了一下sql语句，使得结果返回完整xml路径下的sql语句节点id + sql语句
  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).append(":耗时【").append(time).append("】毫秒");
    String begin = "============================sql begin================================";
    String end = "============================sql end==================================";
    logger.info(begin);
    logger.warn(str.toString() + "\n" + SQLFormatterUtil.getInstance().format(sql));
    logger.info(end);
    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.isEmpty(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;
  }


  public Object plugin(Object arg0) {
    return Plugin.wrap(arg0, this);
  }


  public void setProperties(Properties arg0) {
    this.properties = arg0;
  }
}

第三步：创建mybatis-config.xml配置文件

<?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>

    <!-- 在这个文件放置一些全局性的配置
    <typeAliases>
        <typeAlias type="实体类" alias="别名"/>
    </typeAliases> -->

    <settings>
        <setting name="cacheEnabled" value="false" />
        <!-- 打印查询语句 -->
        <!--<setting name="logImpl" value="STDOUT_LOGGING" />-->
        <!-- 设置超时时间，它决定驱动等待一个数据库响应的时间  -->

        <!--  设置缓存和延迟加载等等重要的运行时的行为方式
         <setting name="defaultStatementTimeout" value="25000"/>
         <!– 这个配置使全局的映射器启用或禁用缓存  –>
         <setting name="cacheEnabled" value="true" />
         <setting name="lazyLoadingEnabled" value="true"/>
         <setting name="useColumnLabel" value="true" />
         <setting name="mapUnderscoreToCamelCase" value="true" />-->
    </settings>
    <!-- 以下内容不再需要手动配置  已经自动映射 -->


    <!--配置sql拦截器-->
    <plugins>
        <plugin interceptor="com.trackable.small.domain.common.util.SqlStatementInterceptor"></plugin>
    </plugins>
    <!--<plugins>
      <plugin interceptor="cn.chenlilin.blog.filter.SqlStatementInterceptor" />
    </plugins>-->

</configuration>

第四步：application.yml配置，特别注意：mybatis要与spring平级，不然sql不打印

server:
  port: 9082

spring:
# datasource
#spring:
  datasource:
    ###dev
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/trackable_manage?characterEncoding=utf8&useSSL=false
    username: root
    password: root


    # 初始化大小，最小，最大
    initialSize: 5
    minIdle: 5
    maxActive: 20
    # 配置获取连接等待超时的时间
    maxWait: 60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置监控统计拦截的filters，去掉后监控界面sql将无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    #useGlobalDataSourceStat=true

mybatis:
  config-location: classpath:mybatis-config.xml
  mapper-locations: classpath:com/trackable/small/domain/mapper/*.xml
  type-aliases-package: com.trackable.small.domain.entity

pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true
  params: count=countsql

logging:
  #path: classpath:log/
  file: trackablesmall.log

sql打印结果如下图，便是配置成功