I have the following query:
SELECT tab1.nom_shopping,
tab1.nom_fantasia,
tab1.luc,
tab1.dat_vencimento,
tab1.num_boleto,
tab1.val_saldo_aberto,
tab2.num_cpfcnpj negativado,
tab2.id_pessoa_vinculo_loja [IdPessoa],
tab1.id_lista_serasa_boleto [IdLista],
tab1.num_cpfcnpj_loja,
tab2.txt_erro_retorno_serasa
FROM lista_serasa_boleto tab1,
pessoa_vinculo_loja tab2
WHERE tab1.cod_status_boleto = “erro retorno serasa”
AND tab2.txt_erro_retorno_serasa IS NOT NULL
AND EXISTS
(
SELECT 1
FROM historico_status_boleto tab5
WHERE tab5.cod_status_boleto = “aguardando retorno serasa”
AND tab5.dat_status_boleto BETWEEN [data de] AND [data ate]
ORDER BY 1,
2,
3
And in class BoletoSerasaSpecification
I'm creating a subquery for select 1 from histórico_status_boleto
as follows:
private static Subquery<HistoricoStatusBoleto> subQueryHistoricoStatus(Root<BoletoSerasa> root, CriteriaQuery<?> query,
CriteriaBuilder builder, LocalDate periodoDe, LocalDate periodoAte) {
Subquery<HistoricoStatusBoleto> subQuery = query.subquery(HistoricoStatusBoleto.class);
Root<HistoricoStatusBoleto> rootHistoricoStatusBoleto = subQuery.from(HistoricoStatusBoleto.class);
subQuery.select(rootHistoricoStatusBoleto);
return subQuery.where(
builder.and(
builder.equal(rootHistoricoStatusBoleto.get("statusBoleto").get("id"), AGUARDANDO_RETORNO_SERASA.getId()),
builder.between(root.get("dataStatusBoleto"), periodoDe, periodoAte)));
}
This subquery will be called later by the "top query" in the following method:
public static Specification<BoletoSerasa> byDataEnvioBoleto(LocalDate periodoDe, LocalDate periodoAte) {
return (root, query, builder) -> {
Subquery<HistoricoStatusBoleto> subQuery1 = subQueryHistoricoStatus(root, query, builder, periodoDe, periodoAte);
Subquery<BoletoSerasa> subQuery2 = query.subquery(BoletoSerasa.class);
Root<BoletoSerasa> rootboletoSerasa = subQuery2.from(BoletoSerasa.class);
subQuery2.select(rootboletoSerasa).where(
builder.and(
builder.equal(rootboletoSerasa.get("statusBoleto").get("id"), ERRO_RETORNO_SERASA.getId())));
return builder.and((builder.exists(subQuery1)), builder.exists(subQuery2));
};
}
My question is to join Pessoa
with BoletoSerasa
. Remember that BoletoSerasa
has a list of Pessoa
.