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

2024-02-02 18:52
文章标签 java 数据 实现 springboot spring 系列 简介 改查 增删 postgressql ltree

本文主要是介绍【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 中文手册

这篇关于【PostgresSQL系列】 ltree简介及基于SpringBoot实现 ltree数据增删改查的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/671643。

相关文章

C++使用栈实现括号匹配的代码详解

C++使用栈实现括号匹配的代码详解

《C++使用栈实现括号匹配的代码详解》在编程中,括号匹配是一个常见问题,尤其是在处理数学表达式、编译器解析等任务时,栈是一种非常适合处理此类问题的数据结构,能够精确地管理括号的匹配问题,本文将通过C+... 目录引言问题描述代码讲解代码解析栈的状态表示测试总结引言在编程中，括号匹配是一个常见问题，尤其是在
阅读更多...
Java实现检查多个时间段是否有重合

Java实现检查多个时间段是否有重合

《Java实现检查多个时间段是否有重合》这篇文章主要为大家详细介绍了如何使用Java实现检查多个时间段是否有重合,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录流程概述步骤详解China编程步骤1：定义时间段类步骤2：添加时间段步骤3：检查时间段是否有重合步骤4：输出结果示例代码结语作
阅读更多...
Java中String字符串使用避坑指南

Java中String字符串使用避坑指南

《Java中String字符串使用避坑指南》Java中的String字符串是我们日常编程中用得最多的类之一,看似简单的String使用,却隐藏着不少“坑”,如果不注意,可能会导致性能问题、意外的错误容... 目录8个避坑点如下：1. 字符串的不可变性：每次修改都创建新对象2. 使用 == 比较字符串，陷阱满
阅读更多...
Java判断多个时间段是否重合的方法小结

Java判断多个时间段是否重合的方法小结

《Java判断多个时间段是否重合的方法小结》这篇文章主要为大家详细介绍了Java中判断多个时间段是否重合的方法,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录判断多个时间段是否有间隔判断时间段集合是否与某时间段重合判断多个时间段是否有间隔实体类内容public class D
阅读更多...
使用C++实现链表元素的反转

使用C++实现链表元素的反转

《使用C++实现链表元素的反转》反转链表是链表操作中一个经典的问题,也是面试中常见的考题,本文将从思路到实现一步步地讲解如何实现链表的反转,帮助初学者理解这一操作,我们将使用C++代码演示具体实现,同... 目录问题定义思路分析代码实现带头节点的链表代码讲解其他实现方式时间和空间复杂度分析总结问题定义给定
阅读更多...
IDEA编译报错“java: 常量字符串过长”的原因及解决方法

IDEA编译报错“java: 常量字符串过长”的原因及解决方法

《IDEA编译报错“java:常量字符串过长”的原因及解决方法》今天在开发过程中,由于尝试将一个文件的Base64字符串设置为常量,结果导致IDEA编译的时候出现了如下报错java:常量字符串过长,... 目录一、问题描述二、问题原因2.1 理论角度2.2 源码角度三、解决方案解决方案①：StringBui
阅读更多...
Java覆盖第三方jar包中的某一个类的实现方法

Java覆盖第三方jar包中的某一个类的实现方法

《Java覆盖第三方jar包中的某一个类的实现方法》在我们日常的开发中,经常需要使用第三方的jar包,有时候我们会发现第三方的jar包中的某一个类有问题,或者我们需要定制化修改其中的逻辑,那么应该如何... 目录一、需求描述二、示例描述三、操作步骤四、验证结果五、实现原理一、需求描述需求描述如下：需要在
阅读更多...
Java中ArrayList和LinkedList有什么区别举例详解

Java中ArrayList和LinkedList有什么区别举例详解

《Java中ArrayList和LinkedList有什么区别举例详解》：本文主要介绍Java中ArrayList和LinkedList区别的相关资料,包括数据结构特性、核心操作性能、内存与GC影... 目录一、底层数据结构二、核心操作性能对比三、内存与 GC 影响四、扩容机制五、线程安全与并发方案六、工程
阅读更多...
JavaScript中的reduce方法执行过程、使用场景及进阶用法

JavaScript中的reduce方法执行过程、使用场景及进阶用法

《JavaScript中的reduce方法执行过程、使用场景及进阶用法》：本文主要介绍JavaScript中的reduce方法执行过程、使用场景及进阶用法的相关资料,reduce是JavaScri... 目录1. 什么是reduce2. reduce语法2.1 语法2.2 参数说明3. reduce执行过程
阅读更多...
如何使用Java实现请求deepseek

如何使用Java实现请求deepseek

《如何使用Java实现请求deepseek》这篇文章主要为大家详细介绍了如何使用Java实现请求deepseek功能,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1.deepseek的api创建2.Java实现请求deepseek2.1 pom文件2.2 json转化文件2.2
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2