Access數據遷移到Oracle的java代碼 – JAVA編程語言程序開發技術文章

   項目比較緊張,寫瞭一個小工具,目的是能夠快速的將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); 
        } 
    } 
 

發佈留言