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);
}
}