mybatis动态sql

mybatis动态sql

if 和 where标签

<select id="getUserByConditions" resultType="com.lxc.test.entity.User">
        select * from user
        <where>
            <if test="user.id!=null and user.id!=0">
                and id=#{user.id}
            </if>

            <if test="user.userName!=null and user.userName!='' ">
                and user_name=#{user.userName}
            </if>

            <if test="user.birthday!=null">
                and birthday=#{user.birthday}
            </if>

            <if test="user.sex!=null and user.sex!='' ">
                and sex=#{user.sex}
            </if>

            <if test="user.address!=null and user.address!='' ">
                and address=#{user.address}
            </if>
        </where>
    </select>

1. if标签中test属性为真，标签包含的内容才会拼接到sql语句中，且test属性中不需要#{}直接写参数即可
2. 当where标签中有内容时，会自动生成where关键字，并且将内容前多余的and和or删除，不能将内容后and和or
3. 当where标签中没有内容时，where关键字会自动删除

trim标签

prefix/suffix: 将trim标签中内容前面或后面添加指定内容
prefixOverrides/suffixOverrides:将trim标签中内容前面或后面删除指定内容
当trim标签中没有内容时，trim标签也不会生效

<select id="getUserByConditions2" resultType="com.lxc.test.entity.User">
    select * from user
    <trim prefix="where" suffix="" prefixOverrides="and|or" suffixOverrides="and|or">
        <if test="user.id!=null and user.id!=0">
            and id=#{user.id}
        </if>

        <if test="user.userName!=null and user.userName!='' ">
            and user_name=#{user.userName}
        </if>

        <if test="user.birthday!=null">
            and birthday=#{user.birthday}
        </if>

        <if test="user.sex!=null and user.sex!='' ">
            and sex=#{user.sex}
        </if>

        <if test="user.address!=null and user.address!='' ">
            and address=#{user.address}
        </if>
    </trim>
</select>

choose when otherwise标签

<select id="getUserByConditionsChoose" resultType="com.lxc.test.entity.User">
select * from user
<where>
    <choose>
        <when test="user.id!=null and user.id!=0">
            id=#{user.id}
        </when>

        <when test="user.userName!=null and user.userName!='' ">
            user_name=#{user.userName}
        </when>

        <when test="user.birthday!=null">
            birthday=#{user.birthday}
        </when>

        <when test="user.sex!=null and user.sex!='' ">
            sex=#{user.sex}
        </when>

        <when test="user.address!=null and user.address!='' ">
            address=#{user.address}
        </when>
        <otherwise>
            id=41
        </otherwise>
    </choose>
</where>

foreach 标签 批量查询

collection: 设置需要循环的数组或集合
item: 表示数组或集合中的每一个元素
separator: 循环体之间的分隔符
open: foreach标签所循环的所有内容的开始符
close: foreach标签所循环的所有内容的结束符

<select id="getUserBatch" resultType="com.lxc.test.entity.User">
    select * from user where id in
    <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</select>

sql标签

<sql id="userColumns">
    id, user_name userName, birthday, sex, address
</sql>

 select <include refid="userColumns"></include> from user