一费薄、前戲
故事背景
公司引入一套阿里云的大數(shù)據(jù)系統(tǒng)，匯集公司的多張表栖雾。
正式環(huán)境使用的是大數(shù)據(jù)中間件2.0楞抡，正式環(huán)境是3.0，3.0環(huán)境查詢(xún)需要 “數(shù)據(jù)庫(kù)名.表名”岩灭，2.0環(huán)境查詢(xún)需要“表名”拌倍。
需要實(shí)現(xiàn)一個(gè)根據(jù)環(huán)境不同，修改表名的功能噪径。
常見(jiàn)的技術(shù)方案選型
- 方式1：最直觀的柱恤，維護(hù)兩套mybatis的sql，根據(jù)環(huán)境不同加載不同sql文件找爱，維護(hù)成本大
- 方式2：提取公共xml文件梗顺，將表名抽成公共變量，進(jìn)行維護(hù)车摄，維護(hù)成本大寺谤，sql編寫(xiě)不直觀。
https://blog.csdn.net/wohaqiyi/article/details/84993681 - 方式3：（推薦）使用mybatis的攔截器吮播，根據(jù)環(huán)境變量的不同变屁，替換表名
https://mybatis.org/mybatis-3/zh/configuration.html#plugins
二、正文
配置文件bootstrap.yml
# 正式站與測(cè)試站表明對(duì)應(yīng)配置文件
# 正式站表名: 測(cè)試站表名
dataworks:
tableName:
prodTableName: testDataBaseName.testTableName
解析配置信息
/**
* @Auther: fatsnake
* @Description":
* @Date:2022/9/14 13:18
* Copyright (c) 2022, zaodao All Rights Reserved.
*/
@Configuration
@ConfigurationProperties(prefix = "dataworks")
public class DataBaseTableNameConfig {
/**
* 表名集合
*/
private Map<String, String> tableName;
/**
* 構(gòu)造函數(shù)
*/
DataBaseTableNameConfig() {
}
public Map<String, String> getTableName() {
return tableName;
}
public void setTableName(Map<String, String> tableName) {
this.tableName = tableName;
}
@Override
public String toString() {
return "DataBaseTableNameConfig{"
+ "tableName=" + tableName
+ '}';
}
}
mybatis插件按環(huán)境條件生效
@Profile({"dev", "test"})
public class FrameMyBatisPluginConfig {
/**
*
* @param sqlSessionFactory sqlSessionFactory
* @return String
*/
@Bean
public String sqlTableNameHandleInterceptor(SqlSessionFactory sqlSessionFactory) {
//實(shí)例化插件
SQLTableNameHandleInterceptor sqlTableNameHandleInterceptor = new SQLTableNameHandleInterceptor();
// 為后續(xù)留好擴(kuò)展
// //創(chuàng)建屬性值
// Properties properties = new Properties();
// properties.setProperty("prop1","value1");
// //將屬性值設(shè)置到插件中
// sqlTableNameHandleInterceptor.setProperties(properties);
//將插件添加到SqlSessionFactory工廠(chǎng)
sqlSessionFactory.getConfiguration().addInterceptor(sqlTableNameHandleInterceptor);
return "interceptor";
}
}
使用mybatis的StatementHandler插件意狠，在sql執(zhí)行前進(jìn)行攔截
此處使用了druid的工具類(lèi)粟关，解析表名，替換表名后重寫(xiě)表名
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.util.JdbcConstants;
/**
* @Auther: fatsnake
* @Description":
* @Date:2022/9/14 12:47
* Copyright (c) 2022, zaodao All Rights Reserved.
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class SQLTableNameHandleInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
DataBaseTableNameConfig dataBaseTableNameConfig
= (DataBaseTableNameConfig)SpringContextUtils.getBean("dataBaseTableNameConfig");
Map<String, String> tableNameMap = dataBaseTableNameConfig.getTableName();
if (!CollectionUtils.isEmpty(tableNameMap)) {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, new DefaultObjectFactory(),
new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
String sql = boundSql.getSql();
List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
MySqlExportTableAliasVisitor visitor = new MySqlExportTableAliasVisitor();
for (SQLStatement stmt : stmtList) {
stmt.accept(visitor);
}
String handleSQL = SQLUtils.toSQLString(stmtList, JdbcConstants.MYSQL);
metaStatementHandler.setValue("delegate.boundSql.sql", handleSQL);
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
實(shí)際替換操作
/**
* @Auther: fatsnake
* @Description":
* @Date:2022/9/14 12:46
* Copyright (c) 2022, zaodao All Rights Reserved.
*/
public class MySqlExportTableAliasVisitor extends MySqlASTVisitorAdapter {
@Override
public boolean visit(SQLExprTableSource x) {
DataBaseTableNameConfig dataBaseTableNameConfig
= (DataBaseTableNameConfig) SpringContextUtils.getBean("dataBaseTableNameConfig");
Map<String, String> tableNameMap = dataBaseTableNameConfig.getTableName();
if (tableNameMap.containsKey(x.getExpr().toString())) {
x.setExpr(tableNameMap.get(x.getExpr().toString()));
}
return true;
}
}
三环戈、尾聲
本來(lái)不了解這個(gè)mybatis的插件機(jī)制的闷板，當(dāng)時(shí)接到這個(gè)需求時(shí)，只是想著如何無(wú)感知的讓業(yè)務(wù)開(kāi)發(fā)人員使用院塞，并且項(xiàng)目便于維護(hù)sql遮晚，最好不要維護(hù)兩套sql，經(jīng)過(guò)百度老師的指導(dǎo)拦止，整合形成了這個(gè)結(jié)束解決方案县遣。
暴露出，對(duì)常用的中間件 還是不熟悉啊创泄，源代碼讀的還是少……
