CODEWITHSUNDEEP
javahibernatepostgresqljpajpql
Swarne27
Swarne27

Reputation: 5747

Remove/Find Nodes in hierarchical table of JPA Hibernate

I have created a table to store hierarchical data for a purpose. In LEAF column Its there to decide whether its a leaf. And PARENT_ID column is for checking which one is the parent of the node. Below is the Database Table.

CREATE TABLE info
(
  id bigint NOT NULL,
  leaf bigint,
  "name" character varying(255),
  parent_id bigint,
  "value" character varying(255),
  "info" character varying(255),
  CONSTRAINT info_pkey PRIMARY KEY (id)
)

TABLE NAME: info

ID LEAF  NAME PARENT_ID VALUE  INFO
-- ----  ---- ------    -----  ----
1   0    ROOT  0        ''     '' 
2   0     S2   1        ''     ''
3   0     S3   1        TEST1  INFO1
4   1     S4   1        TEST2  ''
5   1     S5   2        ''     ''
6   0     S6   3        ''     ''
8   1     S8   6        P8     ''

I'm using JPA Hibernate with PostgreSQL in my project, this is the only table with data. and it has no relation to any other table.

Q1) If I want to delete "S3" how should I do that, since It has "S6" as a child and "S8" is a child of "S6". So It needs to be cascaded on update when I delete "S3". How can I do this in JPQL or Hibernate NativeQuery in JAVA?

Q2) How can I find children and leaf of a specific Parent Node?

Upvotes: 0

Views: 745

Answers (1)

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

What you're looking for is a recursive common table expression (CTE).

I don't know if JPQL exposes that functionality, but a straight up native query for your situation would look like this (which gives all descendants of S3):

with recursive parents
as
(
  select "ID", "NAME", "PARENT_ID"
  from test
  where "NAME" = 'S3'

  union all

  select b."ID", b."NAME", b."PARENT_ID"
  from parents a
         join test b
           on a."ID" = b."PARENT_ID"
)
select *
from parents
;

Keep in mind that that query assumes there are no loops in your data - read through the link for hints on dealing with that situation.

Upvotes: 1

Related Questions