CODEWITHSUNDEEP
javajakarta-eesynchronization
kittu
kittu

Reputation: 7008

Need to sync two tables from different databases in java

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

Upvotes: 0

Views: 5082

Answers (2)

PeterMmm
PeterMmm

Reputation: 24630

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();
    }

Upvotes: 4

atish shimpi
atish shimpi

Reputation: 5023

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.

Upvotes: 0

Related Questions