Mybatis动态SQL

Mybatis动态SQL

使用动态SQL可以简化代码的开发，减少开发者的工作量，程序可以自动根据业务参数来决定SQL的组成

• if标签

   <select id="findAll" resultType="com.southwind.entity.Account">
       select * from t_account
    </select>
    <select id="findById" parameterType="long" resultType="com.southwind.entity.Account">
        select * from t_account where id =#{id}
    </select>
    <select id="findByAccount" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
--         select * from t_account where id =#{id} and username = #{username } and password = #{password} and age= #{age}
    select * from t_account where
    <if test="id!=0" >
        id =#{id}
    </if>
<if test="username != null">
    username = #{username }
</if>
<if test="password !=null">
    password = #{password}
</if>
    <if test="age !=0">
        age= #{age}
    </if>
    </select>

if标签可以根据表达式的结果来决定是否将对应的语句添加到SQL中，如果条件不成立则不添加，如果条件成立则添加。

• where标签

where标签可以自动判断是否要删除语句块中的and关键字，如果检测到where和and拼接，则自动删除and，通常情况下if和where结合起来使用。

  <select id="findAll" resultType="com.southwind.entity.Account">
       select * from t_account
    </select>
    <select id="findById" parameterType="long" resultType="com.southwind.entity.Account">
        select * from t_account where id =#{id}
    </select>
    <select id="findByAccount" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
--         select * from t_account where id =#{id} and username = #{username } and password = #{password} and age= #{age}
    select * from t_account
        <where>
        <if test="id!=0" >
            id =#{id}
        </if>
        <if test="username != null">
            and username = #{username }
        </if>
        <if test="password !=null">
            and password = #{password}
        </if>
        <if test="age !=0">
            and  age= #{age}
        </if>

        </where>
        </select>

• choose,when标签

<select id="findByAccount" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
        select * from t_account  
        <choose>
            <when test="id!= 0">
                id =#{id}
            </when>
            <when test="username !=null">
                username = #{username }
            </when>
            <when test="password !=null">
                password = #{password}
            </when>
            <when test="age !=0">
                age= #{age}
            </when>
        </choose>
</select>

• trim标签

  trim标签中的prefix和suffix属性会被用于生成实际的SQL语句，会和标签内部的语句进行拼接，如果语句前后出现了prefixOverrides或者suffixOverrides属性中指定的值，Mybatis框架会自动将其删除。

    <select id="fiandByAccount" parameterType="com.southwind.entity.Account" resultType="com.southwind.entity.Account">
        select * from t_account
-- 下一行代码表示：where如果和and连接就把and删掉
        <trim prefix="where" prefixOverrides="and">
            <if test="id!=0" >
                id =#{id}
            </if>
            <if test="username != null">
                username = #{username }
            </if>
            <if test="password !=null">
                password = #{password}
            </if>
            <if test="age !=0">
                age= #{age}
            </if>

        </trim>
    </select>

• set标签

set标签用于update操作，会自动根据参数生成Sql语句

<update id="update" parameterType="com.southwind.entity.Account">
    update t_account
    <set>
        <if test="id!=0" >
            id =#{id}
        </if>
        <if test="username != null">
            username = #{username }
        </if>
        <if test="password !=null">
            password = #{password}
        </if>
        <if test="age !=0">
            age= #{age}
        </if>
    </set>

    where id =#{id}
</update>