Mybatis使用动态sql

动态sql

常见的几种:trim、where、set、foreach、if、choose、when

下面通过案例一一演示


 

 

if语法

1 <select id="selectIfTest1" resultType="cn.kgc.mybatisdemo.mode.User">
2         select id , userCode , userName from smbms.smbms_user where 1=1
3           <if test="userName !=null and userName != '' ">
4               and userName like concat('%',#{userName},'%')
5           </if>
6           <if test="roleId != 0">
7               and userRole = #{roleId}
8           </if>
9     </select>

if模块在判断通过后拼接模块内的代码

接下来是where代码

 1 <select id="selectIfTest2" resultType="cn.kgc.mybatisdemo.mode.User">
 2         select id , userCode , userName from smbms.smbms_user
 3         <where>
 4             <if test="userName != null and userName != ''">
 5                 and userName like concat('%',#{userName},'%')
 6             </if>
 7             <if test="roleId != 0">
 8                 and roleId = #{roleId}
 9             </if>
10         </where>
11     </select>

/*
where标签代替了where关键字,他除了能给添加关键字同时,
也可以智能的出去多余的and和or,where标签一般和if一起使用
当where标签中的if条件都不满足要求的时候,where标签不会拼接关键字
*/

set标签的使用

 1 <update id="updateUserTest3">
 2         update smbms.smbms_user
 3         <set>
 4             <if test="userName != null and userName != ''">
 5                 userName = #{userName},
 6             </if>
 7             <if test="userCode != null and userCode != ''">
 8                 userCode = #{userCode},
 9             </if>
10             <if test="userPassword != null and userPassword != ''">
11                 userPassword = #{userPassword},
12             </if>
13             <if test="modifyDate != null" >
14                 modifyDate = now(),
15             </if>
16         </set>
17</update>

 

 

 

 

/*
set标签 代替set关键字,可以智能的删除多余的逗号
其他内容和where一样,修改的时候必须要传值,否则会报错,这样就没意义了
*/

 

trim的使用

 

<update id="updateUserTest4">
        update smbms_user
        
        <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
                <if test="userName != null and userName != ''">
                    userName = #{userName},
                </if>
                <if test="userCode != null and userCode != ''">
                    userCode = #{userCode},
                </if>
                <if test="userPassword != null and userPassword != ''">
                    userPassword = #{userPassword},
                </if>
                <if test="modifyDate != null" >
                    modifyDate = now(),
                </if>
        </trim>
    </update>

 

 

 

 

/*
prefix添加前缀
prefixOverride清除前缀’
suffix添加后缀
suffixOverride清除后缀

 

trim 迄今为止最好用的标签
*/

foreach的三种形式

<select id="selectUserByForeachArray" resultType="cn.kgc.mybatisdemo.mode.User">
        select id,userName,userCode,userPassword from smbms.smbms_user
        where userRole in
        /*
        collection:当前的参数类型(必填)
        open:开始要添加的字符
        separator:分隔符
        close:结束时要添加的字符
        item:相当于变量
        */
        <foreach collection="array" open="(" separator="," close=")" item="roleId">
            #{roleId}
        </foreach>
    </select>

 

 

第二种

    <select id="selectUserByList" resultType="cn.kgc.mybatisdemo.mode.User">
        select id,userName,userCode,userPassword from smbms.smbms_user where userRole in
        <foreach collection="list" item="roleId" open="(" separator="," close=")">
            #{roleId}
        </foreach>
    </select>

 

第三种

 

    <select id="selectUserByMap" resultType="cn.kgc.mybatisdemo.mode.User">
      select id,userName,userCode,userPassword from smbms.smbms_user where userRole in
        /*
          这里的collection的值放的是Map集合的键
        */
      <foreach collection="roleId" open="(" separator="," close=")" item="roleId">
          #{roleId}
      </foreach>
        and gender = #{gender}
    </select>

 

 

 

choose的使用

 

    <select id="selectUserByChoose" resultType="cn.kgc.mybatisdemo.mode.User">
        select id,userName,userCode,userPassword from smbms.smbms_user
        /*
        choose类似于java中的switch when相当于case,只要走一个分支就不进入其他分支
        otherwise是默认,如果上面条件不满足就走它
        */
        <where>
            <choose>
                <when test="id != 0 and id != null">
                    id = #{id}
                </when>
                <when test="userName != null and userName != '' ">
                    userName like concat('%',#{userName},'%')
                </when>
                <when test="gender">
                    gender = #{gender}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

posted @ 2019-07-30 10:20  梦珑  阅读(170)  评论(0编辑  收藏  举报