当前位置：首页 > news >正文

蚌山网站建设/河南网站排名优化

news 2025/7/11 20:15:20

蚌山网站建设,河南网站排名优化,青岛高端网站开发公司,wordpress关注作者前言： 通过表动态存储mybatis 的xml标签，通过动态sql 入参查询，方便更新查询逻辑，无需发版即可；（当前用的是 mybatis-plus ，db用的是oracle【这个无所谓】） 注意事项：这…

前言：

通过表动态存储mybatis 的xml标签，通过动态sql 入参查询，方便更新查询逻辑，无需发版即可；（当前用的是 mybatis-plus ，db用的是oracle【这个无所谓】）

注意事项：这个只适用于简单的db查询拿数据，只要sql 能实现就行，局限性于简单的sql 拿db里面的数据！

倘若需要在代码里面复杂处理的逻辑，这个方法不适用!

步骤：

查询sql标签模板；
查询参数，执行sql 查询；
返回结果；

实现步骤：

1. 表结构设计：

存储mybatis的xml模板（oracle）

DROP TABLE "COM_DYNAMIC_QUERY";
CREATE TABLE "COM_DYNAMIC_QUERY" ("ID" VARCHAR2(64 BYTE) VISIBLE NOT NULL,"FUN_CODE" VARCHAR2(64 BYTE) VISIBLE,"DESCRIPTION" VARCHAR2(512 BYTE) VISIBLE,"SQL_TEMPLATE" VARCHAR2(4000 BYTE) VISIBLE,"EXEC_NUM" NUMBER(11,0) VISIBLE,"CREATE_BY" VARCHAR2(128 BYTE) VISIBLE,"CREATE_TIME" DATE VISIBLE DEFAULT SYSDATE,"UPDATE_BY" VARCHAR2(128 BYTE) VISIBLE,"UPDATE_TIME" DATE VISIBLE DEFAULT SYSDATE,"REMARK" VARCHAR2(256 BYTE) VISIBLE,"FUN_NAME" VARCHAR2(100 BYTE) VISIBLE
)
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1MAXEXTENTS 2147483645BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."ID" IS '主键ID';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."FUN_CODE" IS '功能编码';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."DESCRIPTION" IS '功能描述';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."SQL_TEMPLATE" IS '模板';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."EXEC_NUM" IS '执行次数';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."CREATE_BY" IS '创建人';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."UPDATE_BY" IS '更新人';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."REMARK" IS '备注';
COMMENT ON COLUMN "COM_DYNAMIC_QUERY"."FUN_NAME" IS '功能模块';
COMMENT ON TABLE "COM_DYNAMIC_QUERY" IS '可视化动态sql 查询';

某条标签模板的示例：（注意事项，查询的sql 用query 前缀开来）

SELECTp.machineName,p.portName,p.areaName,ms.MACHINEPROCESSTYPE,p.TRANSFERSTATE PORTSTATENAME,enum.DESCRIPTION AS machineProcessTypecn,subStr( p.LASTEVENTUSER  , 0, instr( p.LASTEVENTUSER  , ':' ) - 1 ) LASTEVENTUSERFROMport pINNER JOIN machinespec ms ON p.machinename = ms.machineNameLEFT JOIN ENUMDEFVALUE enum ON ms.MACHINEPROCESSTYPE = enum.ENUMVALUE AND enum.ENUMNAME = 'Machine_type' where 1=1<if test="query.detailMachineType != null and query.detailMachineType != ''">and ms.detailMachineType = #{query.detailMachineType}</if>order by p.machineName

2. 代码实现

controller 代码：

    @ApiOperation(value = "动态查询执行器")@PostMapping(value = "/executeDynamicQuery")public RestResponse<Object> executeDynamicQuery(@RequestBody HashMap<String, Object> dto){return RestResponse.ok(systemInformationService.executeDynamicQuery(dto));}

service 实现

// 主方法public Object executeDynamicQuery(HashMap<String, Object> dto) {String method = (String) dto.get(TworkConstants.QUERY_METHOD);dto.put(TworkConstants.QUERY, dto.clone());// 1. 获取标签xml，根据入参的funCodeString xmlSql = informationMapper.queryDynamicSqlByMethodCode(method);if (StringUtils.isEmpty(xmlSql)) {ExceptionUtil.error("未查询到当前动态sql模板！");}// 2. 调用mybatis的xml-sql解析方法，将<if><when><foreach>等 mybatis标签解析替换String parseSql = this.parseMybatisTags(xmlSql, dto);log.info("================"+ parseSql);dto.put(TworkConstants.SQL, parseSql);Object responseType = dto.get(TworkConstants.QUERY_TYPE);// 3. 根据响应类型，返回不同格式的json结构if (TworkConstants.QUERY_TYPE_PAGE.equals(responseType)) {Integer current = (Integer) dto.get(TworkConstants.PAGE_NO);Integer size = (Integer) dto.get(TworkConstants.LIMIT);Page<Map<String, Object>> pageParam = new Page<>(current, size);return informationMapper.executePageDynamicQuery(pageParam, dto);} else if (TworkConstants.QUERY_TYPE_ONE.equals(responseType)) {return informationMapper.executeOneDynamicQuery(dto);} else {return informationMapper.executeListDynamicQuery(dto);}}/*** 格式化标签模板的参数* @param xmlSql* @param dto* @return*/private String parseMybatisTags(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("");TworkXMLScriptBuilder xmlScriptBuilder = new TworkXMLScriptBuilder(configuration, context);TworkDynamicSqlSource sqlSource = xmlScriptBuilder.parseTworkScriptNode();return sqlSource.getTworkParseSql(dto);}

    /** 动态模板查询-常量值 **/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";

// 相关的查询 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><select id="queryDynamicSqlByMethodCode" resultType="java.lang.String">SELECT SQL_TEMPLATE FROM "COM_DYNAMIC_QUERY" where FUN_CODE = #{code}</select>

3. 2个类（TworkXMLScriptBuilder、TworkDynamicSqlSource）

package cn.xxx.xx.twork.controller.info;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 TworkDynamicSqlSource implements SqlSource {private final Configuration configuration;private final SqlNode rootSqlNode;public TworkDynamicSqlSource(Configuration configuration, SqlNode rootSqlNode) {this.configuration = configuration;this.rootSqlNode = rootSqlNode;}@Overridepublic BoundSql getBoundSql(Object parameterObject) {DynamicContext context = new DynamicContext(configuration, parameterObject);rootSqlNode.apply(context);// 执行完上面的方法，在这里可以直接获取到解析后带#{} ${}的sqlSqlSourceBuilder 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 getTworkParseSql(Object parameterObject) {DynamicContext context = new DynamicContext(configuration, parameterObject);rootSqlNode.apply(context);// 在这里可以直接获取到解析后带#{} ${}的sqlreturn context.getSql();}}

package cn.xxx.xx.twork.controller.info;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;public class TworkXMLScriptBuilder  extends BaseBuilder {private final XNode context;private boolean isDynamic;private final Class<?> parameterType;private final Map<String, TworkXMLScriptBuilder.NodeHandler> nodeHandlerMap = new HashMap<>();public TworkXMLScriptBuilder(Configuration configuration, XNode context) {this(configuration, context, null);}public TworkXMLScriptBuilder(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 TworkDynamicSqlSource(configuration, rootSqlNode);} else {sqlSource = new RawSqlSource(configuration, rootSqlNode, parameterType);}return sqlSource;}public TworkDynamicSqlSource parseTworkScriptNode() {// 注释掉mybatis自带的，返回我们自己的sqlSourceMixedSqlNode rootSqlNode = parseDynamicTags(context);return new TworkDynamicSqlSource(configuration, rootSqlNode);}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 #628String 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}@Overridepublic 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}@Overridepublic 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}@Overridepublic 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}@Overridepublic 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}@Overridepublic 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}@Overridepublic 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}@Overridepublic void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {MixedSqlNode mixedSqlNode = parseDynamicTags(nodeToHandle);targetContents.add(mixedSqlNode);}}private class ChooseHandler implements NodeHandler {public ChooseHandler() {// Prevent Synthetic Access}@Overridepublic 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;}}
}