早上好,我对此有一个问题。我有一个涉及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);
}
所以我需要帮助来做一个好的@查询
发布于 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;
https://stackoverflow.com/questions/52569949
复制相似问题