MybatisPlus拦截器打印完整SQL、分页、自动填充处理

MybatisPlus拦截器打印完整SQL

MybatisPlus 虽然也自带了一个打印 SQL 的配置，但是不方便查看，也没有时间统计

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

拦截器PrintSqlInterceptor

/**
 * @author CoderKK
 * @date 2020-09-01 00:13
 */
@Slf4j
@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})})
public class PrintSqlInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = null;
        if (invocation.getArgs().length > 1) {
            parameter = invocation.getArgs()[1];
        }
        String sqlId = mappedStatement.getId();
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        Configuration configuration = mappedStatement.getConfiguration();
        long start = System.currentTimeMillis();
        Object returnValue = invocation.proceed();
        long time = System.currentTimeMillis() - start;
        showSql(configuration, boundSql, time, sqlId);
        return returnValue;
    }

    private static void showSql(Configuration configuration, BoundSql boundSql, long time, String sqlId) {
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        //替换空格、换行、tab缩进等
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (parameterMappings.size() > 0 && parameterObject != null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
            } else {
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    }
                }
            }
        }
        logs(time, sql, sqlId);
    }

    private static String getParameterValue(Object obj) {
        String value;
        if (obj instanceof String) {
            value = "'" + obj + "'";
        } 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.replace("$", "\\$");
    }

    private static void logs(long time, String sql, String sqlId) {
        StringBuilder sb = new StringBuilder()
                .append(" Time：").append(time)
                .append(" ms - ID：").append(sqlId)
                .append(StringPool.NEWLINE).append("Execute SQL：")
                .append(sql).append(StringPool.NEWLINE);
        log.info(sb.toString());
    }

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

    @Override
    public void setProperties(Properties properties0) {
    }
}

MybatisPlus配置 MybatisPlusConfig

package com.example.demojs.config;

import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.example.demojs.common.PrintSqlInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.Properties;


@Configuration
public class MybatisPlusConfig {

    /**
     * mybatis-plus分页插件
     */

/*
    // 旧版
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }

    // 新版
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
    // 如何区分新旧版呢？其实你把这两种代码都复制到你的项目里，哪个类存在就用哪个方法。
    // 3.4.0版本对此部分有更新，如果是旧版本升级，会出现分页失效问题，同时idea会提示PaginationInterceptor过时，新版本改用了MybatisPlusInterceptor

*/

    /**
     *自定义mybatis插件 注入方式一
     */
/*
    @Bean
    public MybatisPlusInterceptor mybatisInterceptor() {
        MybatisPlusInterceptor mybatisInterceptor = new MybatisPlusInterceptor();
        Properties properties = new Properties();
        properties.setProperty("name", name);
        mybatisInterceptor.setProperties(properties);
        return mybatisInterceptor;
    }
*/
    /**
     *自定义mybatis插件 注入方式二
     */
    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> {
            //插件拦截链采用了责任链模式，执行顺序和加入连接链的顺序有关
            //MybatisInterceptor mybatisInterceptor = new MybatisInterceptor();
            MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
            //设置参数，比如阈值等，可以在配置文件中配置
            Properties properties = new Properties();
            //properties.setProperty("name", name);
            mybatisPlusInterceptor.setProperties(properties);

                //自定义打印SQL
                PrintSqlInterceptor printSqlInterceptor = new PrintSqlInterceptor();
                configuration.addInterceptor(printSqlInterceptor);

        };
    }


    /**
     * mybatis-plus SQL执行效率插件【生产环境可以关闭】 3.3.2版本移除了该功能，3.0.3和3.0.3之前版本支持。
     */
  /*
    @Bean
    public PerformanceInterceptor performanceInterceptor() {
        return new PerformanceInterceptor();
    }
    */

}

打印效果

2022-08-29 16:42:03.782  INFO 17476 --- [nio-8080-exec-2] c.e.demojs.common.PrintSqlInterceptor    :  Time：397 ms - ID：com.example.demojs.dao.UserMapper.insert
Execute SQL：INSERT INTO test_user ( id, user_id, phone, birthday, sex ) VALUES ( 1564171509361389569, 'abc', '13988776664', 2022-08-29T16:41:59.514, 1 )

自动填充插件

/**
 * @author CoderKK
 * @date 2020/9/6 15:21
 * @desc 自动填充处理器类
 */
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    /**
     * 插入时的填充策略
     *
     * @param metaObject
     */
    @Override
    public void insertFill(MetaObject metaObject) {
        this.setFieldValByName("createTime", LocalDateTime.now(), metaObject);
        this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject);
    }

    /**
     * 更新时的填充策略
     *
     * @param metaObject
     */
    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject);
    }
}

实体类记得加注解

    @TableField(value = "create_time", fill = FieldFill.INSERT)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8")
    private LocalDateTime createTime;

    @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8")
    private LocalDateTime updateTime;

分页插件

注入分页插件后，如何使用呢？

mapper写法：

// 这是mapper自己提供的方法，参数是wrapper 适用于单表查
 <P extends IPage<T>> P selectPage(P page, @Param("ew") Wrapper<T> queryWrapper);

// 自定义sql，适用于多表联查
IPage<UserVO> queryUserList(Page<UserVO> page,  @Param("dto") ConditionDTO conditionDTO);

📢注意的点：
1.Page为IPage的实现类，分页返回和传入Page是同一个对象
2.Page类必须放在第一位
3.第二个参数和后续参数必须加@Param,（基本类型和实体类型都需要加）,否则会报找不到属性错误。

mapper.xml:

<select id="queryUserList" resultType="com.test.vo.UserVO">
    select *
    from auth_user
    where sex = #{dto.sex}
    and cancat_ws(':',name,phone) like concat('%',#{dto.searchKey,'%'})
</select>

service调用mapper

public PageDataVO<UserVO> getUserList( ConditionDTO conditionDTO) {
        // 分页查询
        Page<UserVO> page  = new Page<>(conditionDTO.getPageNum(), conditionDTO.getPageSize());
        IPage<UserVO> result =  userMapper.queryUserList(page, conditionDTO);
        //构建自己的分页实体类
        return new PageDataVO<WorkReviewVO>().build(result);
    }

参考
https://www.modb.pro/db/229426

我的技术博客 https://blog.52ipc.top/