怎么制作游戏u盘,兰州企业网络优化方案,wordpress数据库替换,发布建设网站本文将对PostgresSQL中的ltree进行相关概念介绍#xff0c;并以示例代码讲解ltree数据增删改查功能的实现。 作者#xff1a;后端小肥肠 目录 1.前言
2. 基础概念
2.1. ltree
2.2. lquery
2.3. ltxtquery
2.4. ltree函数及操作符
2.4.1. ltree函数
2.4.2. 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)Child1subpath(ltree, int offset, int len)ltreeltree从位置offset开始长度为len的子路径。如果offset为负则子路径开始于距离路径尾部那么远的位置。如果len为负则从路径的尾部开始丢掉那么多个标签。subpath(Top.Child1.Child2,0,2)Top.Child1subpath(ltree, int offset)ltreeltree从位置offset开始一直延伸到路径末尾的子路径。如果offset为负则子路径开始于距离路径尾部那么远的位置。subpath(Top.Child1.Child2,1)Child1.Child2nlevel(ltree)integer路径中标签的数量nlevel(Top.Child1.Child2)3index(ltree a, ltree b)integera中第一次出现b的位置如果没有找到则为 -1index(0.1.2.3.5.4.5.6.8.5.6.8,5.6)6index(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)9text2ltree(text)ltree把text转换成ltreeltree2text(ltree)text把ltree转换成textlca(ltree, ltree, ...)ltree最低公共祖先即最长的公共路径前缀最多支持 8 个参数lca(1.2.2.3,1.2.3.4.5.6)1.2lca(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祖先的第一个数组项如果没有则是 NULLltree[] ? ltreeltree是ltree祖先的第一个数组项如果没有则是 NULLltree[] ?~ lqueryltree匹配lquery的第一个数组项如果没有则是 NULLltree[] ? ltxtqueryltree匹配lquery的第一个数组项如果没有则是 NULL
操作符、、 以及~有类似的、 ^、^、^、 ^~只是它们不适用索引。它们只对测试目的有用。
3.开发环境搭建
3.1. 所用工具版本
依赖版本Spring Boot2.6.3java1.8postgres13.12 3.2. pom依赖 dependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.postgresql/groupIdartifactIdpostgresql/artifactId/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactId/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-generator/artifactId/dependencydependencygroupIdorg.freemarker/groupIdartifactIdfreemarker/artifactId/dependencydependencygroupIdorg.springframework.cloud/groupIdartifactIdspring-cloud-starter-bootstrap/artifactId/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactId/dependencydependencygroupIdcom.alibaba.fastjson2/groupIdartifactIdfastjson2/artifactIdversion2.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 resTreenew LtreeCatalogDTO(1,null,目录树,null);ListLtreeCatalogDTOresChildrennew ArrayList();ListLtreeCatalogDTOchildrenListnew ArrayList();ListLtreeCatalogDTOrootListnew ArrayList();LambdaQueryWrapperLtreeCatalog queryWrappernew LambdaQueryWrapper();ListLtreeCatalog 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 treebuildTree(childrenList,rootNode);resChildren.add(tree);}resTree.setChildren(resChildren);return resTree;}public LtreeCatalogDTO buildTree(ListLtreeCatalogDTO ltreeCatalogDTOS, LtreeCatalogDTO catalogP) {ListLtreeCatalogDTO 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;}
上述代码很简单就是根据数据表中的pathltree递归构造目录树返回给前端。 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 BaseTypeHandlerString {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(patternyyyy-MM-dd HH:mm:ss,timezoneGMT8)TableField(fill FieldFill.INSERT)private Date createTime;DateTimeFormat(pattern yyyy-MM-dd HH:mm:ss)JsonFormat(patternyyyy-MM-dd HH:mm:ss,timezoneGMT8)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(ltreeCatalogExistnull){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(ltreeCatalognull){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 中文手册
