mybatis 动态语法

//更新
<update id="updateJob" parameterType="SysJob">
        update sys_job
         <trim prefix="SET" suffixOverrides=",">
            <if test="jobName != null and jobName != ''">job_name = #{jobName},</if>
            <if test="jobGroup != null and jobGroup != ''">job_group = #{jobGroup},</if>
            <if test="invokeTarget != null and invokeTarget != ''">invoke_target = #{invokeTarget},</if>
            <if test="cronExpression != null and cronExpression != ''">cron_expression = #{cronExpression},</if>
            <if test="misfirePolicy != null and misfirePolicy != ''">misfire_policy = #{misfirePolicy},</if>
            <if test="concurrent != null and concurrent != ''">concurrent = #{concurrent},</if>
            <if test="status !=null">status = #{status},</if>
            <if test="remark != null and remark != ''">remark = #{remark},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            update_time = now()
          </trim>
        where job_id = #{jobId}
    </update>

//新增
<insert id="insertJob" parameterType="SysJob" useGeneratedKeys="true" keyProperty="jobId">
        insert into sys_job
     <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="jobId != null and jobId != 0">job_id,</if>
            <if test="jobName != null and jobName != ''">job_name,</if>
            <if test="jobGroup != null and jobGroup != ''">job_group,</if>
            <if test="invokeTarget != null and invokeTarget != ''">invoke_target,</if>
            <if test="cronExpression != null and cronExpression != ''">cron_expression,</if>
            <if test="misfirePolicy != null and misfirePolicy != ''">misfire_policy,</if>
            <if test="concurrent != null and concurrent != ''">concurrent,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="remark != null and remark != ''">remark,</if>
            <if test="createBy != null and createBy != ''">create_by,</if>
            create_time
    </trim>
     <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="jobId != null and jobId != 0">#{jobId},</if>
            <if test="jobName != null and jobName != ''">#{jobName},</if>
            <if test="jobGroup != null and jobGroup != ''">#{jobGroup},</if>
            <if test="invokeTarget != null and invokeTarget != ''">#{invokeTarget},</if>
            <if test="cronExpression != null and cronExpression != ''">#{cronExpression},</if>
            <if test="misfirePolicy != null and misfirePolicy != ''">#{misfirePolicy},</if>
            <if test="concurrent != null and concurrent != ''">#{concurrent},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="remark != null and remark != ''">#{remark},</if>
            <if test="createBy != null and createBy != ''">#{createBy},</if>
            now()
        </trim>
    </insert>

        //批量删除
 <delete id="deleteJobByIds" parameterType="Long">
    delete from sys_job where job_id in
    <foreach collection="array" item="jobId" open="(" separator="," close=")">
        #{jobId}
       </foreach>
 </delete>

     //批量更新
    <update id="updateDeptChildren" parameterType="java.util.List">
        update sys_dept set ancestors =
        <foreach collection="depts" item="item" index="index"
            separator=" " open="case dept_id" close="end">
            when #{item.deptId} then #{item.ancestors}
        </foreach>
        where dept_id in
        <foreach collection="depts" item="item" index="index"
            separator="," open="(" close=")">
            #{item.deptId}
        </foreach>
    </update>

    //批量新增
    <insert id="batchInsert">
        insert into ict_order_device ( order_id, device_id, device_type, device_name, 
device_price,device_num,device_ico, create_by, create_time )
        VALUES
        <foreach collection="list" item="item"  separator="," >
            (#{orderId},#{item.deviceId},#{item.deviceType},
            #{item.deviceName},#{item.devicePrice},#{item.deviceNum},
            #{item.deviceIco},#{userName}, sysDate())
        </foreach>
    </insert>

    <update id="deletePersonIds">
        update bus_key_person set del_flag = '1'
        WHERE person_id IN
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>