MyBatis-动态SQL的if、choose、when、otherwise、trim、where、set、foreach使用

最新推荐文章于 2024-07-31 10:12:04 发布
最新推荐文章于 2024-07-31 10:12:04 发布
阅读量832 收藏
点赞数
分类专栏： 技术分析总结

动态SQL是MyBatis最强大的特性之一。用于实现动态SQL的主要元素如下： 
1、if 
2、choose、when、otherwise 
3、trim、where、set 
4、foreach

代码示例：

1、if

EmpMapper.xml配置

<code class="hljs vbnet has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> id=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"getEmpByIf"</span> resultType=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Emp"</span> parameterType=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Emp"</span>>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> * <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>
    <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> test=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"job != null and job != ''"</span>>
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> job = <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{job}</span>
    </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span>>
    <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> test=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"deptno != null "</span>>
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> deptno = <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{deptno}</span>
    </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span>>
</<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span>></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li></ul>

测试代码

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">getEmpByIf</span>() {
        SqlSession sqlSession = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;
        Emp emp = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> Emp();
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//工作为空字符串</span>
        emp.setJob(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">""</span>);
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//部门编号为10</span>
        emp.setDeptno(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>);
        List<Emp> empList = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ArrayList<>();
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            sqlSession = MyBatisUtil.createSqlSession();
            <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//EmpMapper接口中添加对应方法</span>
            empList = sqlSession.getMapper(EmpMapper.class).getEmpByIf(emp);
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (Exception e) {
            e.printStackTrace();
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">finally</span> {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">for</span> (Emp emp1 : empList) {
            logger.debug(emp1.getEmpno() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp1.getEname() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp1.getDeptno());
        }
    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li></ul>

输出结果

这里写图片描述

从结果可以看出因为job为空字符串所以条件没拼接,输出结果正确

2、choose、when、otherwise

类似于Java中的switch case default

EmpMapper.xml配置

<code class="hljs vbnet has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> id=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"getEmpByChoose"</span> resultType=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Emp"</span> parameterType=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Emp"</span>>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> * <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>
    <choose>
        <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span> test=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"job != null"</span>>
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> job = <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{job}</span>
        </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span>>
        <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span> test=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"deptno != null"</span>>
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> deptno = <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{deptno}</span>
        </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span>>
        <otherwise>
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> mgr = <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{mgr}</span>
        </otherwise>
    </choose>
</<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span>></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li></ul>

测试代码

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">getEmpByChoose</span>() {
        SqlSession sqlSession = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;
        Emp emp = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> Emp();
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//工作为CLERK</span>
        emp.setJob(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"CLERK"</span>);
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//部门编号为10</span>
        emp.setDeptno(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>);
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//经理为7698</span>
        emp.setMgr(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7698</span>);
        List<Emp> empList = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ArrayList<>();
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            sqlSession = MyBatisUtil.createSqlSession();
            <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//EmpMapper接口添加对应方法</span>
            empList = sqlSession.getMapper(EmpMapper.class).getEmpByChoose(emp);
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (Exception e) {
            e.printStackTrace();
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">finally</span> {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">for</span> (Emp emp1 : empList) {
            logger.debug(emp1.getEmpno() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp1.getEname() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp1.getDeptno());
        }
    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li></ul>

执行结果

这里写图片描述

可以看出,只有一个条件生效,也就是只执行满足的条件when,没有满足的条件就执行otherwise,表示默认条件。

3、where

EmpMapper.xml配置

<code class="hljs vbnet has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> id=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"getEmpByWhere"</span> resultType=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Emp"</span> parameterType=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Emp"</span>>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> * <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> emp
    <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span>>
        <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> test=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"job != null and job != ''"</span>>
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> job = <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{job}</span>
        </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span>>
        <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> test=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"deptno != null"</span>>
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> deptno = <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{deptno}</span>
        </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span>>
    </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span>>
</<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span>></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li></ul>

测试代码

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">getEmpByWhere</span>() {
        SqlSession sqlSession = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;
        Emp emp = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> Emp();
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//工作为CLERK</span>
        emp.setJob(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"CLERK"</span>);
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//部门编号为10</span>
        emp.setDeptno(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>);
        List<Emp> empList = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ArrayList<>();
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            sqlSession = MyBatisUtil.createSqlSession();
            <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//EmpMapper接口中添加对应方法</span>
            empList = sqlSession.getMapper(EmpMapper.class).getEmpByWhere(emp);
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (Exception e) {
            e.printStackTrace();
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">finally</span> {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">for</span> (Emp emp1 : empList) {
            logger.debug(emp1.getEmpno() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp1.getEname() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp1.getDeptno());
        }
    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li></ul>

执行结果

这里写图片描述

从配置的SQL语句及结果来看,where能在第一次满足添加条件时自动补全where这个单词,而且如果有and会替换掉,如果满足条件拼接的第二个语句没有and,会报错

4、set

EmpMapper.xml配置

<code class="hljs applescript has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><update <span class="hljs-property" style="box-sizing: border-box;">id</span>=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"updateEmpBySet"</span> parameterType=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Emp"</span>>
    update emp
    <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span>>
        <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> test=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"ename != null and ename != ''"</span>>
            ename = <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">#{ename},</span>
        </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span>>
        <<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> test=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"job != null and job != ''"</span>>
            job = <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">#{job},</span>
        </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span>>
    </<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span>>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> empno = <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">#{empno}</span>
</update></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li></ul>

测试代码

<code class="hljs avrasm has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    public void updateEmpBySet() {
        SqlSession sqlSession = null<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
        //要更新的Emp对象
        Emp emp = new Emp()<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
        emp<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.setEmpno</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2333</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
        emp<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.setEname</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"new name"</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
        emp<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.setJob</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"new job"</span>)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>

        try {
            sqlSession = MyBatisUtil<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.createSqlSession</span>()<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
            sqlSession<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.getMapper</span>(EmpMapper<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.class</span>)<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.updateEmpBySet</span>(emp)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
            sqlSession<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.commit</span>()<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
        } catch (Exception e) {
            e<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.printStackTrace</span>()<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
            sqlSession<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.rollback</span>()<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
        } finally {
            MyBatisUtil<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.closeSqlSession</span>(sqlSession)<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">;</span>
        }
    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li></ul>

执行结果

这里写图片描述

可以看出,set会在成功拼接的条件前加上SET单词且最后一个”,”号会被无视掉,但是有可能需要”,”的地方不能省略”,”否则异常。

5、trim

EmpMapper.xml配置

胡乱写了一段代码用来测试(●ˇ∀ˇ●)

这里写图片描述

直接上结果。。。

这里写图片描述

o(^▽^)o可以看到成功匹配掉了开头的$和末尾的*

6、foreach

foreach标签用于遍历生成单个或多个数据,根据传入的参数类型有俩种配置方式

1、参数为数组

这里写图片描述

EmpMapper接口添加方法

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> List<Emp> <span class="hljs-title" style="box-sizing: border-box;">getEmpByArray</span>(String[] deptnos);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

测试代码

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">getEmpByArray</span>() {
        SqlSession sqlSession = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;
        List<Emp> empList = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ArrayList<>();
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//参数为数组</span>
        String[] deptnos = {<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"10"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"20"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"30"</span>};
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            sqlSession = MyBatisUtil.createSqlSession();
            empList = sqlSession.getMapper(EmpMapper.class).getEmpByArray(deptnos);
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (Exception e) {
            e.printStackTrace();
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">finally</span> {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">for</span> (Emp emp : empList) {
            logger.debug(emp.getEmpno() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp.getEname() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp.getDeptno());
        }
    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li></ul>

结果

这里写图片描述

2、参数为List集合

这里写图片描述

EmpMapper接口添加方法

<code class="hljs lasso has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">List</span><span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;"><</span>Emp<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span> getEmpByList(<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">List</span><span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;"><</span><span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span><span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span> deptnos);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

测试代码

<code class="hljs php has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> void getEmpByList() {
        SqlSession sqlSession = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">List</span><Emp> empList = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ArrayList<>();
        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//参数为集合</span>
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">List</span><String> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">list</span> = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ArrayList<>();
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">list</span>.add(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"10"</span>);
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">try</span> {
            sqlSession = MyBatisUtil.createSqlSession();
            empList = sqlSession.getMapper(EmpMapper.class).getEmpByList(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">list</span>);
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">catch</span> (<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">Exception</span> e) {
            e.printStackTrace();
        } <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">finally</span> {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">for</span> (Emp emp : empList) {
            logger.debug(emp.getEmpno() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp.getEname() + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"-->"</span> + emp.getDeptno());
        }
    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li></ul>

结果

这里写图片描述

cleble
关注
专栏目录
MyBatis -- 动态SQL（if、trim、where、set、choose（where、otherwise）、foreach）
weixin_41781654的博客
11-06 406
一、动态SQL 1、基于OGNL表达式 2、完成多条件查询等逻辑实现 3、用于实现动态SQL的元素主要有 if trim where set choose（where、otherwise） foreach 二、if元素 （改造查询用户信息列表的演示示例） 根据用户角色（userRole）和用户姓名（模糊查询）进行查询 if做非空判断 where根据sql，自动添加where，根据条件动态添加/删除and、or mapper.xml文件 Test类 运行代码.
MyBatis学习笔记-07.MyBatis动态Sql语句if、where、trim、set
YuanMxy'Home
03-28 595
在实际的项目中，经常会遇到动态SQL的增、删、改、查问题，这里我们举一个简单的例子：在一个web工程中，我们以学生信息为例(表为 stundet)，经常会有一个搜索框，并且在搜索之前通常会进行一个关键词的过滤，比如可以过滤的条件有：姓名、性别等，如果当我们姓名和性别都不选，则等价于下面的SQL语句select * from student如果我们只填写姓名为"小元"，则等价于下面的SQL语句sel...
mybatisXML中标签＜choose＞＜when＞不生效问题，怎么解决
最新发布
weixin_60153382的博客
07-31 521
mybatisXML中标签不生效问题，怎么解决
MyBatis choose(when, otherwise)标签
wys0127的博客
07-25 1449
when元素表示当 when 中的条件满足的时候就输出其中的内容，跟 JAVA 中的 switch 效果差不多的是按照条件的顺序，当 when 中有条件满足的时候，就会跳出 choose，即所有的 when 和 otherwise 条件中，只有一个会输出，当所有的我很条件都不满足的时候就输出 otherwise 中的内容。所以上述语句的意思非常简单， 当 title!=null 的时候就输出 and content = #{content}，当所有条件都不满足的时候就输出 otherwise 中的内容。
mybatis动态SQL-choose-when-otherwise
小丁的博客
12-13 540
在多个分支条件中，仅执行一个。- 从上到下依次执行条件判断- 遇到的第一个满足条件的分支会被采纳- 被采纳分支后面的分支都将不被考虑- 如果所有的when分支都不满足，那么就执行otherwise分支
mybatis里的when和otherwise用法
雷大大的程序猿的博客
01-10 1万+
在where条件语句中进行判断，当type == x1 时和type == x2时where中的判断条件不同 解决 <select id = "" resultMap = ""> select * from table <where> <if test="type == 'x1' "> and 条件1...
动态SQL(if、where、trim、choose when otherwise、foreach、sql标签等)
weixin_46065214的博客
09-17 592
动态SQL(if、where、trim、choose when otherwise、foreach、sql标签等)
Mybatis中动态SQL，if,where,foreach的使用教程详解
08-28
在多个`if`条件中，如果使用`where`标签，MyBatis会自动移除第一个`AND`，确保SQL语句的正确性。比如，如果没有`where`标签，SQL可能会出现`AND`开头的情况，而有了`<where>`，就可以避免这种问题。 3. **foreach**...
mybatis-demo11-动态SQL语句.zip
08-05
在本项目"mybatis-demo11-动态SQL语句.zip"中，我们将深入探讨如何在XML配置文件中使用MyBatis的动态SQL功能。 1. **什么是动态SQL**： 动态SQL是MyBatis的核心功能之一，它允许我们在XML映射文件或Mapper接口中...
Mybatis动态SQL实战：if、choose、where、set、trim、foreach全解析
在MyBatis中，有几种主要的动态SQL标签，包括`if`、`choose`（`when`、`otherwise`）、`where`、`set`、`trim`和`foreach`。下面我们将逐一解析这些标签的用法和实例。 1. if 标签 `if`标签用于根据条件判断是否...
Mybatis使用choose when
小驴程序源
11-12 4394
Mybatis使用 例子： order by order by CODE_TAB_UUID ${item.direction} CODE ...
Mybatis---动态查询（choose,when,otherwise）
热门推荐
金炎
05-12 19万+
【第一个需求：按照固定的某一个条件进行查询。即，查询条件只能有一个】1.借用上文的工程，复制为Mybatis07，工程结构图不变。2.我们先移除UserInfo.java中的集合属性。如下：[java] view plain copy@SuppressWarnings("serial")  public class UserInfo implements Serializable {       ...
动态sql（if，choose， when，otherwise，where，set，foreach,trim）
Denial_learn的博客
10-30 1448
MyBatis的-一个强大的特性之-通常是它的动态SQL能力。提供了OGNL表达式动态生成SQL的功能。动态SQL有: if choose, when, otherwise where, set foreach 下面都以示例来进行说明动态SQL的用法！ if：进行判断 需求1：查询男性用户，如果输入了姓名，进行模糊查找，如果不输入就按男性用户来查询。 定义接口 /** *查询男性用户，如...
MyBatis 动态SQL之＜choose＞＜when＞＜otherwise＞标签-
yandao的博客
04-06 1万+
MyBatis 中动态语句 choose-when-otherwise 类似于 Java 中的 switch-case-default 语句。由于 MyBatis 并没有为 if 提供对应的 else 标签，如果想要达到…… 的效果，可以借助 、、 来实现。
mybatis使用＜choose＞ ＜when＞
qq_66986418的博客
03-20 1192
choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。 //如果除了以上条件外还有判断的条件,放在chose标签外，不用再写where。 //如果除了以上条件外还有判断的条件,放在chose标签外，不用再写where。所以需要在where条件语句中进行判断，当type == x1 时和type == x2时where中的判断条件不同。
mybatis中＜choose＞, ＜when＞ 标签和＜if＞标签
m0_46516427的博客
07-02 664
注意，在第二个示例中，我们使用了额外的 标签来检查是否所有条件都不满足（即用户名和邮箱都为空），这可能会使 SQL 映射文件变得稍微复杂一些。, 标签与 标签在 MyBatis 中都用于构建动态 SQL，但它们之间存在一些关键的区别和不同的使用场景。逻辑： 标签的逻辑是基于单个条件的，多个 标签可以在同一个 SQL 语句中独立使用，每个 结构： 标签是独立的，它可以包含自己的 test 属性来定义条件。
Mybatis 动态SQL – 使用choose标签动态生成条件语句
Mointor
09-05 1137
本篇主要介绍使用Mybatis提供的choose标签动态生成条件语句
mybatis choose when 多条件_Mybatis—动态SQL详解
weixin_39813574的博客
11-28 9661
前言今天我们来聊聊Mybatis的动态SQL的使用，动态SQL可以说是mybatis的核心，可以对SQL语句进行灵活操作，通过表达式进行判断，对SQL进行灵活拼接，组装。在实际项目开发中，我们还可以将在业务层处理的逻辑转移到SQL中进行处理，因为SQL语句通常会比程序代码执行的要快，毕竟数据库是专门做数据处理和计算的嘛。我们以 student_score 表为例来说明：if+where ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值