最近做的项目中用到将oracle数据导出到access中和将access中数据导入到oracle中，大致操作如下：

使用jdbc进行数据库连接操作

 

  1. 连接oracle：  
  2.  
  3.              Class.forName("oracle.jdbc.driver.OracleDriver");  
  4.              oracleUrl="jdbc:oracle:thin:@<host>:<port>:<SID> ";  
  5.              Connection oracleConn=  DriverManager.getConnection(oracleUrl,"userName","password");  
  6.              String oracleSql="select * from .....";  
  7.              PreparedStatement oraclePs = conn.prepareStatement(oracleSql);  
  8.              //执行查询，查询数据存储在oracleRs结果集中  
  9.              ResultSet oracleRs=oraclePs.execute();  
  10.             //ResultSetMetaData可以获取记录的字段名、字段类型、字段数等信息  
  11.             ResultSetMetaData oracleRsoracleRsmd = oracleRs.getMetaData();  
  12.             while(oracleRs.next()){                  //遍历每一条记录  
  13.                     for（int i = 1; i <=oracleRsmd .getColumnCount(); i++）{ //遍历记录的每一个字段  
  14.  
  15.                             //依次获取每一个字段的值，注意：此处不能重复获取统一个i的值  
  16.  
  17.                              System.out.println(oracleRs.getString(i));  
  18.  
  19.                            //依次获取每个字段的字段名  
  20.  
  21.                             System.out.println(oracleRsmd .getColumnName(i));  
  22.  
  23.                            //依次获取每个字段的类型，注意：此处不要写成oracleRsmd.getColumnType(i)  
  24.  
  25.                              System.out.println(oracleRsmd .getColumnTypeName(i));  
  26.  
  27.                                }  
  28.             }  
  29.  
  30. 连接access：  
  31.  
  32.              Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
  33.  
  34.              //此处对应office2003的access文件2007以上后缀为accdb请注意 另外文件名位置自己改  
  35.  
  36.               String accessUrl="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\test.mdb";  
  37.  
  38.               //连接access的数据库一般来说都不需要密码（自己设了除外）   
  39.  
  40.               Connection accessConn= DriverManager.getConnection(accessUrl,"","")               
  41.  
  42.               String accessSql ="insert ........";  
  43.               PreparedStatement accessPs = accessConn.prepareStatement(accessSql);  
  44.               ResultSet acce***s = accessPs.executeQuery();  
  45.               ResultSetMetaData acce***sacce***smd = acce***s.getMetaData();  
  46.  
  47.              ..............//下面操作与上面一样了   
  48.  
  49. 这两段代码嵌套搭配就可以实现数据的导出了，需要注意的是：oracle的字段类型和access字段类型有差别所以在创建表的时候要先获取字段类型进行判断转换，例如：  
  50.  
  51.             if("VARCHAR2".equals(oracleRsmd .getColumnTypeName(i))){  
  52.  
  53.                 //下面zzz代码access中与varchar2对应的数据类型 sql语句需要循环来拼字符串  
  54.  
  55.                  String accessSql="create table xxx ( yyy zzzz,.............";  
  56.  
  57.            }  
  58.  
  59. 下面附上access与oracle字段对应表  


 


The table below shows the ADO Data Type mapping between Access, SQL Server, and Oracle:




DataType EnumValueAccessSQLServerOracle
adBigInt20 BigInt (SQL Server 2000 +) 
adBinary128 Binary

            TimeStamp		Raw *
adBoolean11YesNoBit 
adChar129 CharChar
adCurrency6CurrencyMoney

            SmallMoney		 
adDate7DateDateTime 
adDBTimeStamp135DateTime (Access 97 (ODBC))DateTime

            SmallDateTime		Date
adDecimal14  Decimal *
adDouble5DoubleFloatFloat
adGUID72ReplicationID (Access 97 (OLEDB)), (Access 2000 (OLEDB))UniqueIdentifier (SQL Server 7.0 +) 
adIDispatch9   
adInteger3AutoNumber

            Integer

            Long		Identity (SQL Server 6.5)

            Int

             		Int *
adLongVarBinary205OLEObjectImageLong Raw *

            Blob (Oracle 8.1.x)
adLongVarChar201Memo (Access 97)

            Hyperlink (Access 97)		TextLong *

            Clob (Oracle 8.1.x)
adLongVarWChar203Memo (Access 2000 (OLEDB))

            Hyperlink (Access 2000 (OLEDB))		NText (SQL Server 7.0 +)NClob (Oracle 8.1.x)
adNumeric131Decimal (Access 2000 (OLEDB))Decimal

            Numeric		Decimal

            Integer

            Number

            SmallInt
adSingle4SingleReal 
adSmallInt2IntegerSmallInt 
adUnsignedTinyInt17ByteTinyInt 
adVarBinary204ReplicationID (Access 97)VarBinary 
adVarChar200Text (Access 97)VarCharVarChar
adVariant12 Sql_Variant (SQL Server 2000 +)VarChar2
adVarWChar202Text (Access 2000 (OLEDB))NVarChar (SQL Server 7.0 +)NVarChar2
adWChar130 NChar (SQL Server 7.0 +) 
* In Oracle 8.0.x - decimal and int are equal to number and number(10).