I have following properties in my current Account Balance object:
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
There are only two actions performed on the balance. Deposits and withdrawals.
-sum all the depositAmount for user
-sum all withdrawalAmount for user
-subtract result of the first summation from second summation
-compare result of subtraction with the amount for user in Balance object that has currentBalanceFlag equal to true
in pseudo code:
resultAmount = select ( sum(depositAmount) - sum(withdrawalAmount) ) from Balance where user=user
amount = select amount from Balance where user=user and currentBalanceFlag=true
And the final boolean result I'd like to get from single call to the database with HQL query:
resultAmount == amount
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
This will return the difference between the sum of all flows and the current balance.
On a side note, you shouldn't have to check the integrity of your data. Unless you have gazillions of lines, computing the current balance with an SQL sum should be fast enough.