当使用union all语句进行本机查询时，如何使用JpaRepository操作（findOne）

Question

我需要将JpaRepository与一个包含union语句的查询一起使用。可能吗？这是我当前的实现，也是我迄今为止遇到的错误：

实体：

@Entity
public class PaymentDetails {
  @Id
  @Column
  private String id;
  @Enumerated(EnumType.STRING)
  @Column(name = "rowtype")
  private PaymentType paymentType;
  @Embedded
  private CardDetails cardDetails;
  @Embedded
  private BankAccountDetails bankAccountDetails;

界面和查询：

public interface PaymentsRepositoryManagerPayment1 extends JpaRepository<PaymentDetails,String> {
  @Query(value = "select id,'CARD' as rowtype, cardnumber, cardtype, nameoncard, expirydate, startdate, securitycode, semafonecr, issuenumber, "
        + "null accountnumber, null accountholdername, null sortcode, null bic, null iban, null currency from pcs.BsbTempCardDetails "
        + "where id = :id union all select id, 'BANK' as rowtype, null cardnumber, null cardtype, null nameoncard, null expirydate, null startdate, "
        + "null securitycode, null semafonecr, null issuenumber, accountnumber, accountholdername, sortcode, bic, iban, currency "
        + "from pcs.BsbTempBankAccountDetails where id = :id", nativeQuery = true) <br>
List< PaymentDetails > findPaymentDetails(@Param("id") String id);

呼叫：

@Autowired private PaymentsRepositoryManagerPayment1 paymentsRepositoryManagerPayment1;
@Transactional(value = "paymentsRepositoryTransactionManager")
public PaymentDetails retrievePaymentDetailsById1(String id) {
  return paymentsRepositoryManagerPayment1.findOne(id);
}

错误：

组织。springframework。刀。InvalidDataAccessResourceUsageException:无法加载实体：[com.bskyb.repository.payments.model.PaymentDetails#cardd]；SQL[选择PaymmentDet0_0.id作为ID20000.PaymmentDe00.PaymmentDe0.PaymentDe000，PaymmentDe00。Account数字作为Account3220000，PaymmentDe00.PaymmentDe0.id作为ID20.id作为id2.id作为ID20，PaymmentDe00.PaymmentDe0.AccountDe0。PaymmentDe0.PaymentDe0.PaymentDe0.PaymentDe0。PaymentDe0。PaymentDe0000。PaymentDe00。PaymentDe00。作为accountnumber作为Accum22222222222222222222222222_0_，付钱给我ntdet0_2;。issuenumber为issuenu11_2_0_0，paymentdet0_0。nameoncard作为nameoncard2_0_，paymentdet0_。证券代码为Security13_2_0_，paymentdet0_。startdate为startdate 2_0_，paymentdet0_。rowtype为rowtype2_0_u来自PaymentDetails paymentdet0_u其中paymentdet0_u。id=？]；嵌套的异常是org。冬眠例外SQLGrammarException:无法加载实体：[com.bskyb.repository.payments.model.PaymentDetails#cardd]java。sql。SQLSyntaxErrorException:ORA-00942:表或视图不存在

Answer 1

你展示的所有内容似乎都与你看到的例外没有太大关系：

1. 异常抱怨表不可用。在发出查询时，请确保Payment细节存在（这可能是您看到异常的原因）。
2. 您调用findOne（...）。因此findPayment细节（...）上的查询声明在用例中根本不起作用。