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、结果

在这里插入图片描述

### 使用 MyBatis-Plus 拦截器实现 SQL 语句的动态拼接 #### 自定义拦截器类 为了实现 SQL 的动态拼接,可以通过创建自定义拦截器来修改 SQL 语句。该拦截器继承 `Interceptor` 接口并重写相应的方法。 ```java import com.baomidou.mybatisplus.core.toolkit.PluginUtils; import net.sf.jsqlparser.JSQLParserException; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.select.Select; import java.util.Properties; public class DynamicTableNameInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { MetaObject metaStatementHandler = PluginUtils.metaObjectForObject(invocation.getArgs()[0]); String originalSql = (String) metaStatementHandler.getValue("delegate.boundSql.sql"); Statement statement = CCJSqlParserUtil.parse(originalSql); if (statement instanceof Select){ // 修改表名为动态获取到的新表名 ((Select) statement).getFromItem().setAlias(new Table(getDynamicTableName())); // 更新原始 SQL 字符串 metaStatementHandler.setValue("delegate.boundSql.sql", statement.toString()); } return invocation.proceed(); } private String getDynamicTableName() { // 这里可以根据业务逻辑返回不同的表名 return "dynamic_table_name"; } @Override public void setProperties(Properties properties) {} } ``` 此代码片段展示了如何通过解析和重构 JSQL 解析树来更改查询中的表名称[^1]。 #### 注册拦截器 为了让上述自定义拦截器生效,在 Spring Boot 应用程序中注册它: ```yaml mybatis-plus: configuration: interceptors: - com.example.DynamicTableNameInterceptor ``` 或者在 Java 配置文件中添加如下配置: ```java @Configuration @MapperScan("com.example.mapper") public class MyBatisConfig { @Bean public DynamicTableNameInterceptor dynamicTableNameInterceptor(){ return new DynamicTableNameInterceptor(); } @Bean public MybatisPlusInterceptor mybatisPlusInterceptor(DynamicTableNameInterceptor interceptor){ MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor(); mybatisPlusInterceptor.addInnerInterceptor(interceptor); return mybatisPlusInterceptor; } } ``` 这样就可以让应用程序根据实际运行情况灵活调整所访问的数据表[^2]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值