首先通过下面的图来展示一下从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();
}
}
}
|
