DEVFYI - Developer Resource - FYI

What is the most efficient method of replicating data betwen databases using JDBC?

JDBC Interview Questions and Answers

(Continued from previous question...)

What is the most efficient method of replicating data betwen databases using JDBC?

Within Java, the most efficient method would be, opening connections using the JDBC and inserting or updating the records from one database to the other database, but it depends upon the databases being replicated. If you are using Oracle databases, it has standard methods for replication, and you do not need the JDBC for the replication. Use snapshots like updateable and read-only.
There are different kind of replication. Let us consider the most widely used ones:

A) One Master - One slave
I) If there is not a significant difference between the structure of the database tables, the following method would be useful.
FromDatabase=A; ToDatabase=B
1) Open JDBC connections between the databases A and B.
2) Read a record (RA ) from A using an SQL query.
3) Store the values in the local variables in the Java program.
4) Insert the record in B if PK does not exist for the record RA in B.
5) If the PK exists in B, update the record in B.
6) Repeat the steps 2-5 'til all the records are read by the query.
7) If there are multiple tables to be replicated, repeat steps 2-7 using the different queries.
II)If there is significant difference between the structure of the database tables, the following method would be useful.
FromDatabase=A; ToDatabase=B
1) Open the JDBC connections to the databases A.
2) Read a record ( RA ) from A using an SQL query.
3) Write the output to an XML file-XMLA, according to the DTD for the records for the database A structure.
4) Repeat steps 2 & 3 'til all the records are written to XMLA.
5) If there are more queries, repeat steps repeat steps from 2-4 and write the records to the different entities in the XML file.
6) Transform the XMLA file using the XSL and XSLT to the format useful for the database B and write to the XML file-XMLB.
7) Open the second JDBC connection to the Database B.
8) Read the XMLB file, one record at a time.
9) Insert the record in B if PK does not exist for the record RA in B.
10) If the PK exists in B, update the record in B.
B) One Master - Multiple slaves
The difference here is to open multiple JDBC connections to write to the different databases one record at a time.
C) Multiple Masters:
For multiple masters, use timestamps to compare the times of the records to find out which is the latest record when a record is found in all the master databases. Alternatively, create a column to store the time and date a record is inserted or updated. When records are deleted, record the event in a log file along with the PK.
Prepared statements and batch updates should be used wherever possible in this scenario.

(Continued on next question...)

Other Interview Questions