hi all,
I am having issues in defining a formula for a Entity attribute. Basically there are 2 entities Account and TransactionRecord. On the Account entity I am trying to define a derived property "creditBalance" which will fetch the sum of all Transaction amounts.
Code:
<hibernate-mapping default-cascade="none">
<class name="org.thoughtwok.cash4j.domain.AccountImpl" table="ACCOUNT" dynamic-insert="false" dynamic-update="false">
<id name="id" type="java.lang.Long" unsaved-value="null">
<column name="ID" sql-type="BIGINT"/>
<generator class="native">
</generator>
</id>
<property name="accountNumber" >
<column name="ACCOUNT_NUMBER" not-null="true" unique="false" sql-type="VARCHAR(256)"/>
<type name="java.lang.String">
</type>
</property>
<property name="creditBalance">
<formula>select sum(tr.amount) from org.thoughtwok.cash4j.domain.TransactionRecordImpl tr where tr.creditAccount.id = id </formula>
</property>
<set name="creditTransactions" order-by="CREDIT_ACCOUNT_FK" lazy="true" fetch="select" inverse="true">
<key foreign-key="TRANSACTION_RECORD_CREDIT_ACCC">
<column name="CREDIT_ACCOUNT_FK" sql-type="BIGINT"/>
</key>
<one-to-many class="org.thoughtwok.cash4j.domain.TransactionRecordImpl"/>
</set>
<set name="debitTransactions" order-by="DEBIT_ACCOUNT_FK" lazy="true" fetch="select" inverse="true">
<key foreign-key="TRANSACTION_RECORD_DEBIT_ACCOC">
<column name="DEBIT_ACCOUNT_FK" sql-type="BIGINT"/>
</key>
<one-to-many class="org.thoughtwok.cash4j.domain.TransactionRecordImpl"/>
</set>
</class>
</hibernate-mapping>
Code:
<hibernate-mapping default-cascade="none">
<class name="org.thoughtwok.cash4j.domain.TransactionRecordImpl" table="TRANSACTION_RECORD" dynamic-insert="false" dynamic-update="false">
<id name="id" type="java.lang.Long" unsaved-value="null">
<column name="ID" sql-type="BIGINT"/>
<generator class="native">
</generator>
</id>
<property name="amount" >
<column name="AMOUNT" not-null="true" unique="false" sql-type="DOUBLE"/>
<type name="java.lang.Double">
</type>
</property>
<many-to-one name="creditAccount" class="org.thoughtwok.cash4j.domain.AccountImpl" foreign-key="TRANSACTION_RECORD_CREDIT_ACCC" lazy="proxy" fetch="select">
<column name="CREDIT_ACCOUNT_FK" not-null="true" sql-type="BIGINT"/>
</many-to-one>
<many-to-one name="debitAccount" class="org.thoughtwok.cash4j.domain.AccountImpl" foreign-key="TRANSACTION_RECORD_DEBIT_ACCOC" lazy="proxy" fetch="select">
<column name="DEBIT_ACCOUNT_FK" not-null="true" sql-type="BIGINT"/>
</many-to-one>
</class>
</hibernate-mapping>
The Account.hbm.xml has a declaration
Code:
<property name="creditBalance">
<formula>select sum(tr.amount) from org.thoughtwok.cash4j.domain.TransactionRecordImpl tr where tr.creditAccount.id = id </formula>
</property>
For some reason this is generating sql queries such as
Code:
select accountimp0_.ID as ID0_0_, accountimp0_.ACCOUNT_NUMBER as ACCOUNT2_0_0_, accountimp0_.DESCRIPTION as DESCRIPT3_0_0_, accountimp0_.CREATED_ON as CREATED4_0_0_, accountimp0_.ACCOUNT_TYPE as ACCOUNT5_0_0_, accountimp0_.ACCOUNT_STATUS as ACCOUNT6_0_0_, select sum(tr.amount) from org.thoughtwok.cash4j.domain.TransactionRecordImpl tr where tr.creditAccount.id = accountimp0_.id as formula0_0_ from ACCOUNT accountimp0_ where accountimp0_.ID=?
The above sql query shows the issue.
select sum(tr.amount) from org.thoughtwok.cash4j.domain.TransactionRecordImpl tr where tr.creditAccount.id = accountimp0_.id as formula0_0_ is appearing as part of the larger select statement and not as a subquery
hibernate is still using the entity name rather than the database table name
I believe (and the documentation says so) that such values will be determined using subqueries. This is the not the case.
Can someone please help in figuring out how to use the
formula element here effectively. Any help would be appreciated
Thanks
anand raman
Login
FAQ
Search
