利用jdbc解决跨数据库之间数据迁移(oracle迁移db2)

二次开发的项目,基本都会牵扯到数据的迁移,记录一下不适用框架利用原生jdbc来访问数据库进行数据的迁移操作.

第一个 数据库(例bd2):

public class ConnectionDB2 {private static final String URL="jdbc:db2://ip地址:端口/名字"; //DB2数据库urlprivate static final String USER="***";//DB2数据库账号private static final String PASSWORD="***"; //DB2数据库密码static{try {Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); //利用反射注册驱动} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}}public Connection getConnection(){Connection conn=null;try {conn=DriverManager.getConnection(URL, USER, PASSWORD);} catch (SQLException e) {e.printStackTrace();}return conn;}}

第二个 数据库(例oracle)

public class ConnectionORACLE {private static final String URL="jdbc:oracle:thin:@ip:端口:nbcsora"; //ORACLE数据库urlprivate static final String USER="***";//ORACLE数据库账号private static final String PASSWORD="***"; //ORACLE数据库密码static{try {Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); //利用反射注册驱动} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}}public Connection getConnection(){Connection conn=null;try {conn=DriverManager.getConnection(URL, USER, PASSWORD);} catch (SQLException e) {e.printStackTrace();}return conn;}}

下面就是公共的连接关闭资源方法:

public class CloseFunction {//关闭连接方法,释放资源public static void closeConnection(Connection conn){if (conn !=null){ try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}//关闭执行sql，释放资源public static void closeExecuteSQL(Statement preparedStatement){if (preparedStatement !=null){try {preparedStatement.close();} catch (SQLException e) {e.printStackTrace();}}}//关闭查询SQL结果，释放资源public static void closeResultSet(ResultSet resultSet){if(resultSet !=null){try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}}}

上面搞定,那就可以写jdbc原生代码进行对数据库的转移了:

public void test(){long startTime=System.currentTimeMillis(); //记录开始时间Connection connDB2=null; //链接DB2数据库Connection connOracle=null; //链接oracle数据库Statement statement=null; ResultSet resultSet=null;Statement statementOR=null; ResultSet resultSetOR=null; //oracle 结果集PreparedStatement preparedStatement=null; try {ConnectionORACLE connectionOracle=new ConnectionORACLE();System.out.println(connectionOracle+"链接oracle成功！");ConnectionDB2 connectionDB2=new ConnectionDB2();System.out.println(connectionDB2+"-->>>链接DB2数据库成功！");connOracle=connectionOracle.getConnection();//oracle语句String oracleSQLT="SELECT transmsn_dt_tm_conv, acq_ins_id_cd_conv,sys_tra_no_conv,frn_ins_tp,msg_tp_conv,transmsn_dt_tm,acq_ins_id_cd,sys_tra_no,related_key,alt_key," +"fwd_ins_tp,acq_ins_tp,rcv_ins_tp,iss_ins_tp,rsn_cd,rsn_cd_conv,trans_rcv_ts,to_ts,moni_dist,moni_in," +"risk_in,pre_proc_st,frn_bat_no,trans_id,trans_id_conv,trans_seq,trans_seq_conv,trans_tp,trans_tp_conv,resnd_num," +"settle_dt,settle_mon,settle_d,cycle_no,sms_dms_conv_in,msg_tp,pri_acct_no,pri_acct_no_conv,bin,cups_card_in," +"cups_sig_card_in,card_brand,card_class,card_attr,trans_chnl,card_media,proc_cd,proc_cd_conv,trans_at,fwd_settle_at," +"rcv_settle_at,fwd_settle_conv_rt,rcv_settle_conv_rt,fee_dir_in,dms_trans_id,iss_ins_id_cd,loc_trans_tm,loc_trans_tm_conv,loc_trans_dt,mchnt_tp," +"mchnt_tp_conv,acq_ins_cntry_cd,pos_entry_md_cd,pos_entry_md_cd_conv,card_seq_id,pos_cond_cd,pos_cond_cd_conv,pos_pin_capture_cd,fwd_ins_id_cd,fwd_ins_id_cd_conv," +"retri_ref_no,retri_ref_no_conv,term_id,term_id_conv,mchnt_cd,mchnt_cd_conv,card_accptr_nm_loc,card_accptr_nm_loc_conv,sec_related_ctrl_inf,addn_pos_inf," +"addn_pos_inf_conv,orig_msg_tp,orig_msg_tp_conv,orig_sys_tra_no,orig_sys_tra_no_conv,orig_transmsn_dt_tm,orig_transmsn_dt_tm_conv,orig_acq_ins_id_cd,orig_acq_ins_id_cd_conv,orig_fwd_ins_id_cd," +"orig_fwd_ins_id_cd_conv,rcv_ins_id_cd,rcv_ins_id_cd_conv,auth_resp,session_id,cgw_svc_name,file_upd_cd,svc_retr_cd,visa_private,fwd_ins_cntry_cd," +"frn_trans_fee_at,frn_trans_proc_fee_at,trans_fee_at,fwd_proc_in,rcv_proc_in,trans_st,trans_fin_ts,frn_settle_dt,trans_curr_cd,fwd_settle_curr_cd," +"rcv_settle_curr_cd,cdhd_at,cdhd_conv_rt,cdhd_curr_cd,expire_dt,conv_dt,ext_pan_cntry_cd,auth_id_resp_cd,iss_resp_cd,iss_resp_cd_conv," +"acq_resp_cd,acq_resp_cd_conv,repl_at,func_cd,orig_data,orig_at,resv_fld1,resv_fld2,addn_pvt_dat,addn_at," +"iss_addn_dat,iss_ins_res,swt_dat,fina_net_dat,fina_net_id,cups_def_fld,cups_def_fld_conv,internal_header,ext_header,visa_header," +"track_1_dat,track_2_dat,track_3_dat,id_no,tfr_in_acct_id,tfr_out_acct_id,cups_res,cups_res_conv,acq_ins_res,pri_acct_no_ext," +"ic_flds,resv_fld3,resv_fld4,resv_fld5,resv_fld6,cdhd_auth_info,cdhd_auth_rslt from TBL_SWT_FRN_TRANS_LOG1  ";//373 400//执行db2数据库sql语句String SQL="INSERT INTO FPDB_SWT.TBL_SWT_FRN_TRANS_LOG3(transmsn_dt_tm_conv, acq_ins_id_cd_conv,sys_tra_no_conv,frn_ins_tp,msg_tp_conv,transmsn_dt_tm,acq_ins_id_cd,sys_tra_no,related_key,alt_key," +"fwd_ins_tp,acq_ins_tp,rcv_ins_tp,iss_ins_tp,rsn_cd,rsn_cd_conv,trans_rcv_ts,to_ts,moni_dist,moni_in," +"risk_in,pre_proc_st,frn_bat_no,trans_id,trans_id_conv,trans_seq,trans_seq_conv,trans_tp,trans_tp_conv,resnd_num," +"settle_dt,settle_mon,settle_d,cycle_no,sms_dms_conv_in,msg_tp,pri_acct_no,pri_acct_no_conv,bin,cups_card_in," +"cups_sig_card_in,card_brand,card_class,card_attr,trans_chnl,card_media,proc_cd,proc_cd_conv,trans_at,fwd_settle_at," +"rcv_settle_at,fwd_settle_conv_rt,rcv_settle_conv_rt,fee_dir_in,dms_trans_id,iss_ins_id_cd,loc_trans_tm,loc_trans_tm_conv,loc_trans_dt,mchnt_tp," +"mchnt_tp_conv,acq_ins_cntry_cd,pos_entry_md_cd,pos_entry_md_cd_conv,card_seq_id,pos_cond_cd,pos_cond_cd_conv,pos_pin_capture_cd,fwd_ins_id_cd,fwd_ins_id_cd_conv," +"retri_ref_no,retri_ref_no_conv,term_id,term_id_conv,mchnt_cd,mchnt_cd_conv,card_accptr_nm_loc,card_accptr_nm_loc_conv,sec_related_ctrl_inf,addn_pos_inf," +"addn_pos_inf_conv,orig_msg_tp,orig_msg_tp_conv,orig_sys_tra_no,orig_sys_tra_no_conv,orig_transmsn_dt_tm,orig_transmsn_dt_tm_conv,orig_acq_ins_id_cd,orig_acq_ins_id_cd_conv,orig_fwd_ins_id_cd," +"orig_fwd_ins_id_cd_conv,rcv_ins_id_cd,rcv_ins_id_cd_conv,auth_resp,session_id,cgw_svc_name,file_upd_cd,svc_retr_cd,visa_private,fwd_ins_cntry_cd," +"frn_trans_fee_at,frn_trans_proc_fee_at,trans_fee_at,fwd_proc_in,rcv_proc_in,trans_st,trans_fin_ts,frn_settle_dt,trans_curr_cd,fwd_settle_curr_cd," +"rcv_settle_curr_cd,cdhd_at,cdhd_conv_rt,cdhd_curr_cd,expire_dt,conv_dt,ext_pan_cntry_cd,auth_id_resp_cd,iss_resp_cd,iss_resp_cd_conv," +"acq_resp_cd,acq_resp_cd_conv,repl_at,func_cd,orig_data,orig_at,resv_fld1,resv_fld2,addn_pvt_dat,addn_at," +"iss_addn_dat,iss_ins_res,swt_dat,fina_net_dat,fina_net_id,cups_def_fld,cups_def_fld_conv,internal_header,ext_header,visa_header," +"track_1_dat,track_2_dat,track_3_dat,id_no,tfr_in_acct_id,tfr_out_acct_id,cups_res,cups_res_conv,acq_ins_res,pri_acct_no_ext," +"ic_flds,resv_fld3,resv_fld4,resv_fld5,resv_fld6,cdhd_auth_info,CDHD_AUTH_RSLT)"+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";statementOR=connOracle.createStatement();statementOR.execute(oracleSQLT);//执行sqlresultSetOR=statementOR.getResultSet();//获取oracle结果集;connDB2=connectionDB2.getConnection();//db2链接statement=connDB2.createStatement();connDB2.setAutoCommit(false);preparedStatement=connDB2.prepareStatement(SQL);//预编译int num=0;while (resultSetOR.next()) {num ++;preparedStatement.setString(1, LangUtils.returnString(resultSetOR.getString("transmsn_dt_tm_conv")));....//preparedStatement.executeUpdate();preparedStatement.addBatch();//每一万次在oracle数据库里提交事务if(num>10000){preparedStatement.executeBatch();connDB2.commit();num=0;}}preparedStatement.executeBatch();connDB2.commit();} catch (Exception e) {try {connDB2.rollback();} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();}finally{new CloseFunction().closeConnection(connOracle); //关闭oracle数据库，释放资源new CloseFunction().closeConnection(connDB2); //关闭DB2数据库，释放资源long endTime=System.currentTimeMillis(); //记录程序结束时间System.out.println("总的时间："+(endTime-startTime)/1000+"秒");}}