Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes best approach to move data between 2 databases Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "best approach to move data between 2 databases" Watch "best approach to move data between 2 databases" New topic
Author

best approach to move data between 2 databases

manish ahuja
Ranch Hand

Joined: Oct 23, 2003
Posts: 312
Hi All,

I have a question on which approach to follow for Migration of data from one database to another.
The scenario is something like this.

The source & target databases are from differnt vendors.

We have created an exact replica of the table & column structure in the Target database similar to the source database.
I am thinkin of writing a java utility to pull data (resultset) from the source database & then dump it into the target database. I will be using plain JDBC approach for the same.

I will have to load the drivers for each of the databases , create separate connections & all the usual JDBC stuff for both databases.Since this is just one utility when I retrieve the resultset from the source database then i will close the connection to the source & connect to the target database.

So my resultset object will not be available.
Can some one throw some light on what would be an elegant solution to the above problem

Regards
manish ahuja
Ranch Hand

Joined: Oct 23, 2003
Posts: 312
Hi All

To ellaborate on the scenario.
I will be having a connection , statement objects directing to the source database which will fetch the resultset.
Then I need to create a new connection , statement objects pointing to the target database & insert the resultset.
The problem is when I fetch the resultset from connection1 it wont be available when i create connection2 & try to insert into database2.

Can someone throw some light on the same

Regards
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
We have created an exact replica of the table & column structure


You should consider using the bulk load/unload tools that come with your databases.

Using Java to select and insert the data will not perform as well as bulk unloading/loading, will take extra time to develop and provides no advantages.

I would write a program to copy the data only if you need to manipulate the data before inserting it.
manish ahuja
Ranch Hand

Joined: Oct 23, 2003
Posts: 312
Hi Scott

Thanks for your response.
You are absolutely right. I should have mentioned the same in my previous posts.
Yes I would have to slight changes to the data before inserting into the target databse & hence the java program but that is not always. In few cases the data will pass through as it is. We dont have huge volumes of data per table but number of tables are high.

At the moment the design is to migrate using this java utility. Do post your suggestions of any approach that may help

Thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Yes I would have to slight changes to the data before inserting into the target databse & hence the java program but that is not always

Most bulk load tools will let you manipulate the data you are loading as you load it. I'd take another look at what your database provides, make sure your requirement is not already met.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Lucas Lee
Ranch Hand

Joined: Oct 02, 2006
Posts: 53
Why do you must close the database connection from source before you get the database connection from target?
You can hold two or more connections if you want to.
You can close them at the end.
[ October 25, 2006: Message edited by: Lucas Lee ]
manish ahuja
Ranch Hand

Joined: Oct 23, 2003
Posts: 312
Lucas

Thanks for the response.
What I meant to ask was how do i take the resultset from the first connection object & fire an insert into the second database using the second connection.

Say when I m doing while (rs.next()) & iterate over each row & then want to insert the results not using the connection1 (statement1) but connection2(statement2).

This is what i want to achieve. Like having 2 valid Statement or Prepared Statements using 2 different connections each mapping to different databases live at the same time simultaneously in a way that i fetch results from statement1 over connection1 from database1 & dump into database2 using preparedstatement2 over connection2.

This is finalized design. So I have to move in this direction & not allowed to use any database specific utilities

Regards
Lucas Lee
Ranch Hand

Joined: Oct 02, 2006
Posts: 53
I don't know what you mean really.
If you have known you can hold tow different connections simultaneously,then what do you want to do? Insert to the source result set directly?
What I think is:
Get ResultSet from source database,then read data from it and insert data to the target database by executing insert sql.
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
What I meant to ask was how do i take the resultset from the first connection object & fire an insert into the second database using the second connection.


There's no magic here. You'll need to take the values from each row of the result set and set them as parameters on an insert prepared statement.

You could use an ORM tool such as Hibernate. You'd retrieve an object from one Session and persist it to the other. This would eliminate the need to copy the values from the result set to the insert prepared statement. Hibernate would take care of generating and executing the necessary sqls.

This won't perform nearly as well as using a bulk load utility though.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


You could use an ORM tool such as Hibernate. You'd retrieve an object from one Session and persist it to the other. This would eliminate the need to copy the values from the result set to the insert prepared statement. Hibernate would take care of generating and executing the necessary sqls.

ORMs tend to have too high an overhead for bulk loading. Better to just use straight JDBC. Or best, as you note, to use the specialized tools the database provides.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: best approach to move data between 2 databases
 
Similar Threads
Insert multiple rows in database
Merge statement for merging data of tables from two different databases
Rowcount in Resultset
Generic Table Maintenance Using Servlets & JSP
Post GIS In Action - Questions