一、背景
项目中有 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 } ) ,
@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工具进行实时同步数据。