mapper.xml CRUD模板

<?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.tn.mdm.mdm.mapper.TnMdMaraFreezeTempMapper">

    <resultMap type="TnMdMaraFreezeTemp" id="TnMdMaraFreezeTempResult">
        <result property="taskId" column="task_id"/>
        <result property="syncFlag" column="sync_flag"/>
        <result property="matCode" column="mat_code"/>
        <result property="matDesc" column="mat_desc"/>
        <result property="matType" column="mat_type"/>
        <result property="freezeType" column="freeze_type"/>
        <result property="freezeScope" column="freeze_scope"/>
        <result property="factory" column="factory"/>
        <result property="reason" column="reason"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
    </resultMap>


    <sql id="selectTnMdMaraFreezeTempVo">
        select task_id,
               sync_flag,
               mat_code,
               mat_desc,
               mat_type,
               freeze_type,
               freeze_scope,
               factory,
               reason,
               create_by,
               create_time,
               update_by,
               update_time,
               remark
        from tn_md_mara_freeze_temp
    </sql>


	<!--查询list-->
    <select id="selectTnMdMaraFreezeTempList" parameterType="TnMdMaraFreezeTemp" resultMap="TnMdMaraFreezeTempResult">
        <include refid="selectTnMdMaraFreezeTempVo"/>
        <where>
            <if test="taskId != null  and taskId != ''">and task_id = #{taskId}</if>
            <if test="syncFlag != null  and syncFlag != ''">and sync_flag = #{syncFlag}</if>
            <if test="matCode != null  and matCode != ''">and mat_code = #{matCode}</if>
            <if test="matDesc != null  and matDesc != ''">and mat_desc = #{matDesc}</if>
            <if test="matType != null  and matType != ''">and mat_type = #{matType}</if>
            <if test="freezeType != null  and freezeType != ''">and freeze_type = #{freezeType}</if>
            <if test="freezeScope != null  and freezeScope != ''">and freeze_scope = #{freezeScope}</if>
            <if test="factory != null  and factory != ''">and factory = #{factory}</if>
            <if test="reason != null  and reason != ''">and reason = #{reason}</if>
        </where>
    </select>


	<!--条件查询list-->
    <select id="selectTnMdMaraFreezeTempByTaskId" parameterType="String" resultMap="TnMdMaraFreezeTempResult">
        <include refid="selectTnMdMaraFreezeTempVo"/>
        where task_id = #{taskId}
    </select>
    
    
	<!--条件查询list-->
    <select id="selectTnMdMaraFreezeTempByTaskIdAndCode" parameterType="String" resultMap="TnMdMaraFreezeTempResult">
        <include refid="selectTnMdMaraFreezeTempVo"/>
        <where>
            task_id = #{taskId} AND mat_code = #{matCode}
            <!--传过来参数为空 or 不为空，后面拼接不同的查询条件-->
            <if test="werks != null">AND factory = #{werks}</if>
            <if test="werks == null">AND factory IS NULL</if>
        </where>
    </select>
    
    
	<!--插入一条-->
    <insert id="insertTnMdMaraFreezeTemp" parameterType="TnMdMaraFreezeTemp">
        insert into tn_md_mara_freeze_temp
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="taskId != null and taskId != ''">task_id,</if>
            <if test="syncFlag != null">sync_flag,</if>
            <if test="matCode != null and matCode != ''">mat_code,</if>
            <if test="matDesc != null and matDesc != ''">mat_desc,</if>
            <if test="matType != null and matType != ''">mat_type,</if>
            <if test="freezeType != null and freezeType != ''">freeze_type,</if>
            <if test="freezeScope != null and freezeScope != ''">freeze_scope,</if>
            <if test="factory != null">factory,</if>
            <if test="reason != null and reason != ''">reason,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="taskId != null and taskId != ''">#{taskId},</if>
            <if test="syncFlag != null">#{syncFlag},</if>
            <if test="matCode != null and matCode != ''">#{matCode},</if>
            <if test="matDesc != null and matDesc != ''">#{matDesc},</if>
            <if test="matType != null and matType != ''">#{matType},</if>
            <if test="freezeType != null and freezeType != ''">#{freezeType},</if>
            <if test="freezeScope != null and freezeScope != ''">#{freezeScope},</if>
            <if test="factory != null">#{factory},</if>
            <if test="reason != null and reason != ''">#{reason},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
        </trim>
    </insert>
    
    
    <!--批量插入-->
    <insert id="batchTnMdMaraFreezeTemp">
        insert into tn_md_mara_freeze_temp(task_id, sync_flag, mat_code, mat_desc, mat_type, freeze_type, freeze_scope,
        factory, reason, create_by, create_time, update_by, update_time, remark) values
        <foreach item="item" index="index" collection="list" separator=",">
            ( #{item.taskId}, #{item.syncFlag}, #{item.matCode}, #{item.matDesc}, #{item.matType}, #{item.freezeType},
            #{item.freezeScope}, #{item.factory}, #{item.reason}, #{item.createBy}, #{item.createTime},
            #{item.updateBy}, #{item.updateTime}, #{item.remark})
        </foreach>
    </insert>
    
    
	<!--更新一条-->
    <update id="updateTnMdMaraFreezeTemp" parameterType="TnMdMaraFreezeTemp">
        update tn_md_mara_freeze_temp
        <trim prefix="SET" suffixOverrides=",">
            <if test="syncFlag != null">sync_flag = #{syncFlag},</if>
            <if test="matDesc != null and matDesc != ''">mat_desc = #{matDesc},</if>
            <if test="matType != null and matType != ''">mat_type = #{matType},</if>
            <if test="freezeType != null and freezeType != ''">freeze_type = #{freezeType},</if>
            <if test="freezeScope != null and freezeScope != ''">freeze_scope = #{freezeScope},</if>
            <if test="reason != null and reason != ''">reason = #{reason},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        <where>
            task_id = #{taskId}
            <if test="matCode != null and matCode != ''">and mat_code = #{matCode}</if>
            <if test="factory != null ">and factory = #{factory}</if>
            <if test="factory == null ">and factory is NULL</if>
        </where>
    </update>


	<!--条件删除-->
    <delete id="deleteTnMdMaraFreezeTempByTaskId" parameterType="String">
        delete
        from tn_md_mara_freeze_temp
        where task_id = #{taskId}
    </delete>


	<!--条件批量删除-->
    <delete id="deleteTnMdMaraFreezeTempByTaskIds" parameterType="String">
        delete from tn_md_mara_freeze_temp where task_id in
        <foreach item="taskId" collection="array" open="(" separator="," close=")">
            #{taskId}
        </foreach>
    </delete>


    <!--保存接口，第二次保存前，如果页面上删了了一条，就要先把这条删除了，然后重新保存-->
    <delete id="deleteTnMdTempsByTaskIdNotInIds" parameterType="String">
        delete from tn_md_mara_temp where task_id = #{taskId}
        AND id not in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>


</mapper>