用JAVA把access数据导入到oracle一半时出错,请高手指点
发布网友
发布时间：2022-04-10 06:47
共2个回答
热心网友
时间：2022-04-10 08:16
import java.sql.*;
import java.sql.ResultSetMetaData;
//
//
//支持 ACCESS MYSQL SQL2000 SQL2005 DB2 ORACLE 需要下载驱动包
public class DatabaseCopyImpl {
Connection conFrom=null,conTo=null;
static public Connection getAccessConnection(String url)throws Exception{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String link="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)}; DBQ="+url;
return DriverManager.getConnection(link);
}
static public Connection getMySQLConnection(String ip,int port,String databaseName,String username,String password)throws Exception{
Class.forName("com.mysql.jdbc.Driver");
if(port==0) port=3306;
String link="jdbc:mysql://"+ip+":"+String.valueOf(port)+"/"+databaseName;
return DriverManager.getConnection(link,username,password);
}
static public Connection getSQL2000Connection(String ip,int port,String databaseName,String username,String password)throws Exception{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
if(port==0) port=1433;
String link="jdbc:microsoft:sqlserver://"+ip+":"+String.valueOf(port)+";databaseName="+databaseName;
return DriverManager.getConnection(link,username,password);
}
static public Connection getSQL2005Connection(String ip,int port,String databaseName,String username,String password)throws Exception{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
if(port==0) port=1433;
String link="jdbc:sqlserver://"+ip+":"+String.valueOf(port)+";databaseName="+databaseName;
return DriverManager.getConnection(link,username,password);
}
static public Connection getOracleConnection(String ip,int port,String databaseName,String username,String password)throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
if(port==0) port=1521;
String link="jdbc:oracle:thin:@"+ip+":"+String.valueOf(port)+":orcl";
return DriverManager.getConnection(link,username,password);
}
static public Connection getDB2Connection(String ip,int port,String databaseName,String username,String password)throws Exception{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
if(port==0) port=50000;
String link="jdbc:db2://"+ip+":"+String.valueOf(port)+"/"+databaseName;
return DriverManager.getConnection(link,username,password);
}
public DatabaseCopyImpl(){
//默认构造函数
}
public Connection getFromDatabase(String type,String url,String ip,int port,String databaseName,String username,String password)throws Exception{
Connection con=null;
if(type.equals("ACCESS")) con=getAccessConnection(url);
else if(type.equals("MYSQL")) con=getMySQLConnection(ip,port,databaseName,username,password);
else if(type.equals("SQL2000")) con=getSQL2000Connection(ip,port,databaseName,username,password);
else if(type.equals("SQL2005")) con=getSQL2005Connection(ip,port,databaseName,username,password);
else if(type.equals("ORACLE")) con=getOracleConnection(ip,port,databaseName,username,password);
else if(type.equals("DB2")) con=getDB2Connection(ip,port,databaseName,username,password);
con.setAutoCommit(false);
conFrom=con;
return con;
}
public Connection getToDatabase(String type,String url,String ip,int port,String databaseName,String username,String password)throws Exception{
Connection con=null;
if(type.equals("ACCESS")) con=getAccessConnection(url);
else if(type.equals("MYSQL")) con=getMySQLConnection(ip,port,databaseName,username,password);
else if(type.equals("SQL2000")) con=getSQL2000Connection(ip,port,databaseName,username,password);
else if(type.equals("SQL2005")) con=getSQL2005Connection(ip,port,databaseName,username,password);
else if(type.equals("ORACLE")) con=getOracleConnection(ip,port,databaseName,username,password);
else if(type.equals("DB2")) con=getDB2Connection(ip,port,databaseName,username,password);
con.setAutoCommit(false);
conTo=con;
return con;
}
public int copyTableDataAll(String fromTable,String toTable,String[] field,String[] fieldType,String key,int keyBeginValue)throws Exception{
//参数：来自表 复制到表 字段名数组 字段类型数组 关键字(不在字段名数组) 关键字起始值
Statement sta1=conTo.createStatement();
Statement sta2=conFrom.createStatement();
ResultSet resultFrom=null;
int sum=0;
String tempKey="id"; //自动主键
String createTableSQL=null;
if(key==null) createTableSQL="CREATE TABLE "+toTable+" ( "+tempKey+" bigint primary key"; //创建表sql语句
else createTableSQL="CREATE TABLE "+toTable+" ( "+key+" bigint primary key";
String selectFromTable=null; //读取表sql语句
String insertToSQL="INSERT INTO "+toTable+"(";
String insertToSQLHead=null;
if(field==null || fieldType==null)
{
resultFrom=sta2.executeQuery("SELECT * FROM "+fromTable);
ResultSetMetaData meta=resultFrom.getMetaData();
int sumField=meta.getColumnCount();
field=new String[sumField];
for(int k=0;k<sumField;k++){
createTableSQL=createTableSQL+","+meta.getColumnName(k)+" "+meta.getColumnType(k);
insertToSQL=insertToSQL+","+meta.getColumnName(k);
field[k]=meta.getColumnName(k);
}
}
else
{
selectFromTable="SELECT "+field[0]; //读取表sql语句
for(int j=1;j<field.length;j++)
selectFromTable=selectFromTable+","+field[j];
if(key!=null) selectFromTable=selectFromTable+","+key+" FROM "+fromTable;
resultFrom=sta2.executeQuery(selectFromTable);
for(int i=0;i<field.length;i++){
createTableSQL=createTableSQL+","+field[i]+" "+fieldType[i];
insertToSQL=insertToSQL+field[i]+",";
}
}
createTableSQL=createTableSQL+")";
if(key==null) insertToSQL=insertToSQL+tempKey+") ";
else insertToSQL=insertToSQL+key+") ";
if(sta1.execute(createTableSQL)) System.out.println("创建表失败，请检查字段:"+createTableSQL);
insertToSQLHead=insertToSQL;
while(resultFrom.next()){
insertToSQL=insertToSQLHead+" VALUES(";
for(int x=0;x<field.length;x++){
insertToSQL=insertToSQL+"'"+resultFrom.getString(field[x])+"',";
}
if(key==null) insertToSQL=insertToSQL+(keyBeginValue++)+")";
else insertToSQL=insertToSQL+resultFrom.getString(key)+")";
if(sta1.executeUpdate(insertToSQL)!=0) sum++;
insertToSQL=null;
}
conTo.commit();
conFrom.commit();
sta1.close();
sta2.close();
conTo.close();
conFrom.close();
return sum;
}
}
热心网友
时间：2022-04-10 09:34
