myhongkongzhen
- 浏览: 93883 次
- 性别:
- 来自: 广州
-
文章分类
最新评论
-
抓不住的风316：
谢谢你的想法 让我从牛角尖中退了出来呵呵
超鏈接點擊次數的統計方法（超簡單） -
白色蜻蜓：
真是强人儿
UC++學習筆記代碼 -
myhongkongzhen：
RT kettas我這裡試驗室成功的，之前也是看了好多文章試驗 ...
win7下vm中linux網絡共享設置 -
kettas：
见过了，没有用过不晓得行不行。
win7下vm中linux網絡共享設置 -
lutian1984：
写的什么啊！有没有点正常人能看懂的东西啊
Struts2 + Pluto + portletV1.0(JSR 168) CRUD Demo
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
分享到：
发表评论
-
substring与split的效率问题
2014-07-04 16:21 1824/** * demo */ package dem ... -
JAVA內存引用的理解
2012-06-19 10:12 7212012年6月19日10:08:02 源碼: ... -
對JAVA引用的一點理解
2012-05-31 23:14 7632012年5月31日23:06:13 ... -
List、Set等類存儲JavaBean的簡單排序實現
2010-06-12 09:44 1061列表排序常用操作： 1、Model類 implements ... -
TOMCAT配置文件配置字符編碼
2010-01-19 15:23 2611TOMCAT配置文件配置字符編碼 總是忘記這兩個參數， ... -
struts2得到request對象
2009-12-23 11:25 9312009年12月23號 週三 天氣晴 struts2得 ... -
JDBC鏈接access數據庫不同機器配置產生亂碼解決方案
2009-09-28 16:54 11442009年9月28號 天氣小雨 週一 JDBC鏈接 ... -
超鏈接點擊次數的統計方法（超簡單）
2009-08-25 11:24 63142009年8月25號 天氣晴 週二 今天一個靈感， ... -
带有checkbox的TREE列表级联的选择子栏目的JAVA标签形式的实现
2009-05-10 12:32 29372009年5月10号 天气晴 星期日 ... -
类似GOOGLE,BAIDU的WEB应用PAGE分页标签JAVA实现
2009-05-09 13:42 27662009年5月9号 星期六 天气晴 以 ... -
根据 crazycode的建议，修改通过IP控制权限访问网站的部分代码
2009-05-07 18:43 11552009年5月7号 根据crazycode前辈的 ... -
网站访问通过IP（段）作为限制条件的实现方法
2009-05-07 16:32 44612009年5月7号 星期四 天气晴 ... -
nutch搜索引擎windows版本，包含中文分词技术
2009-05-05 11:40 32922009年5月5日 星 ...
相关推荐
将Access中的数据导入到Oracle数据库中,支持表与字段的对应选择导出(含原代码).net Form
一款简易的数据导入工具,能将access数据库中的数据导入到oracle数据库中.
access数据库MDB数据文件导入oracle数据库操作步骤.pdf
关于将ACCESS数据库中的表导入到oracle的问题.pdf
Microsoft的Access的数据库数据移植到Oracle服务器的软件。 简单实用，可视化操作。
今天需要把access数据库的内容迁移到oracle数据库，本来想每个表都做一个导入按钮的，后来想想这个办法太死板，经过一个白天的努力，写成这个可以快速迁移数据的软件，因为只是满足自己需要，所以很多方面没有完善，...
access数据库导入Oracle数据库的方法.pdf
access数据库导入Oracle数据库的方法[汇编].pdf
省市县数据库含access 如何转oracle
如何实现将Excel、Access、FoxPox数据导入Oracle数据库.pdf
用plsql将mdb文件导入到oracle数据库中
支持oracle,sqlsever,access等数据库之间数据格式转换:即Oracle转SQL Server；SQL Server转Oracle；Oracle转Access；Access转Oracle；SQL Server转Access, Access转SQL Server等。可以实现数据库的移植，异构数据库...
由于SQL数据库的表不能用Oracle打开，有些数据不能被Oracle使用，此程序完成SQL数据库的表中记录添加到Oracle中，这样既解决了Oracle与SQL不通用时数据库无法使用的情况，也能从该程序中学到VC连接SQL和Oracle两个...
支持oracle,sqlsever,access等数据库之间数据格式转换:即Oracle转SQL Server；SQL Server转Oracle；Oracle转Access；Access转Oracle；SQL Server转Access, Access转SQL Server等。可以实现数据库的移植，异构数据库...
支持oracle,sqlsever,access等数据库之间数据格式转换:即Oracle转SQL Server；SQL Server转Oracle；Oracle转Access；Access转Oracle；SQL Server转Access, Access转SQL Server等。可以实现数据库的移植，异构数据库...
迷你Excel数据导出工具 1.1 中文免费版_可自动导入到access、mssql、oracle等数据库
本资料详细介少了access数据库转oracle数据库，并附关键代码