orcale 在项目里递归循环实战

参照之前的实验orcale使用递归（测试）

1.前端入参

@Data
public class NjryTaskInfoQueryCriteria{
    private String taskId;
    private String taskName;
    private Integer taskType;
    private String procName;
    private Integer startTime;
    private Set<String> taskSet;
}

2.control层

    @GetMapping(value = "/getDependency")
    @Log("获取任务管理依赖列表 根据任务编号获取下级与上级数据")
    @ApiOperation("获取任务管理依赖列表 根据任务编号获取下级与上级数据")
    @PreAuthorize("@el.check('njryTaskInfo:list')")
    public ResponseEntity<PageResult<NjryTaskInfo>> getDependency(NjryTaskInfoQueryCriteria criteria, Page<Object> page){
        Set<String> taskSet  = new LinkedHashSet<>();
//        递归获取当前任务的所有依赖
        List<String> superiorTasks = njryTaskInfoService.getSuperior(criteria.getTaskId(), new ArrayList<>());
        List<String> subTasks = njryTaskInfoService.getSub(criteria.getTaskId(), new ArrayList<>());
        taskSet.addAll(superiorTasks);
        taskSet.addAll(subTasks);
//        查询任务管理列表排除这些依赖不带分页
//        List<NjryTaskInfo> list = njryTaskInfoService.queryAllWithoutDependency(taskSet);
//        return new ResponseEntity<>(list,HttpStatus.OK);
        criteria.setTaskSet(taskSet);
        PageResult<NjryTaskInfo> pageList = njryTaskInfoService.queryAllNoDependency(criteria,page);
        return new ResponseEntity<>(pageList,HttpStatus.OK);
    }

3.service层

public interface NjryTaskInfoService extends IService<NjryTaskInfo> {
    /**
     * 查询数据分页
     * @param criteria 条件
     * @param page 分页参数
     * @return PageResult
     */
    PageResult<NjryTaskInfo> queryAllNoDependency(NjryTaskInfoQueryCriteria criteria, Page<Object> page);
    /**
     * 获取当前任务依赖的任务
     * @param taskId
     * @param superiorTasks
     * @return
     */
    List<String> getSuperior(String taskId,List<String> superiorTasks);

    /**
     * 获取依赖当前任务的任务
     * @param taskId
     * @param subTasks
     * @return
     */
    List<String> getSub(String taskId,List<String> subTasks);
}

4.实现层

@Service
@RequiredArgsConstructor
public class NjryTaskInfoServiceImpl extends ServiceImpl<NjryTaskInfoMapper, NjryTaskInfo> implements NjryTaskInfoService {

    private final NjryTaskInfoMapper njryTaskInfoMapper;
    @Override
    public PageResult<NjryTaskInfo> queryAllNoDependency(NjryTaskInfoQueryCriteria criteria, Page<Object> page){
        return PageUtil.toPage(njryTaskInfoMapper.queryAllNoDependency(criteria, page));
    }
    @Override
    public List<String> getSuperior(String taskId, List<String> superiorTasks) {
        return njryTaskInfoMapper.getSuperior(taskId);
    }

    @Override
    public List<String> getSub(String taskId, List<String> subTasks) {
        return njryTaskInfoMapper.getSub(taskId);
    }
}

5.mapper层

@Mapper
public interface NjryTaskInfoMapper extends BaseMapper<NjryTaskInfo> {

	IPage<NjryTaskInfo> queryAllNoDependency(@Param("criteria") NjryTaskInfoQueryCriteria criteria, Page<Object> page);
    List<String> getSuperior(@Param("taskId") String taskId);

    List<String> getSub(@Param("taskId") String taskId);
}

插曲

是对NJRY_TASK_INFO这个表操作，但是记录相互依赖的是njry_task_relation表

6.xml(简单的递归实现)

 <resultMap id="BaseResultMap" type="com.njry.modules.system.domain.NjryTaskInfo">
        <result column="task_id" property="taskId"/>
        <result column="task_id" property="id"/>
    </resultMap>

    <sql id="Base_Column_List">
        task_id, TASK_NAME, TASK_TYPE, PROC_NAME, START_TIME, IS_VALID, CELLPHONE, CREATE_DATE, CREATE_OPERATOR, MODIFY_DATE, MODIFY_OPERATOR, END_DATE, START_DATE, IS_SUC_MES
    </sql>



	    <select id="queryAllNoDependency" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from NJRY_TASK_INFO
        <where>
            <if test="criteria.taskName != null">
                and TASK_NAME like concat('%'||#{criteria.taskName},'%')
            </if>
            <if test="criteria.taskType != null">
                and TASK_TYPE = #{criteria.taskType}
            </if>
            <if test="criteria.procName != null">
                and PROC_NAME like concat('%'||#{criteria.procName},'%')
            </if>
            <if test="criteria.startTime != null">
                and START_TIME = #{criteria.startTime}
            </if>
            <if test="criteria.taskSet != null and criteria.taskSet.size() > 0">
                and not exists (
                select 1 from  NJRY_TASK_INFO inner where inner.task_id = a.task_id and inner.task_id in
                <foreach collection="criteria.taskSet" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                )
            </if>
        </where>
    </select>
    
    <select id="getSuperior" resultType="java.lang.String">
        select distinct rel_task
        from njry_task_relation
        start with task_id = #{taskId}
        connect by prior rel_task = task_id
    </select>

    <select id="getSub" resultType="java.lang.String">
        select distinct task_id
        from njry_task_relation
        start with rel_task = #{taskId}
        connect by prior task_id = rel_task
    </select>

7.xml之前我大都在plsql实验一下(java.sql.SQLSyntaxErrorException: ORA-00933)

8.实体类返回

@Data
@EqualsAndHashCode(callSuper = true)
@TableName("NJRY_TASK_INFO")
public class NjryTaskInfo extends WorkTypeBaseEntity implements Serializable {

    @NotNull(groups = WorkTypeBaseEntity.Update.class)
    @TableId(value="task_id", type = IdType.INPUT)
    @TableField(exist = false)
    @ApiModelProperty(value = "ID", hidden = true)
    private Long id;

    @ApiModelProperty(value = "任务编号")
    private String taskId;

    @NotBlank
    @ApiModelProperty(value = "任务名称")
    private String taskName;

    @NotNull
    @ApiModelProperty(value = "任务类型   1：日  2：月  3：实时")
    private Integer taskType;

    @NotBlank
    @ApiModelProperty(value = "存储过程名称")
    private String procName;

    @NotNull
    @ApiModelProperty(value = "开始检测时间")
    private Integer startTime;

    @ApiModelProperty(value = "是否有效")
    private Integer isValid;

    @ApiModelProperty(value = "手机号码")
    private String cellphone;

    @ApiModelProperty(value = "结束日期")
    private Date endDate;

    @NotNull
    @ApiModelProperty(value = "开始日期")
    private Date startDate;

    @ApiModelProperty(value = "是否发送成功短信")
    private Integer isSucMes;

    @NotNull
    @TableField(exist = false)
    @ApiModelProperty(value = "参数信息列表")
    private List<NjryTaskArg> njryTaskArgs;


    @TableField(exist = false)
    @ApiModelProperty(value = "依赖列表")
    private List<NjryTaskRelation> dependenceNjryTasks;

    public void copy(NjryTaskInfo source){
        BeanUtil.copyProperties(source,this, CopyOptions.create().setIgnoreNullValue(true));
    }
}