How to Translate this SQL Query to JPA

Question

Good Morning, i've a question with this. I have this SQL Query that involves 3 tables: credits, payment and bill

select c.date, c.pay_number, c.cost from credits c join payment p on c.payment_id = p.id join bill b on b.id = p.bill_id where b.id = :b.id;

When i put this query into dbeaver it works fine, but i need some help to translate to a correct JPA sentence.

This is Bill Entity:

@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;
    };
}

This is Payment Entity:

@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;
    }
}

This is Credits Entity:

@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;
    }
}

The flow of the system goes from when an invoice is generated, the type of payment is recorded in the payment table, if it is paid in cash or credit, if the payment is on credit the information is stored in the table credits, so I need extract from the table credits all the information stored in the credits table by entering only the ID of the invoice.

So in the repository (CreditsRepository) i did this:

@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);
}

So I need help to make a good @Query

Answer 1

Your @Query is absolutely good. You can also remove @Query and use only spring-data-jpa equivalent method name as follows:

//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);
}

A few things about your code.

If you extends CrudRepository you don't need @Repository annotation, and because you are writing an interface, you don't need the@Qualifier too.

You can simply @Autowire in your Service

    @Autowired
    private CreditsRepository creditsRepository;