运行时动态修改SQL

装饰器模式

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

public abstract class AbstractConnection implements Connection {
    protected final Connection delegate;

    public AbstractConnection(Connection delegate) {
        this.delegate = delegate;
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        PreparedStatement ps = this.delegate.prepareStatement(sql);
        return ps;
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        PreparedStatement ps = this.delegate.prepareStatement(sql, resultSetType, resultSetConcurrency);
        return ps;
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        PreparedStatement ps = this.delegate.prepareStatement(sql, autoGeneratedKeys);
        return ps;
    }

    // 其它方法
    @Override
    public Statement createStatement() throws SQLException {
        Statement s = this.delegate.createStatement();
        return s;
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        CallableStatement cs = this.delegate.prepareCall(sql);
        return cs;
    }

    @Override
    public String nativeSQL(String sql) throws SQLException {
        String str = this.delegate.nativeSQL(sql);
        return str;
    }

    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        this.delegate.setAutoCommit(autoCommit);
    }

    @Override
    public boolean getAutoCommit() throws SQLException {
        return this.delegate.getAutoCommit();
    }

    @Override
    public void commit() throws SQLException {
        this.delegate.commit();
    }

    @Override
    public void rollback() throws SQLException {
        this.delegate.rollback();
    }

    @Override
    public void close() throws SQLException {
        this.delegate.close();
    }

    @Override
    public boolean isClosed() throws SQLException {
        return this.delegate.isClosed();
    }

    @Override
    public DatabaseMetaData getMetaData() throws SQLException {
        return this.delegate.getMetaData();
    }

    @Override
    public void setReadOnly(boolean readOnly) throws SQLException {
        this.delegate.setReadOnly(readOnly);

    }

    @Override
    public boolean isReadOnly() throws SQLException {
        return this.delegate.isReadOnly();
    }

    @Override
    public void setCatalog(String catalog) throws SQLException {
        this.delegate.setCatalog(catalog);

    }

    @Override
    public String getCatalog() throws SQLException {
        return this.delegate.getCatalog();
    }

    @Override
    public void setTransactionIsolation(int level) throws SQLException {
        this.delegate.setTransactionIsolation(level);
    }

    @Override
    public int getTransactionIsolation() throws SQLException {
        return this.delegate.getTransactionIsolation();
    }

    @Override
    public SQLWarning getWarnings() throws SQLException {
        return this.delegate.getWarnings();
    }

    @Override
    public void clearWarnings() throws SQLException {
        this.delegate.clearWarnings();
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return this.delegate.createStatement(resultSetType, resultSetConcurrency);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return this.delegate.prepareCall(sql, resultSetType, resultSetConcurrency);
    }

    @Override
    public Map<String, Class<?>> getTypeMap() throws SQLException {
        return this.delegate.getTypeMap();
    }

    @Override
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        this.delegate.setTypeMap(map);
    }

    @Override
    public void setHoldability(int holdability) throws SQLException {
        this.delegate.setHoldability(holdability);
    }

    @Override
    public int getHoldability() throws SQLException {
        return this.delegate.getHoldability();
    }

    @Override
    public Savepoint setSavepoint() throws SQLException {
        return this.delegate.setSavepoint();
    }

    @Override
    public Savepoint setSavepoint(String name) throws SQLException {
        return this.delegate.setSavepoint(name);
    }

    @Override
    public void rollback(Savepoint savepoint) throws SQLException {
        this.delegate.rollback();
    }

    @Override
    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        this.delegate.releaseSavepoint(savepoint);
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return this.delegate.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return this.delegate.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return this.delegate.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
        return this.delegate.prepareStatement(sql, columnIndexes);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
        return this.delegate.prepareStatement(sql, columnNames);
    }

    @Override
    public Clob createClob() throws SQLException {
        return this.delegate.createClob();
    }

    @Override
    public Blob createBlob() throws SQLException {
        return this.delegate.createBlob();
    }

    @Override
    public NClob createNClob() throws SQLException {
        return this.delegate.createNClob();
    }

    @Override
    public SQLXML createSQLXML() throws SQLException {
        return this.delegate.createSQLXML();
    }

    @Override
    public boolean isValid(int timeout) throws SQLException {
        return this.delegate.isValid(timeout);
    }

    @Override
    public void setClientInfo(String name, String value) throws SQLClientInfoException {
        this.delegate.setClientInfo(name, value);
    }

    @Override
    public void setClientInfo(Properties properties) throws SQLClientInfoException {
        this.delegate.setClientInfo(properties);
    }

    @Override
    public String getClientInfo(String name) throws SQLException {
        return this.delegate.getClientInfo(name);
    }

    @Override
    public Properties getClientInfo() throws SQLException {
        return this.delegate.getClientInfo();
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
        return this.delegate.createArrayOf(typeName, elements);
    }

    @Override
    public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
        return this.delegate.createStruct(typeName, attributes);
    }

    @Override
    public void setSchema(String schema) throws SQLException {
        this.delegate.setSchema(schema);
    }

    @Override
    public String getSchema() throws SQLException {
        return this.delegate.getSchema();
    }

    @Override
    public void abort(Executor executor) throws SQLException {
        this.delegate.abort(executor);
    }

    @Override
    public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
        this.delegate.setNetworkTimeout(executor, milliseconds);
    }

    @Override
    public int getNetworkTimeout() throws SQLException {
        return this.delegate.getNetworkTimeout();
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return this.delegate.unwrap(iface);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return this.delegate.isWrapperFor(iface);
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PackConnection extends AbstractConnection {
    public PackConnection(Connection delegate) {
        super(delegate);
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        sql = ModifyExecuteSQL.modifySQL(sql);
        System.err.printf("\033[32m执行SQL: %s%n\033[30m", sql);
        PreparedStatement ps = this.delegate.prepareStatement(sql);
        return PreparedStatementProxyCreator.createProxy(this.getClass().getClassLoader(), ps);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        sql = ModifyExecuteSQL.modifySQL(sql);
        System.err.printf("\033[32m执行SQL: %s%n\033[30m", sql);
        PreparedStatement ps = this.delegate.prepareStatement(sql, resultSetType, resultSetConcurrency);
        return PreparedStatementProxyCreator.createProxy(this.getClass().getClassLoader(), ps);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        sql = ModifyExecuteSQL.modifySQL(sql);
        System.err.printf("\033[32m执行SQL: %s%n\033[30m", sql);
        PreparedStatement ps = this.delegate.prepareStatement(sql, autoGeneratedKeys);
        return PreparedStatementProxyCreator.createProxy(this.getClass().getClassLoader(), ps);
    }

}

动态代理模式

import java.lang.reflect.Proxy;
import java.sql.PreparedStatement;

public class PreparedStatementProxyCreator {

    public static PreparedStatement createProxy(ClassLoader classLoader, PreparedStatement ps) {
        // 定义代理类的加载器 | 指定代理类要实现的接口列表 | 定义代理对象的方法拦截逻辑
        Object proxy = Proxy.newProxyInstance(classLoader, new Class<?>[]{PreparedStatement.class}, new PreparedStatementHandler(ps));
        return (PreparedStatement) proxy;
    }

}

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;

public class PreparedStatementHandler implements InvocationHandler {
    private final PreparedStatement delegate;

    public PreparedStatementHandler(PreparedStatement delegate) {
        this.delegate = delegate;
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        // 如果你需要对参数进行处理，那么你可以在这里进行
        if (method.getName().equals("setString")) {
            // ... 进行数据处理
        }
        return method.invoke(this.delegate, args);
    }
}

Spring AOP

import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;

import javax.sql.DataSource;
import java.lang.reflect.Method;

public class DataSourceInterceptor implements MethodInterceptor {
    private final DataSource targetDataSource;

    public DataSourceInterceptor(DataSource targetDataSource) {
        this.targetDataSource = targetDataSource;
    }

    @Override
    public Object invoke(MethodInvocation invocation) throws Throwable {
        Method method = invocation.getMethod();
        // 当调用的 getConnection 方法时，我们直接通过 PackConnection
        // 包装原始的 Connection 对象（真正工作的还是原始的那个 Connection）
        if ("getConnection".equals(method.getName())) {
            return new PackConnection(this.targetDataSource.getConnection());
        }
        return invocation.proceed();
    }
}

Bean 后置处理器

自动代理 DataSource Bean

import org.springframework.aop.framework.ProxyFactory;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.config.BeanPostProcessor;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

@Component
public class DataSourceBeanPostProcessor implements BeanPostProcessor {
    @Override
    public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
        // 如果是数据源对象（不管你用的什么连接池）
        if (bean instanceof DataSource dataSource) {
            // 创建代理对象
            ProxyFactory factory = new ProxyFactory(bean);
            factory.setProxyTargetClass(true); // 强制使用CGLIB代理（支持类代理
            factory.addAdvice(new DataSourceInterceptor(dataSource)); // 添加拦截逻辑
            return factory.getProxy(); // 返回代理对象
        }
        return bean; // 非DataSource类型，直接返回原Bean
    }
}

工具类查询语句加上 deleted = 0

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.PlainSelect;

public class ModifyExecuteSQL {

    /**
     * 修改SQL语句，自动添加软删除条件
     *
     * @param sqlStr 原始SQL语句
     * @return 修改后的SQL语句，如果解析失败则返回原SQL
     */
    public static String modifySQL(String sqlStr) {
        try {
            Statement statement = parseSQL(sqlStr);
            if (!(statement instanceof PlainSelect)) {
                return sqlStr;
            }

            PlainSelect select = (PlainSelect) statement;
            addSoftDeleteCondition(select);
            return select.toString();

        } catch (JSQLParserException e) {
            logParseError(sqlStr, e);
            return sqlStr;
        }
    }

    /**
     * 解析SQL语句
     */
    private static Statement parseSQL(String sqlStr) throws JSQLParserException {
        return CCJSqlParserUtil.parse(sqlStr);
    }

    /**
     * 添加软删除条件(deleted = 0)
     */
    private static void addSoftDeleteCondition(PlainSelect select) {
        String columnName = buildSoftDeleteColumnName(select);
        Expression newCondition = createSoftDeleteCondition(columnName);
        updateWhereClause(select, newCondition);
    }

    /**
     * 更新WHERE子句，添加新条件
     */
    private static void updateWhereClause(PlainSelect select, Expression newCondition) {
        Expression where = select.getWhere();

        if (where == null) {
            select.setWhere(newCondition);
        } else {
            select.setWhere(new AndExpression(where, newCondition));
        }
    }

    /**
     * 构建软删除列名(带表别名或表名)
     */
    private static String buildSoftDeleteColumnName(PlainSelect select) {
        FromItem fromItem = select.getFromItem();

        // 处理表别名
        if (fromItem.getAlias() != null) {
            return fromItem.getAlias().getName() + ".deleted";
        }

        // 处理表名(无别名情况)
        if (fromItem instanceof Table) {
            return ((Table) fromItem).getName() + ".deleted";
        }

        // 子查询或其他复杂情况，直接使用deleted
        return "deleted";
    }

    /**
     * 创建软删除条件表达式(deleted = 0)
     */
    private static Expression createSoftDeleteCondition(String columnName) {
        Column column = new Column(columnName);
        return new EqualsTo(column, new LongValue(0));
    }

    /**
     * 记录解析错误日志
     */
    private static void logParseError(String sqlStr, Exception e) {
        System.err.println("解析SQL【" + sqlStr + "】错误: " + e.getMessage());
    }

}

更新: 2025-07-19 10:55:25
原文: https://www.yuque.com/lsxxyg/sz/bcpiiinggadk0biv