使用交叉连接的 Hibernate 删除查询

Question

我正在尝试执行如下删除查询:

@Modifying
@Query("delete from Document d where d.requestDocument.request.id = :requestId and d.documentType.lookupCode in (:documentTypeLookupCodes)")
Integer deleteByRequestIdAndDocumentTypes(@Param("requestId") Long requestId, @Param("documentTypeLookupCodes") List<String> documentTypeLookupCodes);

我正在使用 spring data jpa 存储库，当它执行时会生成:

DELETE FROM sakreg_documents cross join request_docs requestdoc1_ cross join sakreg_doc_type documentty2_ where sakreg_request_id=1111 and (lkp_code in ('IN' , 'OUT'))

并抛出

ORA-00933: SQL command not properly ended

文档实体:

@Entity
@Table(name = "SAKREG_DOCUMENTS")
public class Document {
      @Id
      @SequenceGenerator(name = "DocumentSequence", sequenceName = "SAKREG_DOCUMENTS_SEQ", allocationSize = 1)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "DocumentSequence")
      @Column(name = "ID")
      private Long id;

      @OneToOne(mappedBy="document", fetch = FetchType.LAZY)
      private RequestDocument requestDocument;

      @ManyToOne
      @JoinColumn(name = "SAKREG_DOC_TYPE_ID_FK")
      private DocumentType documentType;
}

请求文档实体:

@Entity
@Table(name = "REQUEST_DOCS")
public class RequestDocument {
      @Id
      @SequenceGenerator(name = "requestDocSequence", sequenceName = "REQUEST_DOCS_SEQ", allocationSize = 1)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "requestDocSequence")
      @Column(name = "ID")
      private Long id;

      @ManyToOne
      @JoinColumn(name="SAKREG_REQUEST_ID")
      private Request request;

      @OneToOne
      @JoinColumn(name="SAKREG_DOC_ID")
      private Document document;
}

请求实体:

    @Entity
    @Table(name = "SAKREG_REQUEST")
    public class Request {
          @Id
          @SequenceGenerator(name = "requestIdSequence", sequenceName = "SAKREG_REQUEST_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "requestIdSequence")
    @Column(name = "ID")
    @CmProperty(symbolicName = "RequestId")
    private Long id;
}

文档类型实体:

@Entity
@Table(name = "SAKREG_DOC_TYPE")
public class DocumentType{
@Id
    @GeneratedValue
    @Column(name = "ID")
    private Short id;
}

那么，我该如何编写可以删除文档的删除语句使用具有特定请求 ID 的 RequestDocument

Answer 1

我认为您不能在这样的删除语句中使用多个表。

尝试使用 exists 重写 delete 语句。

@Query("delete from Document d where exists " +
" (select requestDocument from RequestDocument requestDocument " +
"  where requestDocument.request.id = :requestId " +
"  and requestDocument = d.requestDocument)" +
"  and d.documentType.lookupCode in (:documentTypeLookupCodes) ")