mysql - How to implement CTE query via HQL in Java

Question

I have written a MySQL query and I'm trying to implement via Hibernate. Please help us to resolve this. My MySQL CTE query:

WITH RECURSIVE employeeCte AS
(
    select  employee_id, employee_name, manager_id, 1 AS LEVEL from employee where employee_id = ?
    union all
    select  e.employee_id, e.employee_name, e.manager_id, LEVEL + 1 from employee  e join employeeCte m
    on e.employee_id = m.manager_id
)
select * from employeeCte;

Hibernate code:

interface Employeerepository extends JpaRepository<Employee, String>{
  @Query("above query")
  List<Object> getEMployeeDetails(String employeeId);
}

question from:https://stackoverflow.com/questions/65859752/how-to-implement-cte-query-via-hql-in-java

Answer 1

CTEs are not yet supported in HQL, but I'm working on changing this for 6.0. In the meantime, you can use the native query approach as suggested by Mark Rotteveel, or you take a look at what Blaze-Persistence can do for you here, which supports CTEs and also comes with a Spring Data integration.