HQL查询返回与相同逻辑条件查询相比不同的结果

Question

我需要一些HQL专家来帮助我处理复杂的查询。我的映射是AccountingDocument:

AccountingDocument extends Document
@ManyToOne
@JoinColumn(name = "gestiune_id", nullable = false)
private Gestiune gestiune;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "accDoc", cascade = CascadeType.ALL)
private Set<Operatiune> operatiuni = new HashSet<>();

文件：

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Document
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@ManyToOne
@JoinColumn(name = "partner_id", nullable = true)
private Partner partner;
@Column(columnDefinition = "text")
private String name;
@Enumerated(EnumType.STRING)
@Column(nullable = false)
private TipDoc tipDoc;
@Column(precision = 16, scale = 2, nullable = true)
private BigDecimal total;

操作：

@Entity
public class Operatiune
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@ManyToOne
@JoinColumn(name = "accounting_doc_id", nullable = false)
private AccountingDocument accDoc;
@Enumerated(EnumType.STRING)
@Column(nullable = false)
private TipOp tipOp;
@Column(nullable = false)
private String barcode; // taken from product
private String name; // taken from product
private String uom; // taken from product
@Column(precision = 12, scale = 2, nullable = false)
private BigDecimal valoareVanzareFaraTVA;
@Column(precision = 10, scale = 2, nullable = false)
private BigDecimal valoareVanzareTVA;

合作伙伴：

@Entity
public class Partner
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(nullable = false, unique = true)
private String name;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "partner")
private List<Document> documents = new ArrayList<>();

基本上，Operation类是一个销售操作，AccountingDocument类既可以是销售单据(例如：发票(，也可以是现金凭证(例如：收据(。

对于发票，AccountingDocument.total为空。在这种情况下，我们将所有操作的总和相加。操作总数=Operatiune.valoareVanzareFaraTVA+Operatiune.valoareVanzareTVA

对于收据，operatiuni集合为空。在这种情况下，AccountingDocument.total被填充。

要求：退回所有未全额付款的合作伙伴(客户(。totalCashed<totalSold=>未全额支付

我已经为此创建了一个CriteriaQuery+Java过滤，但它太慢了，所以我试图将其转换为HQL。

期望：CriteriaQuery+Java过滤返回与HQL相同的结果。

结果：CriteriaQuery+Java过滤返回29个合作伙伴，而HQL返回6个合作伙伴。

CriteriaQuery+Java过滤(注："incasat"表示"已兑现"(：

final ImmutableList<Partner> unpaidPartners = allUnpaidPartners(); // 29

public ImmutableList<Partner> allUnpaidPartners() {
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Partner> cq = cb.createQuery(Partner.class);
final Root<Partner> rootEntry = cq.from(Partner.class);
final CriteriaQuery<Partner> all = cq.select(rootEntry);
final TypedQuery<Partner> allQuery = em.createQuery(all);
final List<Partner> allPartners = allQuery.getResultList();
return allPartners.stream()
.filter(partner -> globalIsMatch(partner.getName(), Partner.L1_L2, TextFilterMethod.NOT_EQUALS))
.filter(partner -> globalIsMatch(partner.getName(), Partner.OP_INTERNA, TextFilterMethod.NOT_EQUALS))
.filter(partner -> globalIsMatch(partner.getName(), Partner.STAT_PLATA, TextFilterMethod.NOT_EQUALS))
.filter(partner -> globalIsMatch(partner.getName(), Partner.MARFA, TextFilterMethod.NOT_EQUALS))
.filter(partner -> globalIsMatch(partner.getName(), Partner.CARD_NAME, TextFilterMethod.NOT_EQUALS))
.filter(partner -> globalIsMatch(partner.getName(), Partner.STANDARD_PARTNER_NAME, TextFilterMethod.NOT_EQUALS))
.filter(VanzariBean::isNotPaid)
.sorted(Comparator.comparing(Partner::getName))
.collect(toImmutableList());
}
protected static boolean isNotPaid(final Partner partner)
{
final Optional<BigDecimal> totalIncasat = partner.getDocuments().stream()
.filter(AccountingDocument.class::isInstance)
.filter(doc -> doc.getTipDoc().equals(TipDoc.INCASARE))
.map(Document::getTotal)
.collect(Collectors.reducing(BigDecimal::add));
final Optional<BigDecimal> totalSold = partner.getDocuments().stream()
.filter(AccountingDocument.class::isInstance)
.filter(doc -> doc.getTipDoc().equals(TipDoc.VANZARE))
.map(Document::getTotal)
.collect(Collectors.reducing(BigDecimal::add));
return totalIncasat.orElse(BigDecimal.ZERO).compareTo(totalSold.orElse(BigDecimal.ZERO)) < 0;
}
public BigDecimal Document.getTotal()
{
return total;
}
@Override
public BigDecimal AccountingDocument.getTotal()
{
final BigDecimal total = super.getTotal();
if (total == null)
return add(getVanzareTotalFaraTva(), getVanzareTotalTva());
return total;
}
public BigDecimal AccountingDocument.getVanzareTotalFaraTva()
{
return getOperatiuni().stream()
.map(Operatiune::getValoareVanzareFaraTVA)
.reduce(BigDecimal::add)
.orElse(BigDecimal.ZERO);
}
public BigDecimal AccountingDocument.getVanzareTotalTva()
{
return getOperatiuni().stream()
.map(Operatiune::getValoareVanzareTVA)
.reduce(BigDecimal::add)
.orElse(BigDecimal.ZERO);
}

HQL("casat"="cashed"，"vandut"="old"(：

final StringBuilder sb = new StringBuilder();
sb.append("SELECT p FROM Partner p");
sb.append(" LEFT JOIN AccountingDocument incasat WITH incasat.partner = p AND incasat.tipDoc = :qIncasatDoc");
sb.append(" LEFT JOIN AccountingDocument vandut WITH vandut.partner = p AND vandut.tipDoc = :qVandutDoc");
sb.append(" LEFT JOIN vandut.operatiuni opVandut");
sb.append(" WHERE p.name != :qL1L2 AND p.name != :qOpInterna AND p.name != :qStatPlata AND p.name != :qMarfa AND p.name != :qCard AND p.name != :qStandard");
// totalIncasat < totalSold => is not fully paid
sb.append(" AND (select COALESCE(SUM(doc.total), 0) from AccountingDocument doc WHERE doc=incasat) < ")
.append("(select COALESCE(SUM(op.valoareVanzareFaraTVA)+SUM(op.valoareVanzareTVA), 0) from Operatiune op WHERE op=opVandut)");
sb.append(" GROUP BY p");
final Query query = em.createQuery(sb.toString());
query.setParameter("qL1L2", Partner.L1_L2);
query.setParameter("qOpInterna", Partner.OP_INTERNA);
query.setParameter("qStatPlata", Partner.STAT_PLATA);
query.setParameter("qMarfa", Partner.MARFA);
query.setParameter("qCard", Partner.CARD_NAME);
query.setParameter("qStandard", Partner.STANDARD_PARTNER_NAME);
query.setParameter("qIncasatDoc", TipDoc.INCASARE);
query.setParameter("qVandutDoc", TipDoc.VANZARE);
final List result = query.getResultList(); // 6 partners

Answer 1

好吧，看来HQL的逻辑本质上是错误的。它实际上是在检查该合伙人的任何AccDoc的任何现金总额是否小于该合伙人的AccDocs的任何销售总额，而不是实际比较每个合伙人的所有AccDoc的现金总额和销售总额。

因此，因为在HQL中不支持JOIN子句中的子查询，所以我不得不更改为本地查询。因此，正确的查询是：

SELECT * FROM Partner p 
LEFT JOIN 
(
SELECT doc.partner_id, SUM(doc.total) incasat
FROM Document doc
INNER JOIN AccountingDocument accDoc ON accDoc.id = doc.id 
WHERE doc.tipDoc = 'INCASARE'
GROUP BY doc.partner_id
) i ON p.id = i.partner_id
INNER JOIN 
(
SELECT doc.partner_id, SUM(op.valoareVanzareFaraTVA)+SUM(op.valoareVanzareTVA) vandut
FROM Document doc
INNER JOIN AccountingDocument accDoc ON accDoc.id = doc.id 
INNER JOIN Operatiune op ON op.accounting_doc_id = accDoc.id
WHERE doc.tipDoc = 'VANZARE'
GROUP BY doc.partner_id
) v ON p.id = v.partner_id
WHERE p.name != 'L1<->L2' AND p.name != 'OP INTERNA' AND p.name != 'STAT DE PLATA' AND p.name != 'MARFA' AND p.name != 'CARD INCASARE' AND p.name != 'STANDARD' 
AND COALESCE(i.incasat, 0) < COALESCE(v.vandut, 0)

在Java中，这将是：

final StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM Partner p").append(NEWLINE);
sb.append(" LEFT JOIN ").append(NEWLINE)
.append("(").append(NEWLINE)
.append("SELECT doc.partner_id, SUM(doc.total) incasat ").append(NEWLINE)
.append("FROM Document doc ").append(NEWLINE)
.append("INNER JOIN AccountingDocument accDoc ON accDoc.id = doc.id ").append(NEWLINE)
.append("WHERE doc.tipDoc = :qIncasatDoc ").append(NEWLINE)
.append("GROUP BY doc.partner_id ").append(NEWLINE)
.append(") i ON p.id = i.partner_id").append(NEWLINE);
sb.append(" INNER JOIN ").append(NEWLINE)
.append("(").append(NEWLINE)
.append("SELECT doc.partner_id, SUM(op.valoareVanzareFaraTVA)+SUM(op.valoareVanzareTVA) vandut ").append(NEWLINE)
.append("FROM Document doc ").append(NEWLINE)
.append("INNER JOIN AccountingDocument accDoc ON accDoc.id = doc.id ").append(NEWLINE)
.append("INNER JOIN Operatiune op ON op.accounting_doc_id = accDoc.id ").append(NEWLINE)
.append("WHERE doc.tipDoc = :qVandutDoc ").append(NEWLINE)
.append("GROUP BY doc.partner_id ").append(NEWLINE)
.append(") v ON p.id = v.partner_id").append(NEWLINE);
sb.append(" WHERE p.name != :qL1L2 AND p.name != :qOpInterna AND p.name != :qStatPlata AND p.name != :qMarfa AND p.name != :qCard AND p.name != :qStandard").append(NEWLINE);
// totalIncasat < totalSold => is not fully paid
sb.append(" AND COALESCE(i.incasat, 0) < COALESCE(v.vandut, 0)");
final Query query = em.createNativeQuery(sb.toString(), Partner.class);
query.setParameter("qL1L2", Partner.L1_L2);
query.setParameter("qOpInterna", Partner.OP_INTERNA);
query.setParameter("qStatPlata", Partner.STAT_PLATA);
query.setParameter("qMarfa", Partner.MARFA);
query.setParameter("qCard", Partner.CARD_NAME);
query.setParameter("qStandard", Partner.STANDARD_PARTNER_NAME);
query.setParameter("qIncasatDoc", TipDoc.INCASARE.toString());
query.setParameter("qVandutDoc", TipDoc.VANZARE.toString());

小提示在这里！em.createNativeQuery返回一个非通用查询，因此如果您希望结果作为流，并且希望处理该流并强制转换元素，则需要执行以下技巧：

final Stream<?> resultStream = query.getResultStream();

然后你可以处理它：

resultStream
.map(Partner.class::cast)
.flatMap(Partner::getDocumentsStream)