Access数据迁移到Oracle的java代码

    项目比较紧张,写了一个小工具,目的是能够快速的将Access的数据迁移到Oracle平台下，没有重构，没有优化，纯原生态，估计会有各种错误，但是基本的思路在。

1）需要配置Access的数据源

2）需要导入Oracle支持的jar包

3）需要将代码中的statement修改为preparedstatement

4）需要将代码进行重构，更容易维护

5）需要导入pinyin4j的jar包，以为在Access里面有列名是汉字

如下：

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;}// 如果第一个字母为数字的话,则在前面加Qpublic 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));}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);}}}

作者 陈字文（热衷于PM\ORACLE\JAVA等，欢迎同行交流):ziwen#163.com 扣扣:4零9零2零1零零