oracle2db2,从Oracle 到DB2（二）

首先通过下面的图来展示一下从Oracle到DB2的流程：

从上面的图可以看到把Oracle的数据移植到DB2数据库中我们的Java程序起到一个转换器的作用，把Oracle中的不同的数据类型映射为相应的

DB2数据库中的类型同时把从Oracle中查询出的数据插入到DB2数据库中从而完成整个移植过程。下面我就结合Java程序给大家是如何实现这一功能

的

import java.text.*;

import java.util.*;

import java.io.*;

import java.sql.*;

import java.lang.*;

//下面的代码是读属性文件的信息

Properties props=new Properties();

File f=new File("OracleToDb2.properties");

FileInputStream in=new FileInputStream(f);

props.load(in);

String db2_dbname=props.getProperty("db2_dbname");

String db2_username=props.getProperty("db2_username");

String db2_password=props.getProperty("db2_password");

String oracle_url=props.getProperty("oracle_url");

String oracle_dbname=props.getProperty("oracle_dbname");

String oracle_username=props.getProperty("oracle_username");

String oracle_password=props.getProperty("oracle_password");

String io_tables=props.getProperty("io_tables");

通过java的输入流来读取文件中的内容

其中

OracleToDb2.properties为属性文件名；

db2_dbname, db2_username, db2_password 分别为目标DB2数据库的数据库名，数据库用户名，口令。

oracle_url ，oracle_dbname，oracle_username，oracle_password分别为oracle数据库的地址，数据库名，用户名，口令。

io_tables为从Oracle移植到DB2的表名

下面是属性文件的全部内容：

db2_dbname=db2DatabaseName

db2_username=db2UserName

db2_password=db2Password

db2_host=127.0.0.1

oracle_url=127.0.0.1

oracle_dbname=oracleDatabaseName

oracle_username=oracleUsername

oracle_password=oraclePassword

io_tables=io_tables

其中的参数为从属性文件中读入的值，jdbc用的是廋客户机的type4：

DriverManager.registerDriver(new COM.ibm.db2.jdbc.app.DB2Driver());

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

Connection connOra =

DriverManager.getConnection("jdbc:db2:"+db2_dbname, db2_username,db2_password);

//Connection connOra = DriverManager.getConnection("jdbc:db2://"+

db2_host+":"+db2_port+"/"+db2_dbname, db2_username,db2_password);

Connection connDB2 =DriverManager.getConnection("jdbc:oracle:thin:@"+

oracle_url+":1521:"+oracle_dbname,oracle_username, oracle_password);

转换器的核心java类的方法为一个静态的java方法MigrateTable，我在代码中给解释具体的功能的内容和为什么:

//引入oracle和db2的连接及需要移植的表

public static void MigrateTable(Connection connOra, Connection connDB2, String strTableName)

throws Exception

{

//进行防错处理

if (strTableName.length() < 3)

return;

int nRows = 0;

FileWriter fw = null;

PrintWriter pw = null;

Statement stmt = null;

ResultSet rst = null;

Statement stmtDelete = null;

PreparedStatement stmtUp = null;

try

{

//记录转换过程中的信息

fw = new FileWriter("OracleToDb2.log", true);

pw = new PrintWriter(fw);

System.out.println("migrating table " + strTableName);

pw.println("migrating table " + strTableName);

//确定连接赋予SQL语句

stmt = connOra.createStatement();

//进行表内容的查询

rst = stmt.executeQuery("select * from " + strTableName);

//读入表的元数据

ResultSetMetaData meta = rst.getMetaData();

String strSql = "insert into " + strTableName;

String strFields = "(";

String strValues = " values(";

//根据读入的表的元数据的内容进行递归的读取

Object arObjData[] = new Object[meta.getColumnCount()];

for (int i = 0; i < arObjData.length; i ++)

{

if (i > 0)

{

strValues = strValues + ", ";

strFields = strFields + ", ";

}

strValues = strValues + "?";

strFields = strFields + meta.getColumnName(i + 1);

}

strValues = strValues + ")";

strFields = strFields + ")";

strSql = strSql + strFields + strValues;

//建立DB2数据库的连接

stmtDelete = connDB2.createStatement();

//删除原DB2数据库表中的数据

stmtDelete.executeUpdate("delete from " + strTableName);

stmtDelete.close();

stmtUp = connDB2.prepareStatement(strSql);

while (rst.next())

{

for (int i = 0; i < arObjData.length; i ++)

{

arObjData[i] = rst.getObject(i + 1);

//进行二进制、字符类型的转换

if (arObjData[i] != null && arObjData[i] instanceof String)

arObjData[i] = (Object)ISO2GB((String)arObjData[i]);

}

for (int i = 0; i < arObjData.length; i ++)

{

if (meta.getColumnType(i + 1) == java.sql.Types.BLOB)

{

byte arData[] = null;

if (arObjData[i] != null)

arData = ((Blob)arObjData[i]).getBytes(1L, (int)((Blob)arObjData[i]).length());

//读取二进制的数据

stmtUp.setBytes(i + 1, arData);

}

else if (meta.getColumnType(i + 1) == java.sql.Types.CLOB)

{

String strData = null;

if (arObjData[i] != null)

strData = ((Clob)arObjData[i]).getSubString(1L,

(int)((Clob)arObjData[i]).length());

stmtUp.setString(i + 1, ISO2GB(strData));

}

else

{

if (arObjData[i] != null)

stmtUp.setObject(i + 1, arObjData[i]);

else

stmtUp.setString(i + 1, null);

}

}

stmtUp.executeUpdate();

//计数器表示一个表中移植了多少行

nRows ++;

}

上面的代码示整个转换器的关键部分，它实现了整个移植过程的大部分功能而且可以实现代码页，二进制的数据的移植是一个功能强大的移植工具。下面我就运行的步骤作详细的演示：

在操作系统上的类路径中加入jdk的路径。

配置属性文件。

把oracle数据库中的表结构倒成.ddl文件，并调整其中的数据类型映射为DB2中的类型建立表。

编译整个Java文件

执行

查看日志信息

下面是整个程序的完整代码：

import java.text.*;

import java.util.*;

import java.io.*;

import java.sql.*;

import java.lang.*;

public class OracleToDb2

{

public static void main(String[] args)

throws Exception

{

//下面的代码是读属性文件的信息

Properties props=new Properties();

File f=new File("OracleToDb2.properties");

FileInputStream in=new FileInputStream(f);

props.load(in);

String db2_dbname=props.getProperty("db2_dbname");

String db2_username=props.getProperty("db2_username");

String db2_password=props.getProperty("db2_password");

String oracle_url=props.getProperty("oracle_url");

String oracle_dbname=props.getProperty("oracle_dbname");

String oracle_username=props.getProperty("oracle_username");

String oracle_password=props.getProperty("oracle_password");

String io_tables=props.getProperty("io_tables");

//建立DB2和ORACLE数据库的分别连接

// String db2_url = "jdbc:db2://"+db2_host+":"+db2_port+"/"+db2_dbname;

DriverManager.registerDriver(new COM.ibm.db2.jdbc.app.DB2Driver());

//DriverManager.registerDriver(new COM.ibm.db2.jdbc.net.DB2Driver());

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

Connection connOra = DriverManager.getConnection("jdbc:db2:"+db2_dbname,

db2_username,db2_password);

//Connection connOra = DriverManager.getConnection("

jdbc:db2://"+db2_host+":"+db2_port+"/"+db2_dbname, db2_username,db2_password);

Connection connDB2 = DriverManager.getConnection("

jdbc:oracle:thin:@"+oracle_url+":1521:"+oracle_dbname,

oracle_username, oracle_password);

FileReader reader = new FileReader(io_tables);

LineNumberReader lreader = new LineNumberReader(reader);

String strTable = null;

while ((strTable = lreader.readLine()) != null)

{

try

{

MigrateTable(connOra, connDB2, strTable);

}

catch (Exception e)

{

e.printStackTrace();

}

}

reader.close();

connOra.close();

connDB2.close();

System.out.println("ok");

}

public static void MigrateTable(Connection connOra, Connection connDB2,

String strTableName)

throws Exception

{

if (strTableName.length() < 3)

return;

int nRows = 0;

FileWriter fw = null;

PrintWriter pw = null;

Statement stmt = null;

ResultSet rst = null;

Statement stmtDelete = null;

PreparedStatement stmtUp = null;

try

{

fw = new FileWriter("OracleToDb2.log", true);

pw = new PrintWriter(fw);

System.out.println("migrating table " + strTableName);

pw.println("migrating table " + strTableName);

stmt = connOra.createStatement();

rst = stmt.executeQuery("select * from " + strTableName);

ResultSetMetaData meta = rst.getMetaData();

String strSql = "insert into " + strTableName;

String strFields = "(";

String strValues = " values(";

Object arObjData[] = new Object[meta.getColumnCount()];

for (int i = 0; i < arObjData.length; i ++)

{

if (i > 0)

{

strValues = strValues + ", ";

strFields = strFields + ", ";

}

strValues = strValues + "?";

strFields = strFields + meta.getColumnName(i + 1);

}

strValues = strValues + ")";

strFields = strFields + ")";

strSql = strSql + strFields + strValues;

stmtDelete = connDB2.createStatement();

stmtDelete.executeUpdate("delete from " + strTableName);

stmtDelete.close();

stmtUp = connDB2.prepareStatement(strSql);

while (rst.next())

{

for (int i = 0; i < arObjData.length; i ++)

{

arObjData[i] = rst.getObject(i + 1);

if (arObjData[i] != null && arObjData[i] instanceof String)

arObjData[i] = (Object)ISO2GB((String)arObjData[i]);

}

for (int i = 0; i < arObjData.length; i ++)

{

if (meta.getColumnType(i + 1) == java.sql.Types.BLOB)

{

byte arData[] = null;

if (arObjData[i] != null)

arData = ((Blob)arObjData[i]).getBytes(1L,

(int)((Blob)arObjData[i]).length());

stmtUp.setBytes(i + 1, arData);

}

else if (meta.getColumnType(i + 1) == java.sql.Types.CLOB)

{

String strData = null;

if (arObjData[i] != null)

strData = ((Clob)arObjData[i]).getSubString(1L,

(int)((Clob)arObjData[i]).length());

stmtUp.setString(i + 1, ISO2GB(strData));

}

else

{

if (arObjData[i] != null)

stmtUp.setObject(i + 1, arObjData[i]);

else

stmtUp.setString(i + 1, null);

}

}

stmtUp.executeUpdate();

nRows ++;

}

pw.println("" + nRows + " rows migrated");

System.out.println("" + nRows + " rows migrated");

}

catch(Exception e)

{

if (pw != null)

e.printStackTrace(pw);

e.printStackTrace();

}

finally

{

try

{

if (rst != null)

rst.close();

}

catch (Exception ee)

{

ee.printStackTrace(pw);

}

try

{

if (stmt != null)

stmt.close();

}

catch (Exception ee)

{

ee.printStackTrace(pw);

}

try

{

if (stmtDelete != null)

stmtDelete.close();

}

catch (Exception ee)

{

ee.printStackTrace(pw);

}

try

{

if (stmtUp != null)

stmtUp.close();

}

catch (Exception ee)

{

ee.printStackTrace(pw);

}

if (fw != null)

{

fw.flush();

fw.close();

}

}

}