问题：

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

茅曾琪

2023-03-14

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<>();

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（“INCASAT”=“兑现”，“VANDUT”=“出售”）：

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

共有1个答案

毛越

2023-03-14

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

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

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());

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

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

类似资料：

Hql查询到条件查询

和异常：实体：GirMotiuRebuig 这个标准起作用了：
相同的查询-不同的执行计划

问题内容： SQL2008。我有一个测试表：我用10k测试行填充它。我运行以下两个查询：我不知道为什么这两个查询有不同的执行计划。查询1确实针对UQ_Sale_RowVersion索引进行索引搜索。查询2对PK_Sale进行索引扫描。我想查询2做索引查找。我将不胜感激。谢谢你。 [编辑] 尝试使用datetime2而不是rowversion。同样的问题。我也尝试强制使用索引（查
JSP executeQuery（）和MSMS在相同查询中的不同结果

我正在使用tomcat、jsp和MSSQL2008，希望获得一些汇总数据，如每日、每周、每月和每年。从这里，我搜索了如何对来自MSSQL的这些摘要数据使用datapart（）函数。在JSP中，我使用在MSMS中，查询指令进行测试。我在JSP程序中遇到了两个问题，但在MS SQL Server Management Studio中使用相同的查询可以很好地工作。 null 我的问题是：
Spring Mongo条件查询两次相同字段

我正在尝试使用spring查询mongodb。我们有一个集合，其中包含一棵树，并包含一个项目列表作为树路径（因此我们可以轻松遍历树）。我们有一个需要返回特定节点的所有子节点的查询。我们的查询基于选择路径中有节点（父节点）且比父节点（级别）低一级的所有节点。我们的标准如下：唉，当我们在mongodb上调用它时，我们会得到以下异常：组织。springframework。数据mongodb。Inva
DynamoDB对相同数据的扫描与查询

我有一个用例，我必须在Dynamo DB中返回表的所有元素。假设我的表有一个分区键（列X），所有行中的值都相同，比如“monitor”和排序键（列Y），元素不同。以下方法的执行时间是否会有任何差异，还是相同？扫描整张桌子。基于具有“监视器”的分区键查询数据。
Order by查询返回不同的排序

按查询排序返回不同的排序，为什么？结果：为什么？

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

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档