accesToOracle And OracleToAccess

需要的jar

commons-lang-2.5.jar

commons-logging-1.1.1.jar

jackcess-2.1.8.jar

package impAndExp;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.TreeMap;

import com.healthmarketscience.jackcess.Column;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Database.FileFormat;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Table;
import com.healthmarketscience.jackcess.TableMetaData;
import com.healthmarketscience.jackcess.query.Query;
import com.healthmarketscience.jackcess.util.ImportUtil;
import com.sun.jndi.ldap.EntryChangeResponseControl;

public class OracleToAccess {

public static void main(String[] args) throws IOException{

//                          测试oracleToAccess

//     List<String> data = new ArrayList<String>(); 
//     data.add("table1");
//     data.add("table2");
//     String date = "2017";

//     oracleToAccess(data,date);

//              测试accessToOracle

accessToOracle();
}

/**
* 
* @param data 表集合
* @param date 年份
*/
public static void oracleToAccess(List<String> data,String date){
if(data.size()==0 || data==null){
return;
}
Connection con =null;
ResultSet rs=null;
Database db =null;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//创建连接
con= DriverManager.getConnection("jdbc:oracle:thin:@//IP地址:端口号/orcl", 用户名, 口令);
Calendar now = Calendar.getInstance();
String year = String.valueOf(now.get(Calendar.YEAR));
String month = String.valueOf(now.get(Calendar.MONTH)+1);
String day = String.valueOf(now.get(Calendar.DAY_OF_MONTH));
DatabaseBuilder.create(FileFormat.V2010,new File(access数据库名称+year+month+day+".mdb"));
for(int i=0;i<data.size();i++){
  String oracleSql = "select * from "+data.get(i); 
   PreparedStatement statement = con.prepareStatement(oracleSql);
  
   rs = statement.executeQuery(oracleSql);
    //将数据备份到Access数据库   
    db= DatabaseBuilder.open(new File("C:\\zcab"+year+month+day+".mdb"));
    new ImportUtil.Builder(db, data.get(i).toString()).importResultSet(rs);       
  }


} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
con.close();
db.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}     
}

public static void accessToOracle(){

Database db;
Connection con =null;
try {

//加载Oracle驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//创建连接
con= DriverManager.getConnection("jdbc:oracle:thin:@//IP地址:1521/orcl", "demo", "demo");


db = DatabaseBuilder.open(new File(access数据库));
//拿到所有的表名
Set<String> set=db.getTableNames();


for(String name:set){
Table table = db.getTable(name);

List<? extends Column> m=table.getColumns();//获取表的字段数据集
String sql="create table "+name+"(";//oracle建表语句
//创建一个集合用来存字段类型
List fieldType = new ArrayList();
for(int i=0;i<m.size();i++){
sql+=m.get(i).getName()+" ";//获取字段名字
if("NUMERIC".equals(m.get(i).getType().toString())){//字段类型转换
sql+="NUMBER,";
fieldType.add("NUMBER");
}
if("SHORT_DATE_TIME".equals(m.get(i).getType().toString())){
sql+="DATE,";
fieldType.add("DATE");
}
if("TEXT".equals(m.get(i).getType().toString())){
sql+="VARCHAR2("+m.get(i).getLength()+"),";
fieldType.add("VARCHAR2");
}
if("MEMO".equals(m.get(i).getType().toString())){
sql+="VARCHAR2(4000),";
fieldType.add("VARCHAR2");
}

}
sql=sql.substring(0, sql.length()-1);
sql+=")";
System.out.println(sql);
PreparedStatement state=con.prepareStatement(sql);
boolean flag=state.execute(sql);
System.out.println(flag);

int column=table.getColumnCount();//获取表的列数
int count=table.getRowCount();//获取表的行数
//遍历行
for(int i=0;i<count;i++){
String sql3="insert into "+name+" values(";//创建一条sql语句
Set<Entry<String,Object>> entrySet=table.getNextRow().entrySet();
  System.out.println(entrySet.size());
List list= new ArrayList();
  for(Entry<String,Object> entry:entrySet){
    list.add(entry.getValue());   
   }
  for(int z=0;z<fieldType.size();z++){
  if("NUMBER".equals(fieldType.get(z))){
  sql3+=list.get(z)+",";
  }
  if("DATE".equals(fieldType.get(z))){
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  if(list.get(z)==null){
  sql3+=list.get(z)+",";
  }
  if(list.get(z)!=null){
  sql3+="to_date(\'"+sdf.format(list.get(z))+"\',\'YYYY-MM-DD\'),";
  }
 
  }
  if("VARCHAR2".equals(fieldType.get(z))){
  sql3+="\'"+list.get(z)+"\',";
  }
  
  }
   sql3=sql3.substring(0,sql3.length()-1);
   sql3+=")";
   System.out.println(sql3);
  int n = con.prepareStatement(sql3).executeUpdate();
  System.out.println(n);
}

}
} catch (SQLException e){
e.printStackTrace();
} catch (ClassNotFoundException e){
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


}

}