Hi,
I am trying to pull data from two tables into a single list, how can I get the total result ordered correctly so that it orders the full result set. Currently, when I run my query, the data comes back in the correct order for each of the individual queries issued by hibernate, but not as a single correctly ordered list.
To explain slightly better,
I have archived my transaction data into a separate database using a datbase link. So I now have current transactions in my operations database, and the archived transactions in a separate database that is accessed through a view on the operations database.
I have two classes the transaction class with the appropriate detail
and the archived_transaction class which extends the transaction class, with no additional fields or data in it.
I have two hbm files, with identical mappings except that one goes to the transaction table, and the other to the archived transaction view - see below.
Now when I issue my get transactions call
Code:
List<Transaction> transactionList = getHibernateTemplate().find("from com.myproject.dataobject.Transaction t order by t.batch.name, t.id");
the hibernate sql that is generated and run is:-
Code:
Hibernate: select archivedtr0_.TRANSACTION_ID as TRANSACT1_4_0_, archivedba1_.batch_id as batch1_3_1_, archivedtr0_.BATCH_ID as BATCH2_4_0_, archivedtr0_.AMOUNT as AMO7_4_0_, archivedba1_.name as name4_3_1_from ARCHIVED_TRANSACTION archivedtr0_ left outer join archived_batch archivedba1_ on archivedtr0_.BATCH_ID=archivedba1_.batch_id order by archivedba1_.merchant_name, archivedtr0_.BATCH_ID, archivedtr0_.TRANSACTION_ID
Hibernate: select transactio0_.TRANSACTION_ID as TRANSACT1_41_0_, batch1_.batch_id as batch1_8_1_, transactio0_.BATCH_ID as BATCH2_41_0_, transactio0_.AMOUNT as AMO7_41_0_, batch1_.name as name4_8_1_ from TRANSACTION transactio0_ left outer join batch batch1_ on transactio0_.BATCH_ID=batch1_.batch_id order by batch1_.name, transactio0_.BATCH_ID, transactio0_.TRANSACTION_ID
Is there anyway to specify that
1) I want the whole result set from the two queries to be ordered and not the results within each query.
and
2) I want the second query run first (ie the live data before the archive data).
Any help will be greatly appreciated.
Code:
<hibernate-mapping package="com.myproject.dataobject">
<class name="Transaction" table="TRANSACTION">
<id
column="TRANSACTION_ID"
name="id"
type="long"
>
<generator class="native" />
</id>
<many-to-one name="batch"
not-null="true"
class="com.myproject.dataobject.Batch"
cascade="save-update"
>
<column name="BATCH_ID" />
</many-to-one>
<property
column="AMOUNT"
length="20"
name="smount"
not-null="false"
type="big_decimal"
/>
</class>
</hibernate-mapping>
<hibernate-mapping package="com.myproject.dataobject">
<class name="ArchiveTransaction" table="ARCHIVE_TRANSACTION">
<id
column="TRANSACTION_ID"
name="id"
type="long"
>
<generator class="native" />
</id>
<many-to-one name="batch"
not-null="true"
class="com.myproject.dataobject.ArchiveBatch"
cascade="save-update"
>
<column name="BATCH_ID" />
</many-to-one>
<property
column="AMOUNT"
length="20"
name="smount"
not-null="false"
type="big_decimal"
/>
</class>
</hibernate-mapping>
Regards
Login
FAQ
Search