java - JPA - 带有左连接和计数的 TypedQuery

Question

我一直在尝试使用 TypedQuery 和 Criteria Builder 构建下面的 SQL:

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

模型

阿维索帕加门托

@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;

}

科布兰卡

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;
}

我有两个问题:

1. 实体 LoteAvisoPagamento 和 AvisoPagamento ，它们之间没有关系，因此我“强制”与两列合并:ON LoteAvisoPagamento.codigoAviso = AvisoPagamento.numeroAvisoPagamento。与 SQL Native 配合良好。
2. 我需要统计 Cobranca 表中的记录，因此我使用了 count(c.avisoPagamento_id)

我想将此 SQL 重写为 TypedQuery 和 CriteriaBuilder，所以我尝试了以下方法:

//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;

然后 JPA 生成了这个 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

如何使用 TypedQuery 对表 Cobranca 中的记录进行计数以及如何解决此问题:

 where
            1=1 
            and 1=1

很奇怪，我已经阅读了很多有关 TypedQuery 的内容，但我陷入困境

Answer 1

我认为 ON 子句仅适用于 JPA 2.1 版本中的关系。

所以到现在为止你大部分都不能使用

具有两列的联合:ON LoteAvisoPagamento.codigoAviso = AvisoPagamento.numeroAvisoPagamento

因为 JPA 2.1(最新版本)不支持这一点。

所以它不适用于 Criteria 或 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

请尝试下一个 JPQL 你这边并测试它是否有效(我认为它不会工作)，它很简单，但它应该与你想要做的类似(至少到目前为止在一个条件上类似)

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

将:numeroAviso替换为任何有效值，然后将其测试为entityManager.createQuery(将查询放在这里)

无论如何，我在我这边测试了不同的实体但相同的逻辑，并且我得到了预期的异常

注意:我正在将JPA与Hibernate提供商一起使用

这是我得到的异常(exception)

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

所以它期待类似的东西(您的实体不支持)

LEFT JOIN aviso.loteAvisoPagamento loteAviso

在下一个查询中

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