摘要
本文通过代码展示如何自定义一个mybatis拦截器以实现SQL语句的再次封装。
拦截目的
为了在特定业务场景下实现SQL语句的全局封装，而无需在多个逻辑类中手动调用转换方法。这里为了实现输入%或者_时能够查找到带%或_字符的数据内容，一般来说%和_是数据库的特殊字符，是一种通配符，会把所有内容模糊查找出来。
示例代码
1）导入依赖pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-webartifactId>
<exclusions>
<exclusion>
<artifactId>spring-boot-starter-tomcatartifactId>
<groupId>org.springframework.bootgroupId>
exclusion>
exclusions>
dependency>
<dependency>
<groupId>org.projectlombokgroupId>
<artifactId>lombokartifactId>
<optional>trueoptional>
dependency>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-testartifactId>
dependency>
<dependency>
<groupId>com.baomidougroupId>
<artifactId>mybatis-plus-boot-starterartifactId>
dependency>
<dependency>
<groupId>mysqlgroupId>
<artifactId>mysql-connector-javaartifactId>
<version>8.0.29version>
dependency>
<dependency>
<groupId>com.alibabagroupId>
<artifactId>druidartifactId>
<version>1.2.8version>
dependency>
<dependency>
<groupId>p6spygroupId>
<artifactId>p6spyartifactId>
dependency>
dependencies>
2）自定义配置application.yml
#这里省略其他mybatis的默认配置
spring:
datasource:
type:com.alibaba.druid.pool.DruidDataSource
driverClassName:com.p6spy.engine.spy.P6SpyDriver #使用P6Spy驱动
url:jdbc:p6spy:mysql://192.168.233.129:3306/CoffeeBeansDB?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
username:xxx
password:xxx
mybatis:
custom-search:
fields:search,input # 自定义拦截指定的搜索字段
3）读取动态拦截参数配置
package org.coffeebeans.config;
import lombok.Getter;
import lombok.Setter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
import java.util.List;
/**
* ClassName: SearchParamsConfig
* Author: OakWang
*/
@Component
@Getter
@Setter
@ConfigurationProperties(prefix = "mybatis.custom-search")
public class SearchParamsConfig {
private List fields;
}
4）自定义拦截类
package org.coffeebeans.config;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.util.*;
/**
* ClassName: QueryInterceptor
* Author: OakWang
*/
@Component
@Intercepts({
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class QueryInterceptor implements Interceptor {
@Autowired
private SearchParamsConfig searchParamsConfig;
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
Object parameter = args[1];
if (searchParamsConfig.getFields() == null || searchParamsConfig.getFields().isEmpty()) {
return invocation.proceed(); // 如果没有配置字段，则直接返回
}
if (parameter instanceof Map) {
handleMapParameter((Map<String, Object>) parameter);
} else {
handlePojoParameter(parameter);
}
return invocation.proceed();
}
private void handleMapParameter(Map<String, Object> paramMap) {
for (String fieldName : searchParamsConfig.getFields()) {
if (paramMap.containsKey(fieldName)) {
String value = (String) paramMap.get(fieldName);
if (value != null) {
paramMap.put(fieldName, "%" + value.replace("%", "%").replace("_", "_") + "%");
}
}
}
//TODO 扩展 转义 % 和 _（根据数据库类型调整）
// if (isMySQL()) {
// //
// } else if (isSQLServer()) {
// //
// } else if (isPostgreSQL()) {
// //
// }
}
private void handlePojoParameter(Object pojo) {
for (String fieldName : searchParamsConfig.getFields()) {
try {
Field field = pojo.getClass().getDeclaredField(fieldName);
field.setAccessible(true); //实现不区分大小写的字段匹配（需自行处理大小写转换）
Object value = field.get(pojo);
if (value instanceof String) {
field.set(pojo, "%" + ((String)value).replace("%", "%").replace("_", "_") + "%");
}
} catch (NoSuchFieldException | IllegalAccessException ignored) {
// 如果字段不存在或不可访问，则跳过
}
}
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
// 不需要额外处理
}
}
5）定义ORM
实体
package org.coffeebeans.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;
/**
* ClassName: User
* Author: OakWang
*/
@Data
public class User {
@TableField("id")
private Long id;
@TableField("name")
private String name;
@TableField("create_time")
private String createTime;
}
mapper
package org.coffeebeans.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.coffeebeans.entity.User;
import java.util.List;
@Mapper
public interface UserMapper {
List selectBySearch(@Param("search") String search);
}
xml
"1.0" encoding="UTF-8" ?>
mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.coffeebeans.mapper.UserMapper">
<select id="selectBySearch" resultType="org.coffeebeans.entity.User">
SELECT * FROM User WHERE name LIKE #{search}
select>
mapper>
6）测试类
package org.coffeebeans;
import lombok.extern.slf4j.Slf4j;
import org.coffeebeans.service.InterceptorService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
/**
* ClassName: org.org.coffeebeans.InterceptorTest
* Author: OakWang
*/
@Slf4j
@SpringBootTest
public class InterceptorTest {
@Autowired
private InterceptorService interceptorService;
@Test
void test1() {
interceptorService.selectBySearch("1%");
}
@Test
void test2() {
interceptorService.selectBySearch("张_");
}
}
7）测试拦截情况
建测试数据
只找“1%”的数据，而不是所有带“1”的数据
只找“张_”的数据，而不是以“张”开头的数据
断点查看拦截情况
打印SQL，查看拦截后的语句
测试配置修改
总结
以上我们了解了如何在springboot项目中自定义mybatis拦截器以实现sql的全局封装。
关注公众号：咖啡Beans
在这里，我们专注于软件技术的交流与成长，分享开发心得与笔记，涵盖编程、AI、资讯、面试等多个领域。无论是前沿科技的探索，还是实用技巧的总结，我们都致力于为大家呈现有价值的内容。期待与你共同进步，开启技术之旅。