首发于Enaium的集体博客
本文是对Jimmer文档中对象抓取器-自关联递归抓取局部的介绍,之后会比照MyBatisPlus的查问自关联表的能力。
对象抓取器是 jimmer-sql 一个十分弱小的特色，具备可媲美 GraphQL 的能力。
即便用户不采纳任何 GraphQL 相干的技术栈，也能在 SQL 查问层面失去和 GraphQL 类似的对象图查问能力。
筹备数据库和实体类
create table tree_node( node_id bigint not null, name varchar(20) not null, parent_id bigint);alter table tree_node add constraint pk_tree_node primary key(node_id);alter table tree_node add constraint uq_tree_node unique(parent_id, name);alter table tree_node add constraint fk_tree_node__parent foreign key(parent_id) references tree_node(node_id);insert into tree_node( node_id, name, parent_id) values (1, 'Home', null), (2, 'Food', 1), (3, 'Drinks', 2), (4, 'Coca Cola', 3), (5, 'Fanta', 3), (6, 'Bread', 2), (7, 'Baguette', 6), (8, 'Ciabatta', 6), (9, 'Clothing', 1), (10, 'Woman', 9), (11, 'Casual wear', 10), (12, 'Dress', 11), (13, 'Miniskirt', 11), (14, 'Jeans', 11), (15, 'Formal wear', 10), (16, 'Suit', 15), (17, 'Shirt', 15), (18, 'Man', 9), (19, 'Casual wear', 18), (20, 'Jacket', 19), (21, 'Jeans', 19), (22, 'Formal wear', 18), (23, 'Suit', 22), (24, 'Shirt', 22);@Entitypublic interface TreeNode { @Id @Column(name = "NODE_ID") long id(); String name(); @Null @ManyToOne TreeNode parent(); @OneToMany(mappedBy = "parent") List<TreeNode> childNodes();}指定查问的深度
咱们能够看到，这是一个自关联的表，每个节点都有一个父节点，也能够有多个子节点。
应用 Jimmer 的Fetcher性能，咱们能够很容易的查问出这个表的所有节点,并且能够很容易的管制查问的深度,还有条件查问。
TreeNodeTable node = TreeNodeTable.$;List<TreeNode> treeNodes = sqlClient .createQuery(node)//创立一个查问 .where(node.parent().isNull())//查问条件,这里查问出所有的根节点,也就是parent_id为null的节点 .select(//查问的字段 node.fetch( TreeNodeFetcher.$ .name()//查问节点的名称 .childNodes( TreeNodeFetcher.$.name(),//查问子节点的名称 it -> it.depth(2)//查问子节点的深度,这里查问2层 ) ) ) .execute();如果你应用Kotlin,那么你能够这样写
val treeNodes = sqlClient .createQuery(TreeNode::class) { where(table.parent.isNull())//查问条件,这里查问出所有的根节点,也就是parent_id为null的节点 select( table.fetchBy { allScalarFields()//查问节点的所有字段 childNodes({ depth(2)//查问子节点的深度,这里查问2层 }) { allScalarFields()//查问子节点的所有字段 } } ) } .execute()生成的 SQL 语句
第 0 层
select tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID is null第 1 层
select tb_1_.PARENT_ID, tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID in (?)第 2 层
select tb_1_.PARENT_ID, tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID in (?, ?)查问的后果
{ "id": 1, "name": "Home", "childNodes": [ { "id": 9, "name": "Clothing", "childNodes": [ { "id": 18, "name": "Man" }, { "id": 10, "name": "Woman" } ] }, { "id": 2, "name": "Food", "childNodes": [ { "id": 6, "name": "Bread" }, { "id": 3, "name": "Drinks" } ] } ]}查问有限层级的树
如果你想查问有限层级的树,那么你能够这样写
TreeNodeTable node = TreeNodeTable.$;List<TreeNode> treeNodes = sqlClient .createQuery(node) .where(node.parent().isNull()) .select( node.fetch( TreeNodeFetcher.$ .name() .childNodes( TreeNodeFetcher.$.name(), it -> it.recursive()//查问有限层级的树,这里不须要指定深度,也就是把depth()办法去掉换成recursive()办法 ) ) ) .execute();val treeNodes = sqlClient .createQuery(TreeNode::class) { where(table.parent.isNull()) select( table.fetchBy { allScalarFields() childNodes({ recursive() }) { allScalarFields() } } ) } .execute()生成的 SQL 语句
第 0 层
select tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID is null第 1 层
select tb_1_.PARENT_ID, tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID in (?)第 2 层
select tb_1_.PARENT_ID, tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID in (?, ?)第 3 层
select tb_1_.PARENT_ID, tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID in (?, ?, ?, ?)第 4 层
select tb_1_.PARENT_ID, tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID in (?, ?, ?, ?, ?, ?, ?, ?)第 5 层
select tb_1_.PARENT_ID, tb_1_.NODE_ID, tb_1_.NAMEfrom TREE_NODE as tb_1_where tb_1_.PARENT_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?)查问后果
{ "id": 1, "name": "Home", "childNodes": [ { "id": 9, "name": "Clothing", "childNodes": [ { "id": 18, "name": "Man", "childNodes": [ { "id": 19, "name": "Casual wear", "childNodes": [ { "id": 20, "name": "Jacket", "childNodes": [] }, { "id": 21, "name": "Jeans", "childNodes": [] } ] }, { "id": 22, "name": "Formal wear", "childNodes": [ { "id": 24, "name": "Shirt", "childNodes": [] }, { "id": 23, "name": "Suit", "childNodes": [] } ] } ] }, { "id": 10, "name": "Woman", "childNodes": [ { "id": 11, "name": "Casual wear", "childNodes": [ { "id": 12, "name": "Dress", "childNodes": [] }, { "id": 14, "name": "Jeans", "childNodes": [] }, { "id": 13, "name": "Miniskirt", "childNodes": [] } ] }, { "id": 15, "name": "Formal wear", "childNodes": [ { "id": 17, "name": "Shirt", "childNodes": [] }, { "id": 16, "name": "Suit", "childNodes": [] } ] } ] } ] }, { "id": 2, "name": "Food", "childNodes": [ { "id": 6, "name": "Bread", "childNodes": [ { "id": 7, "name": "Baguette", "childNodes": [] }, { "id": 8, "name": "Ciabatta", "childNodes": [] } ] }, { "id": 3, "name": "Drinks", "childNodes": [ { "id": 4, "name": "Coca Cola", "childNodes": [] }, { "id": 5, "name": "Fanta", "childNodes": [] } ] } ] } ]}每个查问的节点是否递归
如果你想每个查问的节点是否递归,那么你能够这样写
TreeNodeTable node = TreeNodeTable.$;List<TreeNode> treeNodes = sqlClient .createQuery(node) .where(node.parent().isNull()) .select( node.fetch( TreeNodeFetcher.$ .name() .childNodes( TreeNodeFetcher.$.name(), it -> it.recursive(args -> !args.getEntity().name().equals("Clothing")//每个查问的节点是否递归,这里能够依据实体的属性来判断是否递归 ) ) ) ) .execute();val treeNodes = sqlClient .createQuery(TreeNode::class) { where(table.parent.isNull()) select( table.fetchBy { allScalarFields() childNodes({ recursive { entity.name != "Clothing"//每个查问的节点是否递归,这里能够依据实体的属性来判断是否递归 } }) { allScalarFields() } } ) } .execute()这样就能够实现每个查问的节点是否递归了
应用 MybatisPlus 来查问树形构造
定义实体
@Data@TableName("tree_node")public class TreeNode { @TableId private Long nodeId; private String name; @TableField(exist = false) private List<TreeNode> childNodes;}定义 Mapper
@Mapperpublic interface TreeNodeMapper extends BaseMapper<TreeNode> {}查问树形构造的 Service
@Service@AllArgsConstructorpublic class TreeNodeService { private final TreeNodeMapper treeNodeMapper; public List<TreeNode> getTree() { // 查问根节点列表 List<TreeNode> rootNodes = selectRoots(); // 遍历根节点，递归查问每个节点的子孙节点 for (TreeNode rootNode : rootNodes) { this.getChildren(rootNode); } return rootNodes; } private void getChildren(TreeNode node) { // 查问子节点 List<TreeNode> children = selectByParentId(node.getNodeId()); // 遍历子节点，递归查问子节点的子孙节点 for (TreeNode child : children) { this.getChildren(child); } node.setChildNodes(children); } public List<TreeNode> selectRoots() { QueryWrapper<TreeNode> wrapper = new QueryWrapper<>(); wrapper.isNull("parent_id");// 查问根节点，parent_id为null return treeNodeMapper.selectList(wrapper); } public List<TreeNode> selectByParentId(Long parentId) { QueryWrapper<TreeNode> wrapper = new QueryWrapper<>(); wrapper.eq("parent_id", parentId);// 查问子节点，parent_id为以后节点的id return treeNodeMapper.selectList(wrapper); }}查问后果
{ "nodeId": 1, "name": "Home", "childNodes": [ { "nodeId": 9, "name": "Clothing", "childNodes": [ { "nodeId": 18, "name": "Man", "childNodes": [ { "nodeId": 19, "name": "Casual wear", "childNodes": [ { "nodeId": 20, "name": "Jacket", "childNodes": [] }, { "nodeId": 21, "name": "Jeans", "childNodes": [] } ] }, { "nodeId": 22, "name": "Formal wear", "childNodes": [ { "nodeId": 24, "name": "Shirt", "childNodes": [] }, { "nodeId": 23, "name": "Suit", "childNodes": [] } ] } ] }, { "nodeId": 10, "name": "Woman", "childNodes": [ { "nodeId": 11, "name": "Casual wear", "childNodes": [ { "nodeId": 12, "name": "Dress", "childNodes": [] }, { "nodeId": 14, "name": "Jeans", "childNodes": [] }, { "nodeId": 13, "name": "Miniskirt", "childNodes": [] } ] }, { "nodeId": 15, "name": "Formal wear", "childNodes": [ { "nodeId": 17, "name": "Shirt", "childNodes": [] }, { "nodeId": 16, "name": "Suit", "childNodes": [] } ] } ] } ] }, { "nodeId": 2, "name": "Food", "childNodes": [ { "nodeId": 6, "name": "Bread", "childNodes": [ { "nodeId": 7, "name": "Baguette", "childNodes": [] }, { "nodeId": 8, "name": "Ciabatta", "childNodes": [] } ] }, { "nodeId": 3, "name": "Drinks", "childNodes": [ { "nodeId": 4, "name": "Coca Cola", "childNodes": [] }, { "nodeId": 5, "name": "Fanta", "childNodes": [] } ] } ] } ]}查问树形构造的 Service 并指定查问深度
@Service@AllArgsConstructorpublic class TreeNodeService { private final TreeNodeMapper treeNodeMapper; public List<TreeNode> getTree(int depth) { // 查问根节点列表 List<TreeNode> rootNodes = selectRoots(); // 遍历根节点，递归查问每个节点的子孙节点 for (TreeNode rootNode : rootNodes) { this.getChildren(rootNode, depth, 0); } return rootNodes; } private void getChildren(TreeNode node, int maxDepth, int currentDepth) { if (currentDepth >= maxDepth) { // 以后深度达到最大深度，终止递归并返回后果 node.setChildNodes(Collections.emptyList()); return; } // 查问子节点 List<TreeNode> children = selectByParentId(node.getNodeId()); // 遍历子节点，递归查问子节点的子孙节点 for (TreeNode child : children) { this.getChildren(child, maxDepth, currentDepth + 1); } node.setChildNodes(children); } public List<TreeNode> selectRoots() { QueryWrapper<TreeNode> wrapper = new QueryWrapper<>(); wrapper.isNull("parent_id"); return treeNodeMapper.selectList(wrapper); } public List<TreeNode> selectByParentId(Long parentId) { QueryWrapper<TreeNode> wrapper = new QueryWrapper<>(); wrapper.eq("parent_id", parentId); return treeNodeMapper.selectList(wrapper); }}查问后果
{ "nodeId": 1, "name": "Home", "childNodes": [ { "nodeId": 9, "name": "Clothing", "childNodes": [ { "nodeId": 18, "name": "Man", "childNodes": [] }, { "nodeId": 10, "name": "Woman", "childNodes": [] } ] }, { "nodeId": 2, "name": "Food", "childNodes": [ { "nodeId": 6, "name": "Bread", "childNodes": [] }, { "nodeId": 3, "name": "Drinks", "childNodes": [] } ] } ]}查问树形构造的 Service 并指定查问深度和查问条件
不好意思，这个性能我还没想好怎么用 MybatisPlus 实现，所以这里就不写了。
总结
这么一比照，应用MybatisPlus的代码量的确多了不少并且很简单,又是递归又是递归计数等等,而Jimmer应用了Fetcher就会更容易的查出所有多层节点,并且代码量也非常少