3

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?

Sivaram Rasathurai
  • 5,533
  • 3
  • 22
  • 45
bee
  • 117
  • 2
  • 14
  • 2
    CTE doesn't work with JPQL, you need to use the SQL instead. – M. Deinum Apr 19 '21 at 05:17
  • How to use recursive SQL but ? – bee Apr 19 '21 at 05:25
  • 1
    Just put the SQL in and set `native-query` to `true`. – M. Deinum Apr 19 '21 at 05:34
  • Yes, I tried. `@Query(value = "sql here",nativeQuery = true)` and got same error. But in `native-query` I tried the SQL which to SQL mentioned in [link](https://stackoverflow.com/questions/54889514/cte-sql-to-simple-subset-query-for-jpa). Is that okay ? – bee Apr 19 '21 at 05:56
  • 1
    The problem is if you are matching the result to a JPA entity your resulting columns have to match the mapping. Looks like that is off in your result set. – M. Deinum Apr 19 '21 at 05:58
  • @M.Deinum,Yes. Made changes and now worked for me. – bee Apr 19 '21 at 07:00

2 Answers2

2

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();
}
Sivaram Rasathurai
  • 5,533
  • 3
  • 22
  • 45
0

If you are using recursive query,creating view might be a way to use it in JPA

For Example,create a view like below:

CREATE VIEW RECUSRSIVE_QUERY_VIEW
AS
[Your Recursive CTE]

Then write a Entity class mapping to RECUSRSIVE_QUERY_VIEW:

@Entity
@Table(name="RECUSRSIVE_QUERY_VIEW")
public class RecursiveQueryView {
  //Declare column and getter/setter
}

Then you should be able to use JpaRepository With this Entity:

interface RecursiveQueryViewJpaRepository extends JpaRepository<RecursiveQueryView,String> {
}

Hope this help

葉皓昀
  • 1