
基于MybatisPlus拦截器实现数据库关键字处理及官方做法
有些老的数据库当中可能会有些字段和数据库关键字冲突,使用mybatisPlus执行Sql的时候有时候会执行失败,前段时间和群友讨论的时候他说遇到了这个问题,当时我提议让他用我以前写的一个里边的工具类改造一下。他说不能实现,然后找到了官方的配置信息,也就是添加最下边的那个。
·
1. 背景
有些老的数据库当中可能会有些字段和数据库关键字冲突,使用mybatisPlus执行Sql的时候有时候会执行失败,前段时间和群友讨论的时候他说遇到了这个问题,当时我提议让他用我以前写的一个自定义注解+mybatis拦截器实现权限控制里边的工具类改造一下。
他说不能实现,然后找到了官方的配置信息,也就是添加最下边的那个column-format
, 具体配置如下:
mybatis-plus:
mapper-locations: classpath*:/mapper/**/*.xml
configuration:
# 这个配置会将执行的sql打印出来,在开发或测试的时候可以用
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 返回类型为Map,显示null对应的字段
call-setters-on-nulls: true
global-config:
# 关闭MP3.0自带的banner
banner: true
db-config:
id-type: none
# 默认数据库表下划线命名
table-underline: true
# 拦截器功能 处理数据库关键字
column-format: "`%s`"
2. 自己实现一个关键字替换的方法
仅仅是一个初步的实现,具体的逻辑还需要变更一下。
主要的实现逻辑就是在字典表中配置上数据库的关键字,然后在Sql执行之前给BoundSql
中的关键字替换一下。也就是keywordHandler
方法的逻辑
package net.lesscoding.keywordhandler.interceptor;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import net.lesscoding.keywordhandler.entity.SysDict;
import net.lesscoding.keywordhandler.mapper.SysDictMapper;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.InvocationTargetException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
@Intercepts(
{
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
}
)
@Slf4j
@Component
@Order(20)
public class KeywordHandlerInterceptor implements Interceptor {
@Autowired
@Lazy
private SysDictMapper dictMapper;
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
MappedStatement mappedStatement = (MappedStatement) args[0];
SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
//获取参数,语句成立,表示sql语句有参数,参数格式是map形式
Object parameter = args.length > 1 ? args[1] : null;
//id为执行的mapper方法的全路径名,如com.xxx.xxxMapper.insertXxx
String sqlId = mappedStatement.getId();
Executor executor = (Executor) invocation.getTarget();
//[BoundSql]封装mybatis最终产生的sql类
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
//获取节点的配置
Configuration configuration = mappedStatement.getConfiguration();
String sql = getSql(configuration, boundSql, sqlId, false);
// 非select语句直接过滤
if (!SqlCommandType.SELECT.equals(sqlCommandType) || sqlId.contains("SysDictMapper")) {
return proceed(invocation, sqlId, sql);
}
String beforeSql = boundSql.getSql().replaceAll("[\\s]+", " ");
log.info("之前的sql {}", beforeSql);
//String afterSql = contactConditions(beforeSql, permissionControl, (Map)parameter);
String afterSql = keywordHandler(beforeSql);
log.info("关键字处理之后的sql {}", afterSql);
ReflectUtil.setFieldValue(boundSql, "sql", afterSql);
log.info("最终执行的sql {}", getSql(configuration, boundSql, sqlId, false));
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
RowBounds rowBounds = (RowBounds) args[2];
ResultHandler resultHandler = (ResultHandler) args[3];
//可以对参数做各种处理
CacheKey cacheKey = executor.createCacheKey(ms, parameterObject, rowBounds, boundSql);
return executor.query(ms, parameterObject, rowBounds, resultHandler, cacheKey, boundSql);
}
private String keywordHandler(String beforeSql) {
List<SysDict> list = dictMapper.selectList(new QueryWrapper<SysDict>()
.eq("dict_type", "keyword"));
for (SysDict item : list) {
beforeSql = beforeSql.replaceAll(StrUtil.format("(?<!_){}", item.getDictCode()), item.getDictValue());
}
return beforeSql;
}
public static void main(String[] args) {
String str = "replace name,but not replace _name, test_name,login_name, user_name";
System.out.println(str.replaceAll("(?<!_)name", "`name`"));
}
private Object proceed(Invocation invocation, String sqlId, String sql) throws Throwable{
try {
//执行完上面的任务后,执行原有sql的执行过程
return invocation.proceed();
} catch (InvocationTargetException e) {
log.error("当前sql执行错误\n {} \n, 方法id ::: {}\n 执行sql ::: {}", e.getMessage(), sqlId, sql);
log.error("sql执行异常", e);
throw new RuntimeException("sql执行异常" + sqlId);
}
}
/**
* 获取最终的sql语句
*
* @param configuration 节点配置
* @param boundSql BoundSql
* @param sqlId mapper方法的全路径名
* @param flag 是否需要拼接mapper方法的全路径名
* @return String
*/
private String getSql(Configuration configuration, BoundSql boundSql, String sqlId, boolean flag) throws NoSuchFieldException {
String sql = showSql(configuration, boundSql);
return flag ? sqlId + ":" + sql : sql;
}
/**
* 进行?的替换
*
* @param configuration 节点的配置
* @param boundSql BoundSql
* @return String
*/
private String showSql(Configuration configuration, BoundSql boundSql) {
//获取参数
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
//sql语句
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (CollUtil.isNotEmpty(parameterMappings) && parameterObject != null) {
//获取类型处理器
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
//对应的类型则进行替换
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));
} else {
MetaObject metaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object objectValue = metaObject.getValue(propertyName);
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(objectValue)));
}
if (boundSql.hasAdditionalParameter(propertyName)) {
//动态sql
Object additionalParameter = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(additionalParameter)));
}
//else {
// sql = sql.replaceFirst("\\?", "");
//}
}
}
}
return sql;
}
/**
* 参数二次处理
*
* @param parameterObject parameterObject
* @return String
*/
private String getParameterValue(Object parameterObject) {
String format = "'%s'";
String value = "";
if (null != parameterObject) {
value = String.valueOf(parameterObject);
}
if (parameterObject instanceof String) {
value = String.valueOf(parameterObject);
}
if (parameterObject instanceof Date) {
//DateFormat dateTimeInstance = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT,
// Locale.CHINA);
//value = dateTimeInstance.format(parameterObject);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
value = sdf.format((Date) parameterObject);
}
if (parameterObject instanceof LocalDateTime) {
value = ((LocalDateTime) parameterObject).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
if (parameterObject instanceof LocalDate) {
value = ((LocalDate) parameterObject).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
}
if (parameterObject instanceof LocalTime) {
value = ((LocalTime) parameterObject).format(DateTimeFormatter.ofPattern("HH:mm:ss"));
}
if (parameterObject instanceof Timestamp) {
value = ((Timestamp) parameterObject).toString();
}
return String.format(format, value);
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
Interceptor.super.setProperties(properties);
}
}

DAMO开发者矩阵,由阿里巴巴达摩院和中国互联网协会联合发起,致力于探讨最前沿的技术趋势与应用成果,搭建高质量的交流与分享平台,推动技术创新与产业应用链接,围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
所有评论(0)