oracle 数据导入 mysql_oracle 数据导入到MySQL

package io.example;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

/**

*

* @author tzj

*

*/

public class DTSo2m {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Class.forName("com.mysql.jdbc.Driver");

//Connection connectionO = DriverManager.getConnection("jdbc:oracle:thin:@10.3.52.x:1521/ship", "***",

//"***");

//

//Connection connectionM=DriverManager.getConnection("jdbc:mysql://123.56.x.x:13306",

//"***" , "***");

Connection connectionO = DriverManager.getConnection("jdbc:oracle:thin:@10.3.x.x:1521/x", "***",

"***");

Connection connectionM=DriverManager.getConnection("jdbc:mysql://47.95.163.x:3306",

"***" , "***");

List

list = new ArrayList<>();

list.add("tb_api_order");

//list.add("TB_GLOBAL_MANAGE_USER");

//list.add("EMS_SEA_ORDER_IN");

//list.add("ems_sea_declare_trans_info");

//list.add("TB_GLOBAL_MENUS");

//list.add("ems_sea_prov");

//list.add("EMS_SEA_COUNTRY_CASCADE");

//list.add("TB_EMS_SEA_DICT");

//list.add("EMS_SEA_ADJUSTMENT");

//list.add("EMS_SEA_ACCOUNT");

//list.add("EMS_SEA_SKU");

//list.add("EMS_SEA_STOREHOUSE");

//list.add("ems_sea_sku_inventory");

//list.add("EMS_SEA_NOTICE");

//list.add("TB_GLOBAL_ROLE_R_MENUS");

//list.add("EMS_SEA_STOCKNUM");

//list.add("EMS_SEA_DECLARE_ORDER");

//list.add("ems_sea_city");

//list.add("TB_GLOBAL_GB_MENU");

//list.add("EMS_SEA_BOX");

//list.add("EMS_SEA_DECLARE_GOODS");

//list.add("EMS_SEA_DELIVERYMODE");

//list.add("TB_GLOBAL_USER_R_ROLE");

//list.add("TB_GLOBAL_ROLE");

//list.add("EMS_SEA_ORDER_IN_AUDIT");

//list.add("EMS_SEA_HEAD_SKY");

//list.add("EMS_SEA_HEAD_SEA");

//list.add("EMS_SEA_FEE_LOG");

//list.add("ems_sea_fee");

//list.add("EMS_SEA_ORDER_OUT");

//list.add("EMS_SEA_ORDER_IN_LOG");

//list.add("EMS_SEA_ORDER_ITEM");

//list.add("EMS_SEA_GOODS_TYPE");

//list.add("EMS_SEA_BASE_INFO");

//list.add("EMS_SEA_COUNTY");

//list.add("TB_GLOBAL_USER_AUDIT");

//list.add("EMS_SEA_ORDER_IN_AUDIT");

//list.add("EMS_SEA_ORDER_OUT_LOG");

//list.add("EMS_SEA_ATTACH_FILE");

//list.add("ems_sea_carrier");

//list.add("EMS_SEA_RETURN_ORDER");

//list.add("EMS_SEA_TRANS_ORDER");

//list.add("EMS_SEA_DECLARE_ORDER_HIS");

//list.add("EMS_SEA_CLIENT_JOIN_ORDER");

//list.add("EMS_SEA_BOX_HIS");

//list.add("EMS_SEA_BOX_GOODS_HIS");

//list.add("EMS_SEA_BOX_GOODS");

//list.add("TMP_EMS_SEA_BOX_GOODS");

//list.add("TMP_EMS_SEA_BOX");

//list.add("EMS_SEA_AUDIT_RECORD");

//list.add("EMS_SEA_RECHARGEDOWN");

//list.add("EMS_SEA_USER_INVENTORY");

//list.add("EMS_SEA_RECHARGE");

for(String pram : list){

oracleData2Mysql(connectionO, connectionM, pram);

}

}

/**

*

* @param oracleConn

* @param mysqlConn

* @param tabname

* @throws SQLException

*/

public static void oracleData2Mysql(Connection oracleConn,Connection mysqlConn,String tabname) throws SQLException{

String where="where createdate> to_date('2017-11-23 00:00:00','yyyy-MM-dd HH24:mi:ss')";

ResultSet resultSet=oracleConn.createStatement().executeQuery("SELECT * FROM "+tabname+where);

StringBuilder insertBuilder=new StringBuilder("insert into `"+tabname+"`");

StringBuilder colBuilder=new StringBuilder("(");

StringBuilder valBuilder=new StringBuilder(" values(");

ResultSetMetaData metaData=

resultSet.getMetaData();

for (int i = 0; i < metaData.getColumnCount(); i++) {

colBuilder.append("`"+metaData.getColumnName(i+1)+"`,");

valBuilder.append("?,");

}

colBuilder.append(")");

valBuilder.append(")");

insertBuilder.append(colBuilder).append(valBuilder);

String prepareSql=insertBuilder.toString().replaceAll(",\\)", ")");

PreparedStatement preparedStatement=mysqlConn.prepareStatement(prepareSql);

int count=0;

System.out.println("执行"+prepareSql);

while(resultSet.next()){

for (int i = 0; i < metaData.getColumnCount(); i++) {

int colindex=i+1;

preparedStatement.setObject(colindex,resultSet.getObject(colindex),metaData.getColumnType(colindex));

preparedStatement.setString(i+1, resultSet.getString(i+1));

}

try {

count+=preparedStatement.executeUpdate();

} catch (MySQLIntegrityConstraintViolationException e) {

e.printStackTrace();

continue;

}

//if(count%100==0){

mysqlConn.commit();

//System.out.println("save");

//}

System.out.println("更新"+count+"条");

}

//mysqlConn.commit();

}

}