Mybatis+PostgreSQL树查询
树结构的查询，根据条件设置树结构中符合条件节点设置标识位
- 数据表
/*
Navicat PGSQL Data Transfer
Source Server : 172.22.24.139
Source Server Version : 101500
Source Host : 172.22.24.139:5432
Source Database : ry-cloud
Source Schema : public
Target Server Type : PGSQL
Target Server Version : 101500
File Encoding : 65001
Date: 2021-07-01 17:34:46
*/
-- ----------------------------
-- Table structure for info_topology_node
-- ----------------------------
DROP TABLE IF EXISTS "public"."info_topology_node";
CREATE TABLE "public"."info_topology_node" (
"id" varchar(255) COLLATE "default" NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL,
"group_id" varchar(255) COLLATE "default" NOT NULL,
"parent_id" varchar(255) COLLATE "default",
"template_id" varchar(255) COLLATE "default",
"apm_id" varchar(255) COLLATE "default",
"create_by" varchar(64) COLLATE "default" DEFAULT ''::character varying,
"create_time" timestamp(6),
"update_by" varchar(64) COLLATE "default" DEFAULT ''::character varying,
"update_time" timestamp(6),
"status" char(1) COLLATE "default" NOT NULL,
"srp_version" varchar(255) COLLATE "default" NOT NULL,
"alias" varchar(255) COLLATE "default",
"sync_status" char(1) COLLATE "default" DEFAULT 0
)
WITH (OIDS=FALSE)
;
COMMENT ON COLUMN "public"."info_topology_node"."id" IS '拓扑的节点id';
COMMENT ON COLUMN "public"."info_topology_node"."name" IS '拓扑节点名称';
COMMENT ON COLUMN "public"."info_topology_node"."group_id" IS '拓扑组id对应的info_topology的id';
COMMENT ON COLUMN "public"."info_topology_node"."parent_id" IS '父节点的id';
COMMENT ON COLUMN "public"."info_topology_node"."template_id" IS '模板的id';
COMMENT ON COLUMN "public"."info_topology_node"."apm_id" IS '同步到AMP对应的APM的id';
COMMENT ON COLUMN "public"."info_topology_node"."create_by" IS '创建者';
COMMENT ON COLUMN "public"."info_topology_node"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."info_topology_node"."update_by" IS '更新者';
COMMENT ON COLUMN "public"."info_topology_node"."update_time" IS '更新时间';
COMMENT ON COLUMN "public"."info_topology_node"."status" IS '状态来源：1默认拓扑新增 2默认拓扑修改 3默认拓扑删除';
COMMENT ON COLUMN "public"."info_topology_node"."srp_version" IS 'srp版本号，为了统计使用，在节点的从SRP创建修改都需要操作此值';
COMMENT ON COLUMN "public"."info_topology_node"."alias" IS '节点的别名，用于批量同步节点信息，唯一';
COMMENT ON COLUMN "public"."info_topology_node"."sync_status" IS '同步状态0 未同步 1已经同步（注：若SRP端拓扑节点变更则此状态一定要更新为未同步）';
-- ----------------------------
-- Records of info_topology_node
-- ----------------------------
INSERT INTO "public"."info_topology_node" VALUES ('0', '台湾XX医院', '25', null, '27', '199', '', null, '', '2021-06-28 14:43:51.507004', '1', 'aaaaaa', '台湾XX医院0.0389305814169347', '1');
INSERT INTO "public"."info_topology_node" VALUES ('1', '高雄荣明总医院', '25', '0', null, null, '', null, '', '2021-06-28 09:05:04.782194', '1', 'aaaaaa', '高雄荣明总医院0.592853620648384', '0');
INSERT INTO "public"."info_topology_node" VALUES ('2', '台南分院', '25', '0', null, null, '', null, '', '2021-06-28 14:04:29.213266', '2', 'aaaaaa', '台南分院0.300062565132976', '0');
INSERT INTO "public"."info_topology_node" VALUES ('3', '台东分院胜利院区', '25', '0', '3', '219', '', null, '', '2021-06-29 14:34:53.97665', '1', 'aaaaaa', '台东分院胜利院区12321312312', '1');
INSERT INTO "public"."info_topology_node" VALUES ('31', '内科部', '25', '3', '3', '220', '', null, '', '2021-06-29 14:34:53.97665', '1', 'aaaaaa', '内科部1232325464', '1');
INSERT INTO "public"."info_topology_node" VALUES ('32', '外科部', '25', '3', '3', '221', '', null, '', '2021-06-29 14:36:18.536205', '1', 'aaaaaa', '外科部977546546464', '1');
INSERT INTO "public"."info_topology_node" VALUES ('4', '屏东大武分院', '25', '0', '3', '201', '', null, '', '2021-06-28 14:44:20.980222', '1', 'aaaaaa', '屏东大武分院0.639218820724636', '1');
INSERT INTO "public"."info_topology_node" VALUES ('41', '内科部', '25', '4', '3', '205', '', null, '', '2021-06-28 14:44:20.980222', '1', 'aaaaaa', '内科部0.0941485241055489', '1');
INSERT INTO "public"."info_topology_node" VALUES ('411', '内分泌新陈代谢', '25', '41', '3', '206', '', null, '', '2021-06-28 14:45:38.722462', '1', 'aaaaaa', '内分泌新陈代谢0.475152659695596', '1');
INSERT INTO "public"."info_topology_node" VALUES ('412', '肾脏科', '25', '41', '3', '207', '', null, '', '2021-06-28 14:45:38.722462', '1', 'aaaaaa', '肾脏科0.0480709779076278', '1');
INSERT INTO "public"."info_topology_node" VALUES ('413', '血液肿瘤科', '25', '41', '3', '208', '', null, '', '2021-06-28 14:45:38.722462', '1', 'aaaaaa', '血液肿瘤科0.558689671568573', '1');
INSERT INTO "public"."info_topology_node" VALUES ('414', '过敏免疫风湿科', '25', '41', '3', '209', '', null, '', '2021-06-28 14:45:38.722462', '1', 'aaaaaa', '过敏免疫风湿科0.193044934887439', '1');
INSERT INTO "public"."info_topology_node" VALUES ('415', '内科部', '25', '41', '3', '215', '', null, '', '2021-06-29 10:26:06.602373', '1', 'aaaaaa', '内科部0.0693092225119472', '1');
INSERT INTO "public"."info_topology_node" VALUES ('416', '心脏内科', '25', '41', '3', '214', '', null, '', '2021-06-29 10:26:06.602373', '1', 'aaaaaa', '心脏内科0.121422637254', '1');
INSERT INTO "public"."info_topology_node" VALUES ('417', '胸腔内科', '25', '41', '3', '213', '', null, '', '2021-06-29 10:26:06.602373', '1', 'aaaaaa', '胸腔内科0.864756926894188', '1');
INSERT INTO "public"."info_topology_node" VALUES ('418', '胃肠肝胆科', '25', '41', null, null, '', null, '', null, '2', 'aaaaaa', '胃肠肝胆科0.994988341350108', '0');
INSERT INTO "public"."info_topology_node" VALUES ('419', '神经内科', '25', '41', null, null, '', null, '', null, '1', 'aaaaaa', '神经内科0.342826233711094', '0');
INSERT INTO "public"."info_topology_node" VALUES ('42', '外科部', '25', '4', '3', '204', '', null, '', '2021-06-28 14:44:20.980222', '1', 'aaaaaa', '外科部0.631245576310903', '1');
INSERT INTO "public"."info_topology_node" VALUES ('420', '感染科', '25', '41', null, null, '', null, '', null, '1', 'aaaaaa', '感染科0.103627046104521', '0');
INSERT INTO "public"."info_topology_node" VALUES ('421', '一般内科', '25', '41', null, null, '', null, '', null, '1', 'aaaaaa', '一般内科0.0469048814848065', '0');
INSERT INTO "public"."info_topology_node" VALUES ('43', '骨科部', '25', '4', null, null, '', null, '', '2021-06-28 14:03:07.143836', '1', 'aaaaaa', '骨科部0.927614740096033', '0');
-- ----------------------------
-- Alter Sequences Owned By
-- ----------------------------
-- ----------------------------
-- Uniques structure for table info_topology_node
-- ----------------------------
ALTER TABLE "public"."info_topology_node" ADD UNIQUE ("alias");
-- ----------------------------
-- Primary Key structure for table info_topology_node
-- ----------------------------
ALTER TABLE "public"."info_topology_node" ADD PRIMARY KEY ("id");
- 实体类
package cn.com.advantech.ihcc.apm.domain;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.ihcc.common.core.web.domain.BaseEntity;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;
import java.util.List;
/**
* @Classname:TopologyNode
* @Description:拓扑节点实体类
* @author: hengying.zhang
* @date: 2021/6/23 17:23
*/
@Data
public class TopologyNode extends BaseEntity {
@ApiModelProperty(value = "唯一值")
private String id;
@ApiModelProperty(value = "拓扑节点的名称")
private String name;
//拓扑节点的别名，此名称唯一，在拓扑传递来之后若不包含此列则默认设置一个不重复的名称
//此名称用于节点同步到APM,与父节点一同操作的情况
@ApiModelProperty(value = "拓扑节点的别名")
private String alias;
@ApiModelProperty(value = "拓扑组信息")
private String groupId;
@ApiModelProperty(value = "父节点的ID")
private String parentId;
@ApiModelProperty(value = "模板的ID")
private String templateId;
@ApiModelProperty(value = "APM资产节点的ID")
private String apmId;
@ApiModelProperty(value = "1默认拓扑新增 2默认拓扑修改 3默认拓扑删除")
private String status;
@ApiModelProperty(value = "同步状态0未同步 1已同步")//（注：若SRP端拓扑节点变更则此状态一定要更新为未同步）
private String syncStatus;
@ApiModelProperty(value = "SRP版本")//（注：节点从SRP传过来（新增、修改、删除）需要记录此值）
private String srpVersion;
@ApiModelProperty(value = "父节点")
private TopologyNode parent;
@ApiModelProperty(value = "设备列表")
private List<TopologyNodeDevice> nodeDeviceList;
@ApiModelProperty(value = "子节点")
private List<TopologyNode> children;
private String queryName;
private String queryStatus;
private String querySrpVersion;
@ApiModelProperty(value = "是否正在查询节点：0否1是")
private String inStatus;
/** 创建者 */
private String createBy;
/** 创建时间 */
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
/** 更新者 */
private String updateBy;
/** 更新时间 */
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updateTime;
}
- TopologyMapper.xml
<!--拓扑节点基本信息结果集-->
<resultMap type="TopologyNode" id="TopologyNodeResult">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="alias" column="alias" />
<result property="groupId" column="group_id" />
<result property="parentId" column="parent_id" />
<result property="templateId" column="template_id" />
<result property="apmId" column="apm_id" />
<result property="status" column="status" />
<result property="syncStatus" column="sync_status" />
<result property="srpVersion" column="srp_version" />
</resultMap>
<!--拓扑节点带着子节点结果信息-->
<resultMap type="TopologyNode" id="TopologyNodeAndCildrenResult" extends="TopologyNodeResult">
<result property="queryName" column="query_name" />
<result property="queryStatus" column="query_status" />
<result property="querySrpVersion" column="query_srp_version" />
<result property="inStatus" column="in_status" />
<collection property="children" column="{id=id,queryName=query_name,queryStatus=query_status,querySrpVersion=query_srp_version}"
ofType="cn.com.advantech.ihcc.apm.domain.TopologyNode"
select="getTopologyNodeChildren">
</collection>
</resultMap>
<!--基础的查询SQL根据实际情况确定-->
<sql id="selectTopologyNodeSpecialVo">
SELECT
<if test="queryName != null and queryName != ''">
(case WHEN name like concat('%', #{queryName}, '%') THEN 1 else 0 END) in_status,
</if>
<if test="queryStatus != null and queryStatus != ''">
(case WHEN (status=#{queryStatus} and sync_status='0' and srp_version=#{querySrpVersion} )THEN 1 else 0 END) in_status,
</if>
#{queryName} as query_name,
#{queryStatus} as query_status,
#{querySrpVersion} as query_srp_version,
id,name,alias,group_id,parent_id,template_id,apm_id,
status,sync_status,srp_version
FROM info_topology_node
</sql>
<!--获取拓扑节点的整个树信息，由于此处从第二层开始取根据实际情况处理-->
<select id="selectTopologyNodeTree" parameterType="TopologyNode" resultMap="TopologyNodeAndCildrenResult">
<include refid="selectTopologyNodeSpecialVo"/>
where parent_id=(select id from info_topology_node where parent_id is null and group_id=#{groupId})
</select>
<!--查询拓扑节点子节点信息,与主节点用同一个ResultMap,此处类似于递归直到叶子节点-->
<select id="getTopologyNodeChildren" resultMap="TopologyNodeAndCildrenResult">
<include refid="selectTopologyNodeSpecialVo"/>
where parent_id = #{id}
</select>
浙公网安备 33010602011771号