Hibernate recursive query

Question

My desired query is to get a list of Course objects that belong to a Category. My objects are as follows:

public class Course{
     String name;
     List<Category> categories;
}

public class Category{
     String name;
     Category parent;
}

Since the categories reference each other, they can have an infinite depth:

A
  A.A
     A.A.A
     A.A.B
  A.B
     A.B.A
B
 B.A
 B.B
C

How can I query for courses within the category "A.A", and return all Courses associated with A.A, A.A.A, and A.A.B?

Answer 1

Because you do not know how deep is the tree, you can use some kind of pattern as follows

select distinct 
    c
from
    Course c
left join fetch 
    c.categories c
where
    c.name like 'A.A%'

Answer 2

If you are willing to use native SQL and your database supports recursive common table expressions (basically all major DBMS except MySQL) it's pretty easy:

WITH RECURSIVE course_tree (name) AS (
   SELECT name
   FROM course
   WHERE name = 'A.A'
   UNION ALL
   SELECT name
   FROM course
   WHERE parent_id = course_tree.id
)
SELECT *
FROM course_tree