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

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

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

前言:

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

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

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

步骤:

  1. 查询sql标签模板;
  2. 查询参数,执行sql 查询;
  3. 返回结果;

实现步骤:

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. 代码实现

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

    2. 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;}}
}

3. 使用方法

http://www.jmfq.cn/news/4744369.html

相关文章:

  • 深圳罗湖做网站公司哪家好/云seo关键词排名优化软件
  • 北京做网站公司有哪些/下载百度app最新版到桌面
  • 汽车网站建设代理加盟/长沙的seo网络公司
  • 网站推广品牌/北京优化网站方法
  • 个人网站公安局备案/网站搜索排优化怎么做
  • 网站的建设过程/seo线上培训多少钱
  • 有做火币网这种网站的吗/搜图片找原图
  • 那个网站能找到人/seo网站优化软件价格
  • 网站怎么被黑/网络营销手段
  • win xp 个人网站免费建设/网络营销有哪些推广方法
  • 电子商务网站设计规划书/服务器
  • 武汉网站建设索q.479185700/百度正版下载并安装
  • 成都网站建设网站建设哪家好/广州百度推广外包
  • 佛山制作网站公司推荐/最新网络营销方式有哪些
  • 网站两侧固定广告代码/宁德市人民政府
  • 石家庄制作网站的公司哪家好/百度知道下载安装
  • 咖啡网站设计模板/网站app免费生成软件
  • wordpress多媒体路径/百度搜索引擎优化
  • 做视频网站推广挣钱吗/网站怎么注册
  • 网站开发流程主要分成什么/爱站工具包官网
  • wordpress容易被黑吗/什么是网站seo
  • 电脑网站怎么创建到桌面上/百度seo教程视频
  • 最好看的免费观看视频/盐城seo排名
  • 中国能源建设集团有限公司怎么样/百度搜索优化建议
  • 北京哪里做网站/免费推广网站推荐
  • 个人建站如何赚钱/软文写作网站
  • 深圳网站建设公司小江/好口碑关键词优化地址
  • 政府网站建设 文件/安徽关键词seo
  • 大型商城网站建设方案/优化软件有哪些
  • 网站营销建设/免费一键搭建网站