java - SQL Hibernate 没有重新调整与直接 SQL 查询相同的结果

Question

我有一个 Java 应用程序，它通过 Hibernate 与 MySQL 交互。

在我的代码中，我让应用程序执行 SQL 命令:

final SQLQuery query = sf.getCurrentSession().createSQLQuery(
            "select\n" +
            "   id, null as store_id, case when transaction_type = 'SALE' then 1 else -1 end as sign, payment_type,\n" +
            "   sum(cost_before_tax) as amount_before_tax, sum(tax_amount) as tax_amount, sum(cost) as amount,\n" +
            "   sum(ticket_count) as count\n" +
            "from settlement_collection_initial_settlement\n" +
            "where\n" +
            "   business_date between :start and :end\n" +
            (storeID != null ? "    and store_id = :store\n" : "") +
            "group by transaction_type, payment_type"
        );
        query.addEntity(AmountRow.class);
        query.setDate("start", start);
        query.setDate("end", end != null ? end : start);
        if (storeID != null) {
            query.setString("store", new UUIDType().toSQLString(storeID));
        }
        return query.list();

query.list() 返回:

但是，如果我在 MySqlWorkbench 中运行完全相同查询:

select
    id, store_id, case when transaction_type = 'SALE' then 1 else -1 end as sign, payment_type,
    sum(cost_before_tax) as amount_before_tax, sum(tax_amount) as tax_amount, sum(cost) as amount,
    sum(ticket_count) as count
from settlement_collection_initial_settlement
where
    business_date between '2018-07-27' and '2018-07-27'
    and store_id = 'xxxxxx'
group by transaction_type, payment_type

我得到的结果:

请注意，这些结果接近，但不一样。查看两个 CASH 行，直接 SQL 显示第二个 CASH 行，具有不同的 sign 和其他值。所以在本质上，Hibernate 执行的 sql 重复了 CASH 行。

对我来说，这两种方式看起来应该返回完全相同的结果。有人看到为什么我的 Hibernate SQL 返回与我直接执行 SQL 时不同(错误)的结果吗？

更新:

这是我的 AmountRow 类:

@Entity
public class AmountRow {

    static final int SIGN_SALE_OR_DEBIT = 1, SIGN_REFUND_OR_CREDIT = -1;

    @Id
    private float id;

    @ManyToOne
    @JoinColumn(name = "store_id", nullable = true)
    private Store store;

    @Column(nullable = true)
    @Enumerated(EnumType.STRING)
    private Payment.Type paymentType;

    private int sign;

    // NOTE: "$" is the name of a custom class
    @Column(nullable = false)
    @org.hibernate.annotations.Type(type = "com.mycompany.service.$Type")
    private $ amountBeforeTax, taxAmount, amount;

    @Column(nullable = false)
    private int count;

    // Hibernate constructor
    protected AmountRow() {}

    // NOTE: "$" is the name of a custom class
    AmountRow(final $ amountBeforeTax, final $ taxAmount, final $ amount, final int count, final int sign) {
        Assert.noneNull(amountBeforeTax, "amountBeforeTax", taxAmount, "taxAmount", amount, "amount");
        Assert.notNegative(count, "count");
        assertValidSign(sign);
        this.amountBeforeTax = amountBeforeTax;
        this.taxAmount = taxAmount;
        this.amount = amount;
        this.count = count;
        this.sign = sign;
    }

    // NOTE: "$" is the name of a custom class
    AmountRow(final $ amountBeforeTax, final $ taxAmount, final $ amount, final int count, final int sign, final Payment.Type paymentType) {
        this(amountBeforeTax, taxAmount, amount, count, sign);
        this.paymentType = paymentType;
    }

    static void assertValidSign(final int sign) {
        if (sign != SIGN_SALE_OR_DEBIT && sign != SIGN_REFUND_OR_CREDIT)
            throw new IllegalArgumentException("invalid sign " + sign);
    }

    public String toString() {
        return "AmountRow[paymentType=" + paymentType + ", sign=" + sign + ", amountBeforeTax=" + amountBeforeTax + ", taxAmount=" + taxAmount + ", amount=" + amount + ", count=" + count + "]";
    }

    public Store getStore() {
        return store;
    }

    public Payment.Type getPaymentType() {
        return paymentType;
    }

    public int getSign() {
        return sign;
    }

    public $ getAmountBeforeTax() {
        return amountBeforeTax;
    }

    public $ getTaxAmount() {
        return taxAmount;
    }

    public $ getAmount() {
        return amount;
    }

    public int getCount() {
        return count;
    }

    public $ getAmountBeforeTaxPerCount() {
        return count != 0 ? amountBeforeTax.divide(count) : null;
    }

    public $ getAmountPerCount() {
        return count != 0 ? amount.divide(count) : null;
    }
}

Answer 1

这是由您的应用程序和数据库之间的不同时区引起的。

或者:

• 使用像 Epoch 毫秒这样的绝对时间来表示时间(不考虑时区)或
• 检查您的应用程序和 sql server 的时区，选择并将您的日期转换为通用时区

您的 HSQL 还暴露了代码中的另一个问题:存在重复的结果。

您应该发布您的 AmountRow 类以进行进一步调查。

更新

在阅读了您的AmountRow 类之后，问题是使用float 作为@Id 键会导致错误的相等性检查，使Hibernate 无法从DB 加载数据。你永远不应该依赖 float /双重平等。

@df778899 对这个问题有很好的解释。

将@Id字段改成long/int来解决重复。