Recursive Query
A common requirement is to handle self-associated property.
From a database perspective, self-associated property means a table's foreign key references itself. From an object model perspective, it represents a tree.
The difficulty of self-associated properties is that the object depth is uncontrollable, theoretically infinite. Jimmer provides great support for this.
Model and Data Preparation
Define the entity interface:
- Java
- Kotlin
@Entity
public interface TreeNode {
@Id
@Column(name = "NODE_ID")
long id();
String name();
@Null
@ManyToOne
TreeNode parent();
@OneToMany(mappedBy = "parent")
List<TreeNode> childNodes();
}
@Entity
interface TreeNode {
@Id
@Column(name = "NODE_ID")
val id: Long
val name: String
@ManyToOne
val parent: TreeNode?
@OneToMany(mappedBy = "parent")
val childNodes: List<TreeNode>
}
Prepare the database:
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)
;
Unlimited Recursion
Top-down, fetch unlimited layers:
- Java
- Kotlin
TreeNodeTable node = Tables.TREE_NODE_TABLE;
List<TreeNode> treeNodes = sqlClient
.createQuery(node)
.where(node.parentId().isNull())
.select(
node.fetch(
Fetchers.TREE_NODE_FETCHER
.name()
.recursiveChildNodes()
)
)
.execute();
val treeNodes = sqlClient
.createQuery(TreeNode::class) {
where(table.parentId.isNull())
select(
table.fetchBy {
allScalarFields()
`childNodes*`()
}
)
}
.execute()
Here, recursiveChildNodes in Java code and childNodes* in Kotlin code represent recursive queries.
-
Jimmer automatically discovers self-associated properties in entities and generates such methods at compile time for users to invoke.
-
Recursive queries do not require specifying the shape of associated objects because recursive queries are very special - the shape of associated objects is the shape of the current object itself.
6 SQL statements are generated:
-
Query root nodes
select
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID is null -
Fetch layer 1
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?) -
Fetch layer 2
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?) -
Fetch layer 3
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?, ?, ?) -
Fetch layer 4
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?, ?, ?, ?, ?, ?, ?) -
Fetch layer 5 (This step does not query any data and the recursive query downstairs ends)
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?)
The printed result is (original compact, formatted for readability):
{
"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": []
}
]
}
]
}
]
}
Limited Depth
Top-down, fetch two layers:
- Java
- Kotlin
TreeNodeTable node = Tables.TREE_NODE_TABLE;
List<TreeNode> treeNodes = sqlClient
.createQuery(node)
.where(node.parentId().isNull())
.select(
node.fetch(
Fetchers.TREE_NODE_FETCHER
.name()
.recursiveChildNodes(
it -> it.depth(2)
)
)
)
.execute();
val treeNodes = sqlClient
.createQuery(TreeNode::class) {
where(table.parentId.isNull())
select(
table.fetchBy {
allScalarFields()
`childNodes*` {
depth(2)
}
}
)
}
.execute()
3 SQLs are generated:
-
Main query to fetch root nodes (layer 0)
select
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID is null -
Fetch layer 1
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?) -
Fetch layer 2
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?)
The printed result is (original compact, formatted for readability):
{
"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"}
]
}
]
}
Note that the 4 marked objects do not show childNodes as [], but lack the childNodes property altogether.
This indicates not knowing at all whether there are deeper child nodes, because the recursive process was prematurely terminated manually.
Control Recursion Per Node
Top-down, fetch unlimited layers. But for the node named "Clothing", stop recursion.
- Java
- Kotlin
TreeNodeTable node = Tables.TREE_NODE_TABLE;
List<TreeNode> treeNodes = sqlClient
.createQuery(node)
.where(node.parentId().isNull())
.select(
node.fetch(
Fetchers.TREE_NODE_FETCHER
.name()
.recursiveChildNodes(
it -> it.recursive(args ->
!args.getEntity().name().equals("Clothing")
)
)
)
)
.execute();
val treeNodes = sqlClient
.createQuery(TreeNode::class) {
where(table.parentId.isNull())
select(
table.fetchBy {
allScalarFields()
`childNodes*` {
recursive {
entity.name != "Clothing"
}
}
}
)
}
.execute()
In the above code, the parameter args of it.recursive(args) is an object providing two properties:
args.getEntity(): The current node object.args.getDepth(): The current node depth. 0 for nodes obtained directly from the main query, incrementally increases with recursion depth.- The return value decided by the user: A boolean variable that decides whether to recursively fetch for the current node.
The above code means all nodes will be recursively fetched except the Clothing node.
4 SQLs are generated:
-
Query root objects
select
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where tb_1_.PARENT_ID is null -
Fetch layer 1
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?) -
Fetch layer 2
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
/*
* Home node has two child nodes:
* "Food" and "Clothing",
*
* However, "Clothing is excluded by user,
* so `in(?)` is used here, not `in(?, ?)`
*/
tb_1_.PARENT_ID in (?) -
Fetch layer 3
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?)- Fetch layer 4
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?, ?, ?)
The printed result is (original compact, formatted for readability):
{
"id":1,
"name":"Home",
"childNodes":[
{"id":9,"name":"Clothing"},
{
"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":[]}
]
}
]
}
]
}
Note that the marked object does not show childNodes as [], but lack the childNodes property altogether.
This indicates not knowing at all whether there are deeper child nodes, because the recursive process was prematurely terminated manually.
Recursion on Multiple Properties
Jimmer supports recursive queries on multiple association properties, as below
- Java
- Kotlin
Tree treeNode = sqlClient
.findById(
Fetchers.TREE_NODE_FETCHER
.name()
.recursiveParent()
.recursiveChildNodes(),
10L
);
val treeNodes = sqlClient
.findById(
newFetcher(TreeNode::class) {
name()
`parent*`()
`childNodes*`()
}
)
This example performs recursive queries on two associated properties:
-
parent: Starting from the current node, keep querying upwards for the parent node until the root node is queried
-
childNodes: Starting from the current node, keep querying downwards for child nodes until the deepest child node is queried
When executed, the following 6 SQL statements are generated:
-
Query current node
select
tb_1_.NODE_ID,
tb_1_.NAME,
tb_1_.PARENT_ID
from TREE_NODE tb_1_
where
tb_1_.NODE_ID = ? /* 10 */ -
Query first layer parent node upstairs
select
tb_1_.NODE_ID,
tb_1_.NAME,
tb_1_.PARENT_ID
from TREE_NODE tb_1_
where
tb_1_.NODE_ID = ? /* 9 */ -
Query second layer parent node upstairs (This step will query the root node and the recursive query upstairs ends)
select
tb_1_.NODE_ID,
tb_1_.NAME,
tb_1_.PARENT_ID
from TREE_NODE tb_1_
where
tb_1_.NODE_ID = ? /* 1 */ -
Query first layer child nodes downstairs
select
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE tb_1_
where
tb_1_.PARENT_ID = ? /* 10 */ -
Query second layer child nodes downstairs
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE tb_1_
where
tb_1_.PARENT_ID in (
? /* 11 */, ? /* 15 */
) -
Query third layer child nodes downstairs (This step does not query any data and the recursive query downstairs ends)
select
tb_1_.PARENT_ID,
tb_1_.NODE_ID,
tb_1_.NAME
from TREE_NODE tb_1_
where
tb_1_.PARENT_ID in (
? /* 12 */, ? /* 13 */, ? /* 14 */, ? /* 16 */, ? /* 17 */
)
Finally, the query result is:
{
"id":10,
"name":"Woman",
"parent":{ // Recursive query upstairs
"id":9,
"name":"Clothing",
"parent":{
"id":1,
"name":"Home",
"parent":null
}
},
"childNodes":[ // Recursive query downstairs
{
"id":11,
"name":"Casual wear",
"childNodes":[
{
"id":12,
"name":"Dress",
"childNodes":[]
},
{
"id":13,
"name":"Miniskirt",
"childNodes":[]
},
{
"id":14,
"name":"Jeans",
"childNodes":[]
}
]
},
{
"id":15,
"name":"Formal wear",
"childNodes":[
{
"id":16,
"name":"Suit",
"childNodes":[]
},
{
"id":17,
"name":"Shirt",
"childNodes":[]
}
]
}
]
}
It is not difficult to see from the correct running of this example that the recursive path of the parent direction and the recursive path of the childNodes direction are completely independent.
The recursive query upstairs and downstairs will not be mixed or alternate.
Therefore, we can slightly correct the previous discussion on why recursive queries do not require specifying the associated object format:
Associated object format = All properties in the current object format that are not recursively queried (whether associated properties or not) + The currently recursively queried property (excluding other recursively queried properties)