执行流程
1 实现mybatis Interceptor接口
2 拦截Executor 对象获取它的第一个参数MappedStatement
3 拦截Executor 对象的第2个参数(要执行的sql参数)params
4 通过MappedStatement的mappedStatement.getBoundSql(param) 方法获取要执行的sql对象BoundSql
5通过tBoundSql对象获取sql
6 修改原始sql产生新的sql
7 以原始sql为key 新sql为值存入ThreadLocal
编写插件
package com.cqbykj.go.settlement.data.plugin;
import com.baomidou.mybatisplus.annotation.TableName;
import com.cqbykj.go.settlement.data.annotation.DynamicTableName;
import com.cqbykj.go.settlement.data.util.DynamicTableNameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.Properties;
/***
* mybatis 动态替换表名插件
*/
@Intercepts({
@Signature(type = Executor.class,method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
@Signature(type = Executor.class,method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = StatementHandler.class,method = "prepare",args = {Connection.class, Integer.class})
})
@Component
public class DynamicTableNamePlugin implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
//动态修改表名
choosePlugin(invocation);
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target,this);
}
@Override
public void setProperties(Properties properties) {
}
/***
* 选择拦截插件对象
*/
public void choosePlugin(Invocation invocation){
Object target = invocation.getTarget();
//拦截执行器
if( target instanceof Executor){
//修改sql表名
changeTableName(invocation);
}else if(target instanceof StatementHandler){
StatementHandler statementHandler = (StatementHandler) target;
changeExecutorSql(statementHandler);
}
}
/***
* 修改sql表名
* @param invocation
*/
public void changeTableName(Invocation invocation){
MappedStatement mappedStatement = (MappedStatement)invocation.getArgs()[0];
//获取参数
Object param = invocation.getArgs()[1];
//如果是查询
if(mappedStatement.getSqlCommandType().equals(SqlCommandType.SELECT)){
//获取返回值类型
Class<?> type = mappedStatement.getResultMaps().get(0).getType();
//判读该表是否需要动态替换
if(!type.isAnnotationPresent(DynamicTableName.class)){return;}
if(!type.isAnnotationPresent(TableName.class)){return;}
TableName tableName = type.getAnnotation(TableName.class);
if(StringUtils.isEmpty(tableName.value())){return;}
//原始表名
String beforeTableName = tableName.value();
// 替换执行sql表名
changeSql(beforeTableName,mappedStatement,param);
}
}
/***
* 替换执行sql表名
*/
public void changeSql(String beforeTableName,MappedStatement mappedStatement, Object param){
String changeTableName = DynamicTableNameUtils.getChangeTableName(beforeTableName);
if(StringUtils.isEmpty(changeTableName)){ return;}
BoundSql boundSql = mappedStatement.getBoundSql(param);
String sql = boundSql.getSql();
//替换sql名称
String pattern = "FROM.*"+beforeTableName;
String target = "FROM " + changeTableName;
String newSql = sql.replaceAll(pattern, target);
DynamicTableNameUtils.setChangeSql(sql,newSql);
//删除修改表名的
DynamicTableNameUtils.removeChangeTableName(beforeTableName);
}
/***
* 改变执行sql
* @param statementHandler
*/
public void changeExecutorSql(StatementHandler statementHandler){
BoundSql boundSql = statementHandler.getBoundSql();
//获取原始sql
String sql = boundSql.getSql();
//获取该sql是否存在修改的队列
String changeSql = DynamicTableNameUtils.getChangeSql(sql);
try {
if(StringUtils.isNotBlank(changeSql)){
Class<? extends BoundSql> sqlClass = boundSql.getClass();
Field sqlField = sqlClass.getDeclaredField("sql");
sqlField.setAccessible(true);
sqlField.set(boundSql,changeSql);
}
DynamicTableNameUtils.removeChangeSql(sql);
}catch (Exception e){
e.printStackTrace();
}
}
}
实体对象
package com.cqbykj.go.settlement.data.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.cqbykj.go.settlement.data.annotation.DynamicTableName;
import com.cqbykj.go.settlement.data.basic.BasicEntity;
import java.io.Serializable;
import java.math.BigDecimal;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
/**
* <p>
* 营收详细
* </p>
*
* @author tools
* @since 2022-07-14
*/
@Getter
@Setter
@TableName("revenue_detail_month")
@DynamicTableName
@ApiModel(value = "RevenueDetailMonth对象", description = "营收详细")
public class RevenueDetailMonth extends BasicEntity {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@ApiModelProperty("创建时间")
@TableField("create_date_time")
private String createDateTime;
@ApiModelProperty("车站键")
@TableField("merchant_id")
private String merchantId;
@ApiModelProperty("车站名称")
@TableField("merchant_name")
private String merchantName;
@ApiModelProperty("线路主键")
@TableField("line_seq")
private String lineSeq;
@ApiModelProperty("线路名称")
@TableField("line_name")
private String lineName;
@ApiModelProperty("车牌号")
@TableField("car_id")
private String carId;
@ApiModelProperty("承运单位id")
@TableField("unit_id")
private String unitId;
@ApiModelProperty("承运单位")
@TableField("unit_name")
private String unitName;
@ApiModelProperty("站务收入")
@TableField("station_income")
private BigDecimal stationIncome;
@ApiModelProperty("客运销售额")
@TableField("passenger_sales_amount")
private BigDecimal passengerSalesAmount;
@ApiModelProperty("客运销售量")
@TableField("passenger_sales_num")
private Long passengerSalesNum;
@ApiModelProperty("退票额")
@TableField("return_amount")
private BigDecimal returnAmount;
@ApiModelProperty("退票量")
@TableField("return_num")
private Long returnNum;
@ApiModelProperty("退票手续费")
@TableField("return_hand_fee")
private BigDecimal returnHandFee;
@ApiModelProperty("站务费")
@TableField("station_fee")
private BigDecimal stationFee;
@ApiModelProperty("客运收入总额")
@TableField("passenger_income_amount")
private BigDecimal passengerIncomeAmount;
@ApiModelProperty("客运代理费")
@TableField("passenger_manager_fee")
private BigDecimal passengerManagerFee;
@ApiModelProperty("代付手续费")
@TableField("paid_fee")
private BigDecimal paidFee;
@ApiModelProperty("扣款")
@TableField("deductions")
private BigDecimal deductions;
@ApiModelProperty("加班费")
@TableField("overtime_fee")
private BigDecimal overtimeFee;
@ApiModelProperty("开班费")
@TableField("classes_fee")
private BigDecimal classesFee;
@ApiModelProperty("清洁费")
@TableField("cleaning_fee")
private BigDecimal cleaningFee;
@ApiModelProperty("整理费")
@TableField("maintenance_fee")
private BigDecimal maintenanceFee;
@ApiModelProperty("安检费")
@TableField("security_fee")
private BigDecimal securityFee;
@ApiModelProperty("停车费")
@TableField("parking_fee")
private BigDecimal parkingFee;
@ApiModelProperty("罚款")
@TableField("fine")
private BigDecimal fine;
@ApiModelProperty("车辆管理费")
@TableField("management_fee")
private BigDecimal managementFee;
@ApiModelProperty("安全统筹金")
@TableField("security_funds")
private BigDecimal securityFunds;
@ApiModelProperty("保险费")
@TableField("insurance_fee")
private BigDecimal insuranceFee;
@ApiModelProperty("其他")
@TableField("other_fee")
private BigDecimal otherFee;
@ApiModelProperty("已结算金额")
@TableField("settled_amount")
private BigDecimal settledAmount;
@ApiModelProperty("已结算量")
@TableField("settled_num")
private Long settledNum;
@ApiModelProperty("未结算金额")
@TableField("unsettled_amount")
private BigDecimal unsettledAmount;
@ApiModelProperty("未结算量")
@TableField("unsettled_num")
private Long unsettledNum;
@ApiModelProperty("本期站务收入同比")
@TableField("income_compared")
private BigDecimal incomeCompared;
@ApiModelProperty("本期站务收入环比")
@TableField("income_chain")
private BigDecimal incomeChain;
@ApiModelProperty("本期客运销售总额同比")
@TableField("passenger_sales_compared")
private BigDecimal passengerSalesCompared;
@ApiModelProperty("本期客运销售总额环比")
@TableField("passenger_sales_chain")
private BigDecimal passengerSalesChain;
@ApiModelProperty("本期退票总额同比")
@TableField("traffic_fee_compared")
private BigDecimal trafficFeeCompared;
@ApiModelProperty("本期支出运费总额环比")
@TableField("traffic_fee_chain")
private BigDecimal trafficFeeChain;
@ApiModelProperty("本期客运销售总额同比")
@TableField("return_amount_compared")
private BigDecimal returnAmountCompared;
@ApiModelProperty("本期退票总额环比")
@TableField("return_amount_chain")
private BigDecimal returnAmountChain;
@ApiModelProperty("日期")
@TableField("statistical_date")
private String statisticalDate;
@ApiModelProperty("备注")
@TableField("remark")
private String remark;
}
编写threadLocal存储sql与要变化的表名
package com.cqbykj.go.settlement.data.util;
import com.baomidou.mybatisplus.annotation.TableName;
import com.google.common.collect.Maps;
import org.springframework.util.CollectionUtils;
import java.util.Map;
/***
* 多线程动态表名处理工具
*/
public class DynamicTableNameUtils {
/***
* 原始sql与替换sql映射
*/
private static final ThreadLocal<Map<String,String>> CHANGE_SQL = new ThreadLocal<>();
/***
* 原始表名与替换表名映射
*/
private static final ThreadLocal<Map<String,String>> CHANGE_TABLE_NAME = new ThreadLocal<>();
/***
* 修改sql
* @param sql 原始sql
* @param changeSql 修改后sql
*/
public static void setChangeSql(String sql,String changeSql){
Map<String, String> map = CHANGE_SQL.get();
if(CollectionUtils.isEmpty(map)){
map = Maps.newHashMap();
}
map.put(sql,changeSql);
CHANGE_SQL.set(map);
}
/***
* 根据原始sql获取
* @param sql
* @return
*/
public static String getChangeSql(String sql){
Map<String, String> map = CHANGE_SQL.get();
return CollectionUtils.isEmpty(map)?null:map.get(sql);
}
/***
* 根据原始sql移除
* @param sql
*/
public static void removeChangeSql(String sql){
Map<String, String> map = CHANGE_SQL.get();
if(!CollectionUtils.isEmpty(map) && map.containsKey(sql)){
map.remove(map);
}
CHANGE_SQL.remove();
}
/***
* 修改表名
* @param tableName 原始表名
* @param changeTableName 目标表名
*/
public static void setChangeTableName(String tableName,String changeTableName){
Map<String, String> map = CHANGE_TABLE_NAME.get();
if(CollectionUtils.isEmpty(map)){
map = Maps.newHashMap();
}
map.put(tableName,changeTableName);
CHANGE_TABLE_NAME.set(map);
}
/***
* 根据原始表名获取修改表名
* @param tableName
* @return
*/
public static String getChangeTableName(String tableName){
Map<String, String> map = CHANGE_TABLE_NAME.get();
System.out.println(map);
return CollectionUtils.isEmpty(map)?null:map.get(tableName);
}
/***
* 根据原始表名移除
* @param tableName
*/
public static void removeChangeTableName(String tableName){
Map<String, String> map = CHANGE_TABLE_NAME.get();
if(!CollectionUtils.isEmpty(map) && map.containsKey(tableName)){
map.remove(tableName);
}
CHANGE_TABLE_NAME.remove();
}
public static void main(String[] args) {
DynamicTableNameUtils.setChangeTableName("user","user_temp");
DynamicTableNameUtils.setChangeTableName("bill","bill_temp");
DynamicTableNameUtils.getChangeTableName("bill");
DynamicTableNameUtils.removeChangeTableName("bill");
DynamicTableNameUtils.getChangeTableName("user");
}
}