Need to sync two tables from different databases in java

Question

Usually to sync a table with in the network I can write a simple sql query and run it to sync the tables, but what if the source database is different(could be db2, mssql, mysql, oracle - from where I can get the data in to destination table).

How should I go about writing the code in java to achieve this?. I know I can create dblinks in java to fetch data from external network database but dblinks works only with database of same type. I am not able to figure out the implementation in my head. Please point me in the right direction so I can take baby steps and learn the same as I am new to java

PeterMmm · Accepted Answer · 2014-12-30 11:19:34Z

4

You can do this with plain JDBC. Code example, not tested:

void migrate() throws SQLException {
        Connection connA = DriverManager.getConnection("jdbc:oracle:thin:@myhost:1521:orcl");
        Connection connB = DriverManager.getConnection("jdbc:mysql://192.168.1.33/test");

        PreparedStatement stmA = connA.prepareStatement("select * from product where 1=1");
        PreparedStatement stmB = connB.prepareStatement("insert into prod values(?,?,?,?)");

        ResultSet rs = stmA.executeQuery();
        while (rs.next()) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                stmB.setObject(i + 1, rs.getObject(i + 1));
            }
            stmB.executeUpdate();
        }

        connA.close();
        connB.close();
    }

answered Dec 30, 2014 at 11:19

PeterMmm

24.7k15 gold badges77 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kittu Over a year ago

Hi Peter I have already tried something similar to what you have answered. Is there any optimized way to do it ? like using API's(example: oracle api) for implementing the same ? Please help!!

PeterMmm Over a year ago

You are new to Java and asks for optimization. What issues do you have, memory, speed ?

kittu Over a year ago

I mean to say instead of implementing the code using normal sql query, I was given a clue that oracle will notify the changes/updates in the tables occurred and based on which I have to develop code. I was guessing it must something related to oracle api and jdbc and to my surprise I found this link on google which talks about notification service. Please guide me if i am going on right path? [link]docs.oracle.com/cd/E11882_01/java.112/e16548/…

atish shimpi · Accepted Answer · 2014-12-30 11:14:17Z

0

Data Migration If you want to migrate data from one database to another database of different types,

Suppose you have A and B two different database, you have to export data from A database in XML format, table name is XML parent node (create structure as per your database).

Then parse that XML's and create objects of each table present in A, after that use any XML parser which will parse your XML, using hibernate save() api put each object in another database manage if tables has relationships in them then insert master table data first then insert child items data.

By this approach you no need to be dependent on destination (B) database each time, with the help of hibernate you can easily change your database.

edited Dec 30, 2014 at 11:14

answered Dec 30, 2014 at 11:00

atish shimpi

5,0212 gold badges35 silver badges51 bronze badges

Collectives™ on Stack Overflow

Need to sync two tables from different databases in java

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related