final HibernateCriteriaBuilder builder = session.getCriteriaBuilder();
final var query = builder.createTupleQuery();
final var nonRecurQuery = builder.createTupleQuery();
final var nonRecurRoot = nonRecurQuery.from(Account.class);
nonRecurQuery.multiselect(
nonRecurRoot.get(Account_.id).alias("id"),
nonRecurRoot.get(Account_.name).alias("name"),
nonRecurRoot
.get(Account_.parent)
.get(Account_.id)
.alias("parent_id"));
nonRecurQuery.where(
builder.like(
builder.upper(nonRecurRoot.get(Account_.name)), "%LIABILITY%"));
final var accountChainTable =
query.withRecursiveUnionAll(
nonRecurQuery,
(cte) -> {
final var innerQuery = builder.createTupleQuery();
final var accountRoot = innerQuery.from(Account.class);
final var cteRoot = innerQuery.from(cte);
innerQuery.multiselect(
accountRoot.get(Account_.id),
accountRoot.get(Account_.name),
accountRoot.get(Account_.parent).get(Account_.id));
innerQuery.where(
builder.equal(
accountRoot.get(Account_.id),
cteRoot.get("parent_id")));
return innerQuery;
});
final var root = query.from(accountChainTable);
query.multiselect(
root.get("id"), root.get("name"), root.get("parent_id"));
for (final var tuple : session.createQuery(query).list()) {
log.info("=== Account:");
for (final var item : tuple.toArray()) {
log.info(" - item: {}", item);
}
}