Mybatis动态SQL

2.if语句添加配置

 <insert id="doCreate" parameterType="News" keyProperty="nid" keyColumn="nid" useGeneratedKeys="true">
        INSERT INTO news(title,content) VALUES
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="title == null">
                ‘NOTitle‘,
            </if>
            <if test="title != null">
                #{title},
            </if>
            <if test="content == null">
                ‘NOContent‘,
            </if>
            <if test="content != null">
                #{content},
            </if>
        </trim>
    </insert>

3.if判断分页查询

<select id="findSplit" resultType="News" parameterType="java.util.Map">
        SELECT nid,title,content FROM news
        <if test="column != null and keyword != null and column != &quot;&quot; and keyword != &quot;&quot;">
            WHERE ${column} LIKE #{keyword}
        </if>
        LIMIT #{start},#{lineSize} ;
    </select>
    <select id="getAllCount" resultType="java.lang.Long" parameterType="java.util.Map">
        SELECT COUNT(*) FROM news
        <if test="column != null and keyword != null and column != &quot;&quot; and keyword != &quot;&quot;">
            WHERE ${column} LIKE #{keyword}
        </if>
    </select>

4.多次判断，if只能执行一次判断

    <select id="findAllCondition" resultType="News" parameterType="java.util.Map">
        SELECT nid,title FROM news
        <where>
            <choose>
                <when test="nid != null and title !=null and content !=null">
                    nid=#{nid} AND title=#{title} AND content=#{content}
                </when>
                <when test="nid != null and title !=null and content==null">
                    nid=#{nid} AND title=#{title}
                </when>
                <when test="nid != null and title ==null and content!=null">
                    nid=#{nid} AND content=#{content}
                </when>
            </choose>
        </where>
    </select>

5.set动态更新

    <update id="doEdit" parameterType="News">
        UPDATE news
        <set>
            <if test="title != null and title != &quot;&quot;">
                title=#{title},
            </if>
            <if test="content != null and content != &quot;&quot;">
                content=#{content},
            </if>
        </set>
        <where>
          <if test="nid != null and nid != 0">
              nid=#{nid}
          </if>
        </where>
    </update>

6.指定范围数据查询foreach

<select id="findByIds" resultType="News" parameterType="java.lang.Long">
        <include refid="selectBase"/>
        <where>
            nid IN
            <foreach collection="array" open="(" close=")" separator="," item="ele">
              #{ele}
            </foreach>
        </where>
    </select>

7.批量删除foreach

<delete id="doRemoveByIds" parameterType="java.lang.Long">
        DELETE FROM news
        <where>
            nid IN
            <foreach collection="array" open="(" close=")" separator="," item="ele">
                #{ele}
            </foreach>
        </where>
    </delete>

Mybatis动态SQL

标签：and   upd   动态sql   范围   ids   配置   lines   test   if判断