mybatis 插件动态执行分表查询

最新推荐文章于 2024-07-16 19:31:45 发布

qq_34135615

最新推荐文章于 2024-07-16 19:31:45 发布

阅读量1.1k

点赞数

文章标签： mybatis java mysql

本文链接：https://blog.csdn.net/qq_34135615/article/details/125918350

版权

执行流程

在这里插入图片描述

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
8 拦截StatementHandler 对象
9 通过StatementHandler.getBoundSql() 方法获取要执行的sql
10 通过要执行的原始sql在ThreadLocal获取拦截Executor 修改的新sql
11 通过反射将BoundSql要执行的sql替换为新sql

编写插件

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");
    }

}