MyBatis使用：拦截器，SpringBoot整合MyBatis

1、目标

本文的主要目标是学习使用MyBatis拦截器，并给出拦截器的实例

2、拦截器的使用

2.1 @Intercepts注解和@Signature注解

@Intercepts注解，指定拦截哪个拦截器的哪个方法，还要指定参数，因为可能发生方法重载

按照顺序可以拦截Executor、StatementHandler、ParameterHandler、ResultSetHandler这4个接口的方法

@Signature注解，指定type是这4个接口中的某个接口，指定method是接口中的一个方法，指定args是方法的入参

2.2 四个接口

public interface Executor {

  ResultHandler NO_RESULT_HANDLER = null;

  int update(MappedStatement ms, Object parameter) throws SQLException;

  <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey cacheKey, BoundSql boundSql) throws SQLException;

  <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException;

  <E> Cursor<E> queryCursor(MappedStatement ms, Object parameter, RowBounds rowBounds) throws SQLException;

  List<BatchResult> flushStatements() throws SQLException;

  void commit(boolean required) throws SQLException;

  void rollback(boolean required) throws SQLException;

  CacheKey createCacheKey(MappedStatement ms, Object parameterObject, RowBounds rowBounds, BoundSql boundSql);

  boolean isCached(MappedStatement ms, CacheKey key);

  void clearLocalCache();

  void deferLoad(MappedStatement ms, MetaObject resultObject, String property, CacheKey key, Class<?> targetType);

  Transaction getTransaction();

  void close(boolean forceRollback);

  boolean isClosed();

  void setExecutorWrapper(Executor executor);

}

Executor的拦截器在SQL语句执行之前和之后执行，可以在这里添加逻辑来处理SQL执行过程中的需求，如日志记录或性能监控

public interface StatementHandler {

  Statement prepare(Connection connection, Integer transactionTimeout)
      throws SQLException;

  void parameterize(Statement statement)
      throws SQLException;

  void batch(Statement statement)
      throws SQLException;

  int update(Statement statement)
      throws SQLException;

  <E> List<E> query(Statement statement, ResultHandler resultHandler)
      throws SQLException;

  <E> Cursor<E> queryCursor(Statement statement)
      throws SQLException;

  BoundSql getBoundSql();

  ParameterHandler getParameterHandler();

}

StatementHandler的拦截器在生成SQL语句和设置参数阶段执行，可以在这里修改SQL语句或参数，也可以打印日志

public interface ParameterHandler {

  Object getParameterObject();

  void setParameters(PreparedStatement ps) throws SQLException;

}

ParameterHandler的拦截器在StatementHandler设置参数之后执行

public interface ResultSetHandler {

  <E> List<E> handleResultSets(Statement stmt) throws SQLException;

  <E> Cursor<E> handleCursorResultSets(Statement stmt) throws SQLException;

  void handleOutputParameters(CallableStatement cs) throws SQLException;

}

ResultSetHandler的拦截器在SQL执行之后、将结果集映射到Java对象之前执行，可以在这里对查询结果进行修改或处理

2.3 Interceptor接口

类需要实现Interceptor接口，重写intercept方法

public interface Interceptor {

  Object intercept(Invocation invocation) throws Throwable;

  default Object plugin(Object target) {
    return Plugin.wrap(target, this);
  }

  default void setProperties(Properties properties) {
    // NOP
  }

}

Interceptor接口的plugin方法和setProperties方法都是default默认方法，可以不重写

Interceptor接口的intercept方法的入参是Invocation对象

public class Invocation {
  private final Object target;
  private final Method method;
  private final Object[] args;
  public Object proceed() throws InvocationTargetException, IllegalAccessException {
    return method.invoke(target, args);
  }
}

Invocation对象的target属性是被代理对象，即4个接口中的一个，method是接口的方法，args是方法的入参

Invocation对象的proceed方法是执行被代理对象的方法，因此拦截器可以在执行被代理对象的方法之前或者之后进行操作，它还有返回值就是被代理对象的方法的返回值

2.4 @Component

实现Interceptor接口的类需要加上@Component注解，将它注入到IOC容器中

3、拦截器的实例

3.1 设计表

create table `news` (
    `id` varchar(100) primary key not null,
    `title` varchar(100) not null,
    `create_user_id` int,
    `create_date_time` date
)

3.2 整体类结构

3.3 pom.xml文件

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.2</version>
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.27</version>
    </dependency>

    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.4</version>
    </dependency>

    <!--MD5依赖-->
    <dependency>
        <groupId>commons-codec</groupId>
        <artifactId>commons-codec</artifactId>
        <version>1.16.0</version>
    </dependency>
</dependencies>

3.4 application.yml文件

server:
  port: 9021

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://ip:port/数据库名字?useUnicode=true
    username: xxx
    password: xxx

mybatis:
  # 扫描resource类路径下的所有xml文件
  mapper-locations: classpath:/*.xml
  # 输出sql
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 扫描包下的所有类，首字母小写作为别名
  type-aliases-package: com.lwc

如果没有配置数据源spring.datasource的话，启动服务的时候会报错

如果没有配置mybatis.mapper-locations是xml文件的位置，就会抛出异常：org.apache.ibatis.binding.BindingException

mybatis.configuration.log-impl: org.apache.ibatis.logging.stdout.StdOutImpl 是输出sql语句

mybatis.type-aliases-package: com.lwc 是com.lwc包下的所有类都取别名是类名首字母小写，在写resultType的时候不用写全类名了，直接写类名首字母小写

3.5 创建主启动类MybatisIntercetorApplication

@SpringBootApplication
public class MybatisIntercetorApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatisIntercetorApplication.class, args);
    }
}

3.6 controller接口

@RestController
@RequiredArgsConstructor
@Log4j2
@RequestMapping("/news")
public class NewsController {

    private final NewsService newsService;

    @PostMapping("/save")
    public String saveNews(@RequestBody Map<String, Object> map) {
        newsService.saveNews(map);
        return "saveNews success";
    }

}

3.7 service接口

@Service
@RequiredArgsConstructor
@Log4j2
public class NewsService {

    private final NewsMapper newsMapper;

    public void saveNews(Map<String, Object> map) {
        List<String> fieldNameList = new ArrayList<>();
        List<Object> fieldValueList = new ArrayList<>();
        map.forEach((k, v) -> {
            fieldNameList.add(k);
            fieldValueList.add(v);
        });
        newsMapper.insertNews(fieldNameList, fieldValueList);
    }

}

3.8 mapper接口

@Mapper
public interface NewsMapper {
    public int insertNews(@Param("fieldNameList") List<String> fieldNameList,
                          @Param("fieldValueList") List<Object> fieldValueList);
}

3.9 mapper.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.lwc.mapper.NewsMapper">
    <insert id="insertNews">
        insert into news
        <foreach collection="fieldNameList" item="fieldName" open="(" separator="," close=")">
            ${fieldName}
        </foreach>
        values
        <foreach collection="fieldValueList" item="fieldValue" open="(" separator="," close=")">
            #{fieldValue}
        </foreach>
    </insert>
</mapper>

其中，字段名字必须用${}，它可以直接替换成字符串，参数值一般用#{}，因为它可以防止sql注入，#{}会替换成?，然后?会替换成参数值，如果参数值是字符串会在字符串加上单引号

3.10 ModifySqlInterceptor拦截器

@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
@Component
@Log4j2
public class ModifySqlInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 目标：在执行StatementHandler对象的prepare方法之前修改sql，因此修改sql之后再调用invocation.proceed()方法
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = statementHandler.getBoundSql();
        String sql = boundSql.getSql();
        log.info("modify before, sql: {}", sql);
        // parameterObject参数必须是Map，并且map包含fieldNameList和fieldNameList，才会修改sql
        Object parameterObject = boundSql.getParameterObject();
        if(!(parameterObject instanceof Map)){
            return invocation.proceed();
        }
        Map<String, Object> map = (Map<String, Object>) parameterObject;
        if(!map.containsKey("fieldNameList") || !map.containsKey("fieldValueList")) {
            return invocation.proceed();
        }
        // 修改sql，添加多个字段
        List<String> fieldNameList = (List<String>) map.get("fieldNameList");
        fieldNameList.add("id");
        fieldNameList.add("create_user_id");
        fieldNameList.add("create_date_time");
        String fieldName = fieldNameList.stream().collect(Collectors.joining(", "));
        String fieldValue = fieldNameList.stream().map(s -> "?").collect(Collectors.joining(", "));
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(sql.substring(0, sql.indexOf("(") + 1));
        stringBuilder.append(" ");
        stringBuilder.append(fieldName);
        stringBuilder.append(" ");
        stringBuilder.append(sql.substring(sql.indexOf(")"), sql.lastIndexOf("(") + 1));
        stringBuilder.append(" ");
        stringBuilder.append(fieldValue);
        stringBuilder.append(" )");
        String newSql = stringBuilder.toString();
        // 采用SystemMetaObject.forObject方法实例化BoundSql对象
        MetaObject metaObject = SystemMetaObject.forObject(boundSql);
        metaObject.setValue("sql", newSql);
        log.info("modify after, sql: {}", boundSql.getSql());
        return invocation.proceed();
    }

}

ModifySqlInterceptor拦截器用来在执行StatementHandler对象的prepare方法之前修改sql，因此修改sql之后再调用invocation.proceed()方法

修改sql需要先得到原来的sql，还要得到参数名字和参数值，它们都可以通过StatementHandler对象的BoundSql对象获取到sql和parameterObject，将参数值全部用?替换可以防止sql注入，最后使用SystemMetaObject.forObject方法实例化BoundSql对象并调用metaObject.setValue(“sql”, newSql)方法修改sql

其中，SystemMetaObject.forObject方法是MyBatis用来反射对象的工具类，它可以获取对象属性，也可以设置对象属性

3.11 AddParamsInterceptor拦截器

@Intercepts({
        @Signature(type = StatementHandler.class, method = "parameterize", args = {Statement.class})
})
@Component
@Log4j2
public class AddParamsInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 目标：在StatementHandler对象的parameterize方法完成参数赋值之后再添加新的参数值，因此先执行invocation.proceed()方法然后添加参数值
        // StatementHandler对象的parameterize方法返回值是void
        invocation.proceed();
        RoutingStatementHandler routingStatementHandler = (RoutingStatementHandler) invocation.getTarget();
        MetaObject metaObject = SystemMetaObject.forObject(routingStatementHandler);
        // 获取StatementHandler对象中的参数名和参数值，得到Map
        Object parameterObject = metaObject.getValue("delegate.boundSql.parameterObject");
        if(!(parameterObject instanceof Map)){
            return null; //不能在执行invocation.proceed();了，否则会添加两次参数值
        }
        Map<String, Object> map = (Map<String, Object>) parameterObject;
        if(!map.containsKey("fieldNameList") || !map.containsKey("fieldValueList")) {
            return null; //不能在执行invocation.proceed();了，否则会添加两次参数值
        }
        List<String> fieldNameList = (List<String>) map.get("fieldNameList");
        List<Object> fieldValueList = (List<Object>) map.get("fieldValueList");
        Map<String, Object> res = new HashMap<>();
        for(int i = 0; i < fieldValueList.size(); i++) {
            res.put(fieldNameList.get(i).toLowerCase(), fieldValueList.get(i));
        }
        // 获取StatementHandler对象中的sql得到表名
        String sql = (String) metaObject.getValue("delegate.boundSql.sql");
        String tableName = sql.substring(sql.indexOf("into") + 5, sql.indexOf("(")).trim();
        // 获取StatementHandler对象中的parameterMappings，得到参数下标，因为parameterize方法的参数值赋值是根据parameterMappings的大小来的
        List<ParameterMapping> parameterMappings = (List<ParameterMapping>) metaObject.getValue("delegate.boundSql.parameterMappings");
        int size = parameterMappings.size();
        // 添加参数值，需要按照顺序，根据修改的sql的字段顺序
        Object[] args = invocation.getArgs();
        PreparedStatement preparedStatement = (PreparedStatement) args[0];
        preparedStatement.setString(++size, getId(tableName, res));
        preparedStatement.setInt(++size, UserIdThreadLocal.getUserId());
        preparedStatement.setDate(++size, new Date(System.currentTimeMillis()));
        return null;
    }

    private String getId(String tableName, Map<String, Object> map) {
        if("news".equals(tableName.toLowerCase())) {
            String title = (String) map.get("title");
            String id = MD5Util.getMd5(title);
            return id;
        }
        return null;
    }

}

AddParamsInterceptor拦截器用来在StatementHandler对象的parameterize方法完成参数赋值之后再添加新的参数值，因此先执行invocation.proceed()方法然后添加参数值

添加参数值是通过parameterize方法的入参PreparedStatement对象的setXxx方法实现的，同时可以防止sql注入因为参数值是字符串的话会加单引号，同时添加参数值的顺序要和ModifySqlInterceptor拦截器添加参数名字的顺序一致

setXxx方法的key：

获取StatementHandler对象中的parameterMappings，得到参数下标，因为parameterize方法的参数值赋值是根据parameterMappings的大小来的

setXxx方法的value：

① 参数id的值是根据表名tableName和title这个字段的值使用MD5哈希算法得到id值，id值是一个字符串因此用setString方法

② 参数create_user_id的值是根据ThreadLocal中的userId得到的，这个userId是前端发送请求会携带userId请求头然后在过滤器解析请求头中的userId并存放到ThreadLocal中的，create_user_id是一个整数因此用setInt方法

③ 参数create_date_time的值是根据当前时间得到的，create_date_time是时间类型因此用setDate方法

3.12 UserIdThreadLocal

public class UserIdThreadLocal {

    private static ThreadLocal<Integer> threadLocal = new ThreadLocal<>();

    public static void setUserId(Integer userId) {
        threadLocal.set(userId);
    }

    public static Integer getUserId() {
        return threadLocal.get();
    }

    public static void removeUserId() {
        threadLocal.remove();
    }

}

UserIdThreadLocal存放了userId，它包含static静态的ThreadLocal

3.13 UserIdFilter过滤器

@WebFilter("/*")
@Component
public class UserIdFilter implements Filter {

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        HttpServletRequest request = (HttpServletRequest) servletRequest;
        String userIdString = request.getHeader("userId");
        if(userIdString == null) {
            filterChain.doFilter(servletRequest, servletResponse);
        }
        Integer userId = Integer.parseInt(userIdString);
        try {
            UserIdThreadLocal.setUserId(userId);
            filterChain.doFilter(servletRequest, servletResponse);
        } finally {
            UserIdThreadLocal.removeUserId();
        }
    }

}

@WebFilter(“/*”)表示过滤所有请求，必须加@Component将过滤器对象注入到IOC容器中

3.14 SqlPrintInterceptor拦截器

@Intercepts({
        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class})
})
@Component
@Log4j2
public class SqlPrintInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 目标：打印替换?的参数的sql语句
        int res = (int) invocation.proceed();
        Object[] args = invocation.getArgs();
        PreparedStatement preparedStatement = (PreparedStatement) args[0];
        MetaObject metaObject = SystemMetaObject.forObject(preparedStatement);
        preparedStatement = (PreparedStatement) metaObject.getValue("h.statement.delegate");
        String sql = preparedStatement.toString().substring(preparedStatement.toString().indexOf(":") + 1).replace("\n", " ");
        List<String> list = new ArrayList<>();
        for (String s : sql.split(" ")) {
            if("".equals(s)) {
                continue;
            }
            list.add(s.trim());
        }
        log.info(list.stream().collect(Collectors.joining(" ")));
        return res;
    }

}

SqlPrintInterceptor拦截器的目标是打印替换?的参数的sql语句，即实际sql执行语句，通过SystemMetaObject.forObject方法得到PreparedStatement对象

3.15 输出结果

可以看到拦截器修改sql成功

这是MyBatis输出的sql日志，可以看到拦截器添加参数值成功，sql中的?和参数值一一对应，其中参数类型已经解析完成

可以看到拦截器打印日志成功，实际执行的sql是将?替换成参数值，为了防止sql注入，字符串类型和日期类型都用单引号防止sql注入，这也是#{}可以防止sql注入的原因，整数类型没有加单引号