There is no official tool to export data from Oracle to MySQL database. Recurrently I need to copy a huge table from the source Oracle to MySQL database. It seems to me that configuring any available tools is more complicated than just using a very simple and therefore reliable java class. The class simply executes in the target database an INSERT statement for each row of the SELECT query executed in the source database. I execute such an tiny application as a background job to copy millions of rows from one VM to another overnight.
The prerequisite is that the target table exists in the target database. But DDL is the easiest part of an export.
public class Main {
static String SOURCE_TABLE_NAME = "ONM_MOL_DATA", TARGET_TABLE_NAME = "ONM_MOL_DATA_COPY";
String SQL = "select * from " + SOURCE_TABLE_NAME;
String INSERT = "insert into " + TARGET_TABLE_NAME + " values()";
String DELETE = "delete from " + TARGET_TABLE_NAME;
void run() throws SQLException, IOException {
try (Connection oracleCon = ConnectionFactory.getOracleConnection();
Connection mySQLCon = ConnectionFactory.getMySQLConnection();
Statement oracleStmt = oracleCon.createStatement();
ResultSet oracleRs = oracleStmt.executeQuery(SQL)) {
ResultSetMetaData md = oracleRs.getMetaData();
String mySQLInsert = getInsert(md.getColumnCount());
// clean
Statement mySQLDeleteStmt = mySQLCon.createStatement();
mySQLDeleteStmt.executeUpdate(DELETE);
// copy
mySQLCon.setAutoCommit(false);
System.out.println(mySQLInsert);
PreparedStatement mySQLInsertStmt = mySQLCon.prepareStatement(mySQLInsert);
int row = 0;
while (oracleRs.next()) {
for (int c = 1; c <= md.getColumnCount(); c++) {
mySQLInsertStmt.setObject(c, oracleRs.getObject(c));
}
mySQLInsertStmt.executeUpdate();
row++;
if (row % 100000 == 0) {
System.out.println("row=" + row);
}
}
mySQLCon.commit();
}
}
String getQuestionMarks(int count) {
String[] ar = new String[count];
Arrays.fill(ar, "?");
return String.join(",", ar);
}
String getInsert(int count) {
return INSERT.replace("values()", "values(" + getQuestionMarks(count) + ")");
}
public static void main(String... args) throws SQLException, IOException {
new Main().run();
}
}
There is nothing special in ConnectionFactory class:
public class ConnectionFactory {
public static Connection getMySQLConnection() throws SQLException {
return DriverManager.getConnection(MYSQL_URL);
}
public static Connection getOracleConnection() throws SQLException {
return DriverManager.getConnection(ORACLE_URL);
}
}
