需要的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();
}
}
}
}