Hibernate recursive query

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

H开发者_如何学编程ow 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?

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

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%'