oracle数据库脚本转为sqlServer、DB2

小乔

package util;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
/**
* @author wyl
* @Description TODO
* @date 2016-5-19
*
*/
public class OracleToOthers {
/**
* @Description: TODO
* @param args  
*/
public static void main(String[] args) {
/*
* 原oracle数据库脚本由plSql导出,导出时只选择create tables选项
* 对于原oracle数据库中的表或者列的注释，如果注释中存在换行符，生成的目标脚本会有错误，请在生成的目标脚本做修改
*/
//原文件路径
String oracleFile = "C:\\Users\\admin\\Desktop\\table.sql";
//目标文件路径
String sqlServerFile = "C:\\Users\\admin\\Desktop\\table_sqlserver.sql";
String db2File = "C:\\Users\\admin\\Desktop\\table_db2.sql";
oracleToSqlserver(oracleFile,sqlServerFile);
oracleToDB2(oracleFile,db2File);

}
/**
* @Description: 由oracle脚本转为sqlserver脚本
* @param oracleFile    原oracle数据库脚本文件路径
* @param sqlServerFile    转为sqlserver脚本文件的目标路径
*/
public static void oracleToSqlserver(String oracleFile,String sqlServerFile) {
File readFile = new File(oracleFile);
File writeFile = new File(sqlServerFile);
BufferedReader reader = null;
BufferedWriter writer = null;
try {
reader = new BufferedReader(new FileReader(readFile));
writer = new BufferedWriter(new FileWriter(writeFile));
StringBuffer sb = new StringBuffer();
String tempString = null;
String tempStr = null;
String tempS = null;
String tableName = null;
String column = null;
String common = null;
int endIndex = 0;
// 一次读入一行，直到读入null为文件结束
while ((tempString = reader.readLine()) != null) {
// 显示行号
//System.out.println("line " + line + ": " + tempString);
//转换数据类型
tempString=tempString.replaceAll("VARCHAR2","VARCHAR");
tempString=tempString.replaceAll("NUMBER","DECIMAL");
tempString=tempString.replaceAll("CLOB","TEXT");
if(tempString.indexOf("TIMESTAMP")!=-1){
tempString=tempString.replaceAll("TIMESTAMP","DATETIME");
int str = tempString.indexOf("(");
int end = tempString.indexOf(")");
System.out.println(tempString.substring(str, end+1));
tempString=tempString.replaceAll("\\("+tempString.substring(str+1, end)+"\\)", "");
}
//添加注释
if(tempString.startsWith("prompt")||tempString.startsWith("set ")||tempString.startsWith("commit;")||tempString.startsWith("spool")){
tempString = "--"+tempString;
}
//更改sql注释脚本
if(tempString.startsWith("comment on")){
tempStr = tempString;
continue;
}
if(tempString.startsWith("  is '")){
tempString = tempStr + tempString;
if(!tempString.endsWith("';")){
tempS = tempString;
tempStr = "";
continue;
}
}
if(tempString.startsWith("';")){
tempString = tempS+tempString;
tempS = "";
}
//表注释
if(tempString.startsWith("comment on table ")&&tempString.endsWith("';")){
endIndex = tempString.indexOf("  is '");
//表名
tableName = tempString.substring(17,endIndex);
String[] strA = tempString.split("'");
common = strA[1];
tempString = "EXECUTE sp_addextendedproperty N'MS_Description', N'"+common+"', N'user', N'dbo', N'table', N'"+tableName+"', NULL, NULL";
}
//列注释
if(tempString.startsWith("comment on column ")&&tempString.endsWith("';")){
endIndex = tempString.indexOf(".");
//表名
tableName = tempString.substring(18,endIndex);
String[] strA = tempString.split("'");
int endIn = strA[0].indexOf("  is");
column = strA[0].substring(endIndex+1, endIn);
common = strA[1];
tempString = "EXECUTE sp_addextendedproperty N'MS_Description', N'"+common+"', N'user', N'dbo', N'table', N'"+tableName+"', N'column', N'"+column+"'";
}
// 打印
                System.out.println(tempString);
sb.append(tempString+"\r\n");
}
reader.close();
//写入目标文件
            writer.write(sb.toString());
writer.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (reader != null) {
try {
reader.close();
} catch (IOException e1) {
}
}
}
}
/**
* @Description: 由oracle脚本转为DB2脚本
* @param oracleFile    原oracle数据库脚本文件路径
* @param db2File    转为DB2脚本的目标文件路径
*/
public static void oracleToDB2(String oracleFile,String db2File) {
File readFile = new File(oracleFile);
File writeFile = new File(db2File);
BufferedReader reader = null;
BufferedWriter writer = null;
try {
reader = new BufferedReader(new FileReader(readFile));
writer = new BufferedWriter(new FileWriter(writeFile));
StringBuffer sb = new StringBuffer();
String tempString = null;
// 一次读入一行，直到读入null为文件结束
while ((tempString = reader.readLine()) != null) {
// 显示行号
//System.out.println("line " + line + ": " + tempString);
//转换数据类型
tempString=tempString.replaceAll("VARCHAR2","VARCHAR");
tempString=tempString.replaceAll("NUMBER","DECIMAL");
tempString=tempString.replaceAll("CLOB","CLOB");
//添加注释
if(tempString.startsWith("prompt")||tempString.startsWith("set ")||tempString.startsWith("commit;")||tempString.startsWith("spool")){
tempString = "--"+tempString;
}
System.out.println(tempString);
sb.append(tempString+"\r\n");
}
reader.close();
//写入目标文件
            writer.write(sb.toString());
writer.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (reader != null) {
try {
reader.close();
} catch (IOException e1) {
}
}
}
}
}