I am new with JPA and Im having many problems with the data returning:
Problems:
- 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.
- If I filter by the guaranteesDni field the query does not return data.
- 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.