项目积累

批量新增

    insert into rrmp_role_company_table(table_code,company_code,role_id,status,lessee_id,created_date,updated_date,created_by,updated_by)

        values

    <foreach collection="codes" item="item" index="index" separator="," >

        (#{item},#{companyCode},#{roleId},1,#{lesseeId},now(),now(),#{operUm},#{operUm})

    </foreach>

</insert>



批量更新

<update id="updateStatus" parameterType="com.paic.rrmp.mdm.roleTable.dto.RoleTableDto">

update rrmp_role_company_table

set status =#{status}, updated_by=#{operUm},updated_date = now()

where id

in

<foreach collection="ids" index="index" item="item"

separator="," open="(" close=")">

#{item}

</foreach>

</update>

递归查询 父任务下的子任务

<select id="selectAllByTaskDtoOLD" parameterType="com.paic.rrmp.dto.task.TaskListDto" resultMap="BaseResultMap">

with RECURSIVE rrmp_temp AS (

    SELECT id,task_name ,company_code,company_name,report_code,frequency,period,task_trigger_date,fill_date,check_date

            ,report_date,parent_task_id,final_man_um,final_man_name,wheel_man_um,wheel_man_name,report_man_um,report_man_name,repeat_man_um

            ,repeat_man_name,report_type,report_bore,status,task_status,task_run_date,lessee_id,created_by,updated_by,created_date

            FROM

            rrmp_task_manage

        <where>

        <if test="wheelManUm != null and wheelManUm != ''">

            or wheel_man_um = #{wheelManUm,jdbcType=VARCHAR}

        </if>

       <if test="finalManUm != null and finalManUm != ''">

            or final_man_um = #{finalManUm,jdbcType=VARCHAR}

        </if>

        <if test="reportManUm != null and reportManUm != ''">

        <bind name="reportManUm" value ="'%'+reportManUm+'%'" />

            or report_man_um  like #{reportManUm,jdbcType=VARCHAR}

        </if>

        <if test="keywords != null and keywords != ''">

        <bind name="keywords" value ="'%'+keywords+'%'" />

            and task_name like #{keywords,jdbcType=VARCHAR}

        </if>

        <if test="taskName != null and taskName !=''">

        <bind name="taskName" value ="'%'+taskName+'%'" />

            and task_name like #{taskName}

        </if> 

        <if test="status != null ">

            and status =#{status}

        </if>

        <if test="taskStatus != null">

            and task_status = #{taskStatus}

        </if> 

        <if test="checkDate != null and checkDate !=''">

            and check_date = #{checkDate}

        </if> 

        <if test="fillDate != null and fillDate !=''">

            and fill_date = #{fillDate}

        </if>

        <if test="reportDate != null and reportDate !=''">

            and report_date = #{reportDate}

        </if>



        <if test ="parentTaskId !=null ">

        and  parent_task_id = #{parentTaskId}

        </if>

        </where>

            UNION

        SELECT

      rts.id,rts.task_name ,rts.company_code,rts.company_name,rts.report_code,rts.frequency,rts.period,rts.task_trigger_date,rts.fill_date,rts.check_date

            ,rts.report_date,rts.parent_task_id,rts.final_man_um,rts.final_man_name,rts.wheel_man_um,rts.wheel_man_name,rts.report_man_um,rts.report_man_name,rts.repeat_man_um

            ,rts.repeat_man_name,rts.report_type,rts.report_bore,rts.status,rts.task_status,rts.task_run_date,rts.lessee_id,rts.created_by,rts.updated_by,rts.created_date

        FROM rrmp_task_manage as rts

        join rrmp_temp as rt on rts.id = rt.parent_task_id



)

SELECT DISTINCT id,task_name ,company_code,company_name,report_code,frequency,period,task_trigger_date,fill_date,check_date

            ,report_date,parent_task_id,final_man_um,final_man_name,wheel_man_um,wheel_man_name,report_man_um,report_man_name,repeat_man_um

            ,repeat_man_name,report_type,report_bore,status,task_status,task_run_date,lessee_id,created_by,updated_by,created_date

    FROM rrmp_temp

    ORDER BY created_date desc

</select>