时间长不写代码，基础的xml都忘了，记录下，有时间再弄下oracle的

选择插入

<insert id="insertSelective" parameterType="com.ppx.mybatis.entity.User">
    insert into user
    <!--prefix:前缀
        suffix:尾缀
        suffixOverrides:尾缀覆盖(删除尾缀前','字符)-->
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="user.id != null and user.id != ''">
            id,
        </if>
        <if test="user.name != null and user.name != ''">
            name,
        </if>
        <if test="user.password != null and user.password != ''">
            password,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="user.id != null and user.id != ''">
            #{user.id},
        </if>
        <if test="user.name != null and user.name != ''">
            #{user.name},
        </if>
        <if test="user.password != null and user.password != ''">
            #{user.password},
        </if>
    </trim>
</insert>

批量插入

<insert id="batchSave" parameterType="java.util.List">
    insert into user (id,name,password) values
    <!--注意：不要open和close-->
    <if test="list != null and list.size() > 0">
        <foreach collection="list" separator="," item="o">
            (#{o.id},#{o.name},#{o.password})
        </foreach>
    </if>
</insert>

批量更新

<update id="batchUpdate" parameterType="java.util.List">
    <!--separator 用分号-->
    <if test="list != null and list.size() > 0">
        <foreach collection="list" separator=";" item="o">
            update user
            <set>
                <if test="o.name != null and o.name != ''">
                    name = #{o.name},
                </if>
                <if test="o.password != null and o.password != ''">
                    password = #{o.password}
                </if>
            </set>
            where id = #{o.id}
        </foreach>
    </if>
</update>

插入或更新

<insert id="saveOrUpdate" parameterType="com.ppx.mybatis.entity.User">
    insert into user (id,name,password) values (#{user.id},#{user.name},#{user.password})
    on duplicate key update
    <if test="user.name != null and user.name != ''">
        name = #{user.name},
    </if>
    <if test="user.password != null and user.password != ''">
        password = #{user.password}
    </if>
</insert>

分页

<select id="pageQuery" resultType="com.ppx.mybatis.entity.User">
    select * from user
    where 1=1
    <if test="req.id != null and req.id != ''">
        and id = #{req.id}
    </if>
    <if test="req.name != null and req.name != ''">
        name = #{req.name}
    </if>
    <if test="req.password != null and req.password != ''">
        password = #{req.password}
    </if>
    order by id asc
    <!--limit 不支持运算符-->
    <!--limit (#{req.pageNum} - 1) * #{req.pageSize},#{req.pageSize}-->
    limit #{req.pageNum},#{req.pageSize}
</select>
<select id="pageQueryCount" resultType="java.lang.Integer">
    select count(*) from user
    where 1=1
    <if test="req.id != null and req.id != ''">
        and id = #{req.id}
    </if>
    <if test="req.name != null and req.name != ''">
        name = #{req.name}
    </if>
    <if test="req.password != null and req.password != ''">
        password = #{req.password}
    </if>
    order by id asc
</select>