Our social:

Thursday, 8 October 2015

How To Transfer Mysql Table Data To Oracle Database Table

hy friendss..
Today i faced a problem or question that most of student face in jdbc programming. and the question is How To Transfer Mysql Table Data To Oracle Database Table. It means copying data from one database to another database. This questionn sounds like it is very complex task but reality is different. you need to follow some steps and then you are ready to start copying...

General

step1: keep tow local database in machine(oracle and mysql).
step2: ready both jdbc api(connector j and ojdbc14.jar).
step3: keep ready driver class name of both jdbc api with url.

Database

In oracle

Table Name Student
 Name                                                    Type
 ----------------------------------------- ---------------------------
 ROLL                                               NUMBER(5)
 SNAME                                            VARCHAR2(20)
 COURSE                                          VARCHAR2(20)

Note: don't insert data in oracle database...bcz we will copy here data of mysql database

In mysql

Table Name Student
 Name                                                    Type
 ----------------------------------------- ---------------------------
 ROLL                                               INT(5)
 SNAME                                            VARCHAR(20)
 COURSE                                          VARCHAR(20)

Note:Insert some values in this table mandatoryy...
GS Blue Funky Casual Shoes





now almost we are ready to write code to copy data from one databse to another database. i am using my eclipse IDE to wirte this code...

                                                     Sample Code:




/**
 *
 */
package org.smartyy.MysqlToOracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
/**
 * @author sunny
 * @Date 08-10-015
 */
public class MysqlToOracleDataTransfer {
public static void main(String[] args) throws Exception {
// Gather input from end user...
// Register Oracle Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// Register Mysql Driver
Class.forName("com.mysql.jdbc.Driver");
// Establish Connection of Oracle Driver
Connection oracon = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
// Establish Connection of Mysql
Connection mycon = DriverManager.getConnection("jdbc:mysql://localhost/sdb1", "root", "root");
// prepare statement to fetch data from mysql....
Statement stmt = mycon.createStatement();
// prepare statement for oracle
PreparedStatement ps = oracon.prepareStatement("insert into student values(?,?,?)");
// process mysql resultset
ResultSet rs = stmt.executeQuery("select * from student");
while (rs.next()) {
int no = rs.getInt(1);
String name = rs.getString(2);
String course = rs.getString(3);
// set values to parameter of prepared statement
ps.setInt(1, no);
ps.setString(2, name);
ps.setString(3, course);
// execute query
ps.executeUpdate();
}// end of while
System.out.println("records are copied to mysql to oracle database.....");
// close jdbc objects
stmt.close();
rs.close();
ps.close();
oracon.close();
mycon.close();
}//main
}//class


0 comments:

Post a Comment