Mybatis高级

动态SQL

if

<if test="条件">
    sql 语句
</if>
当条件成立的时候，会执行sql语句
if (条件){
	sql 语句
}

choose…when…otherwise…

<choose>
    <when test="条件1"> 
    	sql语句1
    </when>
    
    <when test="条件2"> 
    	sql语句2
    </when>
    
    <otherwise>
        sql语句3
    </otherwise>
</choose>

和我们java的if...else if ...else格式一样
当条件1成立，那么就不会执行后面的代码

where

<select id="queryUserByCondition" resultType="com.model.User" parameterType="com.model.User">
    select id,username,password,age,phone from user
    <where>
        <if test="age!=null and age!=''">
            and age=#{age}
        </if>
        <if test="phone!=null and phone!=''">
            and phone=#{phone}
        </if>
    </where>
</select>

set

<update id="updateUser" parameterType="com.model.User">
    update user
    <set>
        <if test="username!=null and username!=''">username=#{username},</if>
        <if test="password!=null and password!=''">password=#{password},</if>
    </set>
    where id=#{id}
</update>

foreach

<select id="queryUsersByIds" resultType="com.model.User"
        parameterType="java.lang.Integer">
    select id,username,password,age,phone from user
    <where>
        <foreach collection="list" item="id" open="id in (" close=")" separator=",">
            #{id}
        </foreach>
    </where>
</select>

trim

替换where
<select id="queryUserByCondition" resultType="com.model.User" parameterType="com.model.User">
    select id,username,password,age,phone from user
    <trim prefix="where" prefixOverrides="and">
        <if test="age!=null and age!=''">
            and age=#{age}
        </if>
        <if test="phone!=null and phone!=''">
            and phone=#{phone}
        </if>
    </trim>
</select>


替换set
<update id="updateUser" parameterType="com.model.User">
    update user
    <trim prefix="set" suffixOverrides=",">
        <if test="password!=null and password!=''">
            password=#{password},
        </if>
        <if test="age!=null and age!=''">
            age=#{age},
        </if>
    </trim>
    where id=#{id}
</update>

Sql片段

<sql id="别名">
    查询的所有字段
</sql>

使用的时候 <include refid="别名"/>