Mybatis常用xml

工作中mybatis常用的xml代码

<?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="com.up.sell.mapper.system.AdvertisementMapper">

    <resultMap type="Advertisement" id="AdvertisementResult">
        <id property="id" column="id" />
        <result property="title" column="title" />
        <result property="imgPath" column="img_path" />
        <result property="url" column="url" />
        <result property="description" column="description" />
        <result property="sort" column="sort" />
        <result property="place" column="place" />
        <result property="provinceId" column="province_id" />
        <result property="cityId" column="city_id" />
        <result property="advFlag" column="adv_flag" />
        <result property="createUser" column="create_user" />
        <result property="createTime" column="create_time" />
        <result property="updateUser" column="update_user" />
        <result property="updateTime" column="update_time" />
        <association property="areas" column="id" javaType="com.up.sell.vo.system.Areas" resultMap="areasResult" />
        <association property="dictionary" column="id" javaType="com.up.sell.vo.system.Dictionary" resultMap="deptResult" />
    </resultMap> 

    <resultMap id="areasResult" type="Areas">
        <id property="id" column="id" />
        <result property="areaName" column="area_name" />
        <result property="parentId" column="parent_id" />
        <result property="shortName" column="short_name" />
    </resultMap>

    <resultMap id="deptResult" type="Dictionary">
        <id property="dKey" column="d_key" />
        <result property="dValue" column="d_value" />
        <result property="dName" column="d_name" />
        <result property="parentKey" column="parent_ey" />
        <result property="flag" column="flag" />
    </resultMap>

    <select id="findList" parameterType="Advertisement" resultMap="AdvertisementResult">
        SELECT
        a.id ,
        a.title ,
        a.img_path ,
        a.url ,
        a.description ,
        a.sort ,
        a.adv_flag,
        s1.short_name,
        d.d_name
        FROM
        advertisement a
        LEFT JOIN areas s1 on
        a.city_id = s1.id
        LEFT JOIN dictionary d ON d.parent_key = 111 AND
        a.place = d.d_value
    </select>

    <select id="findCity" parameterType="Advertisement" resultMap="AdvertisementResult">
        SELECT DISTINCT
        a.id ,
        a.short_name
        FROM
        sys_company s
        LEFT JOIN areas a
        ON(s.city_id = a.id)
        WHERE
        (s.id = #{arg0} OR s.parent_id = #{arg0})
        AND a.id IS NOT
        NULL
        AND a.short_name IS NOT NULL
    </select>

    <select id="findPlace" parameterType="Advertisement" resultMap="AdvertisementResult">
        SELECT
        d.d_value ,
        d_name
        FROM
        dictionary d
        WHERE
        d.parent_key = 111
        AND flag
        = 1
    </select>

    <insert id="inserts" parameterType="Advertisement"
        useGeneratedKeys="true" keyProperty="id">
        insert into advertisement(
        <if test="title != null and title != ''">title,</if>
        <if test="imgPath != null and imgPath != ''">img_path,</if>
        <if test="url != null and url != ''">url,</if>
        <if test="sort != null and sort != ''">sort,</if>
        <if test="place != null and place != ''">place,</if>
        <if test="provinceId != null and provinceId != ''">province_id,</if>
        <if test="cityId != null and cityId != ''">city_id,</if>
        <if test="createUser != null and createUser != ''">create_user,</if>
        create_time
        )values(
        <if test="title != null and title != ''">#{title},</if>
        <if test="imgPath != null and imgPath != ''">#{imgPath},</if>
        <if test="url != null and url != ''">#{url},</if>
        <if test="sort != null and sort != ''">#{sort},</if>
        <if test="place != null and place != ''">#{place},</if>
        <if test="provinceId != null and provinceId != ''">#{provinceId},</if>
        <if test="cityId != null and cityId != ''">#{cityId},</if>
        <if test="createUser != null and createUser != ''">#{createUser},</if>
        sysdate()
        )
    </insert>


    <select id="findSize" resultType="long">
        select count(1) from advertisement
    </select>


    <select id="selectStatus" resultType="Integer">
        select adv_flag from advertisement where id = #{arg0}
    </select>


    <select id="selectId" parameterType="Advertisement" resultMap="AdvertisementResult">
        select * from advertisement where id = #{arg0}
    </select>


    <update id="updates" parameterType="Advertisement">
        update advertisement
        <set>
            <if test="title != null and title != ''">title = #{title},</if>
            <if test="imgPath != null and imgPath != ''">img_path = #{imgPath},</if>
            <if test="url != null and url != ''">url = #{url},</if>
            <if test="description != null and description != ''">description = #{description},</if>
            <if test="sort != null and sort != 0">sort = #{sort},</if>
            <if test="place != null and place != 0">place = #{place},</if>
            <if test="cityId != null and cityId != ''">city_id = #{cityId},</if>
            <if test="updateUser != null and updateUser != 0">update_user = #{updateUser},</if>
            update_time = sysdate()
        </set>
        where id = #{id}
    </update>


    <select id="selectProvinceId" resultType="String">
        select parent_id from areas where id = #{arg0}
    </select>

    <select id="modifyStatus" resultType="Integer">
        select adv_flag from advertisement where id = #{arg0}
    </select>

    <update id="modifyOFF">
        update advertisement set adv_flag = 1 where id = #{arg0}
    </update>
    
    <update id="modifyNO">
        update advertisement set adv_flag = 0 where id = #{arg0}
    </update>
    
    <delete id="delete" parameterType="String">
        delete from advertisement where id = #{arg0}
    </delete>
    
    
</mapper>