How to use CTE expression WITH clause in Spring Data JPA

Question

I want to write a recursive query in SQL Server. So I am using CTE. Query is working properly in SSMS but when I am trying to use the same CTE in JPA as native SQL it gives an error:

Invalid name column Id.

The entity (which I am using in CTE to fetch data recursively) has @Id @Column(name="pk_id") private int Id field.

I also followed this SOQ : cte sql to simple subset query for JPA

But still getting error as invalid name column Id.

I have never used CTE before. How can this be fixed?

Answer 1

You can write the SQL query in JPA Repositories since the @Query annotation takes native query as well. For that, you need to specify the SQL query in the value parameter and nativeQuery is true as follow.

You can write CTE queries as well.

public interface ISomeRepository extends JpaRepository<Entity, Long> {


    @Query(value = "SQL QUERY NEED TO BE WRITTEN HERE", nativeQuery = true)
    List<Long> getEntityIds();
}