mysql-oracle数据同步

package com.mysql.jdbc; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import com.oracle.jdbc.Connection_Oracle; public class Connection_Mysql { private static DataSource my_ds; private static ThreadLocal<Connection> my_tl = new ThreadLocal<Connection>(); private static Properties my_props = new Properties(); private static DataSource ora_ds; private static ThreadLocal<Connection> ora_tl = new ThreadLocal<Connection>(); private static Properties ora_props = new Properties(); //加载类时，实行静态代码块 static { try { InputStream my_in = Connection_Mysql.class.getClassLoader().getResourceAsStream("com/mysql/jdbc/db_MySql.properties"); my_props.load(my_in); //通过配置文件创建连接池 my_ds = BasicDataSourceFactory.createDataSource(my_props); } catch(Exception e) { e.printStackTrace(); System.out.println("加载配置文件失败"); } try { ora_props.load(Connection_Oracle.class.getClassLoader().getResourceAsStream("com/oracle/jdbc/db_Oracle.properties")); ora_ds = BasicDataSourceFactory.createDataSource(ora_props); } catch(Exception e) { e.printStackTrace(); System.out.println("加载配置文件失败！"); } } public static Connection my_getConnection() throws SQLException { Connection my_conn = my_tl.get(); if(my_conn == null) { my_conn = my_ds.getConnection(); my_tl.set(my_conn); } return my_conn; } public static String my_getObject(String key) { return my_props.getProperty(key); } public static void my_close() throws SQLException { Connection my_conn = my_tl.get(); my_tl.set(null); if(my_conn != null && !my_conn.isClosed()) { my_conn.close(); } } public static Connection ora_getConnection() throws SQLException { Connection ora_conn = ora_tl.get(); if(ora_conn == null) { ora_conn = ora_ds.getConnection(); ora_tl.set(ora_conn); } return ora_conn; } public static String ora_getObject(String key) { return ora_props.getProperty(key); } public static void ora_close() throws SQLException { Connection ora_conn = ora_tl.get(); ora_tl.set(null); if(ora_conn != null && !ora_conn.isClosed()) { ora_conn.close(); } } public static void main(String[] args) throws SQLException { Connection my_conn = my_getConnection(); String my_sql = "select * from tx_cticallin_cdr_synchronous where ifcopy = '0' ORDER BY caller LIMIT 50"; PreparedStatement my_ps = my_conn.prepareStatement(my_sql); ResultSet my_rs = my_ps.executeQuery(); Connection ora_conn = ora_getConnection(); int my_i = 0; while(my_rs.next()) { String ora_sql = "insert into o_cticallin_cdr (o_autoid,o_crm_acc_id,o_dh,o_queue," + "o_callid,o_recpath, o_stime,o_etime,o_caller,o_called,o_calltype" + ",o_callstatus,o_queue_stime,o_ringing_stime,o_link_stime,o_times," + " o_queuetimes,o_ringtimes,o_linktimes,o_creator,o_lastmodifyer," + "o_creatortime,o_modifytime,o_iflock,o_owner,o_transfer,o_chan," + "o_context,o_pbxid,o_standby_pbxid,o_satisfaction_f1,o_satisfaction_f2," + "o_satisfaction_f3,o_satisfaction_f4,o_satisfaction_f5,o_userkeyhisid," + "o_user_lastinput_key,o_userkey_1,o_userkey_2,o_userkey_3,o_userkey_4," + "o_userkey_5,o_userkey_6,o_userkey_7,o_userkey_8,o_userkey_9,o_userkey_10," + "o_vms_recordpath,o_transcaller,o_transcalled,o_transtime,o_transferlinkstime," + "o_transtimes,o_translinktimes) " + "values ( '" + my_rs.getString("autoid") + "'," + "'" + my_rs.getString("crm_acc_id") + "'," + "'" + my_rs.getString("dh") + "'," + "'" + my_rs.getString("queue") + "'," + "'" + my_rs.getString("callid") + "', " + "'" + my_rs.getString("recpath") + "'," + "'" + my_rs.getString("stime") + "'," + "'" + my_rs.getString("etime") + "'," + "'" + my_rs.getString("caller") + "'," + "'" + my_rs.getString("called") + "'," + "'" + my_rs.getString("calltype") + "'," + "" + my_rs.getString("callstatus") + "," + "'" + my_rs.getString("queue_stime") + "'," + "'" + my_rs.getString("ringing_stime") + "'," + "'" + my_rs.getString("link_stime") + "'," + "" + my_rs.getString("times") + "," + "" + my_rs.getString("queuetimes") + "," + "" + my_rs.getString("ringtimes") + "," + "" + my_rs.getString("linktimes") + "," + "'" + my_rs.getString("creator") + "'," + "'" + my_rs.getString("lastmodifyer") + "'," + "'" + my_rs.getString("creatortime") + "'," + "'" + my_rs.getString("modifytime") + "'," + "" + my_rs.getString("iflock") + "," + "'" + my_rs.getString("owner") + "'," + "'" + my_rs.getString("transfer") + "'," + "'" + my_rs.getString("chan") + "'," + "'" + my_rs.getString("context") + "'," + "'" + my_rs.getString("pbxid") + "'," + "'" + my_rs.getString("standby_pbxid") + "'," + "'" + my_rs.getString("satisfaction_f1") + "'," + "'" + my_rs.getString("satisfaction_f2") + "'," + "'" + my_rs.getString("satisfaction_f3") + "'," + "'" + my_rs.getString("satisfaction_f4") + "'," + "'" + my_rs.getString("satisfaction_f5") + "'," + "'" + my_rs.getString("userkeyhisid") + "'," + "'" + my_rs.getString("user_lastinput_key") + "'," + "'" + my_rs.getString("userkey_1") + "'," + "'" + my_rs.getString("userkey_2") + "'," + "'" + my_rs.getString("userkey_3") + "'," + "'" + my_rs.getString("userkey_4") + "'," + "'" + my_rs.getString("userkey_5") + "'," + "'" + my_rs.getString("userkey_6") + "'," + "'" + my_rs.getString("userkey_7") + "'," + "'" + my_rs.getString("userkey_8") + "'," + "'" + my_rs.getString("userkey_9") + "'," + "'" + my_rs.getString("userkey_10") + "'," + "'" + my_rs.getString("vms_recordpath") + "'," + "'" + my_rs.getString("transcaller") + "'," + "'" + my_rs.getString("transcalled") + "'," + "'" + my_rs.getString("transtime") + "'," + "'" + my_rs.getString("transferlinkstime") + "'," + "'" + my_rs.getString("transtimes") + "'," + "'" + my_rs.getString("translinktimes") + "')"; // System.out.println(ora_sql); PreparedStatement ora_ps = ora_conn.prepareStatement(ora_sql); int ora_get = ora_ps.executeUpdate(); if(ora_get<1) { String my_sql_update = "UPDATE tx_cticallin_cdr_synchronous SET ifcopy=2 WHERE autoid='" + my_rs.getString("autoid") + "'"; PreparedStatement my_ps_update = my_conn.prepareStatement(my_sql_update); int my_rs_update = my_ps_update.executeUpdate(); my_ps_update.close(); System.out.println("shibai"); } else { String my_sql_update = "UPDATE tx_cticallin_cdr_synchronous SET ifcopy=1 WHERE autoid='" + my_rs.getString("autoid") + "'"; PreparedStatement my_ps_update = my_conn.prepareStatement(my_sql_update); int my_rs_update = my_ps_update.executeUpdate(); my_ps_update.close(); } my_i++; } my_ps.close(); System.out.println(my_i); String my_sql_1 = "select * from tx_cticdr_synchronous where ifcopy = '0' ORDER BY caller LIMIT 50"; PreparedStatement my_ps_1 = my_conn.prepareStatement(my_sql_1); ResultSet my_rs_1 = my_ps_1.executeQuery(); my_i = 0; while(my_rs_1.next()) { String ora_sql_1 = "insert into O_CTICDR_SYNCHRONOUS (o_autoid,o_crm_acc_id,o_channel,o_channeltype,o_relatechannel,o_relatechanneltype,o_callcode,o_clientactionid,o_clientfloatdata,o_agentid,o_agentname,o_caller,o_called,o_r_caller,o_r_called,o_displaycallerid,o_trunkid,o_linkstime,o_linktimes,o_queuestime,o_queueetime,o_queuetime,o_ringstime,o_ringbackstime,o_ringtimes,o_transtime,o_trantimes,o_meetstime,o_meettimes,o_stime,o_etime,o_times,o_calltype,o_callresultcode,o_queue,o_leavereason,o_leaveinfo,o_aftertime,o_actionid,o