动态查询：将mybatis查询sql存在数据库里，执行查询时调用

题主经手一个中台系统，大部分需求为查询数据，发版频繁。因为公司使用k8s，一个服务从提交代码到maven docker k8s发版一个工作流下来，至少要十来分钟，效率十分低下。因为多数为查询需求，经常就是写好sql后还得从requestDto controller service mapper responseDto 写一遍，非常浪费时间。

白衬衫猿是我弟弟

1128人浏览 · 2024-02-28 14:54:19

白衬衫猿是我弟弟 · 2024-02-28 14:54:19 发布

设计背景

题主经手一个中台业务查询系统，所有需求为查询数据，发版频繁。因为公司使用k8s，一个服务从提交代码到maven docker k8s发版一个工作流下来，至少要十来分钟，效率十分低下。
因为多数为查询需求，经常就是写好sql后还得从requestDto controller service mapper responseDto 写一遍，非常浪费时间。
故我和同事们寻求一种高效的开发模式，即将查询sql存储到一个表里，前端调用方法并传入对应的fun_code, 后台通过fun_code查询到对应的sql, 然后执行。

处理思路

因为查询业务基本上都是携带参数，后台根据参数动态生成查询sql, 我们项目之前用的orm框架是mybatis。我们便尝试借用mybatis的参数处理方式。

执行流程

select 
p.wwwww 
from xxxxxxx   P 
where 1=1
<if test="query.productRequestState != null and query.productRequestState != ''">
	and p.state=  #{query.productRequestState}
</if>
<if test="query.startTime != null and query.startTime != ''">
   and p.starttime <![CDATA[>=]]>  to_date( #{query.startTime},'yyyy-mm-dd hh24:mi:ss')
</if>

将上图所示的sql存到数据库中，给定一个funCode.
前端调用接口，传入方法编码funCode, 根据funCode从数据库中查询到sql
借用mybatis的标签处理器，根据传入的参数将等标签解析完，得到parseSql
将parseSql和查询参数作为传参再调用mybatis的select方法，查出对应的数据，返回给前端。

完整代码

controller

    /**
     * @since v3.5.9
     * @author 
     * @param dto 请求参数入参。使用map做为入参最为便捷，与前端约定必须传以下参数
     *            queryType--   查询类型(page or list or one)
     *            queryMethod-- 查询方法，根据此方法进行查询对应的sql
     * */
    @ApiOperation(value = "动态查询执行器")
    @PostMapping(value = "/executeDynamicQuery")
    public RestResponse<Object> executeDynamicQuery(@RequestBody HashMap<String, Object> dto){
        return RestResponse.ok(systemInformationService.executeDynamicQuery(dto));
    }

service

import org.apache.ibatis.builder.xml.XMLMapperEntityResolver;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.parsing.XPathParser;
import org.apache.ibatis.scripting.xmltags.XMLScriptBuilder;
import org.apache.ibatis.session.Configuration;

Constants
String QUERY_METHOD = "queryMethod";
String QUERY = "query";
String SQL= "sql";
String QUERY_TYPE_PAGE = "page";
String QUERY_TYPE_LIST = "list";
String QUERY_TYPE_ONE = "one";
String QUERY_TYPE = "queryType";
String PAGE_NO = "pageNo";
String LIMIT = "limit";

public Object executeDynamicQuery(HashMap<String, Object> dto) {
        String method = (String) dto.get(Constants.QUERY_METHOD);
        dto.put(Constants.QUERY, dto.clone());

        // 1. 获取myabtis xml格式的sql，根据入参的funCode获取
        String xmlSql = informationMapper.queryDynamicSqlByMethodCode(method);// 自己去根据定义的sql存储表去实现
        if (StringUtils.isEmpty(xmlSql)) {
            ExceptionUtil.error("未查询到当前动态sql模板！");
        }

        // 2. 调用mybatis的xml-sql解析方法，将<if><when><foreach>等mybaits标签解析替换
        String parseSql = praseMybatisTags(xmlSql, dto);
        log.info("================"+ parseSql);
        dto.put(Constants.SQL, parseSql);

        Object responseType = dto.get(Constants.QUERY_TYPE);

        // 3. 根据响应类型，返回不同格式的json结构
        if (Constants.QUERY_TYPE_PAGE.equals(responseType)) {
            Integer current = (Integer) dto.get(Constants.PAGE_NO);
            Integer size = (Integer) dto.get(Constants.LIMIT);
            Page<Map<String, Object>> pageParam = new Page<>(current, size);

            return informationMapper.executePageDynamicQuery(pageParam, dto);
        } else if (Constants.QUERY_TYPE_ONE.equals(responseType)) {
            return informationMapper.executeOneDynamicQuery(dto);
        } else {
            return informationMapper.executeListDynamicQuery(dto);
        }
    }
private String praseMybatisTags(String xmlSql, HashMap<String, Object> dto) {
        String sql = "<select>" + xmlSql + "</select>";
        // 实例化解析XML对象
        XPathParser parser = new XPathParser(sql, false, null, new XMLMapperEntityResolver());
        XNode context = parser.evalNode("/select");
        Configuration configuration = new Configuration();
        configuration.setDatabaseId("");
        WkXMLScriptBuilder xmlScriptBuilder = new WkXMLScriptBuilder(configuration, context);
        WkDynamicSqlSource sqlSource = xmlScriptBuilder.parseWkScriptNode();
        String WkParseSql = sqlSource.getWkParseSql(dto);
        return WkParseSql;
        // 获取转换xml标签后的sql对象
//        BoundSql bs = sqlSource.getBoundSql(dto);
        // 获取查询sql
//        return bs.getSql();
    }

WkXMLScriptBuilder
（在mybatis的XMLScriptBuilder类基础上进行微调，改成自己想要的结果）

import org.apache.ibatis.builder.BaseBuilder;
import org.apache.ibatis.builder.BuilderException;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.scripting.defaults.RawSqlSource;
import org.apache.ibatis.scripting.xmltags.*;
import org.apache.ibatis.session.Configuration;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: 
 * @date: 2022-10-28 14:03:02
 **/
public class WkXMLScriptBuilder  extends BaseBuilder {


    private final XNode context;
    private boolean isDynamic;
    private final Class<?> parameterType;
    private final Map<String, WkXMLScriptBuilder.NodeHandler> nodeHandlerMap = new HashMap<>();

    public WkXMLScriptBuilder(Configuration configuration, XNode context) {
        this(configuration, context, null);
    }

    public WkXMLScriptBuilder(Configuration configuration, XNode context, Class<?> parameterType) {
        super(configuration);
        this.context = context;
        this.parameterType = parameterType;
        initNodeHandlerMap();
    }


    private void initNodeHandlerMap() {
        nodeHandlerMap.put("trim", new TrimHandler());
        nodeHandlerMap.put("where", new WhereHandler());
        nodeHandlerMap.put("set", new SetHandler());
        nodeHandlerMap.put("foreach", new ForEachHandler());
        nodeHandlerMap.put("if", new IfHandler());
        nodeHandlerMap.put("choose", new ChooseHandler());
        nodeHandlerMap.put("when", new IfHandler());
        nodeHandlerMap.put("otherwise", new OtherwiseHandler());
        nodeHandlerMap.put("bind", new BindHandler());
    }

    public SqlSource parseScriptNode() {
        MixedSqlNode rootSqlNode = parseDynamicTags(context);
        SqlSource sqlSource;
        if (isDynamic) {
            sqlSource = new DynamicSqlSource(configuration, rootSqlNode);
            // 注释掉mybatis自带的，返回我们自己的sqlSource
//            sqlSource = new WkDynamicSqlSource(configuration, rootSqlNode);
        } else {
            sqlSource = new RawSqlSource(configuration, rootSqlNode, parameterType);
        }
        return sqlSource;
    }

    public WkDynamicSqlSource parseWkScriptNode() {
        // 注释掉mybatis自带的，返回我们自己的sqlSource
        MixedSqlNode rootSqlNode = parseDynamicTags(context);
        return new WkDynamicSqlSource(configuration, rootSqlNode);
//        SqlSource sqlSource;
//        if (isDynamic) {
            sqlSource = new DynamicSqlSource(configuration, rootSqlNode);
//            sqlSource = new WkDynamicSqlSource(configuration, rootSqlNode);
//        } else {
//            sqlSource = new RawSqlSource(configuration, rootSqlNode, parameterType);
//        }
//        return sqlSource;
    }

    protected MixedSqlNode parseDynamicTags(XNode node) {
        List<SqlNode> contents = new ArrayList<>();
        NodeList children = node.getNode().getChildNodes();
        for (int i = 0; i < children.getLength(); i++) {
            XNode child = node.newXNode(children.item(i));
            if (child.getNode().getNodeType() == Node.CDATA_SECTION_NODE || child.getNode().getNodeType() == Node.TEXT_NODE) {
                String data = child.getStringBody("");
                TextSqlNode textSqlNode = new TextSqlNode(data);
                if (textSqlNode.isDynamic()) {
                    contents.add(textSqlNode);
                    isDynamic = true;
                } else {
                    contents.add(new StaticTextSqlNode(data));
                }
            } else if (child.getNode().getNodeType() == Node.ELEMENT_NODE) { // issue #628
                String nodeName = child.getNode().getNodeName();
                NodeHandler handler = nodeHandlerMap.get(nodeName);
                if (handler == null) {
                    throw new BuilderException("Unknown element <" + nodeName + "> in SQL statement.");
                }
                handler.handleNode(child, contents);
                isDynamic = true;
            }
        }
        return new MixedSqlNode(contents);
    }

    private interface NodeHandler {
        void handleNode(XNode nodeToHandle, List<SqlNode> targetContents);
    }

    private class BindHandler implements NodeHandler {
        public BindHandler() {
            // Prevent Synthetic Access
        }

        @Override
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            final String name = nodeToHandle.getStringAttribute("name");
            final String expression = nodeToHandle.getStringAttribute("value");
            final VarDeclSqlNode node = new VarDeclSqlNode(name, expression);
            targetContents.add(node);
        }
    }

    private class TrimHandler implements NodeHandler {
        public TrimHandler() {
            // Prevent Synthetic Access
        }

        @Override
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            MixedSqlNode mixedSqlNode = parseDynamicTags(nodeToHandle);
            String prefix = nodeToHandle.getStringAttribute("prefix");
            String prefixOverrides = nodeToHandle.getStringAttribute("prefixOverrides");
            String suffix = nodeToHandle.getStringAttribute("suffix");
            String suffixOverrides = nodeToHandle.getStringAttribute("suffixOverrides");
            TrimSqlNode trim = new TrimSqlNode(configuration, mixedSqlNode, prefix, prefixOverrides, suffix, suffixOverrides);
            targetContents.add(trim);
        }
    }

    private class WhereHandler implements NodeHandler {
        public WhereHandler() {
            // Prevent Synthetic Access
        }

        @Override
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            MixedSqlNode mixedSqlNode = parseDynamicTags(nodeToHandle);
            WhereSqlNode where = new WhereSqlNode(configuration, mixedSqlNode);
            targetContents.add(where);
        }
    }

    private class SetHandler implements NodeHandler {
        public SetHandler() {
            // Prevent Synthetic Access
        }

        @Override
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            MixedSqlNode mixedSqlNode = parseDynamicTags(nodeToHandle);
            SetSqlNode set = new SetSqlNode(configuration, mixedSqlNode);
            targetContents.add(set);
        }
    }

    private class ForEachHandler implements NodeHandler {
        public ForEachHandler() {
            // Prevent Synthetic Access
        }

        @Override
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            MixedSqlNode mixedSqlNode = parseDynamicTags(nodeToHandle);
            String collection = nodeToHandle.getStringAttribute("collection");
            String item = nodeToHandle.getStringAttribute("item");
            String index = nodeToHandle.getStringAttribute("index");
            String open = nodeToHandle.getStringAttribute("open");
            String close = nodeToHandle.getStringAttribute("close");
            String separator = nodeToHandle.getStringAttribute("separator");
            ForEachSqlNode forEachSqlNode = new ForEachSqlNode(configuration, mixedSqlNode, collection, index, item, open, close, separator);
            targetContents.add(forEachSqlNode);
        }
    }

    private class IfHandler implements NodeHandler {
        public IfHandler() {
            // Prevent Synthetic Access
        }

        @Override
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            MixedSqlNode mixedSqlNode = parseDynamicTags(nodeToHandle);
            String test = nodeToHandle.getStringAttribute("test");
            IfSqlNode ifSqlNode = new IfSqlNode(mixedSqlNode, test);
            targetContents.add(ifSqlNode);
        }
    }

    private class OtherwiseHandler implements NodeHandler {
        public OtherwiseHandler() {
            // Prevent Synthetic Access
        }

        @Override
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            MixedSqlNode mixedSqlNode = parseDynamicTags(nodeToHandle);
            targetContents.add(mixedSqlNode);
        }
    }

    private class ChooseHandler implements NodeHandler {
        public ChooseHandler() {
            // Prevent Synthetic Access
        }

        @Override
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> whenSqlNodes = new ArrayList<>();
            List<SqlNode> otherwiseSqlNodes = new ArrayList<>();
            handleWhenOtherwiseNodes(nodeToHandle, whenSqlNodes, otherwiseSqlNodes);
            SqlNode defaultSqlNode = getDefaultSqlNode(otherwiseSqlNodes);
            ChooseSqlNode chooseSqlNode = new ChooseSqlNode(whenSqlNodes, defaultSqlNode);
            targetContents.add(chooseSqlNode);
        }

        private void handleWhenOtherwiseNodes(XNode chooseSqlNode, List<SqlNode> ifSqlNodes, List<SqlNode> defaultSqlNodes) {
            List<XNode> children = chooseSqlNode.getChildren();
            for (XNode child : children) {
                String nodeName = child.getNode().getNodeName();
                NodeHandler handler = nodeHandlerMap.get(nodeName);
                if (handler instanceof IfHandler) {
                    handler.handleNode(child, ifSqlNodes);
                } else if (handler instanceof OtherwiseHandler) {
                    handler.handleNode(child, defaultSqlNodes);
                }
            }
        }

        private SqlNode getDefaultSqlNode(List<SqlNode> defaultSqlNodes) {
            SqlNode defaultSqlNode = null;
            if (defaultSqlNodes.size() == 1) {
                defaultSqlNode = defaultSqlNodes.get(0);
            } else if (defaultSqlNodes.size() > 1) {
                throw new BuilderException("Too many default (otherwise) elements in choose statement.");
            }
            return defaultSqlNode;
        }
    }
}

WkDynamicSqlSource
（在mybatis的DynamicSqlSource类基础上进行微调，改成自己想要的结果）

import org.apache.ibatis.builder.SqlSourceBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.scripting.xmltags.DynamicContext;
import org.apache.ibatis.scripting.xmltags.SqlNode;
import org.apache.ibatis.session.Configuration;

public class WkDynamicSqlSource implements SqlSource {

    private final Configuration configuration;
    private final SqlNode rootSqlNode;

    public WkDynamicSqlSource(Configuration configuration, SqlNode rootSqlNode) {
        this.configuration = configuration;
        this.rootSqlNode = rootSqlNode;
    }

    @Override
    public BoundSql getBoundSql(Object parameterObject) {
        DynamicContext context = new DynamicContext(configuration, parameterObject);
        rootSqlNode.apply(context);
        // 执行完上面的方法，在这里可以直接获取到解析后带#{} ${}的sql
        SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
        Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();
        SqlSource sqlSource = sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings());
        // 执行完上面的方法，就会#{} ${} 给替换成jdbc的问号
        BoundSql boundSql = sqlSource.getBoundSql(parameterObject);
        context.getBindings().forEach(boundSql::setAdditionalParameter);
        return boundSql;
    }

    public String getWkParseSql(Object parameterObject) {
        DynamicContext context = new DynamicContext(configuration, parameterObject);
        rootSqlNode.apply(context);
        // 在这里可以直接获取到解析后带#{} ${}的sql
        return context.getSql();
//        SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
//        Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();
//        SqlSource sqlSource = sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings());
//        BoundSql boundSql = sqlSource.getBoundSql(parameterObject);
//        context.getBindings().forEach(boundSql::setAdditionalParameter);
//        return boundSql;
    }

}

mapper

@SuppressWarnings("MybatisXMapperMethodInspection")
    List<Map<String, Object>> executeListDynamicQuery(@Param("query") Map<String, Object> dto);

    @SuppressWarnings("MybatisXMapperMethodInspection")
    Map<String, Object> executeOneDynamicQuery(@Param("query") Map<String, Object> dto);

    @SuppressWarnings("MybatisXMapperMethodInspection")
    Page<Map<String, Object>> executePageDynamicQuery(Page page, @Param("query") Map<String, Object> dto);

mapper.xml

<select id="executePageDynamicQuery" parameterType="java.util.Map" resultType="java.util.Map">
        ${query.sql}
    </select>
    <select id="executeListDynamicQuery" resultType="java.util.Map">
        ${query.sql}
    </select>
    <select id="executeOneDynamicQuery" resultType="java.util.Map">
        ${query.sql}
    </select>

K8S/Kubernetes

K8S/Kubernetes社区为您提供最前沿的新闻资讯和知识内容

更多推荐

【深度】阿里巴巴万级规模 K8s 集群全局高可用体系之美

作者 | 韩堂、柘远、沉醉来源 | 阿里巴巴云原生公众号前言台湾作家林清玄在接受记者采访的时候，如此评价自己 30 多年写作生涯：“第一个十年我才华横溢，‘贼光闪现’，令周边黯然失色；第二个十年，我终于‘宝光现形’，不再去抢风头，反而与身边的美丽相得益彰；进入第三个十年，繁华落尽见真醇，我进入了‘醇光初现’的阶段，真正体味到了境界之美”。长夜有穷，真水无香。领略过了 K8s“身在江

K8S/Kubernetes

如何基于 K8s 构建下一代 DevOps 平台？

作者 | 孙健波（天元）导读：当前云原生 DevOps 体系现状如何？面临哪些挑战？如何通过 OAM 解决云原生 DevOps 场景下的诸多问题？云原生开发应用模型 OAM(Open Application Model) 社区核心成员孙健波将为大家一一解答，并分享如何基于 OAM 和 Kubernetes 打造无限能力的下一代 DevOps 平台。什么是 DevOps？为什么基于 Kub