数据开放平台架构设计（二）：SQL 转 API 引擎

@Component
public class SqlDslParser {

    /**
     * 解析 SQL 模板，生成可执行的 SQL 和参数
     */
    public ParsedSql parse(SqlApiConfig config, Map<String, Object> params) {
        String template = config.getSqlTemplate();

        // 第一步：处理 <if>、<choose>、<foreach> 等 DSL 标签
        String processed = processDslTags(template, params);

        // 第二步：替换 #{param} 占位符为 ?
        List<Object> args = new ArrayList<>();
        String finalSql = replacePlaceholders(processed, params, args);

        return new ParsedSql(finalSql, args.toArray());
    }

    private String processDslTags(String template, Map<String, Object> params) {
        // 处理 <if test="xxx">
        Pattern ifPattern = Pattern.compile(
            "<if\\s+test=\"(\\w+)\">(.+?)</if>", Pattern.DOTALL);
        Matcher matcher = ifPattern.matcher(template);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            String condition = matcher.group(1);
            String content = matcher.group(2);
            // 条件为 true 才保留内容
            if (evaluateCondition(condition, params)) {
                matcher.appendReplacement(sb, Matcher.quoteReplacement(content));
            } else {
                matcher.appendReplacement(sb, "");
            }
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    private String replacePlaceholders(String sql, Map<String, Object> params,
                                        List<Object> args) {
        Pattern pattern = Pattern.compile("#\\{(\\w+)}");
        Matcher matcher = pattern.matcher(sql);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            String paramName = matcher.group(1);
            Object value = params.get(paramName);
            args.add(value);
            matcher.appendReplacement(sb, "?");
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    private boolean evaluateCondition(String paramName, Map<String, Object> params) {
        Object value = params.get(paramName);
        if (value == null) return false;
        if (value instanceof String str) return !str.isEmpty();
        if (value instanceof Collection<?> col) return !col.isEmpty();
        return true;
    }
}

@Component
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    private final Map<String, DataSource> dataSourceMap = new ConcurrentHashMap<>();

    @Override
    protected Object determineCurrentLookupKey() {
        // 从 ThreadLocal 获取当前请求绑定的数据源名
        return DataSourceContextHolder.get();
    }

    /**
     * 注册数据源
     */
    public void register(String name, DataSourceProperties props) {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl(props.getUrl());
        ds.setUsername(props.getUsername());
        ds.setPassword(props.getPassword());
        ds.setMaximumPoolSize(props.getMaxPoolSize() != null ?
            props.getMaxPoolSize() : 10);
        ds.setConnectionTimeout(3000);
        dataSourceMap.put(name, ds);
    }

    /**
     * 获取指定数据源
     */
    public DataSource getDataSource(String name) {
        DataSource ds = dataSourceMap.get(name);
        if (ds == null) {
            throw new BusinessException("数据源不存在: " + name);
        }
        return ds;
    }
}

public PageResult<?> executePageQuery(JdbcTemplate jdbc,
                                       ParsedSql parsed,
                                       SqlApiConfig config,
                                       Map<String, Object> params) {
    int pageNum = getIntParam(params, "pageNum", 1);
    int pageSize = getIntParam(params, "pageSize", 20);
    int offset = (pageNum - 1) * pageSize;

    // 1. 查总数
    String countSql = "SELECT COUNT(*) FROM (" + parsed.getSql() + ") _t";
    Long total = jdbc.queryForObject(countSql, Long.class, parsed.getArgs());

    // 2. 拼分页 SQL（根据方言）
    String pageSql = buildPageSql(parsed.getSql(), config.getDialect(),
        offset, pageSize);

    // 3. 查分页数据
    List<Map<String, Object>> list = jdbc.queryForList(pageSql, parsed.getArgs());

    // 4. 组装分页结果
    return PageResult.builder()
        .total(total != null ? total : 0)
        .pageNum(pageNum)
        .pageSize(pageSize)
        .pages(total != null ? (int) Math.ceil((double) total / pageSize) : 0)
        .list(list)
        .build();
}

private String buildPageSql(String sql, String dialect, int offset, int limit) {
    return switch (dialect.toLowerCase()) {
        case "mysql", "postgresql" -> sql + " LIMIT " + limit + " OFFSET " + offset;
        case "oracle" ->
            "SELECT * FROM (SELECT _t.*, ROWNUM _rn FROM (" + sql +
            ") _t WHERE ROWNUM <= " + (offset + limit) +
            ") WHERE _rn > " + offset;
        case "dm" -> sql + " LIMIT " + limit + " OFFSET " + offset;
        case "sqlserver" ->
            sql + " OFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
        default -> sql + " LIMIT " + limit + " OFFSET " + offset;
    };
}

@Component
public class ApiPipeline {

    @Autowired private AuthHandler authHandler;
    @Autowired private ParamValidator paramValidator;
    @Autowired private SqlDslParser sqlDslParser;
    @Autowired private DynamicRoutingDataSource routingDataSource;
    @Autowired private DataTransformEngine transformEngine;
    @Autowired private ResultPostProcessor resultPostProcessor;

    public R<?> execute(SqlApiConfig config, Map<String, Object> params) {
        // ① 鉴权
        authHandler.verify(config);

        // ② 参数校验
        paramValidator.validate(config, params);

        // ③ SQL 解析
        ParsedSql parsed = sqlDslParser.parse(config, params);

        // ④⑤ 数据源路由 + SQL 执行
        DataSourceContextHolder.set(config.getDatasourceName());
        List<Map<String, Object>> rawData;
        try {
            JdbcTemplate jdbc = sqlExecutor.getJdbcTemplate(config.getDatasourceName());
            rawData = jdbc.queryForList(parsed.getSql(), parsed.getArgs());
        } finally {
            DataSourceContextHolder.clear();
        }

        // ⑥ 数据转换
        List<Map<String, Object>> transformed = transformEngine
            .transform(config.getTransformConfig(), rawData);

        // ⑦ 结果包装
        return resultPostProcessor.wrap(config, transformed);
    }
}

@Component
public class SqlSafetyChecker {

    private static final Set<String> FORBIDDEN_KEYWORDS = Set.of(
        "DROP", "TRUNCATE", "ALTER", "CREATE", "GRANT", "REVOKE",
        "EXEC", "EXECUTE", "INTO OUTFILE", "LOAD_FILE"
    );

    public void check(String sql) {
        String upperSql = sql.toUpperCase();
        for (String keyword : FORBIDDEN_KEYWORDS) {
            if (upperSql.contains(keyword)) {
                throw new BusinessException("SQL 包含禁止的关键字: " + keyword);
            }
        }

        // 检查是否只读（SELECT 开头）
        if (!upperSql.trim().startsWith("SELECT")) {
            throw new BusinessException("仅支持 SELECT 查询");
        }
    }
}