mybatis动态sql

mybatis动态sql

动态SQL:if 语句

<select id="selectStudent" resultType="student" parameterType="cn.kgc.mybatis.pojo.Student">
     select * from student where  
     <if test="stu_name != null">
               stu_name=#{stu_name}
     </if>
    <if test="stu_sex!= null">
           and stu_sex=#{stu_sex}
    </if>
</select>

if+where 语句

<select id="selectStudent" resultType="student" parameterType="cn.kgc.mybatis.pojo.Student">
     select * from student 
        <where> 
                 <if test="stu_name != null">
                         stu_name=#{stu_name}
                 </if>
                 <if test="stu_sex!= null">
                         and stu_sex=#{stu_sex}
                 </if>
        </where> 
</select>

动态SQL:if+set 语句

<update id="updateStudentById" parameterType="cn.kgc.mybatis.pojo.Student">
    update student stu
        <set>
            <if test="stuname != null and stuname != ''">
                stu.stu_name = #{stuName},
            </if>
            <if test="stusex != null and stusex != ''">
                stu.stu_sex = #{stuSex}
            </if>
        </set>
     where id=#{id}
</update>

动态SQL:choose(when,otherwise) 语句

<select id="selectStudentByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
      select * from student
      <where>
          <choose>
              <when test="id !='' and id != null">
                  id=#{id}
              </when>
              <when test="username !='' and username != null">
                  and username=#{username}
              </when>
              <otherwise>
                  and sex=#{sex}
              </otherwise>
          </choose>
      </where>
 </select>

动态SQL:trim 语句
用 trim 改写上面的 if+where 语句

<select id="selectStudent" resultType="student" parameterType="cn.kgc.mybatis.pojo.Student">
     select * from user 
       <trim prefix="where" prefixOverrides=" and | or">
            <if test="stu_name != null">
                stu_name=#{stu_name}
             </if>
            <if test="stu_sex!= null">
                and stu_sex=#{stu_sex}
            </if>
       </trim>
</select>

• prefix：前缀
• prefixoverride：去掉第一个and或者是or　
  #用 trim 改写上面的 if+set 语句

<update id="updateStudentById" parameterType="cn.kgc.mybatis.pojo.Student">
    update student stu
       <trim prefix="set" suffixOverrides=",">
            <if test="stuname != null and stuname != ''">
                stu.stu_name = #{stuName},
            </if>
            <if test="stusex != null and stusex != ''">
                stu.stu_sex = #{stuSex}
            </if>
        </trim>
     where id=#{id}
</update>

动态SQL: foreach 语句

<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
    select * from user
    <where>
        <!--
            collection:指定输入对象中的集合属性
            item:每次遍历生成的对象
            open:开始遍历时的拼接字符串
            close:结束时拼接的字符串
            separator:遍历对象之间需要拼接的字符串
            select * from user where 1=1 and (id=1 or id=2 or id=3)
          -->
        <foreach collection="ids" item="id" separator="or">
            id=#{id}
        </foreach>
    </where>
</select>