sqlNode用法

1 动态SQL

传统的使用JDBC的方法，相信大家在组合复杂的的SQL语句的时候，需要去拼接，稍不注意哪怕少了个空格，都会导致错误。Mybatis的动态SQL功能正是为了解决这种问题， 其通过 if, choose, when, otherwise, trim, where, set, foreach标签，可组合成非常灵活的SQL语句，从而提高开发人员的效率

• if sqlNode

<select id="findUserById" resultType="user">
    select * from user where 
        deleteFlag = 0
        <if test="id != null">
               AND id=#{id}
        </if>
  </select>

• where SqlNode

<select id="findUserById" resultType="user">
    select * from user 
        <where>
            <if test="id != null">
                id=#{id}
            </if>
        </where>
</select>

• trim sqlNode

<select id="findUserById" resultType="user">
    select * from user 
<trim prefix="WHERE" prefixOverrides="AND|OR">
       
            <if test="id != null">
              AND  id=#{id}
            </if>
      
</trim>
</select>

• set SqlNode

<update id="updateUser" parameterType="com.dy.entity.User">
    update user
    <trim prefix="SET" suffixOverrides=",">
       
            <if test="name != null">name = #{name},</if> 
            <if test="password != null">password = #{password},</if> 
            <if test="age != null">age = #{age},</if> 
      
    </trim>
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            and deleteFlag = 0;
        </where>
</update>

• foreach sqlNode

<select id="selectPostIn" resultType="domain.blog.Post">
    SELECT *
    FROM POST P
    WHERE ID in
    <foreach item="item" index="index" collection="list"
        open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

• choose sqlNode

<select id="findActiveBlogLike"
     resultType="Blog">
    SELECT * FROM BLOG WHERE state = ‘ACTIVE’
    <choose>
        <when test="title != null">
            AND title like #{title}
        </when>
        <when test="author != null and author.name != null">
            AND author_name like #{author.name}
        </when>
        <otherwise>
            AND featured = 1
        </otherwise>
    </choose>
</select>