Mybatis的动态SQL语句

If标签

根据条件查询
test是判断条件，其中多个条件用and不是&&

<select id="findUserByCondition" resultMap="userMap" parameterType="user">
    select * from user where 1=1
    <if test="userName != null">
      and username = #{userName}
    </if>
    <if test="userSex != null">
        and sex = #{userSex}
    </if>
</select>

where标签

代替where 1 = 1,where会自动将第一个条件的and删除

<select id="findUserByCondition" resultMap="userMap" parameterType="user">
    select * from user
    <where>
        <if test="userName != null">
            and username = #{userName}
        </if>
        <if test="userSex != null">
            and sex = #{userSex}
        </if>
    </where>
</select>

foreach标签

用于遍历，用于sql查询的in(……)
collection:代表要遍历的集合元素，注意编写时不要写#{}
open:代表语句的开始部分
close:代表结束部分
item:代表遍历集合的每个元素，生成的变量名
sperator:代表分隔符

#{uid}与item="uid"相同

<!-- 根据queryvo中的Id集合实现查询用户列表 -->
<select id="findUserInIds" resultMap="userMap" parameterType="queryvo">
    select * from user
    <where>
        <if test="ids != null and ids.size()>0">
            <foreach collection="ids" open="and id in (" item="uid" separator=","  close=")">
                #{uid}
            </foreach>
        </if>
    </where>
</select>

Sql标签

抽取重复的sql语句

<!-- 了解的内容：抽取重复的sql语句-->
<sql id="defaultUser">
    select * from user
</sql>
include 引入重复的sql
<!-- 根据queryvo中的Id集合实现查询用户列表 -->
<select id="findUserInIds" resultMap="userMap" parameterType="queryvo">
    <include refid="defaultUser"></include>
    <where>
        <if test="ids != null and ids.size()>0">
            <foreach collection="ids" open="and id in (" item="uid" separator="," >
                #{uid}
            </foreach>
        </if>
    </where>
</select>