自定义格式化mybatis、mybatis plus中打印的sql

前提条件

连接池使用的是阿里的druid，其他连接池后续有空再考虑

继承`FilterEventAdapter`并重写`statement_close`和`resultSet_next`

statement_close 用于格式化`delete`,`insert`(没有主键id返回),`update`

`resultSet_next` 用户格式化`select`,`insert`（有主键id返回）

代码

package cn.allbs.mybatis.filter;

import cn.allbs.common.constant.DateConstant;
import cn.allbs.mybatis.properties.MybatisProperties;
import cn.allbs.mybatis.utils.TableConsoleUtil;
import com.alibaba.druid.DbType;
import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.filter.FilterEventAdapter;
import com.alibaba.druid.proxy.jdbc.JdbcParameter;
import com.alibaba.druid.proxy.jdbc.ResultSetProxy;
import com.alibaba.druid.proxy.jdbc.StatementProxy;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.util.StringUtils;
import lombok.extern.slf4j.Slf4j;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.*;

/**
 * 类 DruidSqlLogFilter
 * </p>
 *
 * @author ChenQi
 * @since 2023/3/23 9:55
 */
@Slf4j
public class DruidSqlLogFilter extends FilterEventAdapter {

    private static final SQLUtils.FormatOption FORMAT_OPTION = new SQLUtils.FormatOption(false, false);

    private final MybatisProperties mybatisProperties;

    private boolean first = true;

    private static final Set<Integer> BLOB_TYPES = new HashSet<>();

    private ResultSet rs;
    private final Set<Integer> blobColumns = new HashSet<>();

    private int rows;

    private List<String> rowList = new LinkedList<>();

    private boolean STATEMENT_CLOSE_RUN = true;

    static {
        BLOB_TYPES.add(Types.BINARY);
        BLOB_TYPES.add(Types.BLOB);
        BLOB_TYPES.add(Types.CLOB);
        BLOB_TYPES.add(Types.LONGNVARCHAR);
        BLOB_TYPES.add(Types.LONGVARBINARY);
        BLOB_TYPES.add(Types.LONGVARCHAR);
        BLOB_TYPES.add(Types.NCLOB);
        BLOB_TYPES.add(Types.VARBINARY);
    }

    public DruidSqlLogFilter(MybatisProperties mybatisProperties) {
        this.mybatisProperties = mybatisProperties;
    }

    @Override
    protected void statementExecuteBefore(StatementProxy statement, String sql) {
        statement.setLastExecuteStartNano();
    }

    @Override
    protected void statementExecuteBatchBefore(StatementProxy statement) {
        statement.setLastExecuteStartNano();
    }

    @Override
    protected void statementExecuteUpdateBefore(StatementProxy statement, String sql) {
        statement.setLastExecuteStartNano();
    }

    @Override
    protected void statementExecuteQueryBefore(StatementProxy statement, String sql) {
        statement.setLastExecuteStartNano();
    }

    @Override
    protected void statementExecuteAfter(StatementProxy statement, String sql, boolean firstResult) {
        statement.setLastExecuteTimeNano();
    }

    @Override
    protected void statementExecuteBatchAfter(StatementProxy statement, int[] result) {
        statement.setLastExecuteTimeNano();
    }

    @Override
    protected void statementExecuteQueryAfter(StatementProxy statement, String sql, ResultSetProxy resultSet) {
        statement.setLastExecuteTimeNano();
    }

    @Override
    protected void statementExecuteUpdateAfter(StatementProxy statement, String sql, int updateCount) {
        statement.setLastExecuteTimeNano();
    }

    @Override
    public void statement_close(FilterChain chain, StatementProxy statement) throws SQLException {
        super.statement_close(chain, statement);
        // 支持动态开启
        if (!mybatisProperties.isShowSql()) {
            return;
        }

        // 是否开启调试
        if (!log.isInfoEnabled()) {
            return;
        }
        // 如果当前执行sql有结果则不执行 或者是新增语句，因为有id返回也不执行
//        Token token = new SQLStatementParser(statement.getLastExecuteSql()).getExprParser().getLexer().token();
//        if (statement.isFirstResultSet() || token.equals(Token.INSERT)) {
//            return;
//        }
        // 防止有结果时打印两次sql语句
        if (!STATEMENT_CLOSE_RUN) {
            STATEMENT_CLOSE_RUN = true;
            return;
        }
        // 打印可执行的 sql
        String sql = statement.getBatchSql();
        // sql 为空直接返回
        if (StringUtils.isEmpty(sql)) {
            return;
        }
        String executeSql = statement(statement);
        log.info(executeSql);
    }

    public String statement(StatementProxy statement) {
        // 打印可执行的 sql
        String sql = statement.getBatchSql();
        // sql 为空直接返回
        if (StringUtils.isEmpty(sql)) {
            return "";
        }
        int parametersSize = statement.getParametersSize();
        List<Object> parameters = new ArrayList<>(parametersSize);
        for (int i = 0; i < parametersSize; ++i) {
            // 转换参数，处理 java8 时间
            parameters.add(getJdbcParameter(statement.getParameter(i)));
        }
        String dbType = statement.getConnectionProxy().getDirectDataSource().getDbType();
        String formattedSql = SQLUtils.format(sql, DbType.of(dbType), parameters, FORMAT_OPTION);
        return printSql(formattedSql, statement);
    }

    private static Object getJdbcParameter(JdbcParameter jdbcParam) {
        if (jdbcParam == null) {
            return null;
        }
        Object value = jdbcParam.getValue();
        // 处理 java8 时间
        if (value instanceof TemporalAccessor) {
            return value.toString();
        }
        return value;
    }

    private static String printSql(String sql, StatementProxy statement) {
        // 打印 sql
        String sqlLogger = "\n--------------------------------[ %s Sql Log ]---------------------------------" + "\n%s" + "\n--------------------------------[ Sql Execute Time: %s  ]---------------------------------\n";
        return String.format(sqlLogger, LocalDateTime.now().format(DateTimeFormatter.ofPattern(DateConstant.NORM_DATETIME_PATTERN)), sql.trim(), format(statement.getLastExecuteTimeNano()));
    }

    /**
     * 格式化执行时间，单位为 ms 和 s，保留三位小数
     *
     * @param nanos 纳秒
     * @return 格式化后的时间
     */
    private static String format(long nanos) {
        if (nanos < 1) {
            return "0ms";
        }
        double millis = (double) nanos / (1000 * 1000);
        // 不够 1 ms，最小单位为 ms
        if (millis > 1000) {
            return String.format("%.3fs", millis / 1000);
        } else {
            return String.format("%.3fms", millis);
        }
    }

    @Override
    public boolean resultSet_next(FilterChain chain, ResultSetProxy resultSet) throws SQLException {
        boolean next = super.resultSet_next(chain, resultSet);
        // 支持动态开启
        if (!mybatisProperties.isShowSql()) {
            return next;
        }

        // 是否开启调试
        if (!log.isInfoEnabled()) {
            return next;
        }
        if (!next) {
            String querySql = statement(resultSet.getStatementProxy());
            String sqlLogger = querySql + "{}" + "--------------------------------[ Results Total {} ]---------------------------------\n";
            String results = "";
            if (rowList.size() > 0) {
                results = TableConsoleUtil.printResult(rowList);
            }
            log.info(sqlLogger, results, rows);
            rows = 0;
            rowList = new LinkedList<>();
            first = true;
            STATEMENT_CLOSE_RUN = false;
            return false;
        }
        STATEMENT_CLOSE_RUN = true;
        rows++;
        rs = resultSet.getResultSetRaw();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        final int columnCount = rsmd.getColumnCount();
        if (first) {
            first = false;
            printColumnHeaders(rsmd, columnCount);
        }
        printColumnValues(columnCount);
        return true;
    }

    private void printColumnHeaders(ResultSetMetaData rsmd, int columnCount) throws SQLException {
        StringJoiner row = new StringJoiner(",");
        for (int i = 1; i <= columnCount; i++) {
            if (BLOB_TYPES.contains(rsmd.getColumnType(i))) {
                blobColumns.add(i);
            }
            row.add(rsmd.getColumnLabel(i));
        }
        rowList.add(row.toString());
    }

    private void printColumnValues(int columnCount) {
        StringJoiner row = new StringJoiner(",");
        for (int i = 1; i <= columnCount; i++) {
            try {
                if (blobColumns.contains(i)) {
                    row.add("<<BLOB>>");
                } else {
                    row.add(rs.getString(i));
                }
            } catch (SQLException e) {
                // generally can't call getString() on a BLOB column
                row.add("<<Cannot Display>>");
            }
        }
        rowList.add(row.toString());
    }
}

将结果格式化为表格形式的工具代码

目前因为查询出的结果包含中文汉字，为了尽可能整齐，只是默认取了一个汉字为1.5倍的其他字符。有兴趣的自己修改成根据字体、字号获取实际的宽度并生成表格。

package cn.allbs.mybatis.utils;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.List;

/**
 * 类 TableConsoleUtil
 * </p>
 *
 * @author ChenQi
 * @since 2023/3/24 13:45
 */
public class TableConsoleUtil {

    public static String printResult(List<String> rows) {
        StringBuilder sb = new StringBuilder();
        String[] tempA = rows.get(0).split(",");
        int maxLen = tempA.length;
        for (int i = 1; i < rows.size(); i++) {
            tempA = rows.get(i).split(",");
            if (maxLen < tempA.length) maxLen = tempA.length;
        }
        String[][] row = new String[rows.size()][maxLen];
        for (int i = 0; i < row.length; i++)
            for (int j = 0; j < row[0].length; j++)
                row[i][j] = "";
        for (int i = 0; i < rows.size(); i++) {
            tempA = rows.get(i).split(",");
            System.arraycopy(tempA, 0, row[i], 0, tempA.length);
        }
        int[] maxJ = new int[maxLen];
        for (int j = 0; j < maxLen; j++) {
            for (int i = 0; i < rows.size(); i++) {
                int vLen = (getWordCount(row[i][j]) - 1 >> 3) * 8 + 8;
                if (vLen > maxJ[j]) {
                    maxJ[j] = vLen;
                }
            }
        }
        StringBuilder opera = new StringBuilder("+");
        for (int value : maxJ) {
            for (int k = 0; k < value; k++) {
                opera.append('-');

            }
            opera.append('+');
        }
        boolean first = true;
        for (String[] strings : row) {
            if (first) {
                sb.append(opera);
                sb.append("\n");
            }
            sb.append("|");
            for (int j = 0; j < row[0].length; j++) {
                int len = maxJ[j] - getWordCount(strings[j]);
                String format;
                if (len == 0) {
                    format = "" + "%s";
                } else {
                    format = "%" + len + "s";
                }
                sb.append(strings[j]);
                sb.append(String.format(format, ""));
                sb.append("|");
            }
            sb.append("\n");
            if (first) {
                sb.append(opera);
                sb.append("\n");
                first = false;
            }
        }
        sb.append(opera);
        sb.append("\n");
        return sb.toString();
    }

    public static int getWordCount(String s) {
        int length = 0;
        int chineseNum = 0;
        for (int i = 0; i < s.length(); i++) {
            int ascii = Character.codePointAt(s, i);
            if (ascii >= 0 && ascii <= 255) length++;
            else chineseNum++;

        }
        return BigDecimal.valueOf(length + chineseNum * 1.5).setScale(0, RoundingMode.HALF_UP).intValue();
    }
}

注册过滤器

@Bean
public DruidSqlLogFilter sqlLogFilter(MybatisProperties properties) {
    return new DruidSqlLogFilter(properties);
}

添加了配置开关当前日志打印效果

@Data
@Component
@ConfigurationProperties("mybatis-plus")
public class MybatisProperties {

    /**
     * 是否打印可执行 sql
     */
    private boolean showSql = true;
}