`
leitelyaya
  • 浏览: 69024 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

MyBatis中添加对exists子查询的支持

    博客分类：
  • Java
javamybatis 
阅读更多
2016-12-21 更新
已经加入GitHub托管: https://github.com/leitelyaya/mybatis-3 注意3.2.x
另外已修复ForEach污染全局变量的问题
实际使用效果嘛，临时表数据越少越好，如果不少，请放弃此优化
特感谢这篇博文讲解参数使用：http://blog.csdn.net/isea533/article/details/44002219

------------------------------------------------------------------------------------------------------------------------------------------
应用系统在设计多功能过滤时往往想到使用多表联结查询，如过滤字段增多，数据所关联的表增大时，数据量大，导致查询时间过长，本着exists优化多表的原则，为Mybatis提供一个方便的编码方式。

E.g. 订单查询列表页，不输入条件时查询所有，输入订单类条件时过滤订单，输入订单收货人、发票、商品信息时，过滤相关的订单
原始SQL： 
select * 
from order
left join receiver
using (xx)
left join invoice
using (xx)
left join goods
using (xx)
where 
order...= xx
and receiver... = xx
and invoice... = xx
and goods... = xx

用MyBatis则是： 
select * 
from order
left join receiver
using (xx)
left join invoice
using (xx)
left join goods
using (xx)
 ...
<where>
<if test="order...">
and order...= #{order.xx}
</if>
<if test="receiver..."
and receiver...= #{receiver.xx}
</if>
<if test="invoice..."
and invoice...= #{invoice.xx}
</if>
<if test="goods..."
and goods...= #{goods.xx}
</if>
</where>


理想模式下使用exists代替联结优化： 
select * 
from order
where 
order...= xx
and exists (
  select 1 from receiver where order.xx=receiver.xx
  and receiver... = xx
)
and exists (
  select 1 from invoice where order.xx=invoice.xx
  and invoice... = xx
)
and exists (
  select 1 from goods where order.xx=goods.xx
  and goods... = xx
)


MyBatis下写得就繁琐了： 
select * 
from order
<where> 
<if test="order...">
order...= xx
</if>
<if test="receiver.a!=null && receiver.b!=null && receiver.c!=null ...">
and exists (
  select 1 from receiver where order.xx=receiver.xx
  <if test="receiver.a!=null">
  and receiver.a = #{receiver.a}
  </if>
  <if test="receiver.b!=null">
  and receiver.b = #{receiver.b}
  </if>
  <if test="receiver.c!=null">
  and receiver.c = #{receiver.c}
  </if>
)
...
</where>

当查询条件多的时候，exists外层if的查询就难写了，无奈没有现成检测子查询下所有的if语句的结果返回是不符合的情况

理想处理如下： 
select * 
from order
<where> 
<if test="order...">
order...= xx
</if>
<match_any>
and exists (
  select 1 from receiver where order.xx=receiver.xx
  <if test="receiver...">
  and receiver... = xx
  </if>
  <if test="receiver...">
  and receiver... = xx
  </if>
  <if test="receiver...">
  and receiver... = xx
  </if>
)
</match_any>
...
</where>

自定义一个节点，判断上述情况

第一步，更新DTD，使得配置文件验证通过
第二步，配置新解析器，解析自定义节点

新的DTD，在项目资源目录下增加
org.apache.ibatis.builder.xml.mybatis-3-mapper.dtd
注意，项目资源获取优先级大于MyBatis包中的DTD，故而设定为同一个路径，同一个名字，会自动载入改好的文件 
<?xml version="1.0" encoding="UTF-8" ?>
<!--

       Copyright 2009-2016 the original author or authors.

       Licensed under the Apache License, Version 2.0 (the "License");
       you may not use this file except in compliance with the License.
       You may obtain a copy of the License at

          http://www.apache.org/licenses/LICENSE-2.0

       Unless required by applicable law or agreed to in writing, software
       distributed under the License is distributed on an "AS IS" BASIS,
       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
       See the License for the specific language governing permissions and
       limitations under the License.

-->
<!ELEMENT mapper (cache-ref | cache | resultMap* | parameterMap* | sql* | insert* | update* | delete* | select* )+>
<!ATTLIST mapper
        namespace CDATA #IMPLIED
        >

<!ELEMENT cache-ref EMPTY>
<!ATTLIST cache-ref
        namespace CDATA #REQUIRED
        >

<!ELEMENT cache (property*)>
<!ATTLIST cache
        type CDATA #IMPLIED
        eviction CDATA #IMPLIED
        flushInterval CDATA #IMPLIED
        size CDATA #IMPLIED
        readOnly CDATA #IMPLIED
        blocking CDATA #IMPLIED
        >

<!ELEMENT parameterMap (parameter+)?>
<!ATTLIST parameterMap
        id CDATA #REQUIRED
        type CDATA #REQUIRED
        >

<!ELEMENT parameter EMPTY>
<!ATTLIST parameter
        property CDATA #REQUIRED
        javaType CDATA #IMPLIED
        jdbcType CDATA #IMPLIED
        mode (IN | OUT | INOUT) #IMPLIED
        resultMap CDATA #IMPLIED
        scale CDATA #IMPLIED
        typeHandler CDATA #IMPLIED
        >

<!ELEMENT resultMap (constructor?,id*,result*,association*,collection*, discriminator?)>
<!ATTLIST resultMap
        id CDATA #REQUIRED
        type CDATA #REQUIRED
        extends CDATA #IMPLIED
        autoMapping (true|false) #IMPLIED
        >

<!ELEMENT constructor (idArg*,arg*)>

<!ELEMENT id EMPTY>
<!ATTLIST id
        property CDATA #IMPLIED
        javaType CDATA #IMPLIED
        column CDATA #IMPLIED
        jdbcType CDATA #IMPLIED
        typeHandler CDATA #IMPLIED
        >

<!ELEMENT result EMPTY>
<!ATTLIST result
        property CDATA #IMPLIED
        javaType CDATA #IMPLIED
        column CDATA #IMPLIED
        jdbcType CDATA #IMPLIED
        typeHandler CDATA #IMPLIED
        >

<!ELEMENT idArg EMPTY>
<!ATTLIST idArg
        javaType CDATA #IMPLIED
        column CDATA #IMPLIED
        jdbcType CDATA #IMPLIED
        typeHandler CDATA #IMPLIED
        select CDATA #IMPLIED
        resultMap CDATA #IMPLIED
        >

<!ELEMENT arg EMPTY>
<!ATTLIST arg
        javaType CDATA #IMPLIED
        column CDATA #IMPLIED
        jdbcType CDATA #IMPLIED
        typeHandler CDATA #IMPLIED
        select CDATA #IMPLIED
        resultMap CDATA #IMPLIED
        >

<!ELEMENT collection (constructor?,id*,result*,association*,collection*, discriminator?)>
<!ATTLIST collection
        property CDATA #REQUIRED
        column CDATA #IMPLIED
        javaType CDATA #IMPLIED
        ofType CDATA #IMPLIED
        jdbcType CDATA #IMPLIED
        select CDATA #IMPLIED
        resultMap CDATA #IMPLIED
        typeHandler CDATA #IMPLIED
        notNullColumn CDATA #IMPLIED
        columnPrefix CDATA #IMPLIED
        resultSet CDATA #IMPLIED
        foreignColumn CDATA #IMPLIED
        autoMapping (true|false) #IMPLIED
        fetchType (lazy|eager) #IMPLIED
        >

<!ELEMENT association (constructor?,id*,result*,association*,collection*, discriminator?)>
<!ATTLIST association
        property CDATA #REQUIRED
        column CDATA #IMPLIED
        javaType CDATA #IMPLIED
        jdbcType CDATA #IMPLIED
        select CDATA #IMPLIED
        resultMap CDATA #IMPLIED
        typeHandler CDATA #IMPLIED
        notNullColumn CDATA #IMPLIED
        columnPrefix CDATA #IMPLIED
        resultSet CDATA #IMPLIED
        foreignColumn CDATA #IMPLIED
        autoMapping (true|false) #IMPLIED
        fetchType (lazy|eager) #IMPLIED
        >

<!ELEMENT discriminator (case+)>
<!ATTLIST discriminator
        column CDATA #IMPLIED
        javaType CDATA #REQUIRED
        jdbcType CDATA #IMPLIED
        typeHandler CDATA #IMPLIED
        >

<!ELEMENT case (constructor?,id*,result*,association*,collection*, discriminator?)>
<!ATTLIST case
        value CDATA #REQUIRED
        resultMap CDATA #IMPLIED
        resultType CDATA #IMPLIED
        >

<!ELEMENT property EMPTY>
<!ATTLIST property
        name CDATA #REQUIRED
        value CDATA #REQUIRED
        >

<!ELEMENT typeAlias EMPTY>
<!ATTLIST typeAlias
        alias CDATA #REQUIRED
        type CDATA #REQUIRED
        >

<!ELEMENT select (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST select
        id CDATA #REQUIRED
        parameterMap CDATA #IMPLIED
        parameterType CDATA #IMPLIED
        resultMap CDATA #IMPLIED
        resultType CDATA #IMPLIED
        resultSetType (FORWARD_ONLY | SCROLL_INSENSITIVE | SCROLL_SENSITIVE) #IMPLIED
        statementType (STATEMENT|PREPARED|CALLABLE) #IMPLIED
        fetchSize CDATA #IMPLIED
        timeout CDATA #IMPLIED
        flushCache (true|false) #IMPLIED
        useCache (true|false) #IMPLIED
        databaseId CDATA #IMPLIED
        lang CDATA #IMPLIED
        resultOrdered (true|false) #IMPLIED
        resultSets CDATA #IMPLIED
        >

<!ELEMENT insert (#PCDATA | selectKey | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST insert
        id CDATA #REQUIRED
        parameterMap CDATA #IMPLIED
        parameterType CDATA #IMPLIED
        timeout CDATA #IMPLIED
        flushCache (true|false) #IMPLIED
        statementType (STATEMENT|PREPARED|CALLABLE) #IMPLIED
        keyProperty CDATA #IMPLIED
        useGeneratedKeys (true|false) #IMPLIED
        keyColumn CDATA #IMPLIED
        databaseId CDATA #IMPLIED
        lang CDATA #IMPLIED
        >

<!ELEMENT selectKey (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST selectKey
        resultType CDATA #IMPLIED
        statementType (STATEMENT|PREPARED|CALLABLE) #IMPLIED
        keyProperty CDATA #IMPLIED
        keyColumn CDATA #IMPLIED
        order (BEFORE|AFTER) #IMPLIED
        databaseId CDATA #IMPLIED
        >

<!ELEMENT update (#PCDATA | selectKey | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST update
        id CDATA #REQUIRED
        parameterMap CDATA #IMPLIED
        parameterType CDATA #IMPLIED
        timeout CDATA #IMPLIED
        flushCache (true|false) #IMPLIED
        statementType (STATEMENT|PREPARED|CALLABLE) #IMPLIED
        keyProperty CDATA #IMPLIED
        useGeneratedKeys (true|false) #IMPLIED
        keyColumn CDATA #IMPLIED
        databaseId CDATA #IMPLIED
        lang CDATA #IMPLIED
        >

<!ELEMENT delete (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST delete
        id CDATA #REQUIRED
        parameterMap CDATA #IMPLIED
        parameterType CDATA #IMPLIED
        timeout CDATA #IMPLIED
        flushCache (true|false) #IMPLIED
        statementType (STATEMENT|PREPARED|CALLABLE) #IMPLIED
        databaseId CDATA #IMPLIED
        lang CDATA #IMPLIED
        >

<!-- Dynamic -->

<!ELEMENT include (property+)?>
<!ATTLIST include
        refid CDATA #REQUIRED
        >

<!ELEMENT bind EMPTY>
<!ATTLIST bind
        name CDATA #REQUIRED
        value CDATA #REQUIRED
        >

<!ELEMENT sql (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST sql
        id CDATA #REQUIRED
        lang CDATA #IMPLIED
        databaseId CDATA #IMPLIED
        >

<!ELEMENT trim (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST trim
        prefix CDATA #IMPLIED
        prefixOverrides CDATA #IMPLIED
        suffix CDATA #IMPLIED
        suffixOverrides CDATA #IMPLIED
        >
<!ELEMENT where (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ELEMENT set (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>

<!ELEMENT foreach (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST foreach
        collection CDATA #REQUIRED
        item CDATA #IMPLIED
        index CDATA #IMPLIED
        open CDATA #IMPLIED
        close CDATA #IMPLIED
        separator CDATA #IMPLIED
        >

<!ELEMENT choose (when* , otherwise?)>
<!ELEMENT when (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST when
        test CDATA #REQUIRED
        >
<!ELEMENT otherwise (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>

<!ELEMENT if (#PCDATA | include | trim | where | set | foreach | choose | if | bind | match_any)*>
<!ATTLIST if
        test CDATA #REQUIRED
        >

<!ELEMENT match_any (#PCDATA | include | trim | where | set | foreach | choose | if | bind)*>


更改默认mapper语言解析器： 
<settings>
...
<setting name="defaultScriptingLanguage" value="extending"/>
</settings>
<typeAliases>
    <typeAlias type="org.apache.ibatis.scripting.ExtendingXMLLanguageDriver" alias="extending"/>
</typeAliases>


创建解析器：
org.apache.ibatis.scripting.ExtendingXMLLanguageDriver 
public class ExtendingXMLLanguageDriver extends XMLLanguageDriver implements LanguageDriver {

    @Override
    public SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType) {
        return new ExtendingXMLScriptBuilder(configuration, script, parameterType).parseScriptNode();
    }

}

org.apache.ibatis.scripting.xmltags.ExtendingXMLScriptBuilder 
public class ExtendingXMLScriptBuilder extends BaseBuilder {

    private XNode context;
    private boolean isDynamic;
    private Class<?> parameterType;

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

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

    public SqlSource parseScriptNode() {
        List<SqlNode> contents = parseDynamicTags(context);
        MixedSqlNode rootSqlNode = new MixedSqlNode(contents);
        SqlSource sqlSource = null;
        if (isDynamic) {
            sqlSource = new DynamicSqlSource(configuration, rootSqlNode);
        } else {
            sqlSource = new RawSqlSource(configuration, rootSqlNode, parameterType);
        }
        return sqlSource;
    }

    private List<SqlNode> parseDynamicTags(XNode node) {
        List<SqlNode> contents = new ArrayList<SqlNode>();
        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 = nodeHandlers.get(nodeName);
                if (handler == null) {
                    throw new BuilderException("Unknown element <" + nodeName + "> in SQL statement.");
                }
                handler.handleNode(child, contents);
                isDynamic = true;
            }
        }
        return contents;
    }

    private Map<String, NodeHandler> nodeHandlers = new HashMap<String, NodeHandler>() {
        private static final long serialVersionUID = 7123056019193266281L;

        {
            put("trim", new TrimHandler());
            put("where", new WhereHandler());
            put("set", new SetHandler());
            put("foreach", new ForEachHandler());
            put("if", new IfHandler());
            put("choose", new ChooseHandler());
            put("when", new IfHandler());
            put("otherwise", new OtherwiseHandler());
            put("bind", new BindHandler());
            put("match_any", new MatchAnyHandler());
        }
    };

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

    private class BindHandler implements NodeHandler {
        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 void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> contents = parseDynamicTags(nodeToHandle);
            MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
            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 void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> contents = parseDynamicTags(nodeToHandle);
            MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
            WhereSqlNode where = new WhereSqlNode(configuration, mixedSqlNode);
            targetContents.add(where);
        }
    }

    private class SetHandler implements NodeHandler {
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> contents = parseDynamicTags(nodeToHandle);
            MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
            SetSqlNode set = new SetSqlNode(configuration, mixedSqlNode);
            targetContents.add(set);
        }
    }

    private class ForEachHandler implements NodeHandler {
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> contents = parseDynamicTags(nodeToHandle);
            MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
            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 void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> contents = parseDynamicTags(nodeToHandle);
            MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
            String test = nodeToHandle.getStringAttribute("test");
            IfSqlNode ifSqlNode = new IfSqlNode(mixedSqlNode, test);
            targetContents.add(ifSqlNode);
        }
    }

    private class MatchAnyHandler implements NodeHandler {
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> contents = parseDynamicTags(nodeToHandle);
            List<SqlNode> dynamicContents = new ArrayList<>();
            for (SqlNode node : contents) {
                if (node instanceof IfSqlNode ||
                        node instanceof ChooseSqlNode ||
                        node instanceof ForEachHandler) {
                    dynamicContents.add(node);
                }
            }
            MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
            MixedSqlNode dynamicSqlNode = new MixedSqlNode(dynamicContents);
            MatchAnySqlNode matchAnySqlNode = new MatchAnySqlNode(configuration, mixedSqlNode, dynamicSqlNode);
            targetContents.add(matchAnySqlNode);
        }
    }

    private class OtherwiseHandler implements NodeHandler {
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> contents = parseDynamicTags(nodeToHandle);
            MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
            targetContents.add(mixedSqlNode);
        }
    }

    private class ChooseHandler implements NodeHandler {
        public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
            List<SqlNode> whenSqlNodes = new ArrayList<SqlNode>();
            List<SqlNode> otherwiseSqlNodes = new ArrayList<SqlNode>();
            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 = nodeHandlers.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;
        }
    }

}

添加match_any的标签解析器：
org.apache.ibatis.scripting.xmltags.MatchAnySqlNode 
public class MatchAnySqlNode implements SqlNode {
    private final Configuration configuration;
    private final SqlNode dynamicContents;
    private SqlNode contents;

    public MatchAnySqlNode(Configuration configuration, SqlNode contents, SqlNode dynamicContents) {
        this.contents = contents;
        this.dynamicContents = dynamicContents;
        this.configuration = configuration;
    }

    public boolean apply(DynamicContext context) {
        DynamicContext oldContext = context;

        context = new FilterContext(oldContext);
        dynamicContents.apply(context);
        String sqlS = context.getSql();

        context = new FilterContext(oldContext);
        contents.apply(context);
        String sqlN = context.getSql();

        context = oldContext;
        if (sqlS.trim().length() > 0) {
            context.appendSql(sqlN);
            return true;
        }

        return true;
    }

    private class FilterContext extends DynamicContext {
        private DynamicContext delegate;
        StringBuffer buffer;

        public FilterContext(DynamicContext delegate) {
            super(configuration, null);
            buffer = new StringBuffer();
            this.delegate = delegate;
        }

        @Override
        public Map<String, Object> getBindings() {
            return delegate.getBindings();
        }

        @Override
        public void bind(String name, Object value) {
            delegate.bind(name, value);
        }

        @Override
        public void appendSql(String sql) {
            buffer.append(sql);
        }

        @Override
        public String getSql() {
            return buffer.toString();
        }
    }
}

~_~ 简单粗暴搞定..
分享到：
| 改写PySQLPLUS
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    mysql insert if not exists防止插入重复记录的方法

    值得注意的是，如果你的表有复合唯一索引，你可以相应地调整`NOT EXISTS`子查询中的条件，以确保所有相关的字段组合都不重复。 此外，另一种方法是使用`INSERT IGNORE`或`ON DUPLICATE KEY UPDATE`语句，它们也是...

    面试整理.pdf

    尽量使用IN操作符的子查询替换EXISTS，反之亦然，因为EXISTS通常更高效。 - LIKE模糊查询：避免使用%%通配符，因为它会导致全表扫描。考虑使用全文搜索或正则表达式替代。 - 使用LIMIT和BETWEEN：在处理大量数据时...

    北大青鸟SQL第三章

    7. **子查询**：了解如何在SQL查询中嵌套查询，以及子查询在多表操作中的作用。学习使用IN、NOT IN、EXISTS、NOT EXISTS等子查询操作。 8. **视图**：创建和使用视图，理解视图作为虚拟表的功能，以及视图在数据...

    解析复杂结果集解析复杂结果集

    2. **查询重构**：避免在WHERE子句中使用子查询，改写为JOIN或 EXISTS，减少查询层次。 3. **合理使用聚合**：尽量减少不必要的全表扫描，通过WHERE过滤后再聚合，减少计算量。 4. **限制返回数据量**：使用LIMIT...

    mysql 记录不存在时插入 记录存在则更新的实现方法

    1. 使用`NOT EXISTS`子查询： 在插入数据前，先检查数据是否已存在。如果不存在，则执行插入操作。例如，假设我们有一个`clients`表，其中`client_id`是主键，我们可以使用如下SQL语句： ```sql INSERT INTO ...

    sqlserver分页的两种写法分别介绍

    在SQL Server 2005之前，或者不支持窗口函数的版本中，一种常用的分页方法是通过子查询和`NOT IN`或`NOT EXISTS`来实现。以下是一个使用存储过程的例子： ```sql CREATE PROC p_paged1 @pageSize INT, @current...

    Mysql动态更新数据库脚本的示例讲解

    - `EXISTS` 子句用于检查子查询返回的行数，如果至少有一行数据，那么`EXISTS`返回`TRUE`，否则返回`FALSE`。 - `NOT EXISTS` 是`EXISTS`的否定形式，当子查询不返回任何行时，它返回`TRUE`。 4. **数据库脚本的...

    面试总结2019年9月3日.doc

    in则是在主查询中查找符合子查询条件的行，可能涉及全表扫描。 8. **查询语句手写字段与使用*号的区别**：手写字段更精确，只获取所需数据，提高效率；*号会获取所有字段，可能导致不必要的资源消耗。 9. **何时...

    关于java开发的性能问题总结(必看)

    如果希望子事务的异常不回滚主事务，可以设置事务传播属性为`PROPAGATION_REQUIRES_NEW`，这样子事务会在新的事务中运行，即使子事务失败，也不会影响主事务。然而，这可能导致数据一致性问题，因此在设计时需要谨慎...

    中级程序员必备面试题.txt

    - **EXISTS**：检查子查询是否有行返回。 `EXISTS`通常更高效，因为它一旦找到匹配项就会停止执行。 #### 单例模式实现 - **懒汉式**：在首次使用时才创建实例。 - **饿汉式**：在类加载时就创建实例。 - **静态...

Magicbox
Global site tag (gtag.js) - Google Analytics