JPA - TypedQuery with Left Join and Count

I've been trying to construct a SQL below, using TypedQuery with Criteria Builder:

select
        a.id,
        a.numeroAvisoPagamento,
        a.industria_id,
        a.varejo_id,
        a.dataAvisoPagamento,
        a.statusAvisoPagamento,
        a.dataUploadArquivo,
        a.dataImportacaoArquivo,
        a.dataConciliacaoAviso,
        count(c.avisoPagamento_id) as qtdeNotas, 
    from
        AvisoPagamento a  
    left join
        LoteAvisoPagamento l 
            ON l.codigoAviso = a.numeroAvisoPagamento 
    left join
        Cobranca c 
            ON c.avisoPagamento_id = l.id 
    where
        a.industria_id = ? 
        and a.varejo_id = ? 
        and a.numeroAvisoPagamento = ? 
        and a.dataAvisoPagamento between ? and ? 
    group by
        a.id,
        a.numeroAvisoPagamento,
        a.numeroAvisoPagamento,
        a.industria_id,
        a.varejo_id,
        a.dataAvisoPagamento,
        a.statusAvisoPagamento,
        a.dataUploadArquivo,
        a.dataImportacaoArquivo,
        a.dataConciliacaoAviso

Models

AvisoPagamento

@Entity(name = "AvisoPagamento")
public class AvisoPagamento {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;

@OneToMany(mappedBy = "avisoPagamento", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private List<CobrancaAvisoPagamento> cobrancas;

@OneToMany(mappedBy = "avisoPagamento", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@OrderBy("dataAcao ASC")
@JsonIgnore(accept={"AvisoPagamentoController.*"})
private List<LogAvisoPagamento> logAvisoPagamento;
}

LoteAvisoPagamento

@Entity(name = "LoteAvisoPagamento")
public class LoteAvisoPagamento {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;

@Column(nullable = false)
private Long codigoAviso;

}

Cobranca

public class Cobranca {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;

@ManyToOne(fetch = FetchType.LAZY, optional = true)
@JoinColumn(name = "avisoPagamento_id")
@JsonIgnore(accept = { "CobrancaLoteController.listaCobrancas", "CobrancaAdmController.*",
        "ConciliacaoController.*", "CobrancaIndController.*" })
private LoteAvisoPagamento avisoPagamento;
}

I have two problems:

The entities LoteAvisoPagamento and AvisoPagamento , there is no relationship between them so I "forced" union with two columns: ON LoteAvisoPagamento.codigoAviso = AvisoPagamento.numeroAvisoPagamento. Works fine with SQL Native.
I need to count records from Table Cobranca so I used count(c.avisoPagamento_id)

I would like to rewitre this SQL to TypedQuery and CriteriaBuilder so I tried this:

//Create Criteria Builder
        final CriteriaBuilder builder = manager.getCriteriaBuilder();
        //Create CriteriaQuery da Classe AvisoPagamento
        final CriteriaQuery<AvisoPagamento> query = builder.createQuery(AvisoPagamento.class);
        //Create from
        final Root<AvisoPagamento> rootAviso = query.from(AvisoPagamento.class);

        //Left Join Lote Aviso Pagamento
        Root<LoteAvisoPagamento> rootLoteAviso = query.from(LoteAvisoPagamento.class);

        final Predicate predicateLeftJoin = builder.equal(rootAviso.get("numeroAvisoPagamento"), rootLoteAviso.get("codigoAviso"));

        //Conditions
        Predicate predicateAvisoPagamento = builder.and();

        //Join Selects
        Predicate criteria = builder.conjunction();
        criteria = builder.and(criteria, predicateAvisoPagamento);
        criteria = builder.and(criteria, predicateLeftJoin);

        //Passou a Industria
        if (industria != null){
             predicateAvisoPagamento = builder.and(predicateAvisoPagamento, builder.equal(rootAviso.get("industria"), industria));
        }
        //Passou o Varejo
        if (varejo != null){
            predicateAvisoPagamento = builder.and(predicateAvisoPagamento, builder.equal(rootAviso.get("varejo"), varejo));
        }
        //Passou o numero do Aviso
        if (numeroAviso != null){
            predicateAvisoPagamento = builder.and(predicateAvisoPagamento, builder.equal(rootAviso.get("numeroAvisoPagamento"), numeroAviso));
        }

        //Passou as Datas De e Ate
        if (dataDe != null && dataAte != null){
            predicateAvisoPagamento = builder.between(rootAviso.<Date>get("dataAvisoPagamento"), dataDe , dataAte);
        }


        //TypedQuery eh mais robusto, a checagem de tipo é feito na compilacao, eliminando alguns
        //tipos de erros
        final TypedQuery<AvisoPagamento> typedQuery = manager.createQuery(
            query.select(rootAviso).distinct(true)
            .where( criteria )
            .orderBy(builder.desc(rootAviso.get("dataConciliacaoAviso")))
        );


        //return List
        final List<AvisoPagamento> results = typedQuery.getResultList();

        return results;

Then JPA generated this SQL:

select
        distinct avisopagam0_.id as id1_9_,
        avisopagam0_.arquivoFisico as arquivoF2_9_,
        avisopagam0_.dataAvisoPagamento as dataAvis3_9_,
        avisopagam0_.dataConciliacaoAviso as dataConc4_9_,
        avisopagam0_.dataImportacaoArquivo as dataImpo5_9_,
        avisopagam0_.dataUploadArquivo as dataUplo6_9_,
        avisopagam0_.industria_id as industri9_9_,
        avisopagam0_.numeroAvisoPagamento as numeroAv7_9_,
        avisopagam0_.statusAvisoPagamento as statusAv8_9_,
        avisopagam0_.usuario_id as usuario10_9_,
        avisopagam0_.varejo_id as varejo_11_9_ 
    from
        AvisoPagamento avisopagam0_ cross 
    join
        LoteAvisoPagamento loteavisop1_ 
    where
        1=1 
        and 1=1 
        and avisopagam0_.numeroAvisoPagamento=loteavisop1_.codigoAviso 
    order by dataAvisoPagamento desc

How can I count the records from Table Cobranca using TypedQuery and how can I fix this:

 where
            1=1 
            and 1=1

It's strange, I've been reading a lot about TypedQuery but I'm stuck

Solution

I think ON clause is working only with relations in JPA 2.1 release.

So mostly till now you can't use

union with two columns: ON LoteAvisoPagamento.codigoAviso = AvisoPagamento.numeroAvisoPagamento

because JPA 2.1 (last release) doesn't support that.

So it will not work on Criteria or JPQL

Note: CROSS JOIN doesn't need ON clause, and that's why you are seeing it inside generated query also you can't do LEFT JOIN in criteria with that way you are using (impossible) it will be always generated as CROSS JOIN

INNER JOIN and LEFT JOIN needs a relation between entities

Please try next JPQL your side and test if it valid or not (and i think it will not work), it's simple but it should be similar to what you want to do(at least similar on one condition till now)

SELECT aviso.id, aviso.numeroAvisoPagamento, loteAviso.id
FROM AvisoPagamento aviso
LEFT JOIN LoteAvisoPagamento loteAviso 
ON loteAviso.codigoAviso = aviso.numeroAvisoPagamento
WHERE aviso.numeroAvisoPagamento = :numeroAviso

replace :numeroAviso with any valid value then test this as a entityManager.createQuery(put here the query)

Anyway i tested it my side for different entities but same logic and i got an exception as i expected

note: i am using JPA with Hibernate provider

and here are the exception i got

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join!

so it expecting something like that (which your entities doesn't support)

LEFT JOIN aviso.loteAvisoPagamento loteAviso

in next query

SELECT aviso.id, aviso.numeroAvisoPagamento, loteAviso.id
FROM AvisoPagamento aviso
LEFT JOIN aviso.loteAvisoPagamento loteAviso
ON loteAviso.codigoAviso = aviso.numeroAvisoPagamento
WHERE aviso.numeroAvisoPagamento = :numeroAviso