一、需求
表结构里面有两个固定字段,DATA_STATE 和 DELETE_MARKER
char(1) DATA_STATE 数据状态 (1正常 0逻辑删除)
char(1) DELETE_MARKER 删除标记 (0: 未删除,null:删除)
逻辑删除 DATA_STATE='0' 时,需要强制对对应的表字段(DELETE_MARKER)进行置空,
设置DELETE_MARKER=null。
二、方案
由于 DELETE_MARKER 字段是为了优化索引才加入表结构。系统中大量SQL语句,逻辑删除时,都只有 set data_state = '0',如果一个个修改,费事费力。
所以使用 Mybatis 拦截器来做,在SQL预编译的时候,拦截UPDATE类型SQL,判断是否 set data_state = '0' 如果是,修改SQL语句同步更新 delete_marker = null。
三、代码
package com.macrosoft.interceptor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.apache.ibatis.executor.statement.StatementHandler;
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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.scripting.xmltags.OgnlCache;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
* DELETE_MARKER mybatis拦截器
* 需求:
* DATA_STATE 数据状态 (1正常 0逻辑删除)
* DELETE_MARKER 删除标记 (0: 未删除,null:删除)
* 逻辑删除 DATA_STATE=0时,需要强制对对应的表字段(DELETE_MARKER)进行置空,
* 设置DELETE_MARKER=null
* 拦截UPDATE类型SQL,判断是否 update data_state=0
* 修改SQL语句同步更新delete_marker=null
* @Author : linzp
*/
@Slf4j
@Component
@ConditionalOnProperty(prefix = "delete_marker_intercept", value = "enable", havingValue = "true")
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class })})
public class DeleteMarkerInterceptor implements Interceptor {
private static final String LOGICAL_DELETION_STRING_VALUE = "0";
private static final String DATA_STATE_PROPERTY = "dataState";
private static final String DATA_STATE_FIELD_UPPERCASE = "DATA_STATE";
private static final String DATA_STATE_FIELD_LOWERCASE = "data_state";
private static final String DELETE_MARKER = "delete_marker";
@Override
public Object intercept(Invocation invocation) throws Throwable {
try {
if (invocation.getTarget() instanceof StatementHandler) {
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
// MetaObject优雅访问对象的属性(这里访问statementHandler的属性),通过它可以简化代码
MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
// sql语句类型
String sqlCommandType = mappedStatement.getSqlCommandType().toString();
log.info("DeleteMarkerInterceptor拦截到sql语句类型:{}", sqlCommandType);
if (SqlCommandType.UPDATE.name().equals(sqlCommandType)) {
// 获取sql语句
BoundSql boundSql = statementHandler.getBoundSql();
String oldSql = boundSql.getSql();
// update set是否包含data_state字段
if (this.containsField(oldSql)) {
Statement statement = CCJSqlParserUtil.parse(oldSql);
Update updateStatement = (Update) statement;
Object param = boundSql.getParameterObject();
ArrayList<UpdateSet> updateSets = updateStatement.getUpdateSets();
String property = this.getProperty(boundSql);
// 判断data_state是否为0
if (this.isOk(updateSets, param, property)) {
// 拼接上 delete_marker = null
updateStatement.addUpdateSet(new Column(DELETE_MARKER), new NullValue());
String newSql = updateStatement.toString();
log.info("DeleteMarkerInterceptor修改过的新sql:{}", newSql);
Field field = boundSql.getClass().getDeclaredField("sql");
field.setAccessible(true);
field.set(boundSql, newSql);
}
}
}
}
} catch (Exception e) {
// 这里需要捕获异常,为了保证主流程不会被异常阻断
log.error("DeleteMarkerInterceptor 拦截器异常",e);
}
// 继续执行该执行的方法
return invocation.proceed();
}
/**
* 获取Mapper.xml文件配置的OGNL表达式
* 如:
* #{map.dataState} 中的map.dataState
* #{dataState} 中的dataState
* @Author : linzp
* @param boundSql SQL包装对象
* @return OGNL表达式
*/
private String getProperty(BoundSql boundSql) {
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
for (ParameterMapping pm : parameterMappings) {
if (pm.getProperty().contains(DATA_STATE_PROPERTY)) {
return pm.getProperty();
}
}
return DATA_STATE_PROPERTY;
}
/**
* 判断是否需要动态修改SQL语句
* update set 包含data_state字段,且字段值要是0
* 才需要拼接上 delete_marker = null
* SQL格式有如下几种:
* update table set data_state = 0 where condition; --> LongValue
* update table set data_state = '0' where condition; --> StringValue
* update table set data_state = #{dataState} where condition; --> JdbcParameter
* update table set data_state = #{map.dataState} where condition; --> JdbcParameter
* @Author : linzp
* @param updateSets sql语句update set 后面的字段
* @param obj 参数对象
* @param property Ognl表达式
* @return 是否需要动态修改SQL语句
*/
private boolean isOk(ArrayList<UpdateSet> updateSets, Object obj, String property) {
for (UpdateSet updateSet : updateSets) {
// 包含DATA_STATE字段,且字段值要是0
Column col = updateSet.getColumns().get(0);
if (col.getColumnName().equalsIgnoreCase(DATA_STATE_FIELD_UPPERCASE)) {
Expression exp = updateSet.getExpressions().get(0);
if (exp instanceof StringValue) {
StringValue strV = (StringValue) exp;
return LOGICAL_DELETION_STRING_VALUE.equals(strV.getValue());
} else if (exp instanceof JdbcParameter) {
Object value = OgnlCache.getValue(property, obj);
return LOGICAL_DELETION_STRING_VALUE.equals(String.valueOf(value));
} else if (exp instanceof LongValue) {
LongValue longV = (LongValue) exp;
return LOGICAL_DELETION_STRING_VALUE.equals(longV.getStringValue());
}
return false;
}
}
return false;
}
/**
* sql语句 update set是否包含data_state字段
* 快速判断,以便让大部分SQL通过
* @Author : linzp
* @param oldSql 旧SQL
* @return update set是否包含data_state字段
*/
private boolean containsField(String oldSql) {
int begin = !oldSql.contains("set") ? oldSql.indexOf("SET") : oldSql.indexOf("set");
int end = !oldSql.contains("where") ? oldSql.indexOf("WHERE") : oldSql.indexOf("where");
String str = oldSql.substring(begin, end);
return str.contains(DATA_STATE_FIELD_UPPERCASE) || str.contains(DATA_STATE_FIELD_LOWERCASE);
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
代码说明:
@ConditionalOnProperty(prefix = "delete_marker_intercept", value = "enable", havingValue = "true")
条件注解做的一个开关,只有当 application.xml 中配置了
delete_marker_intercept: enable: ${DELETE_MARKER_ENABLE:false}
DELETE_MARKER_ENABLE = true 时,这个拦截器才生效
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class })})
MyBatis拦截器默认可以拦截的类型只有四种,即四种接口类型
Executor、StatementHandler、ParameterHandler和ResultSetHandler。
org.apache.ibatis.executor.Executor //拦截执行器方法
org.apache.ibatis.executor.statement.StatementHandler //拦截SQL语法构建处理
org.apache.ibatis.executor.parameter.ParameterHandler //拦截参数处理
org.apache.ibatis.executor.resultset.ResultSetHandler //拦截结果集处理对于我们的自定义拦截器必须使用MyBatis提供的@Intercepts注解来指明我们要拦截的是四种类型中的哪一种接口。
@Intercepts // 描述:标志该类是一个拦截器
@Signature // 描述:指明该拦截器需要拦截哪一个接口的哪一个方法
type; // 四种类型接口中的某一个接口,如 StatementHandler.class;
method; // 对应接口中的某一个方法名,比如StatementHandler的prepare方法;
args; // 对应接口中的某一个方法的参数,方法因为重载原因,有多个,args就是指明参数类型,从而确定是具体哪一个方法;对于本例中我们拦截的就是 StatementHandler的prepare方法,它有两个参数Connection和Integer。
四、测试用例
TestCase:
@SpringBootTest(classes = {MyApplication.class})
public class MyServiceTest {
@Autowired
private MyService myService;
@Test
public void test1() {
ParamVO vo = new ParamVO();
vo.setDataState("0");
myService.test1();
}
@Test
public void test2() {
myService.test2();
}
@Test
public void test3() {
List<MyVO> list = myService.test3();
}
@Test
public void test4() {
Map<String, Object> map = new HashMap<>();
map.put("dataState", "0");
map.put("name", "lilei");
myService.test4(map);
}
@Test
public void test5() {
myService.test5("0");
}
}
Service:
@Service
public class MyService {
@Autowired(required = false)
private MyMapper myMapper;
public void test1(ParamVO vo) {
myMapper.test1(vo);
}
public void test2() {
myMapper.test2();
}
public List<MyVO> test3() {
return myMapper.test3();
}
public void test4(Map<String, Object> map) {
myMapper.test4(map);
}
public void test5(String dataState) {
myMapper.test5(dataState);
}
}
Mapper.java:
@Mapper
public interface MyMapper extends BaseMapper<MyVO> {
void test1(ParamVO vo);
void test2();
List<MyVO> test3();
void test4(@Param("map") Map<String, Object> map);
void test5(@Param("dataState") String dataState);
}
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.macrosoft.mapper.MyMapper">
<update id="test1">
update my_table set name='lilei', DATA_STATE = #{dataState}, age = 6
where ID = '1';
</update>
<update id="test2">
update my_table c set c.DATA_STATE = '0' where ID = '1';
</update>
<select id="test3" resultType="com.macrosoft.entity.MyVO">
select * from my_table where ID = '1';
</select>
<update id="test4" parameterType="map">
update my_table set name=#{map.name}, DATA_STATE = #{map.dataState}, age = 6
where ID = '1';
</update>
<update id="test5">
update my_table set name='lilei', data_state = #{dataState}, age = 6
where ID = '1';
</update>
</mapper>