如何将此SQL查询转换为JPA
如何将此SQL查询转换为JPA
提问于 2018-09-29 23:41:25
早上好，我对此有一个问题。我有一个涉及3个表的SQL查询: credits、payment和bill
从信用c中选择c.date，c.pay_number，c.cost加入付款p on c.payment_id = p.id加入账单b on b.id = p.bill_id，其中b.id = :b.id；
当我将这个查询放到dbeaver中时，它工作得很好，但我需要一些帮助才能将其转换为正确的JPA句子。
这是Bill实体：
@Entity
@Data
public class Bill {
@Id
@GeneratedValue
private UUID id;
private long dateCreated;
private boolean active;
private String billSeq;
private String stringSeq;
private String priceType;
private double iva;
private double ice;
private double total;
private double subTotal;
private double baseNoTaxes;
private double baseTaxes;
private String ordenDecompra;
private String srr;
private String lugar;
private String otir;
private double discount;
private int discountPercentage;
private String claveDeAcceso;
private String idSri;
private int typeDocument;
private String quotationSeq;
private String stringQuotationSeq;
@ManyToOne
@JoinColumn(name = "client_id")
private Client client;
@ManyToOne
@JoinColumn(name = "user_id")
private UserIntegridad userIntegridad;
@ManyToOne
@JoinColumn(name = "subsidiary_id")
private Subsidiary subsidiary;
@OneToMany(mappedBy = "bill", cascade = CascadeType.ALL)
private List<Detail> details;
@OneToMany(mappedBy = "bill", cascade = CascadeType.ALL)
private List<Payment> payment;
public void setListsNull(){
details = null;
payment = null;
};
public void setFatherListToNull(){
client.setListsNull();
client.setFatherListToNull();
userIntegridad.setListsNull();
userIntegridad.setFatherListToNull();
subsidiary.setListsNull();
subsidiary.setFatherListToNull();
};
@Transient
public static Bill newBillTest(){
Bill bill = new Bill();
bill.setDetails(new ArrayList<>());
bill.setPagos(new ArrayList<>());
bill.setClient(Client.newClientTest());
bill.setUserIntegridad(UserIntegridad.newUserIntegridadTest());
bill.setSubsidiary(Subsidiary.newSubsidiaryTest());
return bill;
};
}这是支付主体：
@Entity
@Data
public class Payment implements Child {
@Id
@GeneratedValue
private UUID id;
private String medio;
private double total;
private String payForm;
private String cardBrand;
private String chequeAccount;
private String chequeBank;
private String chequeNumber;
private String chequeDiasPlazo;
private long fechaCobro;
private int creditoIntervalos;
private int creditoNumeroPagos;
@OneToMany(mappedBy = "payment", cascade = CascadeType.ALL)
private List<Credits> credits;
@ManyToOne
@JoinColumn(name = "bill_id")
private Bill bill;
public void setListsNull(){
credits = null;
}
public void setFatherListToNull(){
bill.setListsNull();
bill.setFatherListToNull();
}
public static Payment newPaymentTest(){
Payment payment = new Payment();
payment.setBill(Bill.newBillTest());
return payment;
}
}这是信用实体：
@Entity
@Data
public class Credits implements Child {
@Id
@GeneratedValue
private UUID id;
private int diasPlazo;
private long fecha;
private int payNumber;
private double valor;
@ManyToOne
@JoinColumn(name = "payment_id")
private Payment payment;
public void setFatherListToNull(){
payment.setListsNull();
payment.setFatherListToNull();
}
public static Credits newCreditsTest(){
Credits credit = new Credits();
credit.setPayment(Payment.newPaymentTest());
return credit;
}
}系统的流程从发票生成时开始，付款类型记录在支付表中，如果是现金或信用支付，如果付款是信用付款，则信息存储在信用表中，因此我需要从表信用中提取存储在信用表中的所有信息，只需输入发票的ID。
所以在存储库(CreditsRepository)中我这样做了：
@Repository
@Qualifier(value="CreditsRepository")
public interface CreditsRepository extends CrudRepository<Credits, UUID>{
Iterable<Credits> findByPago(Pago pago);
@Query("SELECT c FROM Credits c JOIN c.payment p JOIN p.bill b WHERE b.id = :id")
Iterable<Credits> findCreditsOfBillByBillId(@Param("id") UUID id);
}所以我需要帮助来做一个好的@查询
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-09-30 03:26:31
你的@Query绝对不错。您还可以删除@Query，只使用spring-data-jpa等效的方法名称，如下所示：
//no annotation here
public interface CreditsRepository extends CrudRepository<Credits, UUID> {
//if you want to pass Bill object
List<Credits> findAllByPaymentBill(Bill bill);
//if you want to pass an id (UUID)
List<Credits> findAllByPaymentBillId(UUID id);
}关于你的代码的一些事情。
如果您扩展了CrudRepository，就不需要@Repository注释，而且因为您正在编写一个接口，所以也不需要@Qualifier。
您可以简单地在您的服务中使用@Autowire
@Autowired
private CreditsRepository creditsRepository;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52569949
复制相关文章
相似问题