Join with Specification

Asked

Viewed 119 times

2

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 am creating a subquery for the 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 make the Join of Pessoa with BoletoSerasa. Remembering that BoletoSerasa has a list of Pessoa.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.