0

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.

Sftc
  • 1
  • 1
  • You don't mention the database you're using but SQL queries has a DISTINCT operation, SELECT DISTINCT column1, column2, ... FROM table_name; – MZM Nov 26 '22 at 04:03
  • Oracle database (19), I tried ti apply a distinct, but they proyect failed. how You apply a distinct in jpa? – Sftc Nov 26 '22 at 04:08
  • Spring Data JPA uses Hibernate as JPA provider and distinct results are not filter at JPA level but done at SQL query stage. I'm fairly sure Oracle does support DISTINCT queries. – MZM Nov 26 '22 at 04:27
  • I know ITBIS supported they problem si that I don't know how to implement they distinct in My java repository – Sftc Nov 26 '22 at 04:43
  • Look at this article, provided by a co-worker, The best way to use the JPQL DISTINCT keyword with JPA and Hibernate, https://vladmihalcea.com/jpql-distinct-jpa-hibernate/ . – MZM Nov 26 '22 at 05:33

0 Answers0