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

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