动态sql查询

①动态sql标签和include标签（字段引用）        sql标签

<sql id=“a”></sql>

                将select后面字段替换成

<include ref=“a”></include>

②条件查询        if标签

<select id="selectIf" resultType="People">
        select  id,name from people
        where 1=1
        <if test="id!=null">
            and id = #{id}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
</select>

③条件判断        choose标签

 <select id="selectByChoose" resultType="People">
        select <include refid="colunms"></include>
        from people where 1=1
        <choose>
            <when test="id lt 4">
                and id &lt; 4
            </when>
            <when test="id eq 5">
                and id=5
            </when>
            <otherwise>
                and id>4
            </otherwise>
        </choose>
    </select>

④where标签    会自动去掉and开头或者去掉or开头

<select id="selectByWhere" resultType="People">
        select <include refid="colunms"></include>
        from people
        <where>
            <if test="id!=null">
                and id = #{id}
            </if>
            <if test="name!=null">
                and name = #{name}
            </if>
        </where>
</select>

⑤trim标签     加/删除 前后缀       trim内只能有一个if标签，加两个报错

<select id="selectTrim" resultType="People">
        select <include refid="colunms"></include>
        from people
        <where>
            <trim prefix="and" suffixOverrides=",">
                <if test="id!=null">
                     id = #{id},
                </if>
            </trim>

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

⑥set标签    update中set，会去掉最后一个逗号

<update id="updateSet">
        update people
        <set>
            <if test="id!=null">
                id=#{id},
            </if>
            <if test="name!=null">
                name=#{name}
            </if>
        </set>
        where id = #{id}
    </update>

⑦foreach标签    循环标签     批量新增    其中collection可以是mapper中接口的传递参数@Param，也可以是list和array。如果是对象的集合，item相当于集合中的对象，p.id是对象中的一个id属性。如果是Integer的集合，item相当于其中的id属性

<insert id="insertBatch">
        insert into people (id,name)
        <foreach collection="list" open="values(" close=")" item="p" separator="),(">
            #{p.id},#{p.name}
        </foreach>
</insert>



 <select id="selectByForEach" resultType="People">
        select <include refid="colunms"></include>
        from people where id in
        <foreach collection="ids" open="(" close=")" item="i" separator=",">
            #{i}
        </foreach>
 </select>

⑧bind标签   使用场景：模糊查询

<select id="selectByBind" resultType="People">
        <bind name="n" value="'%' + name + '%'"/>
        select <include refid="colunms"></include>
        from people where name like #{n}
</select>