用Java实现SQL Server到Oracle数据迁移

图1 New Gallery窗口7)在“创建数据库用户”窗口中输入用户名和口令参数，并选择默认表空间和临时表图3Manage Libraries 窗口•在“管理库”窗口中选择Libraries 标签。

图11 项目属性窗口图12 项目属性窗口⏹在Add Libraries窗口中找到前面设置的SQLServer JSBC 驱动程序库，单击图13Add Libraries窗口图14 添加Oracle JDBC驱动程序●在JDeveloper 10.1.3中写连接SQL Server的JDBC JAVA测试程序：System.out.println("Error Trace in getConnection() : " + e.getMessage());}return con;}private String getConnectionUrl(){returnurl+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+ selectMethod+";";}private void closeConnection(){try{if(con!=null)con.close();con=null;}catch(Exception e){e.printStackTrace();}}public void displayDbProperties(){java.sql.DatabaseMetaData dm = null;java.sql.ResultSet rs = null;try{con= this.getConnection();if(con!=null){dm = con.getMetaData();System.out.println("驱动器信息：");System.out.println("\t驱动器名字: "+ dm.getDriverName());System.out.println("\t驱动器版本: "+ dm.getDriverVersion ());System.out.println("\n数据库信息：");System.out.println("\t数据库名字: "+ dm.getDatabaseProductName());System.out.println("\t数据库版本: "+ dm.getDatabaseProductVersion());System.out.println("显示可用的数据库目录：");rs = dm.getCatalogs();while(rs.next()){System.out.println("\tcatalog: "+ rs.getString(1));}rs.close();rs = null;closeConnection();}else System.out.println("Error: No active Connection");}catch(Exception e){e.printStackTrace();}dm=null;}public static void main(String[] args) {SQLServerT est sQLServerT est = new SQLServerT est();sQLServerT est.displayDbProperties();}}编译运行，结果如下图15SQL Server JDBC连接测试程序运行结果public SQLT oOracleIV() {}private String getConnectionUrl(){returnurl+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+ selectMethod+";";}private java.sql.Connection getConnection(){try{// A. 注册SQL Server JDBC 驱动程序Class.forNam e("com.microsoft.jdbc.sqlserver.SQLServerDriver");// B. 创建新数据库连接con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);if(con!=null) System.out.println("SQL Server Connection Successful!");}catch(Exception e){e.printStackTrace();System.out.println("Error SQL Server Trace in getConnection() : " + e.getMessage());}return con;}private java.sql.Connection getOracleConnection(){try{// A. 注册Oracle JDBC 驱动程序Class.forNam e("oracle.jdbc.driver.OracleDriver");// B. 创建新数据库连接conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oract", "ct" ,"ct");if(conn!=null) System.out.println("Oracle Connection Successful!");}catch(Exception e){e.printStackTrace();System.out.println("Error Oracle Trace in getConnection() : " + e.getMessage());}return conn;}private void closeSQLServerConnection(){try{if(con!=null)con.close();con=null;}catch(Exception e){e.printStackTrace();}}private void closeOracleConnection(){try{if(conn!=null)conn.close();conn=null;}catch(Exception e){e.printStackTrace();}}public void displayDbProperties(){java.sql.DatabaseMetaData dm = null;String InsertStatem ent ="";try{con = this.getConnection();conn = this.getOracleConnection();if(con!=null && conn!=null){// C. 创建Statement 对象Statement stmt = con.createStatement();Statement stmtOracle = conn.createStatement();// D. 执行SELECT 命令，查询结果存放在ResultSet 对象中ResultSet rset = stmt.executeQuery("SELECT * FROM 入库单");// E. 取得ResultSet 结果集相关的信息ResultSetMetaData md = rset.getMetaData();// F. 取得ResultSet 对象中的字段数量int no_cols = md.getColumnCount();String[] col_names = new String[no_cols];// G. 取得ResultSet 对象中各字段名称for (int i = 0; i < no_cols; i++)col_names[i] = md.getColumnLabel(i+1);// H. 先打印出各字段名称for (int i = 0; i < no_cols; i++)System.out.print(col_names[i]+"\t");System.out.println("\n---------------------------------------");// I. 打印每笔数据列中各字段数据while (rset.next()) {for (int i = 1; i <= no_cols; i++)System.out.print(rset.getString(i) + "\t");System.out.println("");// J. 数据插入Oracle 中InsertStatement="INSERT INTOINPUTVOUCHER(InputVoucher_id,AccountSetCode,VoucherNumber," +"InputDate,ProviderCode,Operator,Checker,CarryForwardSign)" +" VALUES (" + rset.getString(1) + ",'" + rset.getString(2) +"','" + rset.getString(3) + "'," +"TO_DATE(SUBSTR('" + rset.getString(4) +"',1,19),'yyyy-mm-dd hh24:mi:ss'),'" + rset.getString(5) +"','" +rset.getString(6) + "','" + rset.getString(7) + "'," + rset.getString(8) + ")";System.out.print(InsertStatement+"\n");stmtOracle.executeUpdate(InsertStatement);}rset.close();rset = null;stmt.close();closeSQLServerConnection();closeOracleConnection();}else System.out.println("Error: No active Connection");}catch(Exception e){e.printStackTrace();}dm=null;// B. 创建新数据库连接conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oract", "ct" ,"ct");if(conn!=null) System.out.println("Oracle Connection Successful!");}catch(Exception e){e.printStackTrace();System.out.println("Error Oracle Trace in getConnection() : " + e.getMessage());}return conn;}private void closeSQLServerConnection(){try{if(con!=null)con.close();con=null;}catch(Exception e){e.printStackTrace();}}private void closeOracleConnection(){try{if(conn!=null)conn.close();conn=null;}catch(Exception e){e.printStackTrace();}}public void displayDbProperties(){java.sql.DatabaseMetaData dm = null;String InsertStatem ent ="";try{con = this.getConnection();conn = this.getOracleConnection();if(con!=null && conn!=null){// C. 创建Statement 对象Statement stmt = con.createStatement();Statement stmtOracle = conn.createStatement();// D. 执行SELECT 命令，查询结果存放在ResultSet 对象中ResultSet rset = stmt.executeQuery("SELECT * FROM 入库单明细");// E. 取得ResultSet 结果集相关的信息ResultSetMetaData md = rset.getMetaData();// F. 取得ResultSet 对象中的字段数量int no_cols = md.getColumnCount();String[] col_names = new String[no_cols];// G. 取得ResultSet 对象中各字段名称for (int i = 0; i < no_cols; i++)col_names[i] = md.getColumnLabel(i+1);// H. 先打印出各字段名称for (int i = 0; i < no_cols; i++)System.out.print(col_names[i]+"\t");System.out.println("\n---------------------------------------");// I. 打印每笔数据列中各字段数据while (rset.next()) {for (int i = 1; i <= no_cols; i++)System.out.print(rset.getString(i) + "\t");System.out.println("");// J. 数据插入Oracle 中InsertStatement="INSERT INTOINPUTVOUCHERDETAIL(InputVoucherDetail_id,InputVoucher_id,AccountSetCode," +"VoucherNumber,MerchCode,MerchName,Unit,Quantity,Price,Note,KeepBoo kSign, ReturnSign,CarryFforwardSign,Storage_id)" +" VALUES (" + rset.getString(1) + "," + rset.getString(2) + ",'" + rset.getString(3) + "','" +rset.getString(4) + "','" + rset.getString(5) + "','" +rset.getString(6) + "','" + rset.getString(7) + "'," +rset.getString(8) + "," + rset.getString(9) + ",'" +rset.getString(10) + "'," + rset.getString(11) + "," +rset.getString(12) + "," + rset.getString(13) + "," +rset.getString(14) + ")";System.out.print(InsertStatement+"\n");stmtOracle.executeUpdate(InsertStatement);}rset.close();rset = null;stmt.close();closeSQLServerConnection();closeOracleConnection();}else System.out.println("Error: No active Connection");}catch(Exception e){e.printStackTrace();}dm=null;}public static void main(String[] args) {SQLToOracleIVDetail sQLT oOracleIVDetail = new SQLToOracleIVDetail();sQLToOracleIVDetail.displayDbProperties();}}编译、执行，完成SQL Server表“入库单明细”到Oracle表“INPUTVOUCHERDETAIL”的数据迁移。