Mybaits中Mapper文件技巧

附上一个mapper文件，自行体会。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace="ShopUser">
    <resultMap id="ShopUserMap" type="ShopUser">
        <id column="id" property="id"/>
        <result column="shop_id" property="shopId"/>
        <result column="shop_name" property="shopName"/>
        <result column="user_id" property="userId"/>
        <result column="mobile" property="mobile"/>
        <result column="user_name" property="userName"/>
        <result column="discount" property="discount"/>
        <result column="extra_json" property="extraJson"/>
        <result column="created_at" property="createdAt"/>
        <result column="updated_at" property="updatedAt"/>
    </resultMap>


    <sql id="tb">
        vega_shop_users
    </sql>

    <sql id="cols_all">
        id,
        <include refid="cols_exclude_id"/>
    </sql>

    <sql id="cols_exclude_id">
        shop_id, shop_name,mobile, user_id, user_name, discount, extra_json, created_at, updated_at
    </sql>

    <sql id="vals">
        #{shopId}, #{shopName},#{mobile}, #{userId}, #{userName}, #{discount}, #{extraJson}, now(), now()
    </sql>

    <sql id="queryCondition">
        where 1 = 1

        <if test="shopId != null">and `shop_id` = #{shopId}</if>

        <if test="shopName != null">and `shop_name` = #{shopName}</if>

        <if test="userId != null">and `user_id` = #{userId}</if>
        <if test="mobile != null">and `mobile` = #{mobile}</if>
        <if test="userName != null">and `user_name` = #{userName}</if>

        <if test="discount != null">and `discount` = #{discount}</if>

        <if test="extraJson != null">and `extra_json` = #{extraJson}</if>
        <if test="startAt != null">and created_at &gt; #{startAt}</if>
        <if test="endAt != null">and created_at &lt; #{endAt}</if>
    </sql>


    <sql id="criteria">
        <if test="id">and `id` = #{id}</if>
        <if test="name">and `name` = #{name}</if>
        <if test="email">and `email` = #{email}</if>
        <if test="mobile">and `mobile` = #{mobile}</if>
        <if test="status">and `status` = #{status}</if>
        <if test="type">and `type` = #{type}</if>
        <if test="createdFrom">AND `created_at` &gt;= #{createdFrom}</if>
        <if test="createdTo">AND `created_at` &lt; #{createdTo}</if>
        AND roles_json = "[\"BUYER\"]"
    </sql>


    <sql id="order">
        order by id desc
    </sql>

    <sql id="custom_sort">
        <if test="sortBy != null">
            <if test="sortBy == 'id'">ORDER BY id
                <include refid="custom_sort_type"/>
            </if>
            <if test="sortBy == 'updatedAt'">ORDER BY updated_at
                <include refid="custom_sort_type"/>
            </if>
        </if>
    </sql>
    <sql id="custom_sort_type">
        <if test="sortType != null">
            <if test="sortType == 1">ASC</if>
            <if test="sortType == 2">DESC</if>
        </if>
    </sql>

    <insert id="create" parameterType="ShopUser" keyProperty="id" useGeneratedKeys="true">
        INSERT INTO
        <include refid="tb"/>
        (<include refid="cols_exclude_id"/>)
        VALUES
        (<include refid="vals"/>)
    </insert>

    <select id="findById" parameterType="long" resultMap="ShopUserMap">
        SELECT
        <include refid="cols_all"/>
        FROM
        <include refid="tb"/>
        WHERE id = #{id}
    </select>

    <select id="findByMobile" parameterType="string" resultMap="ShopUserMap">
        SELECT
        <include refid="cols_all"/>
        FROM
        <include refid="tb"/>
        WHERE mobile = #{mobile}
    </select>


    <select id="findByMobileAndShopId" parameterType="map" resultMap="ShopUserMap">
        SELECT
        <include refid="cols_all"/>
        FROM
        <include refid="tb"/>
        WHERE mobile = #{mobile} AND shop_id=#{shopId}
    </select>

    <select id="findByUserId" parameterType="long" resultMap="ShopUserMap" >
        SELECT <include refid="cols_all" />
        FROM <include refid="tb" />
        WHERE user_id = #{userId}
    </select>

    <select id="findByIds" parameterType="list" resultMap="ShopUserMap">
        select id,
        <include refid="cols_exclude_id"/>
        from
        <include refid="tb"/>
        where id in
        <foreach collection="list" open="(" separator="," close=")"
                 item="item">
            #{item}
        </foreach>
        <include refid="order"/>
    </select>

    <update id="update" parameterType="ShopUser">
        UPDATE
        <include refid="tb"/>
        <set>
            <if test="shopId != null">shop_id = #{shopId},</if>
            <if test="shopName != null">shop_name = #{shopName},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="discount != null">discount = #{discount},</if>
            <if test="mobile != null">mobile = #{mobile},</if>
            <if test="extraJson != null">extra_json = #{extraJson},</if>


            updated_at=now()
        </set>
        WHERE id = #{id}
    </update>


    <update id="updateShopUserDiscount" parameterType="map">
        UPDATE
        <include refid="tb"/>
        <set>
            <if test="discount != null">discount = #{discount},</if>
            updated_at=now()
        </set>
        WHERE user_id = #{userId} AND shop_id=#{shopId}

    </update>


    <update id="refreshShopUserByUserId" parameterType="map">
        UPDATE
        <include refid="tb"/>
        <set>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="mobile != null">mobile = #{mobile},</if>
            updated_at=now()
        </set>
        WHERE user_id = #{userId}

    </update>


    <delete id="delete" parameterType="long">
        DELETE FROM
        <include refid="tb"/>
        WHERE id = #{id}
    </delete>

    <delete id="deleteByUserId" parameterType="long">
        DELETE FROM
        <include refid="tb"/>
        WHERE user_id = #{userId}
    </delete>

    <select id="paging" parameterType="list" resultMap="ShopUserMap">
        select id,
        <include refid="cols_exclude_id"/>
        from
        <include refid="tb"/>
        <include refid="queryCondition"/>
        order by id desc limit #{offset},#{limit}
    </select>

    <select id="count" parameterType="list" resultType="long">
        select count(1)
        from
        <include refid="tb"/>
        <include refid="queryCondition"/>
    </select>

</mapper>