項目比較緊張,寫瞭一個小工具,目的是能夠快速的將Access的數據遷移到Oracle平臺下,沒有重構,沒有優化,純原生態,估計會有各種錯誤,但是基本的思路在。
1)需要配置Access的數據源
2)需要導入Oracle支持的jar包
3)需要將代碼中的statement修改為preparedstatement
4)需要將代碼進行重構,更容易維護
5)需要導入pinyin4j的jar包,以為在Access裡面有列名是漢字
如下:
[java]
package com.yinhai.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.StringTokenizer;
public class AccessToOracleSql {
// Access的數據源配置
public static String urlAccess = "jdbc:odbc:jgyl";
public static String usrAccess = "admin";
public static String pwdAccess = "xxxxx";
// Oracle的配置
public static String urlOracle = "jdbc:oracle:thin:@10.62.xxx.xxx:1521:xxxx2";
public static String usrOracle = "chenzw";
public static String pwdOracle = "chenzw";
// 常量
public static final int CONNECTION_ORACLE = 1;
public static final int CONNECTION_ACCESS = 0;
// access中需要遷移的表名
public static String accessTables = "00C'C00^01C'C01^1997C'JF1997^1998C'JF1998^1999C'JF1999^2000C'JF2000^" +
"2001年表C'JF2001^2002繳費表'JF2002^2003繳費表'JF2003^2004繳費表'JF2004^2005繳費表'JF2005^2006繳費表'JF2006^" +
"2007繳費表'JF2007^2008繳費表'JF2008^2009繳費表'JF2009^2009繳費表(備份)'JF2009BAK^2010繳費表'JF2010^" +
"2011繳費表'JF2011^2012繳費表'JF2012^97C'C97^98C'C98^99C'C99^" +
"單位表'DANWEIBIAO^繳費轉移記錄'JFZYJL^失業繳費'SYJF^養老繳費表'YLJF^員工名單'YGMD^粘貼錯誤'ZTCW^轉入人員歷史繳費'ZRRYLSJF";
// typeAccessToOracle
public static String typeAccessToOracle = "COUNTER'NUMBER(10)^VARCHAR'VARCHAR2(300)^DOUBLE'NUMBER(10,4)^"
+ "BIT'NUMBER(4)^REAL'NUMBER(10,4)^INTEGER'NUMBER(10,4)^DATETIME'VARCHAR2(20)";
public static Map oracleTableFromAccess;
// 加載驅動
static {
oracleTableFromAccess = AccessToOracleSql.transStringToMap(accessTables);
String driver_Access = "sun.jdbc.odbc.JdbcOdbcDriver";
String driver_Oracle = "oracle.jdbc.driver.OracleDriver";
try {
Class.forName(driver_Access);
Class.forName(driver_Oracle);
} catch (Exception e) {
e.printStackTrace();
}
}
// 獲得連接
public static Connection getConnection(int connectType) {
Connection con = null;
try {
switch (connectType) {
case 0:
con = DriverManager.getConnection(urlAccess, usrAccess,
pwdAccess);
break;
case 1:
con = DriverManager.getConnection(urlOracle, usrOracle,
pwdOracle);
break;
}
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
// 得到Oracle的建表語句
public static Map getOracleSql(ResultSet resultSet, String TableName) {
Map resultMap = new HashMap();
Map transColNameMap = new HashMap();
try {
Map map = transStringToMap(typeAccessToOracle);
StringBuffer sb = new StringBuffer();
ResultSetMetaData meta = resultSet.getMetaData();
sb.append("create table " + TableName + "(\n");
int cols = meta.getColumnCount();
while (resultSet.next()) {
for (int i = 1; i <= cols; i++) {
String oracleColName = Pinyin4j.getPinYin(alterNameAddQ(meta.getColumnName(i))).replace("(", "").replace(")", "").replace("%", "");
sb.append(" " + oracleColName + " ");
sb.append(transType(meta.getColumnTypeName(i)));
transColNameMap.put(meta.getColumnName(i),oracleColName);
sb.append(i == cols ? "\n" : ",\n");
}
break;
}
sb.append(")");
resultMap.put("oracleSql", sb.toString());
resultMap.put("transColNameMap", transColNameMap);
return resultMap;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
// 如果第一個字母為數字的話,則在前面加Q
public static String alterNameAddQ(String tableName) {
String beginChar = tableName.substring(0, 1);
if (beginChar.matches("[0-9]")) {
return "Q" + tableName;
}
return tableName;
}
// 關閉連接
public static void close(ResultSet rs, Statement stmt, Connection con) {
try {
if (rs != null)
rs.close();
} catch (Exception ex) {
ex.printStackTrace();
}
try {
if (stmt != null)
stmt.close();
} catch (Exception ex) {
ex.printStackTrace();
}
try {
if (con != null)
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
// 轉碼
public static String transType(String type) {
return (String) transStringToMap(typeAccessToOracle).get(type);
}
/**
* 方法名稱:transStringToMap 傳入參數:mapString 形如 username'chenziwen^password'1234
* 返回值:Map
*/
public static Map transStringToMap(String mapString) {
Map map = new HashMap();
java.util.StringTokenizer items;
for (StringTokenizer entrys = new StringTokenizer(mapString, "^"); entrys
.hasMoreTokens(); map.put(items.nextToken(), items
.hasMoreTokens() ? ((Object) (items.nextToken())) : null))
items = new StringTokenizer(entrys.nextToken(), "'");
return map;
}
// 主方法
public static void main(String[] args) throws Exception {
java.util.Map.Entry entry;
for(Iterator iterator = oracleTableFromAccess.entrySet().iterator(); iterator.hasNext();){
entry = (java.util.Map.Entry)iterator.next();
String accessTableName = (String)entry.getKey();
String oracleTableName = (String)entry.getValue();
Connection connAccess = getConnection(AccessToOracleSql.CONNECTION_ACCESS);
Connection connOracle = getConnection(AccessToOracleSql.CONNECTION_ORACLE);
Statement stAccess = connAccess.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rsAccess = null;
PreparedStatement psOracle = null;
String createTableSql = "select * from " + accessTableName;
//TODO 刪除
System.out.println("查詢Access表:"+createTableSql);
String dropTableSql = "drop table "+ oracleTableName;
rsAccess = stAccess.executeQuery(createTableSql);
Map map = getOracleSql(rsAccess, oracleTableName);
//TODO 刪除
System.out.println("刪除Oracle的表:"+dropTableSql);
psOracle = connOracle.prepareStatement(dropTableSql);
try {
psOracle.execute();
} catch (Exception e) {
System.out.println("異常錯誤:"+e.getMessage());
}
psOracle = connOracle.prepareStatement((String) map.get("oracleSql"));
psOracle.execute();
Map transColNameMap = (Map)map.get("transColNameMap");
// 循環結果集,將數據插入到Oracle中
ResultSetMetaData meta = rsAccess.getMetaData();
int cols = meta.getColumnCount();
while (rsAccess.next()) {
StringBuffer sbBeginHalf = new StringBuffer();
StringBuffer sbEndHalf = new StringBuffer();
sbBeginHalf.append("insert into " + oracleTableName + "(");
for (int j = 1; j <= cols; j++) {
sbBeginHalf.append((String)transColNameMap.get(meta.getColumnName(j)));
sbBeginHalf.append(j<cols?",":"");
if(("VARCHAR".equals(meta.getColumnTypeName(j))||"DATETIME".equals(meta.getColumnTypeName(j)))&&null!=rsAccess.getString(j)){
sbEndHalf.append("'"+rsAccess.getString(j)+"'");
}else{
sbEndHalf.append(rsAccess.getString(j));
} www.aiwalls.com
sbEndHalf.append(j<cols?",":"");
}
sbBeginHalf.append(") values (");
sbEndHalf.append(")");
String insertSql = sbBeginHalf.append(sbEndHalf.toString()).toString();
psOracle = connOracle.prepareStatement(insertSql);
psOracle.execute();
}
//TODO 刪除
System.out.println("插入oracle的表數據:"+oracleTableName);
// 關閉連接
close(rsAccess, stAccess, connAccess);
close(null, psOracle, connOracle);
}
}
}