两个不同编码的oralce之间的数据迁移

两个不同编码的oralce之间的数据迁移，代码是从网上找的，原来的代码是sql server迁移到oracle。稍做修改，可以实现不同编码或者不同版本（高版本往低版本）oracle的数据迁移。

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.sql.Statement;

public class HandExpData

{

public static String table = "";

// sqlserver

public static String sqlserverDriver = "oracle.jdbc.driver.OracleDriver";

public static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

public static String username = "trswcm65";

public static String password = "trsadmin";

// oracle

public static String oracleDriver = "oracle.jdbc.driver.OracleDriver";

public static String oracleurl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

public static String ousername = "system";

public static String opassword = "trsadmin";

public static Connection con = null;

public static PreparedStatement sm = null;

public static ResultSet rs = null;

public static Connection oraclecon = null;

public static PreparedStatement oraclesm = null;

public void getConnection()

{

try

{

Class.forName(oracleDriver);

oraclecon = DriverManager.getConnection(oracleurl, ousername,

opassword);

}

catch (Exception e)

{

}

}

public static void main(String[] args)

{

String tableString = "wcmdocument,docid;";

String[] tables = tableString.split(";");

int nlength = tables.length;

for(int i=0;i

{

String[] tabs = tables[i].split(",");

table = tabs[0];

String key = tabs[1];

converttableDate(table, key);

}

}

public static void converttableDate(String table, String key)

{

long beginTime = System.currentTimeMillis();

System.out.println(table + "。。。。。。。。。。导入开始。。。。。。。。。。。。"); int auto = 1;

try

{

// 查询sql

String sqlCount = "select count(*) count from " + username+"."+table;

Class.forName(sqlserverDriver);

Connection conCount = DriverManager.getConnection(url, username,

password);

Statement smCount = conCount.createStatement();

ResultSet rsCount = smCount.executeQuery(sqlCount);

int rowCount = 0;

while (rsCount.next())

{

rowCount = rsCount.getInt(1);

}

rsCount.close();

smCount.close();

conCount.close();

// 设置分页

System.out.println("rowCount = " + rowCount);

// 查询sql

String sqlTable = "select * from " + username+"."+table + " order by " + key

+ " asc";

Class.forName(sqlserverDriver);

con = DriverManager.getConnection(url, username, password);

System.out.println(sqlTable);

sm = con.prepareStatement(sqlTable);

rs = sm.executeQuery();

ResultSetMetaData rsmd1 = rs.getMetaData();

//System.out.println(rsmd1.getColumnCount());

// oracle

String values = "insert into " + ousername+"."+table + "(";

for (int k = 1; k <= rsmd1.getColumnCount(); k++)

{

values = values + (rsmd1.getColumnName(k) + ",");

}

values = values.substring(0, values.length() - 1);

values = values + ") values (";

for (int k = 1; k <= rsmd1.getColumnCount(); k++)

{

values = values + ("?,");

}

values = values.substring(0, values.length() - 1);

values = values + ")";

//System.out.println(values);

String insert = values;

Class.forName(oracleDriver);

oraclecon = DriverManager.getConnection(oracleurl, ousername, opassword);

oraclesm = oraclecon.prepareStatement(insert);

while (rs.next())

{

auto++;

//System.out.println(auto);

for (int k = 1; k <= rsmd1.getColumnCount(); k++)

{

//System.out.println(rsmd1.getColumnTypeName(k));

if(rsmd1.getColumnTypeName(k).indexOf("NUMBER")!=-1) {

oraclesm.setLong(k, rs.getLong(k));

}

else if(rsmd1.getColumnTypeName(k).indexOf("DATE")!=-1) {

oraclesm.setTimestamp(k, rs.getTimestamp(k));

}

else

{

oraclesm.setString(k, rs.getString(k));

}

}

oraclesm.addBatch();

if(auto?==0)

{

oraclesm.executeBatch();

System.out.println("入库："+auto);

oraclesm.clearBatch();

}

}

oraclesm.executeBatch();

System.out.println("共入库："+(auto-1));

}

catch (Exception e)

{

System.out.println("========记录=========" + auto);

e.printStackTrace();

System.out.println(e.getMessage());

}

finally

{

try

{

if(rs!=null)

{

rs.close();

rs = null;

}

if(con!=null)

{

con.close();

}

if (oraclesm != null)

{

oraclesm.close();

oraclesm = null;

}

if (oraclecon != null)

{

oraclecon.close();

oraclecon = null;

}

}

catch (SQLException e)

{

e.printStackTrace();

}

}

System.out.println(table + "。。。。。。。。。。导入结束。。。。。。。。。。。。"); long endTime = System.currentTimeMillis();

System.out.println(table + "。。。。。。。。。。导数据用时。。。。。。。。。。。。"

+ (endTime - beginTime) / 1000 + "秒");

}

}