刚好最近做了一次数据迁移,起因是需要导出数据库的表结构和数据,配合WAR包一起发到项目组,但是项目组的数据库是ORACLE,我们测试的时候用的是DB2数据库,需要将数据从DB2迁移一份到ORACLE,并将ORACLE数据导出。
由于表中存在clob大字段,故传统的数据导入insert语句并不能实现。
使用的工具有plsql和dbvisualizer
自己编写的代码有两块
一个是将db2导出的建表语句转换成oracle语法模式
package dataBase;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
public class DB2toOracle {
public static String file = "create table&index.sql";
public static String dest = "D:\\sunds_tb.sql";
public static BufferedReader br = null;
public static BufferedWriter bw = null;
public static void main(String[] args) throws IOException {
loadFile();
fileCopy();
}
/**
* 边读边写,SQL改造
*
* @throws IOException
*/
public static void fileCopy() throws IOException {
String line = ""; // 用来保存每行读取的内容
String tableName = "";
String pkName = "";
String pkParams = "";
StringBuffer sb = new StringBuffer();
String sql = "";
String dropSql = "";
while ((line = br.readLine()) != null) { // 如果 line 为空说明读完了
int begin = 13;
int end = line.indexOf("(");
tableName = line.substring(begin, end).trim();
System.out.println("读取到表名【" + tableName + "】");
String[] str_arr = null;
String pkParam_temp = null;
if ((str_arr = line.split("PRIMARY KEY")).length == 2 ) {
pkParam_temp = line.split("PRIMARY KEY")[1];
pkParams = pkParam_temp.substring(0, pkParam_temp.indexOf(")") + 1);
System.out.println("主键字段" + pkParams);
pkName = tableName.substring(0, tableName.length()-2) + "PK";
System.out.println("读取到主键:" + pkName);
//有主键时,取CONSTRAINT之前的字符串
sql = line.split("CONSTRAINT")[0] + ")TABLESPACE SUNDS241241_SM;" + "ALTER TABLE SUNDS241241." + tableName + " ADD CONSTRAINT " + pkName + " PRIMARY KEY " + pkParams + ";\n";
} else {
//没主键时
sql = line;
}
//拼接删表语句
dropSql = "ALTER TABLE SUNDS241241." + tableName + " DROP PRIMARY KEY CASCADE;" + "\n"
+ "DROP TABLE SUNDS241241." + tableName + " CASCADE CONSTRAINTS;" + "\n\n";
sb.append(dropSql);
sql = sql.replaceAll("CHARACTER", "CHAR").replaceAll("CLOB(1048576)", "CLOB");
bw.write(sql);
bw.newLine();
bw.flush();
}
bw.write(sb.toString());
bw.flush();
// 将读到 buffer 中的内容写出来
bw.close();
br.close();
}
/**
* 建立文件连接
*
* @throws IOException
*/
public static void loadFile() throws IOException {
br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "gbk"));
bw = new BufferedWriter(new FileWriter(dest));
}
}
还有一个是将db2的数据跑批到oracle中
package adatabase;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.StringReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
public class CopyData {
public static Connection db2Conn = null;
public static Connection oracleConn = null;
public static void main(String[] args) throws IOException {
getDB2Conn();
getOracleConn();
copy();
}
private static void copy() throws IOException {
String sql = "select * from SM_PAGE_MODEL_TB";
StringBuffer sb = new StringBuffer();
sb.append("insert into SM_PAGE_MODEL_TB (");
PreparedStatement pstmt_db2 = null;
PreparedStatement pstmt_oracle = null;
StringReader reader = null;
BufferedReader br = null;
// InputStream input = null;
try {
pstmt_db2 = (PreparedStatement) db2Conn.prepareStatement(sql);
ResultSet rs = pstmt_db2.executeQuery();
int col = rs.getMetaData().getColumnCount();
List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
for (int i = 1; i <= col; i++) {
Map<String, String> colName = new HashMap<String, String>();
colName.put(rs.getMetaData().getColumnName(i), rs.getMetaData().getColumnTypeName(i));
mapList.add(colName);
if (i != col) {
sb.append(rs.getMetaData().getColumnName(i));
sb.append(",");
} else {
sb.append(rs.getMetaData().getColumnName(i));
sb.append(") values (");
for (int j = 1; j <= col; j++) {
if (j != col) {
sb.append("?,");
} else {
sb.append("?)");
}
}
}
}
System.out.println(sb.toString());
pstmt_oracle = oracleConn.prepareStatement(sb.toString());
String paramValue = "";
Clob clobValue = null;
String paramName = "";
String paramType = "";
System.out.println("mapList.size():" + mapList.size());
while (rs.next()) {
for (int i = 1; i <= mapList.size(); i++) {
Map<String, String> map = mapList.get(i - 1);
Set<Entry<String, String>> entrySet = map.entrySet();
for (Entry<String, String> entry : entrySet) {
paramName = entry.getKey();
paramType = entry.getValue();
}
if ("CLOB".equalsIgnoreCase(paramType)) {
clobValue = rs.getClob(paramName);
if (clobValue != null) {
br = new BufferedReader(clobValue.getCharacterStream());
StringBuilder stringbuilder = new StringBuilder();
String line = "";
while ((line = br.readLine()) != null) {
stringbuilder.append(line).append("\n");
}
paramValue = stringbuilder.toString();
}
reader = new StringReader(paramValue);
pstmt_oracle.setCharacterStream(i, reader, paramValue.length());
} else if ("VARCHAR".equalsIgnoreCase(paramType) || "VARCHAR2".equalsIgnoreCase(paramType)
|| "CHAR".equalsIgnoreCase(paramType)) {
paramValue = rs.getString(paramName);
pstmt_oracle.setString(i, paramValue);
System.out.println("瀛楃涓茬被鍨嬪弬鏁板悕锛�" + paramName);
System.out.println("瀛楃涓茬被鍨嬪弬鏁板�笺��" + paramValue + "銆�");
if (null != paramValue && paramValue.length() > 50) {
System.out.println("###############瓒呴暱浜�" + paramValue);
}
}
}
// pstmt_oracle.addBatch();
try {
pstmt_oracle.executeUpdate();
} catch (java.sql.SQLException e) {
if (e.toString().indexOf("ORA-00001: 违反唯一约束条件") != -1) {
System.out.println(e.toString());
System.out.println("銆愰噸澶嶃�戝瓧绗︿覆绫诲瀷鍙傛暟鍚嶏細" + paramName);
System.out.println("銆愰噸澶嶃�戝瓧绗︿覆绫诲瀷鍙傛暟鍊笺��" + paramValue + "銆�");
} else {
throw e;
}
}
}
// pstmt_oracle.executeBatch();
oracleConn.commit();
pstmt_oracle.close();
oracleConn.close();
pstmt_db2.close();
db2Conn.close();
} catch (SQLException e) {
try {
oracleConn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
pstmt_oracle.close();
oracleConn.close();
pstmt_db2.close();
db2Conn.close();
br.close();
reader.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static Connection getOracleConn() {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@172.16.3.206:1521:xe";
String username = "sunds241241";
String password = "SUNDS241241";
try {
Class.forName(driver); // classLoader,鍔犺浇瀵瑰簲椹卞姩
oracleConn = (Connection) DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return oracleConn;
}
private static Connection getDB2Conn() {
String driver = "com.ibm.db2.jcc.DB2Driver";
String url = "jdbc:db2://172.16.3.234:50000/sunds241";
String username = "sunds";
String password = "sunds";
try {
Class.forName(driver); // classLoader,鍔犺浇瀵瑰簲椹卞姩
db2Conn = (Connection) DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return db2Conn;
}
}
整体思路是,先将建表语句准备好,然后再oracle中布置表空间,再导入表结构和索引主键。
最后导入init数据,本次的关键点在于clob数据的处理,经查阅资料,已知可以用如下语句进行clob字段的导入
--/
DECLARE
clobValue 表名.字段名%TYPE;
BEGIN
clobValue := 'XXX'; --字段内容
UPDATE 表名 T SET T.字段名 = clobValue WHERE 条件;
COMMIT;
END;
--/
update语句可以换成insert语句
如果不带"--/"无法执行的话,就把头尾的--/加上。
但考虑到如此做法在java代码中不便于实现,因为每张表的clob字段不确定性。故将这个导入方法作为备选排漏使用,跑批个别失败的可用该语句补齐。
之后为避免数据库导入导出clob字段处理麻烦,oracle数据统一导出dmp文件。