ACCESS数据库资源导入到ORACLE数据库表中的实现

 

 

2009年5月8号 天气晴  星期五

 

以下的代码实现了从ACCESS数据库表中将记录导入到ORACLE数据库表中的功能，主要的核心功能是ORACLE数据库ID自动生成的实现方法，应用到ROWID的方式。

 

具体代码如下：   （仅仅是一个可运行的DEMO参考，请修改部分代码后再运行于自己的机器上）

 

package g.cms.exchange;

import g.cms.bean.Archive;
import g.cms.bean.ArchiveType;
import g.cms.business.ArchiveService;
import g.cms.business.ArchiveTypeService;
import g.sql.ConnectionPool;
import g.sql.SQLService;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author Jane(吴贞贞)
 * @email myhongkongzhen@gmail.com
 * @since JDK 1.6
 * @alter 2009年*月*号
 * @version 1.0 2009年3月**号
 */
public class AccessToOracle {
	private final static Log logger = LogFactory.getLog(AccessToOracle.class);

	// +DBFile(AccessDB)
	private static String url = "jdbc:odbc:driver="
			+ "{Microsoft Access Driver (*.mdb)};DBQ=";
	private static Connection accessConn, oracleConn;
	private static Statement accessStmt, accSt, accSts;
	private static PreparedStatement oracleStmt;
	// private static Statement accessStmt, oracleStmt;
	private static ResultSet accessRs, oracleRs, accRs, accRss;

	private ArchiveType archiveType;
	private ArchiveTypeService archiveTypeSrv;
	private Archive archive;
	private ArchiveService archiveSrv;
	private Map<Integer, Integer> idCache = new HashMap<Integer, Integer>();
	private List<String> nameCache = new ArrayList<String>();

	public ArchiveType getArchiveType() {
		return archiveType;
	}

	public void setArchiveType(ArchiveType archiveType) {
		this.archiveType = archiveType;
	}

	public ArchiveTypeService getArchiveTypeSrv() {
		return archiveTypeSrv;
	}

	public void setArchiveTypeSrv(ArchiveTypeService archiveTypeSrv) {
		this.archiveTypeSrv = archiveTypeSrv;
	}

	public Archive getArchive() {
		return archive;
	}

	public void setArchive(Archive archive) {
		this.archive = archive;
	}

	public ArchiveService getArchiveSrv() {
		return archiveSrv;
	}

	public void setArchiveSrv(ArchiveService archiveSrv) {
		this.archiveSrv = archiveSrv;
	}

	public Map<Integer, Integer> getIdCache() {
		return idCache;
	}

	public void setIdCache(Map<Integer, Integer> idCache) {
		this.idCache = idCache;
	}

	public AccessToOracle() {
		super();
		// TODO Auto-generated constructor stub
	}

	static {
		try {
			// Oracle Connection
			String urlo = "jdbc:oracle:thin:@192.168.1.3:1522:orcl";
			String driver = "oracle.jdbc.driver.OracleDriver";
			String password = "yppt";
			String user = "yppt";
			Class.forName(driver);
			oracleConn = DriverManager.getConnection(urlo, user, password);
			// oracleConn = ConnectionPool.getConnection();
			// oracleStmt = oracleConn.createStatement();
			logger.debug(oracleConn);

			// Access Connection
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			accessConn = DriverManager.getConnection(url
					+ "E:/workspace/yppt/docs/DATA/new3000/news3000.mdb");

			logger.debug(accessStmt);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			logger.debug("Access DB數據庫連接異常,類未找到...");
			e.printStackTrace();
		}
	}

	// 欄目數據AccessToOracle
	public void accessTypeToOracleChannel() {
		Object autoIncKeyFromApi = -1;
		ResultSet accRs = null;
		Statement accSt = null;
		Statement accSts = null;
		ResultSet accRss = null;

		try {
			accessStmt = accessConn.createStatement();

			accessRs = accessStmt
					.executeQuery("SELECT t.typeid,t.typename,t.typecontent FROM type t");

			logger.debug("準備查詢Access DB數據源數據...");

			int i = 1;
			int b = 1;
			int s = 1;
			while (accessRs.next()) {
				logger.debug("\n--父欄目導入欄目中..." + (i++));
				Integer typeid = accessRs.getInt("typeid");
				String typename = accessRs.getString("typename");
				String typecontent = accessRs.getString("typecontent");

				archiveType = new ArchiveType();
				archiveType.setName(typename);
				archiveType.setDescription(typecontent);
				archiveTypeSrv = new ArchiveTypeService();

				Integer id = archiveTypeSrv.insert(archiveType);
				// 導入數據后生成的ID同源數據中的ID相對應
				idCache.put(id, typeid);
				logger.debug("\n===\ntypeid : " + typeid + "====id : " + id
						+ "\n===");

				accSt = accessConn.createStatement();
				accRs = accSt
						.executeQuery("SELECT  b.bigclassid,b.bigclasszs,b.bigclassname"
								+ " FROM bigclass b WHERE typeid=" + typeid);
				while (accRs.next()) {
					logger.debug("\n--大類子欄目導入欄目中..." + (b++));
					Integer bigclassid = accRs.getInt("bigclassid");
					String bigclassname = accRs.getString("bigclassname");
					String bigclasszs = accRs.getString("bigclasszs");
					archiveType = new ArchiveType();
					archiveType.setName(bigclassname);
					archiveType.setDescription(bigclasszs);
					archiveType.setPid(id);
					archiveTypeSrv = new ArchiveTypeService();

					Integer bid = archiveTypeSrv.insert(archiveType);
					// 導入數據后生成的ID同源數據中的ID相對應
					idCache.put(bid, bigclassid);
					logger.debug("\n===\nbigclassid : " + bigclassid
							+ "====bid : " + bid + "===typeid : " + typeid
							+ "\n===");

					accSts = accessConn.createStatement();
					accRss = accSts
							.executeQuery("SELECT  s.smallclassid,s.smallclasszs,s.smallclassname "
									+ "FROM smallclass s WHERE s.BigClassID="
									+ bigclassid);
					while (accRss.next()) {
						logger.debug("\n--小類子欄目導入欄目中..." + (s++));
						Integer smallclassid = accRss.getInt("smallclassid");
						String smallclassname = accRss
								.getString("smallclassname");
						String smallclasszs = accRss.getString("smallclasszs");
						archiveType = new ArchiveType();
						archiveType.setName(smallclassname);
						archiveType.setDescription(smallclasszs);
						archiveType.setPid(bid);
						archiveTypeSrv = new ArchiveTypeService();

						Integer sid = archiveTypeSrv.insert(archiveType);
						// 源數據中的ID同導入數據后生成的ID相對應
						idCache.put(sid, smallclassid);
						logger.debug("\n===\nsmallclassid : " + smallclassid
								+ "====sid : " + sid + "===typeid" + typeid
								+ "\n===");

						logger
								.debug("\n-------重新讀取小欄目TYPEID==SMALLCLASSID---------");
					}

					logger.debug("\n-------重新讀取大欄目TYPEID==BIGCLASSID---------");

				}

				logger.debug("\n---------重新讀取欄目TYPEID---------");
			}

			archiveType = new ArchiveType();
			archiveType.setName("評論");
			archiveTypeSrv = new ArchiveTypeService();

			Integer id = archiveTypeSrv.insert(archiveType);
			// 導入數據后生成的ID同源數據中的ID相對應
			idCache.put(id, -1);
			nameCache.add("評論");
			logger.debug("\n====id : " + id + "\n===");

			archiveType = new ArchiveType();
			archiveType.setName("评论");
			archiveType.setPid(id);
			archiveTypeSrv = new ArchiveTypeService();

			Integer zid = archiveTypeSrv.insert(archiveType);
			// 導入數據后生成的ID同源數據中的ID相對應
			idCache.put(zid, id);
			nameCache.add("評論");
			logger.debug("\n====zid : " + zid + "\n===");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionPool.close(accRs);
			ConnectionPool.close(accSt);
		}

	}

	// ACCESS文章導入ORACLE數據庫1
	public void accessNewsToOracleArchive() {
		Object autoIncKeyFromApi = -1;
		ResultSet accRs = null;
		Statement accSt = null;
		Statement accSts = null;
		ResultSet accRss = null;

		logger.debug("\n-----------\n" + idCache.size() + "\n--------");

		try {
			Integer bigclassid = 0;
			Set<Integer> keySet = idCache.keySet();
			for (Integer key : keySet) {
				bigclassid = idCache.get(key);
				logger.debug("\n==smallclassid\n" + bigclassid + "\n===");
				String sql = "SELECT n.newsid,n.title,n.checked,n.author,"
						+ "n.editor,n.updatetime,n.content,n.about,"
						+ "n.encode,n.click FROM news n "
						+ "WHERE n.bigclassid=" + bigclassid;

				accessStmt = accessConn.createStatement();
				accessRs = accessStmt.executeQuery(sql);

				logger.debug("準備查詢Access DB數據源數據...");

				int i = 1;
				while (accessRs.next()) {
					logger.debug("\n--文章導入中..." + (i++));

					oracleStmt = null;
					oracleRs = null;
					oracleStmt = oracleConn.prepareStatement("INSERT INTO "
							+ "archive(id,typeid,"
							+ "title,ischecked,author,sender,sendtime,"
							+ "keyword,click) " + "VALUES("
							+ "(select (nvl(max(to_number(id)), 0)) "
							+ "from archive)+1" + ",?,?,?,?,?,?,?,?)",
							Statement.RETURN_GENERATED_KEYS);

					logger.debug("\n---------欄目TYPEID---------" + key + "\n");

					oracleStmt.setInt(1, key);
					String title = accessRs.getString("title");
					if (null != title) {
						oracleStmt.setString(2, title);
					} else {
						continue;
					}
					String html = accessRs.getString("encode");
					if (null != html) {
						if ("html".equals(html.toLowerCase())) {
							oracleStmt.setInt(3, 1);
						} else {
							oracleStmt.setInt(3, 0);
						}
					} else {
						oracleStmt.setInt(3, 0);
					}
					oracleStmt.setString(4, accessRs.getString("author"));
					oracleStmt.setString(5, accessRs.getString("editor"));
					oracleStmt.setTimestamp(6, accessRs
							.getTimestamp("updatetime"));
					oracleStmt.setString(7, accessRs.getString("about"));
					oracleStmt.setInt(8, accessRs.getInt("click"));
					oracleStmt.executeUpdate();

					oracleRs = oracleStmt.getGeneratedKeys();
					if (oracleRs.next()) {
						autoIncKeyFromApi = oracleRs.getObject(1);
						if (autoIncKeyFromApi instanceof oracle.sql.ROWID) {
							autoIncKeyFromApi = ((oracle.sql.ROWID) autoIncKeyFromApi)
									.stringValue();
							String keySql = "SELECT ID FROM archive WHERE rowid=?";
							oracleStmt = null;
							oracleRs = null;
							oracleStmt = oracleConn.prepareStatement(keySql,
									Statement.RETURN_GENERATED_KEYS);
							oracleStmt.setObject(1, autoIncKeyFromApi);
							oracleRs = oracleStmt.executeQuery();
							if (oracleRs.next()) {
								autoIncKeyFromApi = oracleRs.getInt(1);
								// // 源數據中的ID同導入數據后生成的ID相對應
								// idCache.put((Integer) autoIncKeyFromApi, -1);
								String bolbSql = "UPDATE archive SET BODY=? WHERE ID=?";
								String content = accessRs.getString("content");
								if (null != content) {
									java.sql.Blob body = g.sql.SQLHelper
											.createBlob(content
													.getBytes("UTF-8"));
									Object[] args = new Object[] { body,
											autoIncKeyFromApi };
									SQLService ss = new SQLService();
									ss.update(bolbSql, args);
								} else {
									continue;
								}
							}
						} else {
							throw new RuntimeException(
									"操作失败，没有处理的Oracle RETURN_GENERATED_KEYS类型!");
						}
					}
				}
				logger.debug("\n---------重新讀取欄目TYPEID---------");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionPool.close(accRs);
			ConnectionPool.close(accSt);
		}
	}

	// ACCESS文章導入ORACLE數據庫2
	public void accessNewsToOracleArchive2() {
		Object autoIncKeyFromApi = -1;
		ResultSet accRs = null;
		Statement accSt = null;
		Statement accSts = null;
		ResultSet accRss = null;

		logger.debug("\n-----------\n" + idCache.size() + "\n--------");

		try {
			String sql = "SELECT b.id,b.title,b.content,b.dateandtime,"
					+ "b.upload FROM board b  WHERE 1=1";

			accessStmt = accessConn.createStatement();
			accessRs = accessStmt.executeQuery(sql);

			logger.debug("準備查詢Access DB數據源數據...");

			int i = 1;
			while (accessRs.next()) {
				logger.debug("\n--文章導入中..." + (i++));

				oracleStmt = null;
				oracleRs = null;
				oracleStmt = oracleConn.prepareStatement("INSERT INTO "
						+ "archive(id,typeid,title,sender,sendtime"
						+ ") VALUES(" + "(select (nvl(max(to_number(id)), 0)) "
						+ "from archive)+1" + ",?,?,?,?)",
						Statement.RETURN_GENERATED_KEYS);

				logger.debug("\n---------欄目TYPEID---------0" + "\n");

				oracleStmt.setInt(1, 52);
				String title = accessRs.getString("title");
				if (null != title) {
					oracleStmt.setString(2, title);
				} else {
					continue;
				}
				oracleStmt.setString(3, accessRs.getString("upload"));
				oracleStmt
						.setTimestamp(4, accessRs.getTimestamp("dateandtime"));
				oracleStmt.executeUpdate();

				oracleRs = oracleStmt.getGeneratedKeys();
				if (oracleRs.next()) {
					autoIncKeyFromApi = oracleRs.getObject(1);
					if (autoIncKeyFromApi instanceof oracle.sql.ROWID) {
						autoIncKeyFromApi = ((oracle.sql.ROWID) autoIncKeyFromApi)
								.stringValue();
						String keySql = "SELECT ID FROM archive WHERE rowid=?";
						oracleStmt = null;
						oracleRs = null;
						oracleStmt = oracleConn.prepareStatement(keySql,
								Statement.RETURN_GENERATED_KEYS);
						oracleStmt.setObject(1, autoIncKeyFromApi);
						oracleRs = oracleStmt.executeQuery();
						if (oracleRs.next()) {
							autoIncKeyFromApi = oracleRs.getInt(1);
							// // 源數據中的ID同導入數據后生成的ID相對應
							// idCache.put((Integer) autoIncKeyFromApi, -1);
							String bolbSql = "UPDATE archive SET BODY=? WHERE ID=?";
							String content = accessRs.getString("content");
							if (null != content) {
								java.sql.Blob body = g.sql.SQLHelper
										.createBlob(content.getBytes("UTF-8"));
								Object[] args = new Object[] { body,
										autoIncKeyFromApi };
								SQLService ss = new SQLService();
								ss.update(bolbSql, args);
							} else {
								continue;
							}
						}
					} else {
						throw new RuntimeException(
								"操作失败，没有处理的Oracle RETURN_GENERATED_KEYS类型!");
					}
				}
			}
			logger.debug("\n---------重新讀取文章ID---------");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionPool.close(accRs);
			ConnectionPool.close(accSt);
		}
	}

	// ACCESS文章導入ORACLE數據庫3
	public void accessNewsToOracleArchive3() {
		Object autoIncKeyFromApi = -1;
		ResultSet accRs = null;
		Statement accSt = null;
		Statement accSts = null;
		ResultSet accRss = null;

		logger.debug("\n-----------\n" + idCache.size() + "\n--------");

		try {
			String sql = "SELECT r.reviewid,r.Content,r.author"
					+ ",r.title,r.updatetime FROM Review r WHERE 1=1";

			accessStmt = accessConn.createStatement();
			accessRs = accessStmt.executeQuery(sql);

			logger.debug("準備查詢Access DB數據源數據...");

			int i = 1;
			while (accessRs.next()) {
				logger.debug("\n--文章導入中..." + (i++));

				oracleStmt = null;
				oracleRs = null;
				oracleStmt = oracleConn.prepareStatement("INSERT INTO "
						+ "archive(id,typeid,title,sender,sendtime"
						+ ") VALUES(" + "(select (nvl(max(to_number(id)), 0)) "
						+ "from archive)+1" + ",(SELECT id FROM archive_type "
						+ "WHERE name='评论'),?,?,?)",
						Statement.RETURN_GENERATED_KEYS);

				logger.debug("\n---------欄目TYPEID---------0" + "\n");

				String title = accessRs.getString("title");
				if (null != title) {
					oracleStmt.setString(1, title);
				} else {
					continue;
				}
				oracleStmt.setString(2, accessRs.getString("author"));
				oracleStmt.setTimestamp(3, accessRs.getTimestamp("updatetime"));
				oracleStmt.executeUpdate();

				oracleRs = oracleStmt.getGeneratedKeys();
				if (oracleRs.next()) {
					autoIncKeyFromApi = oracleRs.getObject(1);
					if (autoIncKeyFromApi instanceof oracle.sql.ROWID) {
						autoIncKeyFromApi = ((oracle.sql.ROWID) autoIncKeyFromApi)
								.stringValue();
						String keySql = "SELECT ID FROM archive WHERE rowid=?";
						oracleStmt = null;
						oracleRs = null;
						oracleStmt = oracleConn.prepareStatement(keySql,
								Statement.RETURN_GENERATED_KEYS);
						oracleStmt.setObject(1, autoIncKeyFromApi);
						oracleRs = oracleStmt.executeQuery();
						if (oracleRs.next()) {
							autoIncKeyFromApi = oracleRs.getInt(1);
							// // 源數據中的ID同導入數據后生成的ID相對應
							// idCache.put((Integer) autoIncKeyFromApi, -1);
							String bolbSql = "UPDATE archive SET BODY=? WHERE ID=?";
							String content = accessRs.getString("content");
							if (null != content) {
								java.sql.Blob body = g.sql.SQLHelper
										.createBlob(content.getBytes("UTF-8"));
								Object[] args = new Object[] { body,
										autoIncKeyFromApi };
								SQLService ss = new SQLService();
								ss.update(bolbSql, args);
							} else {
								continue;
							}
						}
					} else {
						throw new RuntimeException(
								"操作失败，没有处理的Oracle RETURN_GENERATED_KEYS类型!");
					}
				}
			}
			logger.debug("\n---------重新讀取文章ID---------");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionPool.close(accRs);
			ConnectionPool.close(accSt);
		}
	}

	// ACCESS用戶導入ORACLE數據庫
	public void accessUserToOracleManager() {
		Object autoIncKeyFromApi = -1;
		ResultSet accRs = null;
		Statement accSt = null;
		Statement accSts = null;
		ResultSet accRss = null;

		logger.debug("\n-----------\n" + idCache.size() + "\n--------");

		try {

			accessStmt = accessConn.createStatement();

			accessRs = accessStmt
					.executeQuery("SELECT a.id,a.username,a.passwd,a.fullname"
							+ " FROM ft_user a WHERE 1=1 ");

			logger.debug("準備查詢Access DB數據源數據...");

			int i = 1;
			int b = 1;
			int s = 1;
			while (accessRs.next()) {
				logger.debug("\n--用戶記錄導入中..." + (i++));
				Integer aid = accessRs.getInt("id");
				String username = accessRs.getString("username");
				String passwd = accessRs.getString("passwd");
				String fullname = accessRs.getString("fullname");

				oracleStmt = null;
				oracleRs = null;
				oracleStmt = oracleConn.prepareStatement("INSERT INTO "
						+ "manager(id,userid,password,name) " + "VALUES("
						+ "(select (nvl(max(to_number(id)), 0)) "
						+ "from manager)+1" + ",?,?,?)",
						Statement.RETURN_GENERATED_KEYS);

				oracleStmt.setString(1, username);
				oracleStmt.setString(2, passwd);
				oracleStmt.setString(3, fullname);
				oracleStmt.executeUpdate();

				nameCache.add(username);

				oracleRs = oracleStmt.getGeneratedKeys();
				if (oracleRs.next()) {
					autoIncKeyFromApi = oracleRs.getObject(1);
					if (autoIncKeyFromApi instanceof oracle.sql.ROWID) {
						autoIncKeyFromApi = ((oracle.sql.ROWID) autoIncKeyFromApi)
								.stringValue();
						String keySql = "SELECT ID FROM manager WHERE rowid=?";
						oracleStmt = null;
						oracleRs = null;
						oracleStmt = oracleConn.prepareStatement(keySql,
								Statement.RETURN_GENERATED_KEYS);
						oracleStmt.setObject(1, autoIncKeyFromApi);
						oracleRs = oracleStmt.executeQuery();
						if (oracleRs.next()) {
							autoIncKeyFromApi = oracleRs.getInt(1);
							// 源數據中的ID同導入數據后生成的ID相對應
							idCache.put((Integer) autoIncKeyFromApi, aid);
							logger.debug("\n===\nautoIncKeyFromApi : "
									+ autoIncKeyFromApi + "====aid : " + aid
									+ "\n===");
						}
					} else {
						throw new RuntimeException(
								"操作失败，没有处理的Oracle RETURN_GENERATED_KEYS类型!");
					}
				}

				logger.debug("\n---------重新讀取用戶ID---------");
			}

			oracleStmt = null;
			oracleRs = null;
			oracleStmt = oracleConn.prepareStatement("INSERT INTO "
					+ "manager(id,userid,password,name) " + "VALUES("
					+ "(select (nvl(max(to_number(id)), 0)) "
					+ "from manager)+1" + ",?,?,?)",
					Statement.RETURN_GENERATED_KEYS);

			oracleStmt.setString(1, "admin");
			oracleStmt.setString(2, "admin");
			oracleStmt.setString(3, "admin");
			oracleStmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionPool.close(accRs);
			ConnectionPool.close(accSt);
		}
	}

	// ACCESS統計數據導入ORACLE數據庫
	public void accessCountersToOracleCounters() {
		Object autoIncKeyFromApi = -1;
		ResultSet accRs = null;
		Statement accSt = null;
		Statement accSts = null;
		ResultSet accRss = null;

		logger.debug("\n-----------\n" + idCache.size() + "\n--------");

		try {

			accessStmt = accessConn.createStatement();

			accessRs = accessStmt
					.executeQuery("SELECT c.total,c.today,c.yesterday"
							+ ",c.month,c.bmonth FROM counters c WHERE 1=1 ");

			logger.debug("準備查詢Access DB數據源數據...");

			int i = 1;
			while (accessRs.next()) {
				logger.debug("\n--統計記錄導入中..." + (i++));

				oracleStmt = null;
				oracleRs = null;
				oracleStmt = oracleConn.prepareStatement("INSERT INTO "
						+ "counters(id,total,taday,yesterday,month,bmonth) "
						+ "VALUES(" + "(select (nvl(max(to_number(id)), 0)) "
						+ "from counters)+1" + ",?,?,?,?,?)",
						Statement.RETURN_GENERATED_KEYS);

				oracleStmt.setInt(1, accessRs.getInt("total"));
				oracleStmt.setInt(2, accessRs.getInt("today"));
				oracleStmt.setInt(3, accessRs.getInt("yesterday"));
				oracleStmt.setInt(4, accessRs.getInt("month"));
				oracleStmt.setInt(5, accessRs.getInt("bmonth"));
				oracleStmt.executeUpdate();

				logger.debug("\n---------重新讀取統計ID---------");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionPool.close(accRs);
			ConnectionPool.close(accSt);
		}
	}

	// ACCESS權限導入ORACLE數據庫
	public void accessToOracleSys_group() {
		Object autoIncKeyFromApi = -1;
		ResultSet accRs = null;
		Statement accSt = null;
		Statement accSts = null;
		ResultSet accRss = null;

		logger.debug("\n-----------\n" + nameCache.size() + "\n--------");

		try {
			oracleStmt = null;
			oracleRs = null;
			oracleStmt = oracleConn.prepareStatement("INSERT INTO "
					+ "sys_group(id,name,arctypeadd,"
					+ "arctypesee,arctypeedit,arctypedel,arcadd,"
					+ "arcsee,arcedit,arcdel) " + "VALUES("
					+ "(select (nvl(max(to_number(id)), 0)) "
					+ "from sys_group)+1" + ",?,?,?,?,?,?,?,?,?)",
					Statement.RETURN_GENERATED_KEYS);

			oracleStmt.setString(1, "admin");
			oracleStmt.setInt(2, 1);
			oracleStmt.setInt(3, 1);
			oracleStmt.setInt(4, 1);
			oracleStmt.setInt(5, 1);
			oracleStmt.setInt(6, 1);
			oracleStmt.setInt(7, 1);
			oracleStmt.setInt(8, 1);
			oracleStmt.setInt(9, 1);
			oracleStmt.executeUpdate();

			oracleRs = oracleStmt.getGeneratedKeys();
			if (oracleRs.next()) {
				autoIncKeyFromApi = oracleRs.getObject(1);
				if (autoIncKeyFromApi instanceof oracle.sql.ROWID) {
					autoIncKeyFromApi = ((oracle.sql.ROWID) autoIncKeyFromApi)
							.stringValue();
					String keySql = "SELECT ID FROM sys_group WHERE rowid=?";
					oracleStmt = null;
					oracleRs = null;
					oracleStmt = oracleConn.prepareStatement(keySql,
							Statement.RETURN_GENERATED_KEYS);
					oracleStmt.setObject(1, autoIncKeyFromApi);
					oracleRs = oracleStmt.executeQuery();
					if (oracleRs.next()) {
						autoIncKeyFromApi = oracleRs.getInt(1);
						// 源數據中的ID同導入數據后生成的ID相對應
						idCache.put((Integer) autoIncKeyFromApi, -1);
						logger.debug("\n===\nautoIncKeyFromApi : "
								+ autoIncKeyFromApi + "=======");
					}
				} else {
					throw new RuntimeException(
							"操作失败，没有处理的Oracle RETURN_GENERATED_KEYS类型!");
				}
			}

			logger.debug("\n---------重新讀取權限ID---------");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionPool.close(accRs);
			ConnectionPool.close(accSt);
		}
	}

	// // ACCESS權限導入ORACLE數據庫
	// public void accessToOracleSys_group() {
	// Object autoIncKeyFromApi = -1;
	// ResultSet accRs = null;
	// Statement accSt = null;
	// Statement accSts = null;
	// ResultSet accRss = null;
	//		
	// logger.debug("\n-----------\n" + nameCache.size() + "\n--------");
	//		
	// try {
	// for (String username : nameCache) {
	//				
	// accessStmt = accessConn.createStatement();
	// accessRs = accessStmt.executeQuery("SELECT a.username"
	// + " FROM admin a WHERE a.username=" + username);
	//				
	// logger.debug("準備查詢Access DB數據源數據...");
	//				
	// int i = 1;
	// int b = 1;
	// int s = 1;
	// while (accessRs.next()) {
	// logger.debug("\n--權限記錄導入中..." + (i++));
	// String uname = accessRs.getString("username");
	//					
	// oracleStmt = null;
	// oracleRs = null;
	// oracleStmt = oracleConn.prepareStatement("SELECT m.id "
	// + " FROM manager m WHERE m.username=" + username,
	// Statement.RETURN_GENERATED_KEYS);
	//					
	// oracleStmt = null;
	// oracleRs = null;
	// oracleStmt = oracleConn.prepareStatement("INSERT INTO "
	// + "sys_group(id,userid,password,name) " + "VALUES("
	// + "(select (nvl(max(to_number(id)), 0)) "
	// + "from manager)+1" + ",?,?,?)",
	// Statement.RETURN_GENERATED_KEYS);
	//					
	// oracleStmt.setString(1, username);
	// oracleStmt.setString(2, passwd);
	// oracleStmt.setString(3, fullname);
	// oracleStmt.executeUpdate();
	//					
	// oracleRs = oracleStmt.getGeneratedKeys();
	// if (oracleRs.next()) {
	// autoIncKeyFromApi = oracleRs.getObject(1);
	// if (autoIncKeyFromApi instanceof oracle.sql.ROWID) {
	// autoIncKeyFromApi = ((oracle.sql.ROWID) autoIncKeyFromApi)
	// .stringValue();
	// String keySql = "SELECT ID FROM manager WHERE rowid=?";
	// oracleStmt = null;
	// oracleRs = null;
	// oracleStmt = oracleConn.prepareStatement(keySql,
	// Statement.RETURN_GENERATED_KEYS);
	// oracleStmt.setObject(1, autoIncKeyFromApi);
	// oracleRs = oracleStmt.executeQuery();
	// if (oracleRs.next()) {
	// autoIncKeyFromApi = oracleRs.getInt(1);
	// // 源數據中的ID同導入數據后生成的ID相對應
	// idCache.put((Integer) autoIncKeyFromApi, aid);
	// }
	// } else {
	// throw new RuntimeException(
	// "操作失败，没有处理的Oracle RETURN_GENERATED_KEYS类型!");
	// }
	// }
	//					
	// logger.debug("\n---------重新讀取用戶ID---------");
	// }
	// }
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// } finally {
	// ConnectionPool.close(accRs);
	// ConnectionPool.close(accSt);
	// }
	// }

	public void close() {

		// try {
		// accRss.close();
		// accSts.close();
		// } catch (SQLException e1) {
		// // TODO Auto-generated catch block
		// e1.printStackTrace();
		// }
		//
		// try {
		// accRs.close();
		// accSt.close();
		// } catch (SQLException e1) {
		// // TODO Auto-generated catch block
		// e1.printStackTrace();
		// }
		//
		// try {
		// accessRs.close();
		// accessStmt.close();
		// } catch (SQLException e1) {
		// // TODO Auto-generated catch block
		// e1.printStackTrace();
		// }
		//
		// try {
		// accessConn.close();
		// } catch (SQLException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// }
		//
		// try {
		// oracleConn.close();
		// } catch (SQLException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// }

	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		AccessToOracle atoTest = new AccessToOracle();

		// 欄目導入
		atoTest.accessTypeToOracleChannel();

		// 文章導入
		atoTest.accessNewsToOracleArchive();
		atoTest.accessNewsToOracleArchive2();
		atoTest.accessNewsToOracleArchive3();

		// 用戶導入
		atoTest.accessUserToOracleManager();

		// 統計導入
		atoTest.accessCountersToOracleCounters();

		// 權限導入
		atoTest.accessToOracleSys_group();

		atoTest.close();
	}

}

 

 

如果有不明白的地方可以联系我：qq511134962，msn：myhongkongzhen@gmail.com