SQL if标签判断 发表于 2022-12-05 | 分类于 ---代码备份 | v1123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158// 2022-12-02 14:36 findTagListpublic 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);} v2123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418package 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@Slf4jpublic 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); }}