Many a times we are required to move data from one Database Management system to another. Unfortunately very little is done by the database vendors to make this an easier experience. I obviously understand that no vendor would want that people should move data from their product to another but that is something which customers deserve.

Well in the absence a good tool to do that we are left with only one option, write a script in our preferred language and move the data physically from one database to another. My preferred language is JAVA but you can use any other language. I’ll show how to connect to Oracle and MySQL and then we’ll move data from Oracle to MySQL. Piece of Cake. :P.

CONNECT TO ORACLE    ##->    READ FROM ORACLE    ##->    CONNECT TO MySQL    ##->    WRITE INTO MySQL    ##-> CLOSE CONNECTION

Connect to ORACLE (version 9i)

Prerequisite : Add the path of the following file in the CLASSPATH.
C:\oracle\ora90\jdbc\lib\classes12.zip


Code :

Class.forName(“oracle.jdbc.driver.OracleDriver”); //Loading the driver
String urlora=”jdbc:oracle:thin:@127.0.0.1:1521:home”; //URL for connecting to the database
Connection connora=DriverManager.getConnection(urlora,”username”,”password”);
//Creating Connection class object.
Statement stmtora=connora.createStatement(); //Statement class object to execute the query
ResultSet rsetora=stmtora.executeQuery(“QUERY inside double quotes”);
//Write the query here.
//ResultSet class object contains the retrieved data after the successful execution of the query.


So once you have the data retrieved from the Oracle database, you just need to write it into the MySQL database(Checkout the next part).

Connect to MySQL

Prerequisite : Download the drivers required to connect to the MySQL database from here. Extract it and add the path of the .jar file in your classpath.

Code :

String urlmy=”jdbc:mysql://localhost/sushant”;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
Connection connmy=DriverManager.getConnection(urlmy,”username”,”password”);
Statement stmtmy=connmy.createStatement();
stmtmy.executeUpdate(“Query inside quotes.”);  //Write all the data read from Oracle into MySQL

And you are done!!! You can run this in loop and print the number of records of data moved from one database to other.

Note : The process can also be reverted to move data from MySQL to Oracle.

Advertisements

Comments on: "Move Data from Oracle to MySQL using JAVA" (1)

  1. hi,

    i want to create table and then i want to move the data?

    How can i create table i.e based on source oracle and destination mysql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: