数据库隔离切面

我是乌龟我有壳

已于 2023-06-19 17:21:32 修改

阅读量67

点赞数

文章标签：数据库 java 后端

于 2023-06-19 17:20:41 首次发布

本文链接：https://blog.csdn.net/syj_111111/article/details/131291898

版权

该代码实现了一个基于SpringAOP的Schema管理，通过注解`SchemaIntercept`标识Controller方法，从请求头获取`projectType`作为Schema，在SQL执行前动态插入到SQL语句中，以适应多Schema环境。`SchemaAspect`处理请求头信息，`SchemaHolder`存储和传递Schema，`StatementHandlerInterceptor`拦截并修改SQL。

摘要由CSDN通过智能技术生成

package com.cnpc.epai.reservecalculation.manager;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.cnpc.epai.reservecalculation.exception.BizException;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;

/**
 * Schema切面, 提取header头中的schema保存到SchemaHolder中
 *
 * @since 2023-03-16
 */
@Aspect
@Component
@Order(9999)
public class SchemaAspect {

    @Pointcut("@annotation(com.cnpc.epai.reservecalculation.manager.SchemaIntercept)"
    +"|| @within(com.cnpc.epai.reservecalculation.manager.SchemaIntercept)")
    void schema() {

    }

    /**
     * 从请求头提取
     *
     * @param joinPoint
     */
    @Before("schema()")
    public void setSchema(JoinPoint joinPoint) {
        String schema = getSchemaFromHeader();
        SchemaHolder.set(schema);
    }

    @After("schema()")
    public void clearSchema(JoinPoint joinPoint) {
        SchemaHolder.clear();
    }

    /**
     * 从请求头中后去schema信息
     *
     * @return schema
     */
    private String getSchemaFromHeader() {
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        String schema = request.getHeader("projectType");
        if (StringUtils.isEmpty(schema)){
            schema="ric";
        }
        return schema;
    }
}

package com.cnpc.epai.reservecalculation.manager;

/**
 * Schema持有类. 用于在异步线程或者跨多个方法传递schema信息
 *
 * @since 2023-03-16
 */
public class SchemaHolder {
    private static ThreadLocal<String> schema = new ThreadLocal<>();

    public static void set(String sch) {
        schema.set(sch);
    }

    public static String get() {
        return schema.get();
    }

    public static void clear() {
        schema.remove();
    }
}

package com.cnpc.epai.reservecalculation.manager;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * schema拦截器注解。修饰controller接口类，用以区分是否走切换schema逻辑
 *
 * @since 2023-03-16
 */
@Target({ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface SchemaIntercept {

}

package com.cnpc.epai.reservecalculation.manager;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.Properties;

/**
 * StatementHandler拦截器. 在prepare方法执行前拦截，修改sql语句，增加schema.
 *
 * @since 2023-03-16
 */
@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class StatementHandlerInterceptor implements Interceptor {
    private static final Logger LOGGER = LoggerFactory.getLogger(StatementHandlerInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (StringUtils.isEmpty(SchemaHolder.get())) {
            return invocation.proceed();
        }
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = statementHandler.getBoundSql();
        String sql = boundSql.getSql();
        String newSql = replaceSqlWithSchema(sql);
        //通过反射修改sql语句
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, newSql);
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object object) {
        if (object instanceof StatementHandler) {
            return Plugin.wrap(object, this);
        } else {
            return object;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }

    private String replaceSqlWithSchema(String originalSql) {
        // 替换sql中的表名，加上schema
        String schema = SchemaHolder.get();
        if (originalSql.contains("INSERT INTO ")) {
            return originalSql.replaceAll("INSERT INTO ", "INSERT INTO " + schema + ".");
        } else if (originalSql.contains("UPDATE ")) {
            return originalSql.replaceAll("UPDATE ", "UPDATE " + schema + ".");
        } else if (originalSql.contains("DELETE FROM ")) {
            return originalSql.replaceAll("DELETE FROM ", "DELETE FROM " + schema + ".");
        } else {
            return originalSql.replaceAll("FROM ", "FROM " + schema + ".")
                    .replaceAll("from ", "from " + schema + ".")
                    .replaceAll("JOIN ", "JOIN " + schema + ".")
                    .replaceAll("join ", "join " + schema + ".");
        }
    }
}

在controller层添加注解@SchemaIntercept 即可实现