Creating hierarchies of Java objects from flat lists using Collector

Occasionally, you may need to fetch a hierarchy of data using a SQL query. The flat representation of the data may look like this:

“`
SELECT id, parent_id, label FROM t_directory;
“`

The query result would be:

“`
|id |parent_id|label |
|—|———|—————–|
|1 | |C: |
|2 |1 |eclipse |
|3 |2 |configuration |
|4 |2 |dropins |
|5 |2 |features |
|7 |2 |plugins |
|8 |2 |readme |
|9 |8 |readme_eclipse.html|
|10 |2 |src |
|11 |2 |eclipse.exe |
“`

To convert the flat representation of data into a JSON tree, you had to run a recursive PostgreSQL query. However, with jOOQ 3.19, you can achieve this entirely using jOOQ by using a Collector. Here is an example:

“`
List result = ctx.select(T_DIRECTORY.ID, T_DIRECTORY.PARENT_ID, T_DIRECTORY.LABEL)
.from(T_DIRECTORY)
.orderBy(T_DIRECTORY.ID)
.collect(Records.intoHierarchy(
r -> r.value1(),
r -> r.value2(),
r -> new File(r.value1(), r.value3(), new ArrayList<>()),
(p, c) -> p.children().add(c)
));
“`

You can now use Jackson to serialize your data as follows:

“`
new ObjectMapper()
.writerWithDefaultPrettyPrinter()
.writeValue(System.out, result);
“`

The output will be:

“`
[
{
“id”: 1,
“name”: “C:”,
“children”: [
{
“id”: 2,
“name”: “eclipse”,
“children”: [
{
“id”: 3,
“name”: “configuration”,
“children”: []
},
{
“id”: 4,
“name”: “dropins”,
“children”: []
},
{
“id”: 5,
“name”: “features”,
“children”: []
},
{
“id”: 7,
“name”: “plugins”,
“children”: []
},
{
“id”: 8,
“name”: “readme”,
“children”: [
{
“id”: 9,
“name”: “readme_eclipse.html”,
“children”: []
}
]
},
{
“id”: 10,
“name”: “src”,
“children”: []
},
{
“id”: 11,
“name”: “eclipse.exe”,
“children”: []
}
]
}
]
}
]
“`

Source link