Mybatis————主要的动态SQL

where语句中如果空的条件就 省略，并且默认有 自动剔除第一个 and 或者or

  <select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
            select * from user 
            <where>
                <if test="username != null">
                   and username=#{username}
                </if>

                <if test="username != null">
                   and sex=#{sex}
                </if>
            </where> 
        </select>

多个查询条件 只要一个满足就好用choose

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
            select * from user 
            <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>

foreach

    需求：我们需要查询 user 表中 id 分别为1,2,3的用户

　sql语句：

    1、select * from user where id=1 or id=2 or id=3

        <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" open="and (" close=")" separator="or">
                    id=#{id}
                </foreach>
            </where>
        </select>

　2、select * from user where id in (1,2,3)

 <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 in (1,2,3)
                  -->
                <foreach collection="ids" item="id" open="and id in (" close=") " separator=",">
                    #{id}
                </foreach>
            </where>
        </select>