(Multiple datasources) Mirror data using hibernate+spring

Using hibernate+spring+AS400 database in a web application:

There are 3 datasources (one per region), DS for Australia, DS for UK and DS for USA. Schemas are the same in all the datasources.

Now data needs to be persisted to the datasource, rule is that the primary region (selected by the end u开发者_运维问答ser using UI) should always be used as primary datasource to save data. In addition, if the primary region is not USA (say user selected UK region) then data should be persisted to both UK datasource and USA datasource.

I am aware of a simple manual approach of opening all the session factories and managing everything manually. http://www.java-forums.org/database/867-hibernate-multiple-databases.html

What are other alternates available and the best way to implement this ?

Does it needs to be ACID? If not, my first idea would be to add a @PostPersist which adds the entity to a JMS topic, which is read by 3 clients, each representing one database. Each client would, then, verify if the entity is already updated in its database (by checking the optimistic locking column, for instance).

With this approach you may have data consistency problems and you would need to act on each failure for each client. On the other hand, you'd avoid the latency (which is why you have 3 DataSources in the first place).

But really, there are several possible solutions :-)

a system-level (iSeries) approach would be to enable remote journaling of your Australia and UK data source to US site, and have USA site apply journal changes to its local DB2. Note that SQL sources on DB2/400 are always journaled (to provide commitment control capabilities) so you will just need to enable remote journaling. For an overview of this feature, go to http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fcl%2Fapyjrnchg.htm or similar depending on your OS version.

An alternative to developing your own program that applies journal changes is to use one of the packaged replication solutions -- IBM DataReplicator, Vision Solutions' ODS/ODM, etc.