使用SpringBoot实现一个执行通用SQL的Rest接口

使用SpringBoot实现一个执行通用SQL的Rest接口

接口定义(JSON格式)

输入参数

• traceId: 追踪sql执行的id, 可以是任意字符串
• dbName: 要查询的后台db名称
• queryFlag: 是否是查询sql, 还是 dml
• scalarQueryFlag: 是否是标量查询
• needTransactionFlag: 是否要显式放到一个事务中执行, 如果一次提交多个DML语句, 需要放到事务中执行
• sql: sql语句
• outputFieldNames: 输出字段名清单, 以逗号分隔, 如果没有设置输出字段名清单, 系统会自动推导
• parameters: 绑定变量数组
  • name: 单个绑定变量的名称
  • value: 单个绑定变量的取值

输出参数

• traceId: 返回输入参数中的traceId
• resultCode: 执行成功为0， 失败为非零值
• errorMessage: 报错信息
• scalarValue: 标量查询的结果值
• affectedRowCount: DML语句影响了多少行
• rows: 数组类型, 非标量查询的结果集, 数组中的每个元素对应一个查询结果行

实现关键技术

使用 NamedParameteredJdbcTemplate 执行SQL

使用 NamedParameteredJdbcTemplate 而不是常用的 JdbcTemplate, 因为它可以更容易灵活地为SQL传参. 尤其要将不定数量查询词参数传入的In clause, 不需要客户端拼sql, 只需要将一个数组传入即可.

自动推导结果记录行字段

可以通过 outputFieldNames 参数自行设定输出字段名, 也可以让程序自动推导, 两种实现方式：

1. 先作一次空结果查询, 将传入的sql作为 sub query where 1=0 这样的查询中, 使用Spring的ResultSetExtractor<ResultSetMetaData> 获取元数据集信息.
2. NamedParameterJdbcTemplate 执行查询后，可以直接通过 SqlRowSet 获取元数据，不需要预先执行一次 1=0 的查询。

SqlRowSet rowSet = jdbcTemplate.queryForRowSet(request.getSql(), paramSource);
// 直接从结果集中提取列名（元数据）, 因为结果 rowSet 是已断开连接的 SqlRowSet, 所以无法直接使用 BeanPropertyRowMapper 完成结果对象赋值, 下面使用 columnNames 完成赋值. 
SqlRowSetMetaData metaData = rowSet.getMetaData();
List<String> columnNames = Arrays.asList(metaData.getColumnNames());           
List<Object> results = mapRowSetToObjects(rowSet, dynamicClazz, columnNames);

private List<Object> mapRowSetToObjects(SqlRowSet rowSet, Class<?> clazz, List<String> columns) throws Exception {
    List<Object> list = new ArrayList<>();
    while (rowSet.next()) {
        Object obj = clazz.getDeclaredConstructor().newInstance();
        for (String col : columns) {
            Object value = rowSet.getObject(col);
            // 通过反射调用 setter，Javassist 生成的类包含 public 字段也可直接赋值
            Field field = clazz.getField(col);
            field.set(obj, value);
        }
        list.add(obj);
    }
    return list;
}

使用 javassist 类库动态生成结果记录行对象

因为是通用SQL执行， 结果集结构不能实现确定， 也就无法实现编写对应的结果Class类， 这里使用 javassist 类库, 动态创建一个结果行 Class。

• 类名构造： 为了确保Class名称唯一， 构建方式为 C_{Pid}_{Tid}_{CurrentTimeMills} ， 即包含进程Id和线程Id和距今的毫秒数。但这不是一个很好的设计，每次查询都创建一个类， 最终会导致JVM发生OOM， 更好的做法是根据 outputFieldNames MD5签名作为类名， 再结合 ClassLoader 来检查MD5摘要类是否存在。
• 类属性设定， 依据 outputFieldNames, 动态设定属性和它们的 Setter/Getter 方法。

import javassist.*;
import java.security.MessageDigest;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

public class DynamicClassGenerator {
    // 静态缓存，防止类重复创建导致 Metaspace OOM
    private static final Map<String, Class<?>> CLASS_CACHE = new ConcurrentHashMap<>();

    public static Class<?> getOrCreateClass(List<String> fields) throws Exception {
        // 1. 将字段排序并合并，生成唯一的 MD5 签名
        String fingerprint = generateMD5(fields.stream().sorted().collect(Collectors.joining(",")));
        String className = "com.gen.DynamicRow_" + fingerprint;

        // 2. 检查缓存
        if (CLASS_CACHE.containsKey(className)) {
            return CLASS_CACHE.get(className);
        }

        // 3. 同步块内二次检查并创建（DCL 模式）
        synchronized (CLASS_CACHE) {
            if (CLASS_CACHE.containsKey(className)) return CLASS_CACHE.get(className);

            ClassPool pool = ClassPool.getDefault();
            CtClass ctClass = pool.makeClass(className);

            for (String field : fields) {
                // 定义公共属性
                CtField ctField = new CtField(pool.get("java.lang.Object"), field, ctClass);
                ctField.setModifiers(Modifier.PUBLIC);
                ctClass.addField(ctField);
                
                // 生成标准的 Getter 和 Setter
                ctClass.addMethod(CtNewMethod.getter("get" + capitalize(field), ctField));
                ctClass.addMethod(CtNewMethod.setter("set" + capitalize(field), ctField));
            }
            
            Class<?> clazz = ctClass.toClass();
            CLASS_CACHE.put(className, clazz);
            return clazz;
        }
    }

    private static String generateMD5(String input) throws Exception {
        MessageDigest md = MessageDigest.getInstance("MD5");
        byte[] hashInBytes = md.digest(input.getBytes("UTF-8"));
        StringBuilder sb = new StringBuilder();
        for (byte b : hashInBytes) sb.append(String.format("%02x", b));
        return sb.toString();
    }

    private static String capitalize(String str) {
        return str.substring(0, 1).toUpperCase() + str.substring(1);
    }
}