TaoPanfeng's Blog

改造自己，总比禁止别人来得难。

SQL if标签判断

发表于 2022-12-05 | 分类于 ---代码备份 |

v1

// 2022-12-02 14:36 findTagList
public static void main(String[] args) throws Exception {
    String sqlTemplate = FileUtil.readUtf8String("C:\\Users\\TaoPanfeng\\AppData\\Roaming\\JetBrains\\IntelliJIdea2020.1\\scratches\\f2");

    ViewQry viewQry = ViewQry.of("")
            .putSingleQuote("tenant_id", "anta")
            .putSingleQuote("prodDivision", "服装")
            .putSingleQuote("c", "c");

    new Thread(() -> {
        {
            long start = System.currentTimeMillis();
            for (int i = 0; i < 100000; i++) {
                xxxxx(sqlTemplate, viewQry);
            }
            System.out.println("xxxxx：" + (System.currentTimeMillis() - start));
        }
    }).start();

    new Thread(() -> {
        {
            long start = System.currentTimeMillis();
            for (int i = 0; i < 100000; i++) {
                doMultiIfTag(sqlTemplate, viewQry);
            }
            System.out.println("doMultiIfTag：" + (System.currentTimeMillis() - start));
        }
    }).start();

}

/**
 * 在文本内容中查找标签列表
 *
 * @param tagName 标签名称。例如："if"
 * @param text    文本内容
 * @since 1.0.8
 */
private static List<String> findTagList(String tagName, String text) {
    LinkedList<String> tagList = new LinkedList<>();

    String startTag = "<" + tagName;// <if
    String endTag = StrUtil.format("</{}>", tagName);// </if>
    int startIndex = text.length(), endIndex;
    Set<Integer> endIndexSet = new TreeSet<>();
    while (true) {
        startIndex = text.lastIndexOf(startTag, startIndex - endTag.length());
        if (startIndex == -1) {
            break;
        }

        endIndex = text.indexOf(endTag, startIndex) + endTag.length();
        if (endIndexSet.contains(endIndex)) {
            Integer maxEndIndex = CollUtil.max(endIndexSet);
            endIndex = text.indexOf(endTag, maxEndIndex) + endTag.length();
        }
        endIndexSet.add(endIndex);

        String substring = text.substring(startIndex, endIndex);
        tagList.addFirst(substring);// 从内向外查，头插（根据 startIndex排序）
    }

    return tagList;
}

/**
 * 处理多个 if标签
 *
 * @param sqlTemplate sql模版
 * @param viewQry     视图查询对象（封装SQL变量）
 * @since 1.0.8      支持if标签嵌套
 */
private static String doMultiIfTag(String sqlTemplate, ViewQry viewQry) {
    List<String> ifTagList = findTagList("if", sqlTemplate);

    for (String ifTag : ifTagList) {
        if (sqlTemplate.contains(ifTag)) {
            String doIfTag = doSingleIfTag(ifTag, viewQry);
            sqlTemplate = sqlTemplate.replace(ifTag, doIfTag);
        }
    }
    return sqlTemplate;
}

/**
 * 处理单个 if标签（仅处理最外层的 if标签）
 *
 * @since 1.0.0
 * @since 1.0.4      if判断字符串（不单单只可以判空）
 */
private static String doSingleIfTag(String ifTag, ViewQry viewQry) {
    String ifReg = "<if test=\"([\\s\\S]+?)\">([\\s\\S]*)</if>";
    String test = ReUtil.findAll(ifReg, ifTag, 1).get(0).trim();// test 引号内
    String body = " " + ReUtil.findAll(ifReg, ifTag, 2).get(0).trim() + " ";// body 中的变量，在 buildSql 填充

    String expression = "if(test) return body;".replace("test", test);

    Dict mvelVars = Dict.create().set("body", body);

    // 找到 test 所有变量
    // sqlVariable => dict
    List<String> testVars = ReUtil.findAllGroup1("\\$(\\w+)\\$", test);
    testVars.forEach(e -> mvelVars.set("$" + e + "$", viewQry.getAdaptive(e)));

    // test通过，返回 if 标签体
    // test不通过，返回 null => ""
    String res = MVEL.eval(expression, mvelVars, String.class);// " and storeId in [$storeId$] "
    return StrUtil.blankToDefault(res, "");
}

void ____________________________________() {
}

public static String xxxxx(String sqlTemplate, ViewQry viewQry) {

    Document document = Jsoup.parse("<if test='true'>" + sqlTemplate + "</if>");

    Element rootElement = document.select("if").get(0);

    StringBuilder sb = new StringBuilder();
    test(rootElement, sb, viewQry);

    return sb.toString();
}

public static void test(Element element, StringBuilder sb, ViewQry viewQry) {
    String test = element.attr("test");
    if (!test(test, viewQry)) {
        return;
    }

    element.childNodes().forEach(childNode -> {
        if (childNode instanceof Element) {
            Element childElement = (Element) childNode;
            if ("if".equals(childElement.tagName())) {
                test(childElement, sb, viewQry);
            }
        } else if (childNode instanceof TextNode) {
            sb.append(childNode);
        }
    });
}

private static boolean test(String test, ViewQry viewQry) {
    // 找出 test 中所有变量名称
    // Map k=$变量名$ v=变量值
    Map<String, Object> vars = ReUtil.findAllGroup1("\\$(\\w+)\\$", test)
            .stream()
            .collect(Collectors.toMap(
                    e -> "$" + e + "$",
                    viewQry::getAdaptive,
                    (v1, v2) -> v1,
                    LinkedHashMap::new
            ));

    // test 表达式是否匹配
    return MVEL.eval(test, vars, Boolean.class);
}

v2

package datart.api.service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.lang.Dict;
import cn.hutool.core.lang.TypeReference;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.digest.DigestUtil;
import cn.hutool.http.HtmlUtil;
import cn.hutool.json.JSONConfig;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import datart.api.config.DatartProperties;
import datart.api.feign.dataobject.ViewDetailDTO;
import datart.api.service.custom.exception.DataProviderException;
import datart.api.service.custom.qry.SqlQry;
import datart.api.service.custom.qry.ViewQry;
import datart.api.service.custom.result.QueryResult;
import datart.api.service.custom.result.Row;
import datart.api.service.jdbc.JdbcDataProvider;
import lombok.extern.slf4j.Slf4j;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.nodes.TextNode;
import org.mvel2.MVEL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.stereotype.Component;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;
import java.util.function.Supplier;

/**
 * 数据供应商服务 => 实现
 *
 * @author 陶攀峰
 * @date 2022-05-25 19:42
 * @since 1.0.0
 */
@Component
@Slf4j
public class DataProviderServiceImpl implements DataProviderService {

    @Autowired
    private DatartProperties datartProperties;

    /**
     * @since 1.0.8     本地维护数据源
     */
    @Autowired
    private JdbcDataProvider jdbcDataProvider;

    /**
     * @since 1.0.1      Redis视图路径查询支持
     */
    @Autowired
    private StringRedisTemplate redis;

    // -------------------------base-------------------------------

    private Row one(String sourceId, String sql) {
        QueryResult queryResult = list(sourceId, sql);
        if (CollectionUtil.isEmpty(queryResult)) {
            throw new DataProviderException("selectOne 错误，查询为空。sourceId=[{}], sql=[{}]", sourceId, sql);
        }
        if (queryResult.size() != 1) {
            throw new DataProviderException("selectOne 错误，查询记录不唯一。sourceId=[{}], sql=[{}]", sourceId, sql);
        }

        return queryResult.get(0);
    }

    /**
     * @since 1.0.0 feign => datart => db（datart维护数据源）
     * @since 1.0.8 本地 => db（本地直连db，本地维护数据源）
     */
    private QueryResult list(String sourceId, String sql) {
        checkTenantId(sql);
        checkSqlVariable(sql);

        try {
            QueryResult queryResult = jdbcDataProvider.execute(sourceId, sql);

            printSql(queryResult.size(), sql);
            return queryResult;
        } catch (Exception e) {
            throw new DataProviderException(e, "取数出现异常：sourceId=[{}], sql=[{}]", sourceId, sql);
        }
    }

    /**
     * 检查 sql 中必须包含 tenant_id 字段
     */
    private void checkTenantId(String sql) {
        if (!sql.contains("tenant_id")) {
            throw new DataProviderException("sql 中不包含 'tenant_id' 字段，请检查 sql=[{}]", sql);
        }
    }

    /**
     * 检查 SQL 中是否有未替换的变量
     */
    private void checkSqlVariable(String sql) {
        List<String> variableList = ReUtil.findAllGroup1("(\\$\\w+\\$)", sql);

        if (CollectionUtil.isNotEmpty(variableList)) {
            throw new DataProviderException("存在未替换的变量：sql=[{}]", sql);
        }
    }

    /**
     * 打印 SQL
     *
     * @param sql 变量全部替换完成后的 SQL
     */
    private void printSql(int size, String sql) {
        if (isEnabledPrintSql()) {
            log.info("datart print sql ===========================> size=[{}], sql={}", size, sql);
        }
    }

    // -------------------------sql-------------------------------

    @Override
    public Row selectOne(SqlQry sqlQry) {
        return one(sqlQry.getSourceId(), sqlQry.getSql());
    }

    @Override
    public QueryResult selectList(SqlQry sqlQry) {
        return list(sqlQry.getSourceId(), sqlQry.getSql());
    }

    // -------------------------view-------------------------------

    @Override
    public Row selectOne(ViewQry viewQry) {
        ViewDetailDTO dto = viewDetailDTO(viewQry);

        return selectViewCache(dto, () -> one(dto.getSourceId(), dto.getScript()));
    }

    @Override
    public QueryResult selectList(ViewQry viewQry) {
        ViewDetailDTO dto = viewDetailDTO(viewQry);

        return selectViewCache(dto, () -> list(dto.getSourceId(), dto.getScript()));
    }

    /**
     * 查询视图缓存
     *
     * @param dto    视图详情 DTO
     * @param select datart feign 调用查询
     * @return QueryResult or Row
     * @since 1.0.3      查询视图缓存
     * @since 1.0.8      缓存序列化，反序列化：setOrder，setNullValue
     */
    public <T> T selectViewCache(ViewDetailDTO dto, Supplier<T> select) {
        String sourceId = dto.getSourceId();
        String sql = dto.getScript();
        JSONObject config = JSONUtil.parseObj(dto.getConfig());
        Boolean cache = config.getBool("cache");
        Long cacheExpires = config.getLong("cacheExpires");

        String md5Hex = DigestUtil.md5Hex(sourceId + sql);
        String redisKey = redisKeySelectViewCache(md5Hex);

        if (cache) {
            String hit = redis.opsForValue().get(redisKey);
            if (hit != null) {
                return jsonStr2Result(hit);
            }
        }

        T t = select.get();

        if (cache) {
            redis.opsForValue().set(redisKey, JSONUtil.toJsonStr(t, jsonConfig()), cacheExpires, TimeUnit.SECONDS);
        }

        return t;
    }

    /**
     * json配置：有序，不忽略 null值
     */
    private static JSONConfig jsonConfig() {
        return JSONConfig.create().setOrder(true).setIgnoreNullValue(false);
    }

    /**
     * JsonStr => QueryResult or Row
     *
     * @since 1.0.3      查询视图缓存
     */
    public static <T> T jsonStr2Result(String jsonStr) {

        if (JSONUtil.isJsonArray(jsonStr)) {
            return (T) JSONUtil.parse(jsonStr, jsonConfig()).toBean(new TypeReference<QueryResult>() {
            });
        }
        if (JSONUtil.isJsonObj(jsonStr)) {
            return (T) JSONUtil.parse(jsonStr, jsonConfig()).toBean(new TypeReference<Row>() {
            });
        }

        return null;
    }

    /**
     * 视图查询对象 => 视图详情 DTO
     */
    private ViewDetailDTO viewDetailDTO(ViewQry viewQry) {
        String viewName = viewQry.getViewName();

        // 0、检查视图名
        checkViewName(viewName);

        // 1、获取视图详情
        ViewDetailDTO dto = viewDetailDTO(viewName);

        // 2、构建 sql => set dto
        String sql = buildSql(dto.getScript(), viewQry);
        dto.setScript(sql);

        return dto;
    }

    /**
     * 检查视图名
     */
    private void checkViewName(String viewName) {
        Assert.notBlank(viewName, "viewName=[{}] 不能为空，请设置！！！", viewName);
        Assert.isTrue(viewName.startsWith("/"), "viewName=[{}] 必须以 '/' 开头！！！", viewName);
    }

    /**
     * 获取视图详情 DTO
     *
     * @param viewName 视图名。必须以 "/" 开头。
     * @return ViewDetailDTO（包含两个重要属性：sourceId=数据源id，sql=字符串SQL）
     * @since 1.0.1      Redis视图路径查询支持
     */
    private ViewDetailDTO viewDetailDTO(String viewName) {
        String redisKey = redisKeyView();
        Object redisValue = redis.opsForHash().get(redisKey, viewName);
        Assert.notNull(redisValue, "viewName=[{}] 视图查询无果", viewName);
        return JSONUtil.toBean(redisValue.toString(), ViewDetailDTO.class);
    }

    /**
     * 根据变量，填充 SQL
     *
     * @param sqlTemplate sql模版
     * @param viewQry     视图查询对象：获取视图变量
     * @return 被变量填充后的 sql
     */
    private static String buildSql(String sqlTemplate, ViewQry viewQry) {
        sqlTemplate = doIfTag(sqlTemplate, viewQry);

        sqlTemplate = sqlVariableReplace(sqlTemplate, viewQry);

        return sqlTemplate;
    }

    /**
     * if 标签判断
     *
     * @since 1.0.0
     * @since 1.0.4      if判断字符串（不单单只可以判空）
     */
    private static String ifTagCheck(String sqlTemplate, ViewQry viewQry) {
        String ifReg = "<if test=\"([\\s\\S]+?)\">([\\s\\S]+?)</if>";
        List<String> g0 = ReUtil.findAll(ifReg, sqlTemplate, 0);// 整体
        List<String> g1 = ReUtil.findAll(ifReg, sqlTemplate, 1);// test 引号内
        List<String> g2 = ReUtil.findAll(ifReg, sqlTemplate, 2);// if 标签体

        for (int i = 0; i < g0.size(); i++) {
            String find = g0.get(i);
            String test = " " + g1.get(i).trim() + " ";// 前后拼接空格，防止 SQL 粘连
            String body = " " + g2.get(i).trim() + " ";// body 中的变量，在 buildSql 填充

            String expression = "if(test) return body;".replace("test", test);

            Dict mvelVars = Dict.create().set("body", body);

            // 找到 test 所有变量
            // sqlVariable => dict
            List<String> testVars = ReUtil.findAllGroup1("\\$(\\w+)\\$", test);
            testVars.forEach(e -> mvelVars.set("$" + e + "$", viewQry.getAdaptive(e)));

            // test通过，返回 if 标签体
            // test不通过，返回 null => ""
            String res = MVEL.eval(expression, mvelVars, String.class);// " and storeId in [$storeId$] "
            if (StrUtil.isBlank(res)) {
                res = "";
            }
            sqlTemplate = sqlTemplate.replace(find, res);

        }
        return sqlTemplate;
    }

    /**
     * SQL 变量替换
     *
     * @since 1.0.0
     */
    private static String sqlVariableReplace(String sqlTemplate, ViewQry viewQry) {
        if (MapUtil.isEmpty(viewQry.getSqlVariable())) {
            return sqlTemplate;
        }

        // 找到所有的变量列表
        List<String> vars = ReUtil.findAllGroup1("\\$(\\w+)\\$", sqlTemplate);
        for (String var : vars) {
            String key = "$" + var + "$";
            Object value = viewQry.getSqlVariable(var);
            if (value == null) {
                throw new DataProviderException("找不到变量 变量名=[{}]：sql=[{}]", var, sqlTemplate);
            }

            sqlTemplate = sqlTemplate.replace(key, ViewQry.convertToString(value));
        }

        return sqlTemplate;
    }

    //------------------------------------

    private boolean isEnabledPrintSql() {
        return datartProperties.isEnabledPrintSql();
    }

    private String redisKeyView() {
        return datartProperties.redisKeyView();
    }

    private String redisKeySelectViewCache(String md5Hex) {
        return datartProperties.redisKeySelectViewCache(md5Hex);
    }

    /**
     * 处理 if标签，返回处理后的字符串
     *
     * @param sqlTemplate sql模版
     * @param viewQry     视图查询对象（封装SQL变量）
     * @since 1.0.0      支持if判空（null）
     * @since 1.0.4      支持if判断字符串（不单单只可以判空）
     * @since 1.0.8      支持if标签嵌套
     */
    public static String doIfTag(String sqlTemplate, ViewQry viewQry) {

        Document document = Jsoup.parse("<if test='true'>" + sqlTemplate + "</if>");

        Element rootElement = document.select("if").get(0);

        StringBuilder sb = new StringBuilder();
        doIfTag(rootElement, sb, viewQry);

        // 去除html转义，去除空格换行符，去除首尾空格
        return HtmlUtil.unescape(sb.toString()).replaceAll("\\s+", " ").trim();
    }

    /**
     * 递归处理 if标签
     *
     * @param element if标签
     * @param sb      用于拼接字符串
     * @param viewQry 视图查询变量
     */
    public static void doIfTag(Element element, StringBuilder sb, ViewQry viewQry) {
        String test = element.attr("test");
        if (!test(test, viewQry)) {
            return;
        }

        element.childNodes().forEach(childNode -> {
            if (childNode instanceof Element) {
                Element childElement = (Element) childNode;
                if ("if".equals(childElement.tagName())) {
                    doIfTag(childElement, sb, viewQry);
                }
            } else if (childNode instanceof TextNode) {
                sb.append(childNode);
            }
        });
    }

    /**
     * 判断 if标签中的 test表达式是否匹配
     *
     * @param test    if标签中的 test字符串表达式
     * @param viewQry 视图查询变量
     */
    private static boolean test(String test, ViewQry viewQry) {
        // 找出 test 中所有变量名称
        // Map k=$变量名$ v=变量值
        Map<String, Object> vars = new LinkedHashMap<>();
        ReUtil.findAllGroup1("\\$(\\w+)\\$", test)
                .stream()
                .distinct()
                .forEach(e -> {
                    vars.put("$" + e + "$", viewQry.getAdaptive(e));
                });

        // test 表达式是否匹配
        return MVEL.eval(test, vars, Boolean.class);
    }

}