本文将对PostgresSQL中的ltree进行相关概念介绍,并以示例代码讲解ltree数据增删改查功能的实现。
作者:后端小肥肠
目录
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) |
ltree | ltree的从位置start到位置end-1(从 0 开始计)的子路径 | subltree('Top.Child1.Child2',1,2) | Child1 |
subpath(ltree, int offset, int len) |
ltree | ltree从位置offset开始长度为len的子路径。如果offset为负,则子路径开始于距离路径尾部那么远的位置。如果len为负,则从路径的尾部开始丢掉那么多个标签。 | subpath('Top.Child1.Child2',0,2) | Top.Child1 |
subpath(ltree, int offset) |
ltree | ltree从位置offset开始一直延伸到路径末尾的子路径。如果offset为负,则子路径开始于距离路径尾部那么远的位置。 | subpath('Top.Child1.Child2',1) | Child1.Child2 |
nlevel(ltree) |
integer | 路径中标签的数量 | nlevel('Top.Child1.Child2') | 3 |
index(ltree a, ltree b) |
integer | a中第一次出现b的位置,如果没有找到则为 -1 | index('0.1.2.3.5.4.5.6.8.5.6.8','5.6') | 6 |
index(ltree a, ltree b, int offset) |
integer | a中第一次出现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 @> ltree | boolean | 左参数是不是右参数的一个祖先(或者相等)? |
ltree <@ ltree | boolean | 左参数是不是右参数的一个后代(或者相等)? |
ltree ~ lquery | boolean | ltree匹配lquery吗? |
lquery ~ ltree | boolean | ltree匹配lquery吗? |
ltree ? lquery[] | boolean | ltree匹配数组中的任意lquery吗? |
lquery[] ? ltree | boolean | ltree匹配数组中的任意lquery吗? |
ltree @ ltxtquery | boolean | ltree匹配ltxtquery吗? |
ltxtquery @ ltree | boolean | ltree匹配ltxtquery吗? |
ltree || ltree | ltree | 串接ltree路径 |
ltree || text | ltree | 把文本转换成ltree并且串接 |
text || ltree | ltree | 把文本转换成ltree并且串接 |
ltree[] @> ltree | boolean | 数组是否包含ltree的一个祖先? |
ltree <@ ltree[] | boolean | 数组是否包含ltree的一个祖先? |
ltree[] <@ ltree | boolean | 数组是否包含ltree的一个后代? |
ltree @> ltree[] | boolean | 数组是否包含ltree的一个后代? |
ltree[] ~ lquery | boolean | 数组是否包含匹配lquery的路径? |
lquery ~ ltree[] | boolean | 数组是否包含匹配lquery的路径? |
ltree[] ? lquery[] | boolean | ltree数组是否包含匹配任意lquery的路径? |
lquery[] ? ltree[] | boolean | ltree数组是否包含匹配任意lquery的路径? |
ltree[] @ ltxtquery | boolean | 数组是否包含匹配ltxtquery的路径? |
ltxtquery @ ltree[] | boolean | 数组是否包含匹配ltxtquery的路径? |
ltree[] ?@> ltree | ltree | 是ltree祖先的第一个数组项;如果没有则是 NULL |
ltree[] ?<@ ltree | ltree | 是ltree祖先的第一个数组项;如果没有则是 NULL |
ltree[] ?~ lquery | ltree | 匹配lquery的第一个数组项;如果没有则是 NULL |
ltree[] ?@ ltxtquery | ltree | 匹配lquery的第一个数组项;如果没有则是 NULL |
操作符<@、@>、 @以及~有类似的、 ^<@、^@>、^@、 ^~,只是它们不适用索引。它们只对测试目的有用。
3.开发环境搭建
3.1. 所用工具版本
依赖 | 版本 |
---|---|
Spring Boot | 2.6.3 |
java | 1.8 |
postgres | 13.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> {
@Override
public 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);
}
@Override
public String getNullableResult(ResultSet resultSet, String s) throws SQLException {
return resultSet.getString(s);
}
@Override
public String getNullableResult(ResultSet resultSet, int i) throws SQLException {
return resultSet.getString(i);
}
@Override
public 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;
@Version
private Integer version;
}
发送请求:
4.3. 更新目录树节点
1.controller层
@PutMapping("")
public String updateCatalog(@RequestBody LtreeCatalog ltreeCatalog) throws Exception {
return ltreeCatalogService.updateCatalog(ltreeCatalog);
}
2. service方法
@Override
public 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操作符中的<@来实现:
@Override
public 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
提供了一种有力的工具,可以应对各种层次结构的数据建模需求。