In my application I have a model that relates to itself in a parent/child relationship. Posts can have parents that are also posts. I've written a query to delete a target post and its descendants. When I execute the query outside of Spring it works perfectly. However when running it in Spring, the query executes successfully but throws the following exception:

在我的应用程序中，我有一个在父/子关系中与自身相关的模型。帖子可以有同时也是帖子的父母。我写了一个查询来删除目标帖子及其后代。当我在Spring之外执行查询时，它可以完美地工作。但是，当在Spring中运行查询时，查询会成功执行，但会引发以下异常：

WARN  SqlExceptionHelper:144 - SQL Error: 0, SQLState: null
ERROR SqlExceptionHelper:146 - The statement did not return a result set.
ERROR [dispatcherServlet]:182 - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

My query runs from an interface that extends JpaRepository

我的查询从扩展JpaRepository的接口运行

@Query(value = "WITH ParentTree AS (\n" +
            "  SELECT Parent.post_key,\n" +
            "    1 AS Level\n" +
            "  FROM community_post AS Parent\n" +
            "    WHERE Parent.post_key = ?1\n" +
            "    UNION ALL\n" +
            "      SELECT Child.post_key,\n" +
            "        pt.Level + 1\n" +
            "      FROM community_post AS Child\n" +
            "      INNER JOIN ParentTree AS pt\n" +
            "        ON Child.post_parent = pt.post_key\n" +
            "      WHERE Child.post_parent IS NOT NULL\n" +
            ")\n" +
            "DELETE FROM community_post\n" +
            "  WHERE post_key IN (\n" +
            "      SELECT post_key\n" +
            "      FROM ParentTree\n" +
            "  )", nativeQuery = true)
    void recursiveDelete(long targetKey);

I think you want to add the @Modifying annotation as well. See the documentation here. It's because SQL Delete does not return a resultset.

我认为您还想添加@Modifying注释。请参阅此处的文档。这是因为SQL Delete不返回结果集。

EDIT 1:

编辑1:

It comes down to execute (or executeQuery) vs executeUpdate if you're familiar with the JDBC API. Looks like Spring has the expectation that your method annotated with @Query will return a resultset and so it's disappointed when it doesn't. Related SO question/answer here.

如果您熟悉JDBC API，那么可以归结为execute（或executeQuery）与executeUpdate。看起来Spring期望用@Query注释的方法将返回一个结果集，所以当它没有返回时，它会感到失望。此处为相关SO问题/答案。

Add @Modifying annotation if it is missing with @Query.

如果@Query缺少“修改注释”，请添加该注释。

I am getting the same error when using @Modifying though I use the Create query using the store procedure.

虽然我使用了存储过程的Create查询，但在使用@Modification时也会出现同样的错误。

I need to use @Transactional with @Modifying that solve my problem.

我需要使用@Transactional和@Modification来解决我的问题。

my code:

我的代码：

@Modifying
@Transactional
@Query(value = "exec [cmpl].[CreateComplaintHistory] :complaintNumber,:eventType, :eventCreator, " +
            ":deptFrom, :deptTo, :assignedUser ",  nativeQuery = true)

void createComplaintHistory(
        @Param("complaintNumber") String complaintNumber,
        @Param("eventType") String eventType,
        @Param("eventCreator") String eventCreator,
        @Param("deptFrom") String deptFrom,
        @Param("deptTo") String deptTo,
        @Param("assignedUser") String assignedUser
    );

Another solution for my store procedure

我的存储过程的另一个解决方案

I use @Procedure annotation and remove @Modifying @Transactional

我使用@Procedure注释并删除@Modifying@Transactional

code:

代码：

@Procedure(procedureName = "cmpl.CreateComplaintHistory")
void createComplaintHistory(@Param("complaintNumber") String complaintNumber,
        @Param("eventType") String eventType,
        @Param("eventCreator") String eventCreator,
        @Param("deptFrom") String deptFrom,
        @Param("deptTo") String deptTo,
        @Param("assignedUser") String assignedUser
    );