Heres my scenario:

I make a call to my external database which returns +1000 records. I would then cache all of them in DB2 using Hibernate3. I only use my cache as a local read-only copy of the external database. If I want to add an additional record, that record will be sent to the external database to be sorted or verified before it is committed. Once that is successful, I make an additional call to that external database to get those +1000 records again. My local copy of those data would all be wiped out from the cache and an add is performed on those new records again.

The problem is that the external database has special knowledge of how records are ordered. I have to make that additional external database call to get the accurate list of records to be cached. As a result, I have no way of reattaching the persisted object to be cached again.

I am new to Hibernate and database concepts so please help me. Does anyone have any tips on how to improve the above procedure with all the adding, deleting, and adding again all in one transaction?

Thanks
Sub

I am not sure if I understand the problem correctly, but I suggest you to use the second level cache provided by Hibernate instead of a local database.

The cache content will be managed transparently to the application by hibernate, performance will be better and, more importantly, application code will be MUCH simpler.