一、背景
- 项目中有 30 多个功能点每个功能对应一张表。要求:每张表的增、删(逻辑删相当修改)、改的数据都需要同步到下游系统中。
- 客户方不让使用 Flink、Datax、canal 等工具。
- 这里的删除是逻辑删,相当于修改。
- 因为项目中有 30 多个功能点,我这里不想在接口中进行判断。因此考虑 mybatis 的拦截器。
二、思路
- 继承 Interceptor 接口实现自定义拦截器。
- 需要在自定义拦截器中获取到表名和字段值,主要是修改时间。
- 因为所有的表中都有修改时间字段,并且接口中所有对数据的新增修改都会更新这个时间。因此在拦截器中获取到表名和修改时间后反查表,捞出修改时间对应的所有数据。
- 整理获取到数据,发送到下游系统。
三、代码
- maven相关依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.6.5</version>
</dependency>
- CDCInterceptor.java //自定义拦截器,功能核心
package com.liran.middle.common.mybatis.interceptor;
import cn.hutool.core.convert.Convert;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.annotation.IEnum;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.liran.middle.common.base.utils.ThreadPoolUtil;
import com.liran.middle.common.mybatis.mapper.CommonMapper;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.ibatis.executor.BatchResult;
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.reflection.SystemMetaObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
@Component
@Intercepts({
@Signature(
type = Executor.class,
method = "update",
args = {MappedStatement.class, Object.class}),
// mybatis plus的批量方法最后后执行这个方法,因此进行拦截
@Signature(
type = Executor.class,
method = "flushStatements",
args = {})
})
@Slf4j
public class CDCInterceptor implements Interceptor {
@Autowired
public CommonMapper commonMapper;
@Override
public void setProperties(Properties properties) {
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object proceed = invocation.proceed();
try {
String str = Convert.toStr(proceed, "-1");
if ("-2147482646".equals(str)) {
return proceed;
}
Map<Date, String> updateAtMap;
if ("flushStatements".equals(invocation.getMethod().getName())) {
updateAtMap = getBatchOperationsUpdateAt((List<Map<String, Object>>) proceed);
} else {
updateAtMap = getUpdateAt(invocation);
}
if (CollectionUtils.isEmpty(updateAtMap) || updateAtMap.values().stream().anyMatch(Arrays.asList("file_process_logs", "table2", "table3")::contains)) {
log.info("表 {} 加入黑名单,不进行CDC同步数据", updateAtMap.values().stream().limit(1).toString());
return proceed;
}
for (Map.Entry<Date, String> entry : updateAtMap.entrySet()) {
sendData(entry.getValue(), entry.getKey());
}
} catch (Exception e) {
log.error("CDC同步数据失败 error", e);
return proceed;
}
return proceed;
}
private Map<Date, String> getBatchOperationsUpdateAt(List<Map<String, Object>> proceedList) throws Exception {
BatchResult batchResult = (BatchResult) proceedList.get(0);
SqlCommandType sqlCommandType = batchResult.getMappedStatement().getSqlCommandType();
List<Object> parameterObjects = batchResult.getParameterObjects();
Statement statement = CCJSqlParserUtil.parse(batchResult.getSql());
String tableName = "";
Map<Date, String> updateAtMap = new HashMap<>();
if (sqlCommandType == SqlCommandType.INSERT) {
Insert apply = (Insert) statement;
tableName = apply.getTable().getName();
for (Object parameterObject : parameterObjects) {
Map etMap = JSON.parseObject(JSON.toJSONString(parameterObject), Map.class);
updateAtMap.put(new Date(Convert.toLong(etMap.get("updatedAt"))), tableName);
}
} else if (sqlCommandType == SqlCommandType.UPDATE) {
Update apply = (Update) statement;
tableName = apply.getTable().getName();
for (Object parameterObject : parameterObjects) {
Map<String, Object> paraMap = (Map<String, Object>) parameterObject;
Map etMap = JSON.parseObject(JSON.toJSONString(paraMap.get("et")), Map.class);
updateAtMap.put(new Date(Convert.toLong(etMap.get("updatedAt"))), tableName);
}
} else {
return Collections.emptyMap();
}
log.info("{} 这个是批量操作的方法!", tableName);
return updateAtMap;
}
private Map<Date, String> getUpdateAt(Invocation invocation) throws Exception {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
Object parameter = invocation.getArgs()[1];
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
Statement statement = CCJSqlParserUtil.parse(boundSql.getSql());
String tableName = "";
Map<String, Object> columnDatas = new HashMap<>();
if (sqlCommandType == SqlCommandType.INSERT) {
Insert apply = (Insert) statement;
tableName = apply.getTable().getName();
Map<String, Object> map = JSON.parseObject(JSON.toJSONString(parameter), Map.class);
if (!Objects.isNull(map.get("collection"))) {
List<Map<String, Object>> collectionList = (List<Map<String, Object>>) map.get("collection");
for (Map<String, Object> collection : collectionList) {
columnDatas.put("UPDATED_AT", new Date(Convert.toLong(collection.get("updatedAt"))));
}
} else {
columnDatas = getUpdatedColumnDatas(tableName, boundSql, statement);
}
log.info(tableName + "处理插入操作 ");
} else if (sqlCommandType == SqlCommandType.UPDATE) {
Update apply = (Update) statement;
tableName = apply.getTable().getName();
columnDatas = getUpdatedColumnDatas(tableName, boundSql, statement);
log.info(tableName + "处理更新操作 ");
} else if (sqlCommandType == SqlCommandType.DELETE) {
log.info(tableName + "处理删除操作 ");
return Collections.emptyMap();
}
Object updatedObject = columnDatas.get("UPDATED_AT");
if (updatedObject instanceof String) {
Date data = dateFormat((String) columnDatas.get("UPDATED_AT"));
return Collections.singletonMap(data, tableName);
} else {
return Collections.singletonMap((Date) updatedObject, tableName);
}
}
private void sendData(String tableName, Date updatedAt) {
ThreadPoolUtil.submit(() -> {
List<Map<String, Object>> data = commonMapper.getData(tableName, updatedAt);
log.info("同步到其他系统的数据为 send data: {}", JSON.toJSONString(data));
});
}
protected Map<String, Object> getUpdatedColumnDatas(String tableName, BoundSql updateSql, Statement statement) {
Map<String, Object> columnNameValMap = new HashMap<>(updateSql.getParameterMappings().size());
Map<Integer, String> columnSetIndexMap = new HashMap<>(updateSql.getParameterMappings().size());
List<Column> selectItemsFromUpdateSql = new ArrayList<>();
if (statement instanceof Update) {
Update updateStmt = (Update) statement;
int index = 0;
selectItemsFromUpdateSql.addAll(updateStmt.getColumns());
List<Expression> updateList = updateStmt.getExpressions();
for (int i = 0; i < updateList.size(); ++i) {
Expression updateExps = updateList.get(i);
if (!(updateExps instanceof JdbcParameter)) {
columnNameValMap.put(updateStmt.getColumns().get(i).getColumnName().toUpperCase(), updateExps.toString());
}
columnSetIndexMap.put(index++, updateStmt.getColumns().get(i).getColumnName().toUpperCase());
}
} else if (statement instanceof Insert) {
Insert insert = (Insert) statement;
selectItemsFromUpdateSql.addAll(insert.getColumns());
}
Map<String, String> relatedColumnsUpperCaseWithoutUnderline = new HashMap<>(selectItemsFromUpdateSql.size(), 1);
for (Column item : selectItemsFromUpdateSql) {
relatedColumnsUpperCaseWithoutUnderline.put(item.getColumnName().replaceAll("[._\\-$]", "").toUpperCase(), item.getColumnName().toUpperCase());
}
MetaObject metaObject = SystemMetaObject.forObject(updateSql.getParameterObject());
int index = 0;
for (ParameterMapping parameterMapping : updateSql.getParameterMappings()) {
String propertyName = parameterMapping.getProperty();
if (propertyName.startsWith("ew.paramNameValuePairs")) {
++index;
continue;
}
String[] arr = propertyName.split("\\.");
String propertyNameTrim = arr[arr.length - 1].replace("_", "").toUpperCase();
try {
final String columnName = columnSetIndexMap.getOrDefault(index++, getColumnNameByProperty(propertyNameTrim, tableName));
if (relatedColumnsUpperCaseWithoutUnderline.containsKey(propertyNameTrim)) {
String colkey = relatedColumnsUpperCaseWithoutUnderline.get(propertyNameTrim);
Object valObj = metaObject.getValue(propertyName);
if (valObj instanceof IEnum) {
valObj = ((IEnum<?>) valObj).getValue();
} else if (valObj instanceof Enum) {
valObj = getEnumValue((Enum) valObj);
}
if (columnNameValMap.containsKey(colkey)) {
columnNameValMap.put(relatedColumnsUpperCaseWithoutUnderline.get(propertyNameTrim), String.valueOf(columnNameValMap.get(colkey)).replace("?", valObj == null ? "" : valObj.toString()));
}
if (columnName != null && !columnNameValMap.containsKey(columnName)) {
columnNameValMap.put(columnName, valObj);
}
} else {
if (columnName != null) {
columnNameValMap.put(columnName, String.valueOf(metaObject.getValue(propertyName)));
}
}
} catch (Exception e) {
log.warn("get value error,propertyName:{},parameterMapping:{}", propertyName, parameterMapping);
}
}
dealWithUpdateWrapper(columnSetIndexMap, columnNameValMap, updateSql);
return columnNameValMap;
}
private void dealWithUpdateWrapper(Map<Integer, String> columnSetIndexMap, Map<String, Object> columnNameValMap, BoundSql updateSql) {
if (columnSetIndexMap.size() <= columnNameValMap.size()) {
return;
}
MetaObject mpgenVal = SystemMetaObject.forObject(updateSql.getParameterObject());
if (!mpgenVal.hasGetter(Constants.WRAPPER)) {
return;
}
Object ew = mpgenVal.getValue(Constants.WRAPPER);
if (ew instanceof UpdateWrapper || ew instanceof LambdaUpdateWrapper) {
final String sqlSet = ew instanceof UpdateWrapper ? ((UpdateWrapper) ew).getSqlSet() : ((LambdaUpdateWrapper) ew).getSqlSet();
if (sqlSet == null) {
return;
}
MetaObject ewMeta = SystemMetaObject.forObject(ew);
Map paramNameValuePairs = (Map) ewMeta.getValue("paramNameValuePairs");
String[] setItems = sqlSet.split(",");
for (String setItem : setItems) {
String[] nameAndValuePair = setItem.split("=", 2);
if (nameAndValuePair.length == 2) {
String setColName = nameAndValuePair[0].trim().toUpperCase();
String setColVal = nameAndValuePair[1].trim();
if (columnSetIndexMap.containsValue(setColName)) {
String[] mpGenKeyArray = setColVal.split("\\.");
String mpGenKey = mpGenKeyArray[mpGenKeyArray.length - 1].replace("}", "");
final Object setVal = paramNameValuePairs.get(mpGenKey);
if (setVal instanceof IEnum) {
columnNameValMap.put(setColName, String.valueOf(((IEnum<?>) setVal).getValue()));
} else {
columnNameValMap.put(setColName, String.valueOf(setVal));
}
}
}
}
}
}
private Object getEnumValue(Enum enumVal) {
Optional<String> enumValueFieldName = MybatisEnumTypeHandler.findEnumValueFieldName(enumVal.getClass());
if (enumValueFieldName.isPresent()) {
return SystemMetaObject.forObject(enumVal).getValue(enumValueFieldName.get());
}
return enumVal;
}
private String getColumnNameByProperty(String propertyName, String tableName) {
for (TableInfo tableInfo : TableInfoHelper.getTableInfos()) {
if (tableName.equalsIgnoreCase(tableInfo.getTableName())) {
final List<TableFieldInfo> fieldList = tableInfo.getFieldList();
if (CollectionUtils.isEmpty(fieldList)) {
return propertyName;
}
for (TableFieldInfo tableFieldInfo : fieldList) {
if (propertyName.equalsIgnoreCase(tableFieldInfo.getProperty())) {
return tableFieldInfo.getColumn().toUpperCase();
}
}
return propertyName;
}
}
return propertyName;
}
private Date dateFormat(String dataString) {
SimpleDateFormat inputFormat1 = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", java.util.Locale.ENGLISH);
SimpleDateFormat inputFormat2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date parsedDate;
try {
parsedDate = inputFormat1.parse(dataString);
} catch (ParseException e) {
log.info("BISendDorisServiceImpl#dateFormat-日期 {} 与格式 EEE MMM dd HH:mm:ss zzz yyyy 不匹配", dataString);
try {
parsedDate = inputFormat2.parse(dataString);
} catch (ParseException ex) {
log.info("BISendDorisServiceImpl#dateFormat-日期 {} 与格式 yyyy-MM-dd HH:mm:ss 不匹配", dataString);
log.info("BISendDorisServiceImpl#dateFormat-日期解析失败,返回为空");
return null;
}
}
return parsedDate;
}
}
- CommonMapper.java // 反查表时 SQL 语句
package com.liran.middle.common.mybatis.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.Date;
import java.util.List;
import java.util.Map;
public interface CommonMapper extends BaseMapper<Object> {
@Select("select * from ${tableName} where updated_at = #{updatedAt}")
List<Map<String, Object>> getData(@Param("tableName") String tableName,
@Param("updatedAt") Date updatedAt);
}
- ThreadPoolUtil.java // 线程池工具
package com.liran.middle.common.base.utils;
import java.util.concurrent.*;
public class ThreadPoolUtil {
private static final ExecutorService EXECUTOR_SERVICE;
static {
EXECUTOR_SERVICE = new ThreadPoolExecutor(
5,
10,
60L,
TimeUnit.SECONDS,
new LinkedBlockingQueue<>(1000),
ThreadPoolUtil.threadFactory("async-CDC-"),
new ThreadPoolExecutor.CallerRunsPolicy()
);
}
private static ThreadFactory threadFactory(String name) {
return runnable -> {
Thread thread = new Thread(runnable, name);
thread.setDaemon(true);
return thread;
};
}
public static void submit(Runnable task) {
EXECUTOR_SERVICE.submit(task);
}
public static void shutdown() {
if (EXECUTOR_SERVICE != null) {
EXECUTOR_SERVICE.shutdown();
}
}
}
四、注意事项
- 代码中生成的修改时间要和数据库保存的保持一致。比如:如果实体类中修改时间使用的 Date 类型数据库中字段使用 datetime 类型,并且使用 new Date() 生成当前时间,那么当毫秒值大于 500 时保存到数据库中时会自动加上一秒。导致代码中和数据库中时间不一致。
- mybatis plus 中提供了很多批量操作 updateBatchById、saveBatch 等。其中每条数据会触发一遍拦截器,并且所有的数据都加载完成后统一入库。因此我这边做了拦截,只有最后一条数据入库后才进行反查数据。
- 这种实现方式有很多限制:
- 每个表都要有修改时间。
- 拦截器中业务过于复杂时会有性能问题。所有的新增、修改的SQL都会慢。
- 通过修改时间反查表时可能获取不到。
- 多人在同一时间点修改了数据,导致相同的数据推送给下游系统多条。
- 推荐使用现有的CDC工具进行实时同步数据。