SpringBoot集成Mybatis自定义拦截器拼接sql带结果

SpringBoot集成Mybatis自定义拦截器实现拼接sql和修改

一个大菜鸟写的第一个博客，请大佬们指教，谢谢！
借鉴的：https://blog.youkuaiyun.com/qq_29653517/article/details/86299928 这篇文章

1、应用场景

1.分页，如Mybatis—Plus的分页插件实现；

2.拦截sql做日志监控；

3.统一对某些sql进行统一条件拼接，类似于分页。

2、MyBatis的拦截器简介

然后我们要知道拦截器拦截什么样的对象，拦截对象的什么行为，什么时候拦截？

在Mybatis框架中，已经给我们提供了拦截器接口，防止我们改动源码来添加行为实现拦截。说到拦截器，不得不提一下，拦截器是通过动态代理对Mybatis加入一些自己的行为。

拦截对象

确立拦截对象范围：要拦对人，既要保证拦对人，又要保证对正确的人执行正确的拦截动作

拦截地点

在Mybatis的源码中

拦截时机

如果mybatis为我们提供了拦截的功能，我们应该在Mybatis执行过程的哪一步拦截更加合适呢？

拦截某个对象干某件事的时候，拦截的时机要对，过早的拦截会耽误别人做自己的工作，拦截太晚达不到目的。

Mybatis实际也是一个JDBC执行的过程，只不过被包装起来了而已，我们要拦截Mybatis的执行，最迟也要在获取PreparedStatement时拦截：

PreparedStatementstatement=conn.prepareStatement(sql.toString());

在此处偷偷的将sql语句换掉，就可以改变mybatis的执行，加入自己想要的执行行为。

而获取Mybatis的Statement是在StatementHandler中进行的。

3、代码示例

1.先搭建个新项目，SpringBoot集成MyBatis和MyBatis-Plus

2.然后配置application.propertities

#描述数据源
spring.datasource.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver
mybatis.mapper-locations=classpath:mappers/*.xml

#mybatis-plus
mybatis-plus.mapper-locations=classpath:mappers/*.xml
mybatis-plus.type-aliases-package=com.syxy.demo.entity
mybatis-plus.configuration.map-underscore-to-camel-case: true
#SQL打印
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

3.新建用户表

4.MyBatis-Plus分页插件

package com.syxy.demo.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableTransactionManagement
@Configuration
@MapperScan("com.syxy.demo.dao")
public class MybatisPlusConfig {
    @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;
    }
}

5.创建实体类，dao层，Service层，xml文件

实体类：

package com.syxy.demo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
@TableName("user")
public class UserEntity implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId(value = "user_id",type = IdType.AUTO)
    @TableField("user_id")
    private Integer userId;
    @TableField("user_name")
    private String userName;
    @TableField("password")
    private String password;
    @TableField("age")
    private Integer age;
    @TableField("phone")
    private String phone;
    @TableField("create_time")
    private Date createTime;
}

dao：加上自定义注解 @InterceptAnnotation(flag = true) 注解才会被拦截。

package com.syxy.demo.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.syxy.demo.annotation.InterceptAnnotation;
import com.syxy.demo.entity.UserEntity;
import org.springframework.stereotype.Repository;

@Repository
public interface UserDao extends BaseMapper<UserEntity> {
    @InterceptAnnotation(flag = true)
    IPage<UserEntity> selectPageVo(Page<UserEntity> page, Integer age);

}

Service：由于是做测试，直接在Test里用dao层调用的方法，所以这个service暂时没用到

package com.syxy.demo.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.syxy.demo.entity.UserEntity;

public interface UserService extends IService<UserEntity> {
    IPage<UserEntity> selectPageVo(Page<UserEntity> page, Integer age);
}

package com.syxy.demo.service.imp;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.syxy.demo.dao.UserDao;
import com.syxy.demo.entity.UserEntity;
import com.syxy.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl extends ServiceImpl<UserDao, UserEntity> implements UserService {
    @Autowired
    private UserDao userDao;
    @Override
    public IPage<UserEntity> selectPageVo(Page<UserEntity> page, Integer age) {
        return userDao.selectPageVo(page,age);
    }
}

XML：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.syxy.demo.dao.UserDao">
    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.syxy.demo.entity.UserEntity" id="userMap">
        <result property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="createTime" column="create_time"/>
    </resultMap>
    <select id="selectPageVo" resultType="com.syxy.demo.entity.UserEntity">
        SELECT * FROM user WHERE age=#{age}
    </select>
</mapper>

4.拦截器

（mybatis环境算是集成好了，可以运行一个测试类试试能否从数据库读取数据。）

定义一个类实现Mybatis的Interceptor接口，@Component注解必须要添加，不然可能出现拦截器无效的情况！！！

package com.syxy.demo.interceptor;

import com.syxy.demo.annotation.InterceptAnnotation;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.Properties;

@Component
@Intercepts({
        @Signature(
                type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class
        })
})
public class MySqlInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 方法一
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
        /*
         * 先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，
         * 然后就到BaseStatementHandler的成员变量mappedStatement
         */
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        //id为执行的mapper方法的全路径名，如com.uv.dao.UserDao.selectPageVo
        String id = mappedStatement.getId();
        //sql语句类型 select、delete、insert、update
        String sqlCommandType = mappedStatement.getSqlCommandType().toString();
        BoundSql boundSql = statementHandler.getBoundSql();

        //获取到原始sql语句
        String sql = boundSql.getSql();
        String mSql = sql;

        //TODO 修改位置

        //注解逻辑判断  添加注解了才拦截
        Class<?> classType = Class.forName(mappedStatement.getId().substring(0, mappedStatement.getId().lastIndexOf(".")));
        String mName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1, mappedStatement.getId().length());
        for (Method method : classType.getDeclaredMethods()) {
            if (method.isAnnotationPresent(InterceptAnnotation.class) && mName.equals(method.getName())) {
                InterceptAnnotation interceptorAnnotation = method.getAnnotation(InterceptAnnotation.class);
                if (interceptorAnnotation.flag()) {
                    //可以在此处修改sql，用字符串拼接即可
                    mSql = "select * from ("+sql+") user " + " order by create_time";
                }
            }
        }
        //通过反射修改sql语句
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, mSql);
        return invocation.proceed();
    }
    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }
    @Override
    public void setProperties(Properties properties) {

    }
}

此外，定义了一个方法层面的注解，实现局部指定拦截

package com.syxy.demo.annotation;

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

@Target({ElementType.METHOD,ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
public @interface InterceptAnnotation {
    boolean flag() default true;
}

4、测试

package com.syxy.demo;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.syxy.demo.dao.UserDao;
import com.syxy.demo.entity.UserEntity;
import com.syxy.demo.service.UserService;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;

@SpringBootTest
@RunWith(SpringRunner.class)
class DemoApplicationTests {

    @Autowired
    private UserDao userDao;
    //插入测试数据
    @Test
    void contextLoads() {
        UserEntity userEntity = new UserEntity();
        for (int i = 0;i<=10;i++) {
            userEntity.setUserName("zhangsan"+i);
            userEntity.setPassword("123456");
            userEntity.setAge(18);
            userEntity.setPhone("138156921967");
            userDao.insert(userEntity);
        }
    }

    @Test
    void selectUser(){   
        Page<UserEntity> page = new Page<>();
        page.setSize(10);
        page.setCurrent(1);
        IPage<UserEntity> userEntityIPage = userDao.selectPageVo(page, 18);
        System.out.println(userEntityIPage);
    }

}

5、结果