Mybatis入门三----动态sql

           Mybatyis强大的功能之一就是可以方便的使用动态sql，以前使用jdbc来操作数据库的时候，能感受到那种在根据不同条件进行拼接sql语句带来的不方便。在Mybatis中利用关系标签就能很方便的解决这一问题。

           标签：

                   if、choose、where、set、trim、foreach

           实体类用员工（emp）和部门（dept）

           实体类中的属性名：

               Emp:empno、ename、job、sal、deptno

               Dept:deptno、dname

            <resultMap id="test1" type="Emp" autoMapping="true">
                   <association property="dept" column="deptno" javaType="Dept" autoMapping="true">
                   </association>
            </resultMap>

’          （1）if 标签：以查询为例子

                      <select id="quary1" resultMap="test1">

                              select e.*,d.dname from emp e,dept d where d.deptno=e.deptno

                              <if test="ename!=null and ename!=''">

                                        <!--test中写条件是否成立的判断条件-->

                                         and ename=#{ename}

                              </if>

                              <if test="sal!=null and sal>0">

                                         and sal=#{sal}

                              </if>

                     </select>

            （2）choose...when...otherwise标签：以查询为例子

                     <select id="quary2"  resultMap="test1">

                         select e.*,d.dname from emp e,dept d where e.deptno=d.deptno

                          <choose>

                                <!--只要一个when符合条件了，之后其余的when不管有没有符合条件都不再判断-->

                                 <when test="ename!=null and ename!=''">

                                        and ename=#{ename}

                                 </when>

                                 <when test="sal!=null and sal!=''">

                                          and sal=#{sal}

                                  </when>

                                   <otherwise>

                                             1=1

                                    </otherwise>

                              </choose>

                          </select>

           （3）where...if标签

                     <select id="quary3" resultType="Emp">

                               select * form emp

                               <where>

                                      <if test="ename!=null and ename!=''">

                                             and ename=#{ename}

                                       </if>

                                        <if test="sal!=null and sal>0">

                                              and sal=#{sal}

                                         </if>

                                </where>

                       </select>

            （4） set...if  标签：更新语句为例

                      <update id="update1" paramterType="Emp">

                               <!--paramterType可写可不写-->

                               update emp

                              <!--实现动态更新，有·值就更新-->

                                <set>

                                        <if test="ename!=null and ename!=''">

                                                  ename=#{ename}

                                         </if>

                                         <if test="sal!=null and sal>0">

                                                   sal=#{sal}

                                          </if>

                                         <if test="job!=null and job!=''">

                                                   job=#{job}

                                          </if>

                                 </set>

                                 where empno=#{empno}

                      </update>

             （5）trim(where\set)标签（对where标签和set标签的补充，功能和where\set标签一样）

                          <!--trim（where）标签测试 trim(where)相当于where标签-->
                          <select id="quary6" resultMap="test1">
                                  select * from emp
                                 <trim prefix="where" prefixOverrides="and |or">
                                        <!--prefixOverrides会将第一个符合的条件的and或or删除，and和“|”中间有一个空格-->
                    <if test="ename!=null and ename!=''">
                         and ename=#{ename}
                    </if>
                    <if test="sal!=null and sal>0">
                         or sal=#{sal}
                    </if>
                </trim>
            </select>

 

            <!--trim(set)标签测试 trim(set)相当于set标签-->
                       <update id="update2">
                               update emp
                               <trim prefix="set" suffixOverrides=",">
                                    <!--suffixOverrides会将最后一个符合的条件的“,”删除-->
                  <if test="ename!=null and ename!=''">
                        ename=#{ename},
                  </if>
                  <if test="sal!=null and sal>0">
                        sal=#{sal},
                  </if>
                  <if test="job!=null and job!=''">
                        job=#{job},
                  </if>
                  <if test="hirbate!=null and hirbate!=''">
                        hirbate=#{hirbate},
                  </if>
                  <if test="deptno!=null and deptno>0">
                        deptno=#{deptno}
                  </if>
              </trim>
               where empno=#{empno}
         </update>

            （6）foreach标签：批量操作

                     标签中的属性：collection:集合的类型，list或者array或者map

                                              index:集合的索引

                                              item:集合中元素的名称

                                              separator:循环体的分隔符，向循环体中添加的

                                              open:开始内容

                                              close:结束内容

                      <!--foreach标签测试-->
                     <!--批量插入-->
                <insert id="insert1" parameterType="Emp">
                  insert into emp(ename,job,hiredate,sal,deptno) values
                  <!--在批量插入中，使用“<foreach collection="list" index="index" item="item" open="(" separator="," close=")">”是不行的
                  -->
                  <foreach collection="list" index="index" item="item" separator=",">
                       (#{item.ename},#{item.job},#{item.hiredate},#{item.sal},#{item.deptno})
                  </foreach>
              </insert>
         <!--批量删除-->
             <delete id="delete" parameterType="Emp">
                  delete from emp where empno in
                 <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                      #{item.empno}
                 </foreach>
             </delete>
        <!--批量更新-->

             <!--批量更新要在全局配置文件中连接数据库的地方加上allowMultiQueries=true

                     <property name="url" value="jdbc:mysql://localhost:3306/hbb?allowMultiQueries=true"/>

                -->
            <update id="update3">
                <foreach collection="list" item="item">
                     update emp set job=#{item.job},sal=#{item.sal} where empno=#{item.empno};
                </foreach>
           </update>