在使用达梦的迁移工具dts迁移oracle数据时有个数据表一直未能迁移成功,找达梦技术支持未能解决,遂开发此功能。可在此基础之上扩展其他数据库应用及迁移的数据类型。用到的包有ojdbc6.jar,fastjson-1.2.73.jar,Dm7JdbcDriver18.jar,暂时解决问题,供以后使用。
package cn.topnet;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import java.io.*;
import java.sql.*;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.*;
public class Main {
static int onceReadRows = 8000;//每次读取8000条默认
static List<String> columnsList = null;
static List<String> columnsTypeList = null;
static SimpleDateFormat simpleDateFormat = null;
static int onceCurrRows = 0;
public static void main(String[] args) {
Connection fromCon, toCon;
int fromDBType = 0, toDBType = 0;
PageEntity pageEntity = new PageEntity();
// write your code here
String currPath = null, tempFiles = null;
try {
currPath = new File(".").getCanonicalPath();
} catch (IOException e) {
e.printStackTrace();
}
if (currPath != null && currPath.length() > 0) {
tempFiles = currPath + "\\temp\\temp.config";
}
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
//读取配置文件
BufferedReader reader = null;
if (tempFiles != null) {
System.out.println("config path:" + tempFiles);
File file = new File(tempFiles);
Long fileLengthLong = file.length();
char[] fileContent = new char[fileLengthLong.intValue()];
try {
String config = "", tempStr = null;
String[] tempArr = null;
reader = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-8"));
while ((tempStr = reader.readLine()) != null) {
config += tempStr;
}
if (config != null) {
//解析配置文件
JSONObject jsonObject = JSON.parseObject(config);
if (jsonObject.containsKey("fromDB") && jsonObject.containsKey("toDB") && jsonObject.containsKey("tables")) {
fromCon = getJdbcCon(jsonObject.getJSONObject("fromDB"));
fromDBType = jsonObject.getJSONObject("fromDB").getIntValue("dbtype");
if (fromCon != null && !fromCon.isClosed()) {
System.out.println("source DB Connectioned");
} else {
System.out.println("source DB Open err!");
}
toCon = getJdbcCon(jsonObject.getJSONObject("toDB"));
toDBType = jsonObject.getJSONObject("toDB").getIntValue("dbtype");
if (toCon != null && !toCon.isClosed()) {
System.out.println("aim DB Connectioned");
} else {
System.out.println("aim DB Open err!");
}
if (fromCon != null && toCon != null) {
JSONArray tables = jsonObject.getJSONArray("tables");
if (tables != null) {
for (int i = 0; i < tables.size(); i++) {
String tabName = tables.getJSONObject(i).getString("name");
//数据开始条数
int startRows = 0;
onceReadRows = 8000;
if (tables.getJSONObject(i).containsKey("startrows")) {
startRows = tables.getJSONObject(i).getIntValue("startrows");
}
if (tables.getJSONObject(i).containsKey("oncereadrows") && tables.getJSONObject(i).getIntValue("oncereadrows") > 0) {
onceReadRows = tables.getJSONObject(i).getIntValue("oncereadrows");
}
pageEntity.setNextReadRows(startRows);
pageEntity.setCurrRows(startRows);
JSONArray columns = tables.getJSONObject(i).getJSONArray("columns");
//查询语句
String selSql = "";
String selcolu = "";
//取总条数
String selCountSql = "select count(*) from " + tabName + " ";
columnsList = new ArrayList<>();
columnsTypeList = new ArrayList<>();
for (int j = 0; j < columns.size(); j++) {
tempArr = columns.getString(j).split("_");
columnsList.add(tempArr[0]);
columnsTypeList.add(tempArr[1]);
selcolu += tempArr[0] + ",";
}
if (selcolu != null && selcolu.length() > 0) {
selSql = "select " + selcolu.substring(0, selcolu.length() - 1) + " from " + tabName + " ";
}
if (columnsList.size() == columnsTypeList.size() || columnsTypeList.size() > 0) {
System.out.println("ready migration table data table name:" + tabName);
int totalRows = getDataCount(fromCon, selCountSql);
PreparedStatement statement = null;
PreparedStatement writeStatement = null;
ResultSet rs = null;
//取总条数
pageEntity.setTotalRows(totalRows);
while (pageEntity.getTotalRows() > pageEntity.getNextReadRows()) {
tempStr = getPageSql(pageEntity, selSql, fromDBType);
if (tempStr != null && tempStr.length() > 0) {
System.out.println("ready:" + pageEntity.getCurrRows() + "~" + pageEntity.getNextReadRows() + " total:" + pageEntity.getTotalRows() + " surplus:" + (pageEntity.getTotalRows() - pageEntity.getCurrRows()));
//读数据时的
statement = fromCon.prepareStatement(tempStr);
//获取数据
rs = getSqlResult(statement, tempStr);
//获取插入数据模板
writeStatement = GetInsertPreparedStatement(tabName, columnsList, columnsTypeList, rs, toCon);
onceCurrRows = 0;
while (rs.next()) {
//拼装单条插入数据
GetDataStatement(writeStatement, columnsList, columnsTypeList, rs);
writeStatement.addBatch();
//SaveSelSql(tabName, columnsList, columnsTypeList, rs, toCon);
onceCurrRows++;
}
if (rs != null) {
rs.close();
}
if (writeStatement != null) {
writeStatement.executeBatch();
writeStatement.close();
}
if (statement != null) {
statement.close();
}
System.out.println(simpleDateFormat.format(new java.util.Date()) + " insert:" + pageEntity.getCurrRows() + "~" + pageEntity.getNextReadRows() + "completed" + " total:" + pageEntity.getTotalRows() + " surplus:" + (pageEntity.getTotalRows() - pageEntity.getCurrRows()));
}
}
System.out.println("table name:" + tabName + " import data completed!ready check data");
totalRows = getDataCount(toCon, selCountSql);
int differCount = 0;
boolean dataInsertSucess = true;
differCount = pageEntity.getTotalRows() - totalRows;
if (differCount != 0) {
dataInsertSucess = false;
}
System.out.println("source rows total:" + pageEntity.getTotalRows() + " aim rows total:" + totalRows + " differ:" + differCount);
int stotalRows = 0;
for (int j = 0; j < columnsList.size(); j++) {
totalRows = getDataCount(toCon, selCountSql + " where " + columnsList.get(j) + " is null");
stotalRows = getDataCount(fromCon, selCountSql + " where " + columnsList.get(j) + " is null");
differCount = stotalRows - totalRows;
System.out.printf("source rowsName :%s total null:%d aim total null:%d differ:%d ", columnsList.get(j), stotalRows, totalRows, differCount);
System.out.println();
if (differCount != 0) {
dataInsertSucess = false;
}
}
if (dataInsertSucess) {
System.out.println("*********** tablename " + tabName + " data migration sucess!!");
} else {
System.out.println("*********** tablename " + tabName + " Data migration may be wrong please checked!!");
}
System.out.println();
System.out.println();
}
}
}
}
if (fromCon != null && !fromCon.isClosed()) {
fromCon.close();
}
if (toCon != null && !toCon.isClosed()) {
toCon.close();
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 获取数据条数
*
* @param conn
* @param sql
* @return
*/
private static int getDataCount(Connection conn, String sql) {
PreparedStatement statement = null;
ResultSet rs = null;
int rtn = 0;
try {
statement = conn.prepareStatement(sql);
rs = getSqlResult(statement, sql);
if (rs.next()) {
rtn = rs.getInt(1);
}
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return rtn;
}
/**
* 获取插入的PreparedStatement单条数据
*
* @param statement
* @param columns
* @param columnsTypeList
* @param rs
* @return
*/
private static boolean GetDataStatement(PreparedStatement statement, List<String> columns, List<String> columnsTypeList, ResultSet rs) {
for (int i = 0; i < columnsTypeList.size(); i++) {
getPrepart(statement, columns.get(i), i + 1, columnsTypeList.get(i), rs);
}
return true;
}
/**
* 获取原始PreparedStatement模板
*
* @param tableName 表名
* @param columns 列表list
* @param columnsTypeList 列typelist
* @param rs 要插入的数据游标
* @param conn 目标库连接
* @return
*/
private static PreparedStatement GetInsertPreparedStatement(String tableName, List<String> columns, List<String> columnsTypeList, ResultSet rs, Connection conn) {
String sql = "insert into " + tableName + "(";
String valSql = "values(";
PreparedStatement statement = null;
try {
if (rs != null) {
for (int i = 0; i < columns.size(); i++) {
sql += columns.get(i) + ",";
valSql += "?,";
}
if (sql.endsWith(",")) {
sql = sql.substring(0, sql.length() - 1) + ")";
}
if (valSql.endsWith(",")) {
valSql = valSql.substring(0, valSql.length() - 1) + ")";
}
sql = sql + valSql;
statement = conn.prepareStatement(sql);
return statement;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 保存单条数据
*
* @param tableName
* @param columns
* @param columnsTypeList
* @param rs
* @param conn
* @return
*/
private static boolean SaveSingleSql(String tableName, List<String> columns, List<String> columnsTypeList, ResultSet rs, Connection conn) {
String sql = "insert into " + tableName + "(";
String valSql = "values(";
PreparedStatement statement = null;
try {
if (rs != null) {
for (int i = 0; i < columns.size(); i++) {
sql += columns.get(i) + ",";
valSql += "?,";
}
if (sql.endsWith(",")) {
sql = sql.substring(0, sql.length() - 1) + ")";
}
if (valSql.endsWith(",")) {
valSql = valSql.substring(0, valSql.length() - 1) + ")";
}
sql = sql + valSql;
statement = conn.prepareStatement(sql);
for (int i = 0; i < columnsTypeList.size(); i++) {
getPrepart(statement, columns.get(i), i + 1, columnsTypeList.get(i), rs);
}
int rtn = statement.executeUpdate();
if (rtn > 0) {
return true;
} else {
return false;
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return true;
}
/**
* 根据数据类型获取单条数据
*
* @param statement
* @param name
* @param index
* @param type
* @param rs
*/
private static void getPrepart(PreparedStatement statement, String name, int index, String type, ResultSet rs) {
try {
Object obj = rs.getObject(index);
if (obj != null) {
switch (type) {
case "Long":
statement.setLong(index, Long.parseLong(obj.toString()));
break;
case "String":
statement.setString(index, obj.toString());
break;
case "Date":
if (obj.toString().toLowerCase().indexOf("timestamp") >= 0) {
statement.setTimestamp(index, rs.getTimestamp(index));
} else if (obj.toString().toLowerCase().indexOf("date") >= 0) {
statement.setDate(index, rs.getDate(index));
} else {
statement.setTimestamp(index, rs.getTimestamp(index));
}
break;
case "Double":
if (obj != null) {
Double doubleObj = Double.parseDouble(obj.toString());
statement.setDouble(index, doubleObj);
}
break;
case "BLOB":
statement.setBlob(index, rs.getBlob(index));
break;
default:
throw new Exception("db columns type err name" + columnsList.get(index - 1) + "index:" + (index - 1) + " error rows:" + onceCurrRows);
}
} else {
statement.setObject(index, null);
}
} catch (Exception e) {
try {
throw new Exception("data turn error e" + " error rows:" + onceCurrRows + ":" + e.toString());
} catch (Exception e1) {
e1.printStackTrace();
}
}
}
private static String getPageSql(PageEntity pageEntity, String sql, int DBType) {
String tempSql = "";
pageEntity.setCurrRows(pageEntity.getNextReadRows());
pageEntity.setNextReadRows(pageEntity.getCurrRows() + onceReadRows);
if (pageEntity.getNextReadRows() > pageEntity.getTotalRows()) {
pageEntity.setNextReadRows(pageEntity.getTotalRows());
}
switch (DBType) {
case 1:
tempSql = "select * from (select * from (select a.*,ROWNUM AS rowno from (" + sql + ") a)c where c.rowno<=" + pageEntity.getNextReadRows() + ")b where b.rowno>" + pageEntity.getCurrRows();
break;
case 2:
break;
}
return tempSql;
}
private static Connection getJdbcCon(JSONObject jdbcstr) {
Connection conn = null;
String URL = "";
String USER = "";
String PASSWORD = "";
if (!jdbcstr.containsKey("dbtype") || !jdbcstr.containsKey("service") || !jdbcstr.containsKey("prot") || !jdbcstr.containsKey("sid") || !jdbcstr.containsKey("user") || !jdbcstr.containsKey("pwd")) {
try {
throw new Exception("DB Error");
} catch (Exception e) {
e.printStackTrace();
}
}
try {
switch (jdbcstr.getIntValue("dbtype")) {
case 1://oracle
Class.forName("oracle.jdbc.driver.OracleDriver");
URL = "jdbc:oracle:thin:@" + jdbcstr.getString("service") + ":" + jdbcstr.getString("prot") + ":" + jdbcstr.getString("sid");
break;
case 2://dm
Class.forName("dm.jdbc.driver.DmDriver");
URL = "jdbc:dm://" + jdbcstr.getString("service") + ":" + jdbcstr.getString("prot") + "/" + jdbcstr.getString("sid");
break;
}
USER = jdbcstr.getString("user");
PASSWORD = jdbcstr.getString("pwd");
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
System.out.printf("data connection err:%s", e.toString());
System.out.println();
}
return conn;
}
private static ResultSet getSqlResult(PreparedStatement statement, String sql) {
ResultSet rs = null;
try {
//statement.setString(1, name);
rs = statement.executeQuery();
} catch (Exception e) {
try {
throw new Exception("sql err sql:" + sql + "\r\n" + e.toString());
} catch (Exception es) {
es.printStackTrace();
}
}
return rs;
}
}
以下为配置文件
{
"fromDB": {
"dbtype":1,
"service": "127.0.0.1",
"prot": 1522,
"sid": "sesss",
"user": "wwmh",
"pwd": "oracle"
},
"toDB": {
"dbtype": 2,
"service": "127.0.0.1",
"prot": 5238,
"sid": "ORACLETEST",
"user": "wwmh",
"pwd": "123456789"
},
"tables": [
{
"name": "ENTSVR_ADM_APP_BANNER",
"oncereadrows":20000,
"startrows":4,
"columns": [
"ID_String",
"TITLE_String",
"PICURI_String",
"IMAGE_BLOB",
"TOURI_String",
"DISORDER_String",
"CHOSIGN_String"
]
}
]
}