How you can create hierarchies of Java objects from flat lists with Collector

Sometimes, you need to write a SQL question and fetch a hierarchy of knowledge, whose flat illustration could seem like this:

SELECT id, parent_id, label
FROM t_directory;

The end result could be:

|id |parent_id|label              |
|---|---------|-------------------|
|1  |         |C:                 |
|2  |1        |eclipse            |
|3  |2        |configuration      |
|4  |2        |dropins            |
|5  |2        |options           |
|7  |2        |plugins            |
|8  |2        |readme             |
|9  |8        |readme_eclipse.html|
|10 |2        |src                |
|11 |2        |eclipse.exe        |

Get the hierarchy with SQL

Now, you can run a recursive PostgreSQL question just like the under monster to show that right into a JSON doc:

WITH RECURSIVE
  d1 (id, parent_id, identify) as (
    SELECT id, parent_id, label
    FROM t_directory
  ),
  d2 AS (
    SELECT d1.*, 0 AS degree
    FROM d1
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d1.*, d2.degree + 1
    FROM d1
    JOIN d2 ON d2.id = d1.parent_id
  ),
  d3 AS (
    SELECT d2.*, null::jsonb kids
    FROM d2
    WHERE degree = (SELECT max(degree) FROM d2)
    UNION (
      SELECT 
        (branch_parent).*, 
        jsonb_strip_nulls(
          jsonb_agg(branch_child - 'parent_id' - 'degree' 
            ORDER BY branch_child->>'identify'
          ) FILTER (
            WHERE branch_child->>'parent_id' = (branch_parent).id::textual content
          )
        )
      FROM (
        SELECT
          branch_parent,
          to_jsonb(branch_child) AS branch_child
        FROM d2 branch_parent
        JOIN d3 branch_child 
          ON branch_child.degree = branch_parent.degree + 1
      ) department
      GROUP BY branch_parent
    )
  )
SELECT 
  jsonb_pretty(jsonb_agg(to_jsonb(d3) - 'parent_id' - 'degree')) AS tree
FROM d3
WHERE degree = 0;

I’ve given this question additionally as a solution to this Stack Overflow query. Some inspiration for the question on this weblog submit.

And behold, now we have a JSON tree:

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

However that’s fairly a beast of a SQL question, and maybe, you don’t want to do that with SQL within the first place.

Doing this with jOOQ 3.19

The truth is, ranging from jOOQ 3.19 and #12341, you are able to do this solely with jOOQ, utilizing a Collector.

Assuming you may have this shopper aspect illustration to your information:

report File(int id, String identify, Listing kids) {}

Now, you may write:

Listing end result =
ctx.choose(T_DIRECTORY.ID, T_DIRECTORY.PARENT_ID, T_DIRECTORY.LABEL)
   .from(T_DIRECTORY)
   .orderBy(T_DIRECTORY.ID)
   .gather(Information.intoHierarchy(
       r -> r.value1(),
       r -> r.value2(),
       r -> new File(r.value1(), r.value3(), new ArrayList<>()),
       (p, c) -> p.kids().add(c)
   ));

That’s it! While you print the end result, you’ll get:

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

Or, if you happen to want JSON output, simply use Jackson, or no matter, to serialise your information as follows:

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

And now, you’re getting:

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

Very cool, huh?

Don’t use jOOQ? No downside, simply copy this Collector:

The above isn’t actually jOOQ particular magic. You’ll be able to simply copy the next Collector from jOOQ to realize the identical factor together with your pure Java code:

public static ultimate 
Collector> intoHierarchy(
    Operate tremendous R, ? extends Okay> keyMapper,
    Operate tremendous R, ? extends Okay> parentKeyMapper,
    Operate tremendous R, ? extends E> nodeMapper,
    BiConsumer tremendous E, ? tremendous E> parentChildAppender
) {
    return Collectors.collectingAndThen(
        Collectors.toMap(keyMapper, r -> new SimpleImmutableEntry(
            r, nodeMapper.apply(r)
        )),
        m -> {
            Listing r = new ArrayList<>();

            m.forEach((ok, v) -> {
                Entry dad or mum = m.get(
                    parentKeyMapper.apply(v.getKey())
                );

                if (dad or mum != null)
                    parentChildAppender.settle for(
                        dad or mum.getValue(), v.getValue()
                    );
                else
                    r.add(v.getValue());
            });

            return r;
        }
    );
}

With this collector, and the next sorts / information:

report Flat(int id, int parentId, String identify) {}
report Hierarchical(int id, String identify, Listing kids) {}

Listing information = Listing.of(
    new Flat(1, 0, "C:"),
    new Flat(2, 1, "eclipse"),
    new Flat(3, 2, "configuration"),
    new Flat(4, 2, "dropins"),
    new Flat(5, 2, "options"),
    new Flat(7, 2, "plugins"),
    new Flat(8, 2, "readme"),
    new Flat(9, 8, "readme_eclipse.html"),
    new Flat(10, 2, "src"),
    new Flat(11, 2, "eclipse.exe")
);

Now you can create the identical hierarchy once more, utilizing the Collector straight on the listing:

Listing end result =
information.stream().gather(intoHierarchy(
    e -> e.id(),
    e -> e.parentId(),
    e -> new Hierarchical(e.id(), e.identify(), new ArrayList<>()),
    (p, c) -> p.kids().add(c)
));

Another API

A earlier model of this weblog submit used another API design for the Collector:

public static ultimate  Collector> intoHierarchy(
    Operate tremendous R, ? extends Okay> keyMapper,
    Operate tremendous R, ? extends Okay> parentKeyMapper,
    BiFunction tremendous R, ? tremendous Listing, ? extends E> recordMapper
) {
    report Tuple3(T1 t1, T2 t2, T3 t3) {}
    return Collectors.collectingAndThen(
        Collectors.toMap(keyMapper, r -> {
            Listing e = new ArrayList<>();
            return new Tuple3(r, e, recordMapper.apply(r, e));
        }),
        m -> {
            Listing r = new ArrayList<>();

            m.forEach((ok, v) -> {
                Okay dad or mum = parentKeyMapper.apply(v.t1());
                E little one = v.t3();

                if (m.containsKey(dad or mum))
                    m.get(dad or mum).t2().add(little one);
                else
                    r.add(little one);
            });

            return r;
        }
    );
}

This could result in extra compact usages in shopper code:

Listing end result =
information.stream().gather(intoHierarchy(
    e -> e.id(),
    e -> e.parentId(),
    (e, l) -> new Hierarchical(e.id(), e.identify(), l)
));

Nonetheless, it depends on kind inference of the goal kind (see JEP 101). As quickly as you don’t trace the goal kind anymore, inference falls appart, so this received’t compile:

Listing> end result =
information.stream().gather(intoHierarchy(
    e -> e.id(),
    e -> e.parentId(),
    (e, l) -> new Hierarchical(e.id(), e.identify(), l)
));

This design can be fairly impractical for customers, particularly when writing advanced jOOQ queries, so it was rejected.

A extra advanced jOOQ instance

In jOOQ, all outcomes, together with nested collections (e.g. these produced by MULTISET) could be collected, so when you have a nested hierarchy, corresponding to feedback on a weblog submit, simply gather them with jOOQ.

Assuming this schema:

CREATE TABLE submit (
  id INT PRIMARY KEY,
  title TEXT
);

CREATE TABLE remark (
  id INT PRIMARY KEY,
  parent_id INT REFERENCES remark,
  post_id INT REFERENCES submit,
  textual content TEXT
);

INSERT INTO submit 
VALUES
  (1, 'Helo'),
  (2, 'World');
  
INSERT INTO remark
VALUES 
  (1, NULL, 1, 'You misspelled "Hi there"'),
  (2, 1, 1, 'Thanks, will repair quickly'),
  (3, 2, 1, 'Nonetheless not fastened'),
  (4, NULL, 2, 'Impeccable weblog submit, thanks');

You could possibly write a question like this:

report Publish(int id, String title, Listing feedback) {}
report Remark(int id, String textual content, Listing replies) {}

Listing end result =
ctx.choose(
       POST.ID, 
       POST.TITLE,
       multiset(
           choose(COMMENT.ID, COMMENT.PARENT_ID, COMMENT.TEXT)
           .from(COMMENT)
           .the place(COMMENT.POST_ID.eq(POST.ID))
       ).convertFrom(r -> r.gather(intoHierarchy(
           r -> r.value1(),
           r -> r.value2(),
           r -> new Remark(r.value1(), r.value3(), new ArrayList<>()),
           (p, c) -> p.replies().add(c)
       )))
   )
   .from(POST)
   .orderBy(POST.ID)
   .fetch(mapping(Publish::new));

All of that is type-safe, as at all times with jOOQ!

Now, try what this prints, when serialised with Jackson:

[ {
  "id" : 1,
  "title" : "Helo",
  "comments" : [ {
    "id" : 1,
    "text" : "You misspelled "Hello"",
    "replies" : [ {
      "id" : 2,
      "text" : "Thanks, will fix soon",
      "replies" : [ {
        "id" : 3,
        "text" : "Still not fixed"
      } ]
    } ]
  } ]
}, {
  "id" : 2,
  "title" : "World",
  "feedback" : [ {
    "id" : 4,
    "text" : "Impeccable blog post, thanks"
  } ]
} ]

Be aware, if you happen to solely need to present a subtree, or a tree up till a sure depth, you may nonetheless run a hierarchical question in your MULTISET subquery utilizing WITH RECURSIVE or CONNECT BY.

Conclusion

Collector is a a lot underrated API within the JDK. Any JDK Assortment could be changed into a Stream and its parts could be collected. In jOOQ, a ResultQuery is an Iterable, which additionally presents a handy gather() technique (it simply executes the question, streams outcomes, and collects data into your Collector).

Our practical library jOOλ has many extra collectors in its Agg class, e.g. for:

Bitwise aggregation
Statistical aggregation, like commonplace deviation, correlation, percentiles, and so forth.

Amassing issues right into a hierarchy isn’t actually that particular. It’s simply one other collector, which I’m certain, you’ll be utilizing way more continuously any longer!