我一直在尝试使用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;
}
我有两个问题：
实体LoteAvisoPagamento和AvisoPagamento之间没有关系，因此我用两列“强制”结合：ON LoteAvisoPagamento.codigoAviso = AvisoPagamento.numeroAvisoPagamento。与SQL Native一起正常工作。
我需要对表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的书，但我被卡住了
最佳答案
我认为ON子句仅适用于JPA 2.1版本中的关系。
所以大多数时候到现在为止你不能使用
具有两列的并集：ON LoteAvisoPagamento.codigoAviso = AvisoPagamento.numeroAvisoPagamento
因为JPA 2.1（最新版本）不支持该功能。
因此它不适用于Criteria或JPQL
注意：CROSS JOIN不需要ON子句，这就是为什么您在生成的查询中看到它的原因，也不能在其中执行LEFT JOIN
使用这种方式的标准（不可能），它将始终
作为CROSS JOIN生成
INNER JOIN和LEFT JOIN需要实体之间的关系
请尝试下一个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（在此处输入查询）进行测试
无论如何，我在不同实体但逻辑相同的情况下对它进行了测试，并且出现了我所期望的异常
注意：我正在与Hibernate提供程序一起使用JPA
这是我遇到的例外
引起原因：org.hibernate.hql.internal.ast.QuerySyntaxException：预期要加入的路径！
所以它期望这样的东西（您的实体不支持）
左加入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