【PostgresSQL系列】 ltree简介及基于SpringBoot实现 ltree数据增删改查

本文将对PostgresSQL中的ltree进行相关概念介绍，并以示例代码讲解ltree数据增删改查功能的实现。

作者：后端小肥肠

目录

 1.前言

2. 基础概念

2.1. ltree

2.2. lquery

2.3. ltxtquery

2.4. ltree函数及操作符

2.4.1. ltree函数

2.4.2. ltree操作符 

3.开发环境搭建

3.1. 所用工具版本

 3.2. pom依赖

4. 代码讲解

4.1. 目录树查询

4.2. 新增目录树节点

4.3. 更新目录树节点

4.4. 删除目录树节点

5. 结语

6. 参考链接

 1.前言

        在数据库设计和数据管理领域，有效地处理层次结构数据是一项关键任务。PostgreSQL 提供了 ltree 扩展模块，为处理这类数据提供了强大的工具。在本文中，我们将深入探讨 ltree 的相关概念和用法。

2. 基础概念

2.1. ltree

        在 PostgreSQL 中，ltree 是一种专门用于表示层次结构数据的数据类型。每个 ltree 值都可以被看作是一个节点标签序列，这些标签通过点号分隔，形成路径。例如，'A.B.C' 表示一个具有三个节点的树状结构，其中每个节点的标签分别是 A、B 和 C。

       在创建表时，可以使用 ltree 数据类型定义列，从而存储具有层次结构的数据。下面是一个简单的示例：

1. 创建ltree扩展


CREATE EXTENSION ltree;

2. 创建ltree序列

CREATE TABLE my_table (id serial PRIMARY KEY,path ltree
);

3. 插入数据

INSERT INTO my_table (path) VALUES('A.B.C'),('A.D.E'),('X.Y.Z');-- 查询以 A 开头的路径
SELECT * FROM my_table WHERE path ~ 'A.*';

2.2. lquery

        lquery表示一个用于匹配ltree值的类正则表达式的模式。一个简单词匹配一个路径中的那个标签。它允许我们指定路径的模式，以便查找符合条件的数据。通配符和操作符可以用于创建灵活的匹配规则。

以下为使用示例：

-- 查询以 A 开头，以 C 结尾的路径
SELECT * FROM my_table WHERE path ~ 'A.*.C';

2.3. ltxtquery

        ltxtquery表示一种用于匹配ltree值的类全文搜索的模式。它引入了逻辑运算符，如 AND、OR、NOT，以及括号，以支持更复杂的查询操作。

以下为使用示例：

-- 查询路径中同时包含 A 和 B，或者包含 C 的记录
SELECT * FROM my_table WHERE path @ 'A & B | C';

        注意：ltxtquery允许符号之间的空白，但是ltree和lquery不允许。一个ltxtquery值包含词，也可能在末尾带有修饰符@、*、%，修饰符具有和lquery中相同的含义。词可以用&（AND）、|（OR）、!（NOT）以及圆括号组合。

2.4. ltree函数及操作符

2.4.1. ltree函数
函数返回类型描述例子结果
subltree(ltree, int start, int end)ltreeltree的从位置start到位置end-1（从 0 开始计）的子路径subltree('Top.Child1.Child2',1,2)Child1
subpath(ltree, int offset, int len)ltreeltree从位置offset开始长度为len的子路径。如果offset为负，则子路径开始于距离路径尾部那么远的位置。如果len为负，则从路径的尾部开始丢掉那么多个标签。subpath('Top.Child1.Child2',0,2)Top.Child1
subpath(ltree, int offset)ltreeltree从位置offset开始一直延伸到路径末尾的子路径。如果offset为负，则子路径开始于距离路径尾部那么远的位置。subpath('Top.Child1.Child2',1)Child1.Child2
nlevel(ltree)integer路径中标签的数量nlevel('Top.Child1.Child2')3
index(ltree a, ltree b)integera中第一次出现b的位置，如果没有找到则为 -1index('0.1.2.3.5.4.5.6.8.5.6.8','5.6')6
index(ltree a, ltree b, int offset)integera中第一次出现b的位置，搜索从offset开始。负的offset表示从距路径尾部-offset个标签的位置开始index('0.1.2.3.5.4.5.6.8.5.6.8','5.6',-4)9
text2ltree(text)ltree把text转换成ltree
ltree2text(ltree)text把ltree转换成text
lca(ltree, ltree, ...)ltree最低公共祖先，即最长的公共路径前缀（最多支持 8 个参数）lca('1.2.2.3','1.2.3.4.5.6')1.2
lca(ltree[])ltree最低公共祖先，即最长的公共路径前缀lca(array['1.2.2.3'::ltree,'1.2.3'])1.2
2.4.2. ltree操作符 
操作符返回值描述
ltree @> ltreeboolean左参数是不是右参数的一个祖先（或者相等）？
ltree <@ ltreeboolean左参数是不是右参数的一个后代（或者相等）？
ltree ~ lquerybooleanltree匹配lquery吗？
lquery ~ ltreebooleanltree匹配lquery吗？
ltree ? lquery[]booleanltree匹配数组中的任意lquery吗？
lquery[] ? ltreebooleanltree匹配数组中的任意lquery吗？
ltree @ ltxtquerybooleanltree匹配ltxtquery吗？
ltxtquery @ ltreebooleanltree匹配ltxtquery吗？
ltree || ltreeltree串接ltree路径
ltree || textltree把文本转换成ltree并且串接
text || ltreeltree把文本转换成ltree并且串接
ltree[] @> ltreeboolean数组是否包含ltree的一个祖先？
ltree <@ ltree[]boolean数组是否包含ltree的一个祖先？
ltree[] <@ ltreeboolean数组是否包含ltree的一个后代？
ltree @> ltree[]boolean数组是否包含ltree的一个后代？
ltree[] ~ lqueryboolean数组是否包含匹配lquery的路径？
lquery ~ ltree[]boolean数组是否包含匹配lquery的路径？
ltree[] ? lquery[]booleanltree数组是否包含匹配任意lquery的路径？
lquery[] ? ltree[]booleanltree数组是否包含匹配任意lquery的路径？
ltree[] @ ltxtqueryboolean数组是否包含匹配ltxtquery的路径？
ltxtquery @ ltree[]boolean数组是否包含匹配ltxtquery的路径？
ltree[] ?@> ltreeltree是ltree祖先的第一个数组项；如果没有则是 NULL
ltree[] ?<@ ltreeltree是ltree祖先的第一个数组项；如果没有则是 NULL
ltree[] ?~ lqueryltree匹配lquery的第一个数组项；如果没有则是 NULL
ltree[] ?@ ltxtqueryltree匹配lquery的第一个数组项；如果没有则是 NULL

操作符<@、@>、 @以及~有类似的、 ^<@、^@>、^@、 ^~，只是它们不适用索引。它们只对测试目的有用。

3.开发环境搭建

3.1. 所用工具版本

依赖版本
Spring Boot2.6.3
java1.8
postgres13.12

 3.2. pom依赖

 <dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-generator</artifactId></dependency><dependency><groupId>org.freemarker</groupId><artifactId>freemarker</artifactId></dependency><dependency><groupId>org.springframework.cloud</groupId><artifactId>spring-cloud-starter-bootstrap</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>com.alibaba.fastjson2</groupId><artifactId>fastjson2</artifactId><version>2.0.25</version></dependency></dependencies>

4. 代码讲解

        本文代码为基于ltree构建目录树，包含目录树查询，新增目录节点，修改目录节点，删除目录节点。

4.1. 目录树查询

1. controller层

  @GetMapping("/search")public LtreeCatalogDTO getCatalogList() {return ltreeCatalogService.getCatalogList();}

2. service方法

    public LtreeCatalogDTO getCatalogList() {//设置原始根节点（需要写死一个原始根节点，我在这里直接写死了，可以通过yml配置文件动态指定）LtreeCatalogDTO resTree=new LtreeCatalogDTO("1",null,"目录树",null);List<LtreeCatalogDTO>resChildren=new ArrayList<>();List<LtreeCatalogDTO>childrenList=new ArrayList<>();List<LtreeCatalogDTO>rootList=new ArrayList<>();LambdaQueryWrapper<LtreeCatalog> queryWrapper=new LambdaQueryWrapper<>();List<LtreeCatalog> ltreeCatalogs = baseMapper.selectList(queryWrapper.orderByDesc(LtreeCatalog::getCreateTime));for (LtreeCatalog ltreeCatalog : ltreeCatalogs) {if(("1").equals(ltreeCatalog.getId())){continue;}LtreeCatalog parentCatalog= baseMapper.getParentByChildId(ltreeCatalog.getId());if("1".equals(parentCatalog.getId())){rootList.add(new LtreeCatalogDTO(ltreeCatalog.getId(),parentCatalog.getId(),ltreeCatalog.getName(),null));}else {childrenList.add(new LtreeCatalogDTO(ltreeCatalog.getId(),parentCatalog.getId(),ltreeCatalog.getName(),null));}}for (LtreeCatalogDTO rootNode : rootList) {LtreeCatalogDTO tree=buildTree(childrenList,rootNode);resChildren.add(tree);}resTree.setChildren(resChildren);return resTree;}public  LtreeCatalogDTO buildTree(List<LtreeCatalogDTO> ltreeCatalogDTOS, LtreeCatalogDTO catalogP) {List<LtreeCatalogDTO> childrenList = new ArrayList<>();for (LtreeCatalogDTO catalogC : ltreeCatalogDTOS) {// 当前数据的 parentId 等于 父节点的 id，则该数据是当前父级节点的子级。if (catalogC!=null && catalogC.getParentId().equals(catalogP.getId())) {// 递归调用childrenList.add(buildTree(ltreeCatalogDTOS, catalogC));}}catalogP.setChildren(childrenList);return catalogP;}

上述代码很简单，就是根据数据表中的path（ltree）递归构造目录树，返回给前端。

4.2. 新增目录树节点

1.controller层

    @PostMapping("")public String addCatalog(@RequestBody LtreeCatalog ltreeCatalog) {return ltreeCatalogService.addCatalog(ltreeCatalog);}

2. service方法

    public String addCatalog(LtreeCatalog ltreeCatalog) {return baseMapper.insert(ltreeCatalog)==1?"新增成功":"新增失败";}

ps：在新增数据时会报错 字段 "path" 的类型为 ltree, 但表达式的类型为 character varying 建议：你需要重写或转换表达式，解决的方法新增一个LTreeTypeHandler类，配置到表字段中即可。

LTreeTypeHandler类：

@MappedTypes(String.class)
public class LTreeTypeHandler extends BaseTypeHandler<String> {@Overridepublic void setNonNullParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException {PGobject pgObject = new PGobject();pgObject.setType("ltree");pgObject.setValue(s);preparedStatement.setObject(i, pgObject);}@Overridepublic String getNullableResult(ResultSet resultSet, String s) throws SQLException {return resultSet.getString(s);}@Overridepublic String getNullableResult(ResultSet resultSet, int i) throws SQLException {return resultSet.getString(i);}@Overridepublic String getNullableResult(CallableStatement callableStatement, int i) throws SQLException {return callableStatement.getString(i);}
}

配置到实体类表字段中：

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class LtreeCatalog implements Serializable {private static final long serialVersionUID = 1L;@TableId(value = "id", type = IdType.ASSIGN_ID)private String id;private String name;@TableField(value = "path", typeHandler = LTreeTypeHandler.class)private String path;private Boolean isDeleted;@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")@TableField(fill = FieldFill.INSERT)private Date createTime;@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")@TableField(fill = FieldFill.INSERT_UPDATE)private Date updateTime;@Versionprivate Integer version;}

发送请求：

4.3. 更新目录树节点

1.controller层

    @PutMapping("")public String updateCatalog(@RequestBody LtreeCatalog ltreeCatalog) throws Exception {return ltreeCatalogService.updateCatalog(ltreeCatalog);}

2. service方法

    @Overridepublic String updateCatalog(LtreeCatalog ltreeCatalog) throws Exception {LtreeCatalog ltreeCatalogExist = baseMapper.selectById(ltreeCatalog.getId());if(ltreeCatalogExist==null){throw new Exception("节点不存在");}return baseMapper.updateById(ltreeCatalog)==1?"新增成功":"新增失败";}

发送请求：

4.4. 删除目录树节点

1.controller层

    @DeleteMapping("/{id}")public void delCatalog(@PathVariable("id") String id) throws Exception {ltreeCatalogService.delCatalog(id);}

2. service方法

目录树删除节点时需要连带删除子节点，那么我们就可以使用ltree操作符中的<@来实现：

    @Overridepublic void delCatalog(String id) throws Exception {LtreeCatalog ltreeCatalog = baseMapper.selectById(id);if(ltreeCatalog==null){throw new Exception("节点不存在");}baseMapper.delCatalog(ltreeCatalog.getPath());}

  baseMapper.delCatalog方法：

    @Delete("select * FROM ltree_catalog WHERE path <@ #{path}::ltree;")void delCatalog(String path);

发送请求：

发送请求前先确定要删除的数据：

 如上图所示我想删除目录b及其下属子节点，那么我们需要把目录b这条数据的id传递给后台：

回看数据表发现目录树b及其子节点已经被删除：

5. 结语

        在本文中，我们探讨了 PostgreSQL 中 ltree 扩展的基本概念和使用方法。通过使用 ltree，我们可以更有效地处理和查询层次结构数据，使得数据库设计和数据管理变得更为灵活和强大。在实际应用中，ltree 提供了一种有力的工具，可以应对各种层次结构的数据建模需求。

6. 参考链接

ltree - PostgreSQL9.6 中文手册

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/250261.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

软考 系统分析师系列知识点之需求管理（3）

软考 系统分析师系列知识点之需求管理（3）

接前一篇文章&#xff1a;软考 系统分析师系列知识点之需求管理&#xff08;2&#xff09; 所属章节&#xff1a; 第11章. 软件需求工程 第8节. 需求管理 11.8.4 需求跟踪 根据IEEE的定义&#xff0c;可跟踪性包含两个层面的含义&#xff1a;一个是开发过程的两个或多个产品之…
阅读更多...
统计图表在线配置服务-百度 SugarBI的学习笔记

统计图表在线配置服务-百度 SugarBI的学习笔记

最近&#xff0c;有个产品要支持统计图表在线可配置&#xff0c;这样&#xff0c;当用户有新增统计指标的需求时&#xff0c;运维人员通过界面化配置&#xff0c;就可以增加统计指标了&#xff0c;不用开发写代码&#xff0c;画页面了。 上网查了下相关的组件&#xff0c;感觉…
阅读更多...
OSPF排错

OSPF排错

目录 实验拓扑图 实验要求 实验排错 故障一 故障现象 故障分析 故障解决 故障二 故障现象 故障分析 故障解决 故障三 故障现象 故障分析 故障解决 故障四 故障现象 故障分析 故障解决 故障五 故障现象 故障分析 故障解决 故障六 故障现象 故障分析 …
阅读更多...
Linux服务详解

Linux服务详解

如有错误或有补充&#xff0c;以及任何改进的意见&#xff0c;请在评论区留下您的高见&#xff0c;同时文中给出大部分命令的示例&#xff0c;即是您暂时无法在Linux中查看&#xff0c;您也可以知道各种操作的功能以及输出 如果觉得本文写的不错&#xff0c;不妨点个赞&#x…
阅读更多...
曲线拟合、多项式拟合、最小二乘法

曲线拟合、多项式拟合、最小二乘法

最近在做自车轨迹预测的工作&#xff0c;遇到 曲线拟合、多项式拟合、最小二乘法这些概念有点不清晰&#xff0c; 做一些概念区别的总结&#xff1a; 曲线拟合用于查找一系列数据点的“最佳拟合”线或曲线。 大多数情况下&#xff0c;曲线拟合将产生一个函数&#xff0c;可用于…
阅读更多...
[嵌入式软件][启蒙篇][仿真平台] STM32F103实现SPI控制OLED屏幕

[嵌入式软件][启蒙篇][仿真平台] STM32F103实现SPI控制OLED屏幕

上一篇&#xff1a; [嵌入式软件][启蒙篇][仿真平台] STM32F103实现LED、按键 [嵌入式软件][启蒙篇][仿真平台] STM32F103实现串口输出输入、ADC采集 [嵌入式软件][启蒙篇][仿真平台]STM32F103实现定时器 [嵌入式软件][启蒙篇][仿真平台] STM32F103实现IIC控制OLED屏幕 文章目…
阅读更多...
Git 怎么设置用户的权限

Git 怎么设置用户的权限

在团队协作的软件开发中&#xff0c;对于版本控制系统Git来说&#xff0c;确保代码与数据的安全性至关重要。为了实现这一目标&#xff0c;Git提供了灵活且可定制的用户权限管理机制。下面将简单的探讨一下Git如何设置用户的权限&#xff0c;以及如何保护代码和数据。 用户身份…
阅读更多...
提高 NFS Azure 文件共享性能

提高 NFS Azure 文件共享性能

本文内容 适用于增加预读大小以提高读取吞吐量Nconnect另请参阅 本文介绍如何提高 NFS Azure 文件共享的性能。 适用于 展开表 文件共享类型SMBNFS标准文件共享 (GPv2)、LRS/ZRS 标准文件共享 (GPv2)、GRS/GZRS 高级文件共享 (FileStorage)、LRS/ZRS 增加预读大…
阅读更多...
Spring Boot集成Redisson详细介绍

Spring Boot集成Redisson详细介绍

Redisson是一个用于Java的分布式和高可用的Java对象的框架&#xff0c;它基于Redis实现。在Spring Boot应用程序中集成Redisson可以帮助我们更轻松地实现分布式锁、分布式对象、分布式集合等功能。本文将介绍如何在Spring Boot项目中集成Redisson&#xff0c;并展示一些基本用法…
阅读更多...
git远程仓库基本操作

git远程仓库基本操作

目录 gitremote &#xff08;查看远程仓库&#xff09; git remote add [仓库名] [url] git clone [url]&#xff08;克隆远程仓库到本地&#xff09; git push [名][分支名]&#xff08;提交到远程仓库&#xff09;​编辑 git pull [名][分支名]从远程仓库拉取​编辑 注意操作…
阅读更多...
混乱字母排序——欧拉路数论

混乱字母排序——欧拉路数论

题目描述 小明接到一个神秘的任务&#xff1a;对于给定的 n 个没有顺序的字母对&#xff08;无序代表这两个字母可以前后顺序颠倒&#xff0c;区分大小写&#xff09;。请构造一个有 (n1) 个字母的混乱字符串使得每个字母对都在这个字符串中出现。 输入输出格式 输入格式 第…
阅读更多...
three.js CSS2DRenderer、CSS2DObject渲染HTML标签

three.js CSS2DRenderer、CSS2DObject渲染HTML标签

有空的老铁关注一下我的抖音&#xff1a; 效果&#xff1a; <template><div><el-container><el-main><div class"box-card-left"><div id"threejs" style"border: 1px solid red;position: relative;"><…
阅读更多...
SpringCloud_学习笔记_1

SpringCloud_学习笔记_1

SpringCloud01 1.认识微服务 随着互联网行业的发展&#xff0c;对服务的要求也越来越高&#xff0c;服务架构也从单体架构逐渐演变为现在流行的微服务架构。这些架构之间有怎样的差别呢&#xff1f; 1.0.学习目标 了解微服务架构的优缺点 1.1.单体架构 单体架构&#xff…
阅读更多...
FairGuard游戏加固入选《CCSIP 2023中国网络安全行业全景册（第六版）》

FairGuard游戏加固入选《CCSIP 2023中国网络安全行业全景册（第六版）》

2024年1月24日&#xff0c; FreeBuf咨询正式发布《CCSIP 2023中国网络安全行业全景册(第六版)》。本次发布的全景图&#xff0c;共计展示20个一级分类、108个细分安全领域&#xff0c;旨在为广大企业提供网络安全产品选型参考&#xff0c;帮助企业了解中国网络安全技术与市场的…
阅读更多...
一道sql注入的ctf题目致使用phpmyadmin上传 webshell 拿后台权限

一道sql注入的ctf题目致使用phpmyadmin上传 webshell 拿后台权限

以下均为靶场测试环境渗透&#xff0c;非正式环境。 遇见登录框&#xff0c;直接万能密码’or(11)or’/1 直接登录成功并返回结果: 既然存在sql注入&#xff0c;那就用sqlmap跑一下吧&#xff1a; 输出所有的数据库&#xff1a; sqlmap -u <目标URL> --dbs 要输出数据库…
阅读更多...
docker中三种常用的持久化数据的方式

docker中三种常用的持久化数据的方式

文章目录 介绍1.docker run -v2.volumes3.bind mounts 介绍 “前些天发现了一个巨牛的人工智能学习网站&#xff0c;通俗易懂&#xff0c;风趣幽默&#xff0c;忍不住分享一下给大家。点击跳转到网站。” 在Docker中&#xff0c;有以下三种常用的持久化数据的方式&#xff0c;可…
阅读更多...
Kotlin快速入门系列10

Kotlin快速入门系列10

Kotlin的委托 委托模式是常见的设计模式之一。在委托模式中&#xff0c;有两个对象参与处理同一个请求&#xff0c;接受请求的对象将请求委托给另一个对象来处理。与Java一样&#xff0c;Kotlin也支持委托模式&#xff0c;通过关键字by。 类委托 类的委托即一个类中定义的方…
阅读更多...
2024美赛数学建模A题思路分析 - 资源可用性和性别比例

2024美赛数学建模A题思路分析 - 资源可用性和性别比例

1 赛题 问题A&#xff1a;资源可用性和性别比例 虽然一些动物物种存在于通常的雄性或雌性性别之外&#xff0c;但大多数物种实质上是雄性或雌性。虽然许多物种在出生时的性别比例为1&#xff1a;1&#xff0c;但其他物种的性别比例并不均匀。这被称为适应性性别比例的变化。例…
阅读更多...
【Javaweb程序】【C00155】基于SSM的旅游旅行管理系统（论文+PPT）

【Javaweb程序】【C00155】基于SSM的旅游旅行管理系统（论文+PPT）

基于SSM的旅游旅行管理系统&#xff08;论文PPT&#xff09; 项目简介项目获取开发环境项目技术运行截图 项目简介 这是一个基于SSM的旅游旅行管理系统 本系统分为前台系统模块、管理员模块、用户模块以及商家模块 其中前台系统模块的权限为&#xff1a;当游客打开系统的网址后…
阅读更多...
C++ 数论相关题目 博弈论：拆分-Nim游戏

C++ 数论相关题目 博弈论：拆分-Nim游戏

给定 n 堆石子&#xff0c;两位玩家轮流操作&#xff0c;每次操作可以取走其中的一堆石子&#xff0c;然后放入两堆规模更小的石子&#xff08;新堆规模可以为 0 &#xff0c;且两个新堆的石子总数可以大于取走的那堆石子数&#xff09;&#xff0c;最后无法进行操作的人视为失…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023