Transaction Manager with two databases

[pedrofrv]

Hello.

I'm doing very small command line application that imports some data from a database to another database using the JDBC helper classes.
I have to write to the two databases:
-In the destination database i write the imported data
-In the source the database i write in a temporary table the data that was imported.

Because It's a very small and simple application it doesn't use JTA but I would like to configure the transaction Manager to rollback if i enconter a problem in anyone of the databases.


Can we do something like this?

            transactionDest.execute(new TransactionCallback() {			
				public Object doInTransaction(TransactionStatus arg0) {
					transactionSource.execute(new TransactionCallback() {					
						public Object doInTransaction(TransactionStatus arg0) {
							migrateCompany(contractId);
							return null;
						}					
					});
					return null;
				}			
			});

I don't think that this rollback the two databases in case of an error.
What do you sugest?

[Costin Leau]

First of all try to search the forums - I recall some some threads about this topic. I think you should do one of the following:

1. use JTA
2. write a facade transaction manager that will delegate the rollback to both databases
3. search a solution outside Spring like C-JDBC.

[pedrofrv]

The migration is not going to be made in the contect of a full J2EE server so I can't use JTA.
C-JDBC it seems to be a very complex solution to something this simple.
Can you elaborate the second solution.

I just need something simple like this:

try{
  db1.startTransaction();
  db2.startTransaction();
  doMigration();
  db1.commit();
  db2.commit();
} catch(DataAcessException ex) {
  db1.rolback();
  db2.rollback();
}

I know that some cases this might not work (error on the second commit) but I can recover manually if that ever happens.