Mybatis 完美打印SQL方案(MySQL)

测试用的mybatis-plus, mybatis也一样通用, 支持druid

测试版本如下:

mybatis-plus-boot-starter: 3.5.4.1
mysql-connector-j: 8.0.33
druid-spring-boot-starter(可选): 1.2.20
spring-boot: 3.1.5

首先看一下测试效果

测试代码放在了最下方

mybatis-plus打印结果:

此方案打印效果:

建表测试

create table test_any
(
    id            bigint primary key comment 'id, 数字类型',
    test_varchar  varchar(100) comment '字符串',
    test_json     json comment 'JSON',
    test_bit      bit comment '字节',
    test_blob     blob comment '文件',
    test_datetime datetime comment '日期类型'
) comment 'MySQL 日志打印 各种类型测试';

对应实体类(篇幅原因, 去掉了注释)

注意json字段指定了typeHandler = JacksonTypeHandler.class, 如果不想指定可以注册全局typeHandler

package kim.nzxy.ly.modules.test.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import com.fasterxml.jackson.databind.node.ObjectNode;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDateTime;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestAny {
    @TableId
    private Long id;
    private String testVarchar;
    @TableField(typeHandler = JacksonTypeHandler.class)
    private ObjectNode testJson;
    private Boolean testBit;
    private byte[] testBlob;
    private LocalDateTime testDatetime;
}

上实现方式

package kim.nzxy.ly.common.interceptor;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.springframework.stereotype.Component;

import java.lang.reflect.Proxy;
import java.sql.Statement;
import java.util.Collection;

/**
 * mybatis sql 日志拦截器, 用于打印SQL
 *
 * @author ly-chn
 */
@Slf4j
@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),
        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
        @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})})
public class MybatisLogSqlInterceptor implements Interceptor {
    private static <T> T metaValue(Object target, String name) {
        if (Proxy.isProxyClass(target.getClass())) {
            return metaValue(SystemMetaObject.forObject(target).getValue(name), name);
        }
        // noinspection unchecked
        return (T) target;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long startTime = System.currentTimeMillis();
        int lines = 1;
        String status = "failed";
        try {
            Object proceed = invocation.proceed();
            if (proceed instanceof Collection<?>) {
                lines = ((Collection<?>) proceed).size();
            }
            status = "succeeded";
            return proceed;
        } finally {
            try (Statement statement = metaValue(invocation.getArgs()[0], "h.statement")) {
                String sql = statement.toString();
                sql = sql.substring(sql.indexOf(':'));
                long cost = System.currentTimeMillis() - startTime;
                log.info("sql exec {} took {}ms rest {}rows: {}", status, cost, lines, sql);
            } catch (Exception e) {
                log.error("解析sql异常", e);
            }
        }
    }
}

测试代码

jdk17实现, 低版本需要自己转义一下代码中用到的json

package kim.nzxy.ly.modules.test.mapper;

import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import kim.nzxy.ly.modules.test.entity.TestAny;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;

@SpringBootTest
@AutoConfigureMockMvc
@Transactional
@ActiveProfiles("test")
class TestAnyMapperTest {
    @Autowired
    private TestAnyMapper mapper;

    @Test
    void search() throws JsonProcessingException {
        TestAny testAny = new TestAny();
        testAny.setId(IdWorker.getId());
        testAny.setTestBlob("你好我是凉云".getBytes());
        testAny.setTestBit(false);
        ObjectMapper objectMapper = new ObjectMapper();
        testAny.setTestJson(objectMapper.readValue("""
                {
                  "a": 1,
                  "b": "2",
                  "c": false,
                  "d": [
                    1,
                    2,
                    3
                  ],
                  "e": null,
                  "f": 1.23
                }""", ObjectNode.class));
        testAny.setTestDatetime(LocalDateTime.now());
        testAny.setTestVarchar("测试字符串");
        mapper.insert(testAny);
    }
}