使用 Java 和 Hibernate HQL 验证帐户余额

Question

我的当前帐户余额对象具有以下属性：

long id;                     // Database ID
Date date;                   // date when this balance object was created
boolean currentBalanceFlag;  // indicates this is the most recent balance
float amount;                // the total sum currently in the account balance
float depositAmount;         // amount deposited that resulted in this objects amount 
float withdrawalAmount;      // amount withdrawn that resulted in this objects amount
Balance lastBalance;         // last balance object for traversing
User user;                   // owner of the balance
String note;                 // detailed description of transaction that resulted in current blanace

仅对余额执行了两个操作。存款和取款。

问题是：

如何创建 HQL 查询，该查询将：

-sum 所有 user
的 depositAmount -用户的所有withdrawalAmount总和
-用第二次求和减去第一次求和的结果
- 将减法结果与 Balance 对象中 user 的 amount 进行比较，该对象的 currentBalanceFlag 等于 true

伪代码中的

resultAmount = select ( sum(depositAmount) - sum(withdrawalAmount) ) from Balance where user=user
amount = select amount from Balance where user=user and currentBalanceFlag=true

：我希望通过使用 HQL 查询对数据库进行单次调用获得最终的布尔结果：

resultAmount == amount

Answer 1

select (sum(flow.depositAmount) - sum(flow.withdrawalAmount) - current.amount) 
from Balance flow, Balance current
where flow.user=:user
and current.user=:user 
and current.currentBalanceFlag=true

这将返回所有流量总和与当前余额之间的差额。

顺便说一句，您不必检查数据的完整性。除非您有无数行，否则使用 SQL 总和计算当前余额应该足够快。