如何用java编程实现将数据从Access数据库导入到Oracle, 求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.2cto.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);
}
}
}