如何用java编程实现将数据从Access数据库导入到Oracle, 求java详细代码和解释！

Question

如何用java编程实现将数据从Access数据库导入到Oracle, 求java详细代码和解释！

Answer 1

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); 
    } 
} 

}