递归的使用

最新推荐文章于 2023-06-06 12:51:01 发布

胡八一、

最新推荐文章于 2023-06-06 12:51:01 发布

阅读量143

点赞数

CC 4.0 BY-SA版权

分类专栏： java 文章标签：递归算法 java sql

本文链接：https://blog.csdn.net/qq_44732146/article/details/118723913

java 专栏收录该内容

96 篇文章

订阅专栏

实体类

@Data
@ApiModel(value = "项目实体")
@TableName(value = "t_project")
public class Project {
    @ApiModelProperty(value = "id主键")
    @TableId(type = IdType.AUTO)
    private Long id;
    @ApiModelProperty(value = "项目名称",required = true)
    private String projectName;
    @ApiModelProperty(value = "状态:0-未标注1-正在标注2-标注完成",hidden = true)
    private Integer status;
    @ApiModelProperty(value = "创建时间",hidden = true)
    private LocalDateTime createTime;
    @ApiModelProperty(value = "更新时间",hidden = true)
    private LocalDateTime updateTime;
    @ApiModelProperty(value = "创建人id",required = true)
    private Long createUserId;
    @ApiModelProperty(value = "项目层级")
    private Integer projectLevel;
    @TableField(updateStrategy=FieldStrategy.IGNORED)
    @ApiModelProperty(value = "父id")
    private Long parentPid;
    @ApiModelProperty(value = "子数据")
    @TableField(exist = false)
    private List<Project> children;
}

实体类DTO

@Data
@ApiModel(value = "项目展示实体")
public class ProjectDto {
    @ApiModelProperty(value = "id主键")
    private Long id;
    @ApiModelProperty(value = "项目名称")
    private String projectName;
    @ApiModelProperty(value = "状态:0-未标注1-正在标注2-标注完成")
    private Integer status;
    @ApiModelProperty(value = "创建时间")
    private LocalDate createTime;
    @ApiModelProperty(value = "更新时间")
    private LocalDate updateTime;
    @ApiModelProperty(value = "创建人id")
    private Long createUserId;
    @ApiModelProperty(value = "完成数量")
    private Integer completeNum;
    @ApiModelProperty(value = "总数量")
    private Integer totalDocumentNum;
    @ApiModelProperty(value = "层级")
    private Integer projectLevel;
    @ApiModelProperty(value = "子数据")
    @TableField(exist = false)
    private List<ProjectDto> children;
    @TableField(exist = false)
    private Long parentPid;
}

数据库表的sql

/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 8.0.24 : Database - mark
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mark` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `mark`;

/*Table structure for table `act_literature` */

DROP TABLE IF EXISTS `act_literature`;

CREATE TABLE `act_literature` (
  `id` bigint NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `release_time` date DEFAULT NULL,
  `source` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `data_source` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `key_word` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `belong_to_project` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `mark_status` int DEFAULT '7' COMMENT '标注状态(0>未标注，1->标注中，2->待审核，3->审核通过，4->审核驳回，5->审核中，6->审核驳回再次提交,7->未分配)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

/*Data for the table `act_literature` */

insert  into `act_literature`(`id`,`name`,`release_time`,`source`,`data_source`,`key_word`,`belong_to_project`,`mark_status`) values 
(1,'aa',NULL,NULL,NULL,NULL,'test08',2),
(2,'bb',NULL,NULL,NULL,NULL,'test08',3),
(3,'cc',NULL,NULL,NULL,NULL,'test06',5),
(4,'dd',NULL,NULL,NULL,NULL,'test09',3),
(5,'ee',NULL,NULL,NULL,NULL,'test11',3),
(66,'ff',NULL,NULL,NULL,NULL,'test11',3),
(77,'gg',NULL,NULL,NULL,NULL,'test12',3);

/*Table structure for table `kv_project` */

DROP TABLE IF EXISTS `kv_project`;

CREATE TABLE `kv_project` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id主键',
  `project_name` varchar(255) DEFAULT NULL COMMENT '项目名称',
  `status` int DEFAULT NULL COMMENT '0-未标注 1正在标注2标注完成',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `create_user_id` bigint DEFAULT NULL COMMENT '项目创建人id',
  `parent_pid` bigint DEFAULT NULL COMMENT '父id',
  `project_level` int DEFAULT NULL COMMENT '层级',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `kv_project` */

insert  into `kv_project`(`id`,`project_name`,`status`,`create_time`,`update_time`,`create_user_id`,`parent_pid`,`project_level`) values 
(1,'test01',1,'2021-07-17 16:39:03','2021-07-12 16:39:06',19,NULL,1),
(2,'test02',2,'2021-07-12 16:39:25','2021-07-13 11:30:45',19,NULL,1),
(34,'test03',0,'2021-07-12 16:40:41','2021-07-12 16:40:41',19,NULL,1),
(35,'test04',0,'2021-07-12 16:40:53','2021-07-12 16:40:53',19,NULL,1),
(36,'test05',0,'2021-07-12 16:41:01','2021-07-12 16:41:01',19,NULL,1),
(37,'test06',0,'2021-07-12 16:41:56','2021-07-12 16:41:56',19,1,2),
(38,'test07',0,'2021-07-12 16:43:48','2021-07-12 16:43:48',19,1,2),
(39,'test08',0,'2021-07-12 16:45:36','2021-07-12 16:45:36',19,38,3),
(40,'test09',NULL,'2021-07-12 20:35:59','2021-07-12 20:36:01',20,38,3),
(41,'test10',NULL,'2021-07-12 21:05:26','2021-07-12 21:05:27',10,40,4),
(42,'test11',2,'2021-07-12 22:06:31','2021-07-13 11:30:29',20,2,2),
(43,'test12',2,'2021-07-13 18:48:16','2021-07-13 11:30:45',20,2,2);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

   /**
     * 集合递归查询
     * @param projectList 封装好的树状数据
     * @param 查询到的集合
     * @param 要查找名称
     */
    private void findByName(List<ProjectDto> projectList,List<ProjectDto> results,String name){
        Pattern pattern = Pattern.compile(name);
        for(int i=0; i < projectList.size(); i++){
            if(projectList.get(i).getChildren().size()>0&&projectList.get(i)!=null){
            	//递归查找
                findByName(projectList.get(i).getChildren(),results,name);
            }
            Matcher matcher = pattern.matcher((projectList.get(i)).getProjectName());
            if(matcher.find()){
                //找到之后放进去
                results.add(projectList.get(i));
            }
        }
    }

 /**
     * 根据子id得到父id
     * @param project
     * @param list
     */
    private void getByCid(ProjectDto project, Set<ProjectDto> list) {
        ProjectDto pid = baseMapper.selectByPid(project.getParentPid());
        if (pid != null) {
        	//查到之后添加进去
            list.add(pid);
        }
        if (pid.getParentPid() != null) {
       		//递归查询
            getByCid(pid, list);
        }
    }

sql语句

 select * from kv_project where id=#{parentPid}

    /**
     * 根据父id递归得到下面的子数据
     * @param project 父项目
     * @return
     */
    private void getChildren(List<ProjectDto> project) {
        ProjectQueryParam projectQueryParam = new ProjectQueryParam();
        if (project != null && project.size() > 0) {
            project.stream().forEach(p -> {
                projectQueryParam.setPid(p.getId());
                List<ProjectDto> child = baseMapper.queryAdminProjectListByPidOrLevel(projectQueryParam);
                getChildren(child);
                //进行回溯的时候进行计算
                setCompleteNumAndTotalNum(p, child);
                p.setChildren(child);
            });
        }
    }

mapper的方法

List<ProjectDto> queryAdminProjectListByPidOrLevel(ProjectQueryParam projectQueryParam);

sql语句

<!-- and或or 只会去掉多余的，不会自动加上 -->
    <select id="queryAdminProjectListByPidOrLevel" resultType="com.kv.mark.admin.dto.ProjectDto" parameterType="com.kv.mark.admin.dto.ProjectQueryParam">
                SELECT p.*,t3.total_document_num,t3.complete_num FROM   kv_project p  LEFT JOIN  (
SELECT t1.*,t2.complete_num FROM ( SELECT * FROM kv_project p,(SELECT belong_to_project,COUNT(*)AS total_document_num  FROM act_literature GROUP BY belong_to_project)AS temp
        WHERE p.project_name=temp.belong_to_project) AS t1 LEFT JOIN (

        SELECT p.id,temp.num AS complete_num FROM kv_project p,(SELECT belong_to_project,COUNT(*)AS num  FROM act_literature WHERE mark_status=3 GROUP BY belong_to_project)AS temp
        WHERE p.project_name=temp.belong_to_project) AS t2  ON t1.id=t2.id
        ORDER BY t1.create_time ASC) AS t3 ON
        t3.project_name = p.`project_name`
            <trim prefix="WHERE" prefixOverrides="AND | OR">
            <if test="pid !=null and pid!=''">
               and p.parent_pid=#{pid}
            </if>
            <if test="level!=null and level!=''">
               and p.project_level=#{level}
            </if>
            <if test="projectName!=null and projectName!=''">
               and  p.project_name like concat('%',#{projectName},'%')
            </if>
            <if test="status == 0">
                and  p.status in (0,1)
            </if>
            <if test="status == 1">
                and  p.status =2
            </if>
            </trim>
        <if test="orderByCreateTime=='ASC'">
        order by p.create_time ASC
        </if>
        <if test="orderByCreateTime=='DESC'">
            order by p.create_time DESC
        </if>
    </select>

 /**
     * 递归查找所有菜单的子菜单
     *
     * @param root
     * @param all
     * @return
     */
    private List<ProjectDto> getChildren(ProjectDto root, List<ProjectDto> all) {
        List<ProjectDto> children = all.stream().filter(projectDto -> {
            return root.getId().equals(projectDto.getParentPid());
        }).map(projectDto -> {
            //1、找到子菜单(递归)
            projectDto.setChildren(getChildren(projectDto, all));
            return projectDto;
        }).collect(Collectors.toList());
        return children;
    }

		//调用
		//封装树形数据
        List<ProjectDto> projectDtosWithTree = projectDtos.stream()
                .filter(e -> e.getParentPid() == null)
                .map((menu) -> {
                    menu.setChildren(getChildren(menu, projectDtos));
                    return menu;
                })
                .collect(Collectors.toList());