从oracle中导出数据到access中，以mdb格式导出文件

@Overridepublic void exportMdbData(Invocation inv, Long taId, Paging page) throws Exception {    //设置表名       String tablename1="tb_09as0y4kyet2";        // 空白mdb文件路径. 直接保存在src/cn/iwoo/dataexport/common/下.       String blankMdbFilePath = "com/lzzj/prodma/controllers/archives/";       // 空白mdb文件名       String blankMdbFileName = "123.mdb";           // 新mdb文件路径       String defaultSavedMdbFilePath = "com/lzzj/prodma/controllers/archives/";       // 新mdb文件名       String defaultSavedMdbFileName = "data.mdb";       // mdb文件后缀       String defaultSavedMdbFileExtension = ".mdb";           // 需要保存到的新的mdb文件路径和名       String savedMdbFilePathAndName = defaultSavedMdbFilePath + defaultSavedMdbFileName;           //将空白mdb文件拷贝到特定目录    InputStream is = this.getClass().getClassLoader().getResourceAsStream(blankMdbFilePath + blankMdbFileName);       savedMdbFilePathAndName=getClass().getClassLoader().getResource("/").getPath()+savedMdbFilePathAndName;        OutputStream out = new FileOutputStream(savedMdbFilePathAndName);           byte[] buffer = new byte[1024];           int numRead;           while ((numRead = is.read(buffer)) != -1) {               out.write(buffer, 0, numRead);           }           is.close();           out.close();                  //开始从oracle取数据        Connection conn =null;        PreparedStatement ps=null;        ResultSet rs =null;        String createTableSql1="";//一个建表语句        Map map1=new HashMap();//一个map存一个表的字段        List<Map> list1 = new ArrayList<Map>();//一个list存一个表的记录        int mapSize1=0;//一个表的字段个数        String value="";//用于拼接sql        try{                    Class.forName("oracle.jdbc.driver.OracleDriver");            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.3.157:1521:ORCL", "pde", "pde");            map1=getCols(conn,ps,rs,tablename1);//map中存有该表中的所有字段            mapSize1=map1.size();            //拼接access中的建表sql语句            createTableSql1=createTableSql(tablename1,map1);//建表sql                        String sql2="select * from "+tablename1;//提取ORACLE表数据的sql            ps = conn.prepareStatement(sql2);            rs = ps.executeQuery();            String aa="";            String bb="";            while (rs.next()) {            Map dateMap=new HashMap();//一条记录一个dateMap            for(int z=1;z<=mapSize1;z++){            aa=String.valueOf(map1.get(z+""));            bb=rs.getString(z)==null?"":rs.getString(z);            dateMap.put(aa, bb);            }            list1.add(dateMap);            }          //结束从oracle取数据        }catch(Exception e){        e.printStackTrace();        }finally{         // 关闭记录集            if (rs != null) {                try {                    rs.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            // 关闭声明            if (ps != null) {                try {                    ps.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            // 关闭链接对象            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }                //打开对mdb文件的jdbc-odbc连接        Connection connection=null;       Statement statement=null;          try{                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");             String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+ savedMdbFilePathAndName.substring(1).trim(); //, *.accdb            connection = DriverManager.getConnection(database);              statement = connection.createStatement();               statement.execute(createTableSql1);   //创建ACCESS表sql            String insertSql="";            int listSize=list1.size();            //拼接access中的insert语句            for(int x=0;x<listSize;x++){            insertSql="insert into "+tablename1+" values(";            for(int k=1;k<=mapSize1;k++){                value=String.valueOf(list1.get(x).get(map1.get(k+"")));                if(k==mapSize1){                insertSql+="'"+value+"');";                }else{                insertSql+="'"+value+"',";                }                                }            statement.execute(insertSql);            }            //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型              inv.getResponse().setContentType("multipart/form-data");              //2.设置文件头：最后一个参数是设置下载文件名(假如我们叫data.mdb)              inv.getResponse().setHeader("Content-Disposition", "attachment;fileName="+"data.mdb");              File file = new File(savedMdbFilePathAndName);            OutputStream out1;              FileInputStream inputStream = new FileInputStream(file);            //3.通过response获取ServletOutputStream对象(out)              out1 = inv.getResponse().getOutputStream();             try {                                  int b = 0;                  byte[] buffer1 = new byte[8192];                  while (b != -1){                      b = inputStream.read(buffer1);                      //4.写到输出流(out)中                      out1.write(buffer1,0,b);                  }                    } catch (IOException e) {                  e.printStackTrace();              }finally{            out1.flush();                inputStream.close();                  out1.close();                  file.delete();            }          }catch(Exception e){        e.printStackTrace();        }finally{        // 关闭声明        if (statement != null) {                try {                statement.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        //关闭连接            if (connection != null) {                try {                connection.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }        }public Map getCols(Connection conn,PreparedStatement ps,ResultSet rs,String tableNmae) throws SQLException{//获取某张表中的所有字段        Map map=new HashMap();        String sql1="select  COLUMN_NAME from user_tab_cols where table_name=upper('"+tableNmae+"') order by column_id";//获取该表中的所有字段        ps = conn.prepareStatement(sql1);        rs = ps.executeQuery();                int j=0;                while (rs.next()) {        j++;        map.put(j+"", rs.getString(1));//存表中的所有字段        }                return map;}public String createTableSql(String tableNmae,Map map){String createTableSql="CREATE TABLE "+tableNmae+" ( ";        int mapSize=map.size();//字段个数        String value="";        for(int k=1;k<=mapSize;k++){        value=String.valueOf(map.get(k+""));        if(k==mapSize){        createTableSql+=value+" Memo );";        }else{        createTableSql+=value+" Memo ,";        }                }return createTableSql;}

封装的方法比较笨，待优化