如何在Spring Data JPA中使用CTE表达式的WITH子句

Question

我想在 SQL Server 中编写递归查询，因此我正在使用 CTE。在 SSMS 中查询正常工作，但当我尝试在 JPA 中将相同的 CTE 用作本机 SQL 时，会出现错误：

无效的名称列 Id。

我在实体类中使用 @Id @Column(name="pk_id") private int Id 字段来使用 CTE 递归获取数据。我也遵循了这个SOQ: cte sql to simple subset query for JPA，但仍然得到 "invalid name column Id" 的错误。 我以前从未使用过 CTE。如何修复这个问题？

- bee

---

CTE在JPQL中无法使用，需要使用SQL。 - M. Deinum

如何使用递归 SQL 呢？ - bee

只需将SQL放入其中，并将“native-query”设置为“true”。 - M. Deinum

是的，我尝试过 @Query(value = "sql here",nativeQuery = true)，但是得到了相同的错误。但是在 native-query 中，我尝试了在 链接 中提到的 SQL。这样可以吗？ - bee

问题在于，如果你将结果与JPA实体匹配，那么你得到的列必须与映射相匹配。看起来你的结果集中出了偏差。 - M. Deinum

@M.Deinum，是的。我做了一些更改，现在它对我起作用了。 - bee

Answer 1

您可以在 JPA 存储库中编写 SQL 查询，因为 @Query 注释也接受原生查询。为此，您需要在 value 参数中指定 SQL 查询，并将 nativeQuery 设置为 true，如下所示。

您也可以编写 CTE 查询。

public interface ISomeRepository extends JpaRepository<Entity, Long> {


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

- Sivaram Rasathurai

Answer 2

如果您正在使用递归查询，创建视图可能是在JPA中使用它的一种方法

例如，创建以下视图：

CREATE VIEW RECUSRSIVE_QUERY_VIEW
AS
[Your Recursive CTE]

然后编写一个实体类映射到递归查询视图（RECUSRSIVE_QUERY_VIEW）：

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

那么你应该能够使用JpaRepository与这个实体：

interface RecursiveQueryViewJpaRepository extends JpaRepository<RecursiveQueryView,String> {
}

希望这可以帮到你

- 葉皓昀