How to resolve duplicity with JPA and join query?

45 Views Asked by At

I am new with JPA and Im having many problems with the data returning:

Problems:

  1. The LoansRequest should be able to filter the query by each of its fields and always return just one loan, but it is returning data twice when I filter by the debtorDni field.
  2. If I filter by the guaranteesDni field the query does not return data.
  3. paymentsNumber is returning a null value , but it has data in DB ->{123, 456}

DATA

SELECT * FROM LOANS;

ID DEBTOR_DNI LOAN_AMOUNT PAYMENTS DETAIL_ID 1 287654357 30000 {123, 456} 1

SELECT * FROM DEBTOR_GUARANTEES;

ID GUARANTEES_DNI NAME LOAN_ID 1 238976542 DEAN YOUNG 1 2 219875642 RACHEL GREEN 1

Dto

@Data
@AllArgsConstructor
@NoArgsConstructor
public class LoansResponse {

    
    private Long id;
    private String debtorDni;
    private String loanAmount;
    private List<String> paymentsNumber;
    private List<DebtorGuarantees> guarantees;

}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class LoansRequest {

    
    private Long id;
    private String debtorDni;
    private String guaranteesDni;
    private String amount;


}

Entities

@Entity
@Table(name = "LOANS", schema = "H2")
@Data
public class LoansEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "LOANS_ID_GEN")
    @SequenceGenerator(name = "LOANS_ID_GEN", sequenceName = "LOANS_SEQ", initialValue = 1,  allocationSize = 1)
    @Column(name = "ID", unique = true, nullable = false)
    private Long id;

    @Column(name = "DEBTOR_DNI")
    private String debtorDni;
    
    @Column(name = "LOAN_AMOUNT")
    private String loanAmount;

    @Column(name = "PAYMENTS NUMBER")
    private String paymentsNumber;


    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
    @JoinColumn(name = "LOANS_ID", referencedColumnName = "id")
    private List<DebtorGuaranteesEntity> guarantees;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name = "DETAIL_ID")
    private DebtorDetailEntity debtorDetail;



@Entity
@Table(name = "DEBTOR_GUARANTEES", schema = "H2")
@Data
public class DebtorGuaranteesEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "DEBTOR_ID_GEN")
    @SequenceGenerator(name = "DEBTOR_ID_GEN", sequenceName = "DEBTOR_SEQ", initialValue = 1, allocationSize = 1)
    @Column(name = "ID", unique = true, nullable = false)
    private Long id;

    @Column(name = "GUARANTEES_DNI")
    private String dni;

    @Column(name = "NAME")
    private String guaranteesDni;


    @Column(name = "DEBTOR_RELATION")
    private String debtorRelation;

    @ManyToOne
    private LoansEntity loans;

}

Impl

@Override
    public LoansResponse getLoans(LoansRequest request) {

        LoansResponse response = new LoansResponse();
        List<LoansEntity> responseList = repo.filters(request);
        List<LoansResponse> list = responseList.stream().map(this::toLoansDto).collect(Collectors.toList());
        response.setLoans(list);
        return response;

    }
    
    private LoansResponse toLoansDto(LoansEntity entity) {
    LoansResponse dto = modelMapper.map(entity, LoansResponse.class);
    return dto;
    }

Repository

public List<LoansEntity> filters(LoansRequest request){
        
        List<Predicate> predicates = new ArrayList<>();
        
        String debrDni = request.getDebtorDni();
        String guarDni = request.getGuaranteesDni();
        String amountCredit = request.getLAmount();
        
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<LoansEntity> cq = cb.createQuery(LoansEntity.class);
    
        
        Root<LoansEntity> loan = cq.from(LoansEntity.class);
        Join<LoansEntity, DebtorGuaranteesEntity> guarantees = loans.join("guarantees" ,JoinType.LEFT);
        
    
        Predicate prDebtorDni = cb.like(loan.get("debtorDni"), "%" + debrDni + "%");
        Predicate prGuaranteesDni = cb.like(guarantees.get("guaranteesDni"), "%" + guarDni + "%");
        Predicate prAmount = cb.like(loan.get("amount"), "%" + amountCredit + "%" );
        
        
        predicates.add(prDebtorDni);
        predicates.add(prGuaranteesDni);
        predicates.add(prAmount);
        
        cq.where(predicates.toArray(new Predicate[] {}));
        cq.orderBy(cb.desc(debtors.get("debtorDni")));
        TypedQuery<LoansEntity> query = em.createQuery(cq);
        
    return query.getResultList();
        
    }

Json Response

{
    "loans": [
        {
            "id": 1,
            "debtorDni": "287654357",
            "loanAmount": "30000",
            "paymentsNumber": null,
            "debtors": [
                {
                    "id": 1,
                    "guaranteesDni": "238976542",
                    "name": "DEAN YOUNG"
                },
                {
                    "id": 2,
                    "guaranteesDni": "219875642",
                    "name": "RACHEL GREEN"
                }
            ]
        },
        {
            "id": 1,
            "debtorDni": "287654357",
            "loanAmount": "30000",
            "paymentsNumber": null,
            "debtors": [
                {
                    "id": 1,
                    "guaranteesDni": "238976542",
                    "name": "DEAN YOUNG"
                },
                {
                    "id": 2,
                    "guaranteesDni": "219875642",
                    "name": "RACHEL GREEN"
                }
            ]
        }
    ]
}

I´ve tried to change the query plenty of times but it does no work. I am stuck.

0

There are 0 best solutions below