java操作access数据库

最近项目里非要加一个生成access数据库的功能，而这个任务恰巧落到了我头上。没办法先研究研究怎么操作access数据库在说吧。先在网上翻翻了网页最后在找到了解决问题的办法，其操作它和操作别的数据库都一样只是ODBC驱动用什么而已了，就贴出一段代码与大家分享吧。
/**
     * 生成access数据库
     * @param townId  供电所Id
     * @param year  发行年份    
     * @param month 发行月份
     * @param request
     */
    public String saveAccess(int townId, String year, String month, HttpServletRequest request) {
       String year_month  = year + month;
       IFxInitializeBo fxInitializeBo = (IFxInitializeBo)WebEnvironment.getBean("fxInitializeBo");
       String nowFxYearMonth = fxInitializeBo.getNowFxYearMonth(townId);
       if(year_month.compareTo(nowFxYearMonth) > 0){
           return "【当前年月大于发行年月】";
       }
       //待生成access数据库名
       String access_name = "issuance_yh_" + year_month + "_" + townId + ".mdb";
       String src = request.getSession().getServletContext().getRealPath("/access/issuance_yh.mdb");
       String dest = request.getSession().getServletContext().getRealPath("/access/"+access_name+"");
       //删除旧数据库
       String sql_delete = String.format("delete from sl_access where town_id = %s and year = '%s' and month = '%s' and access_name = '%s'", townId, year, month, access_name);
       this.dbo.executeSqlUpdate(sql_delete);
       this.deleteFile(dest);
       //拷贝新数据库
       this.copyFile(src, dest);
       String sql = String.format("select '%s' nf, " +
                                     "          '%s' yf, " +
                                   "t2.town_code tid, " +
                                  "t2.town_name town, " +
                             "t3.village_code vid, " +
                         "t3.village_name village, " +
                           "t4.card_book_code cid, " +
                          "t4.card_book_name card, " +
                          "              '-' line, " +
                         "t5.type_order_no typeid, " +
                          "t5.elec_type_name type, " +
                          "        t5.price price, " +
                          "            t1.zhs zhs, " +
                          "          t1.fxhs fxhs, " +
                          "          t1.elec elec, " +
                          "t1.elec_back elec_back, " +
                          "  t1.elec_sum elec_sum, " +
                          "    t1.fee_sum fee_sum, " +
                         "t1.replace_fee4 fee_wgf, " +
                    "t1.replace_fee_sum fee_dsfhj, " +
                                        "0 fee_pq, " +
                                        "0 fee_fq, " +
                                        "0 fee_gq, " +
                          "t1.replace_fee1 fee_sx, " +
                          "t1.replace_fee2 fee_dj, " +
                          "t1.replace_fee3 fee_fj, " +
                       "t1.replace_fee6 fee_kzsny, " +
                           "t1.replace_fee5 fee_skjj, " +
                            "t1.replace_fee7 fee_dfjj " +
                       "from (select a.village_id, " +
                                  "a.card_book_id, " +
                                "a.elec_type_name, " +
                                           "a.zhs, " +
                             "nvl(b.fxhs, 0) fxhs, " +
                                           "a.elec, " +
                                      "a.elec_back, " +
                                       "a.elec_sum, " +
                                        "a.fee_sum, " +
                               "a.replace_fee_sum, " +
                                  "a.replace_fee1, " +
                                  "a.replace_fee2, " +
                                  "a.replace_fee3, " +
                                  "a.replace_fee4, " +
                                  "a.replace_fee5, " +
                                  "a.replace_fee6, " +
                                  "a.replace_fee7  " +
                                               "from " +
                            "(select count(1) zhs, " + //当月发行
                                      "village_id, " +
                                    "card_book_id, " +
                                  "elec_type_name, " +
                                  "sum(elec) elec, " +
                        "sum(elec_back) elec_back, " +
                          "sum(elec_sum) elec_sum, " +
                            "sum(fee_sum) fee_sum, " +
            "sum(replace_fee_sum) replace_fee_sum, " +
                  "sum(replace_fee1) replace_fee1, " +
                  "sum(replace_fee2) replace_fee2, " +
                  "sum(replace_fee3) replace_fee3, " +
                  "sum(replace_fee4) replace_fee4, " +
                  "sum(replace_fee5) replace_fee5, " +
                  "sum(replace_fee6) replace_fee6, " +
                  "sum(replace_fee7) replace_fee7  " +
                                   "from sv_fxreport " +
                                 "where town_id = %s " + //供电所id
                            "and year_month = '%s' " +
"group by village_id, card_book_id, elec_type_name) a, " +
                           "(select count(1) fxhs, " + //发行户数
                                      "village_id, " +
                                    "card_book_id, " +
                                   "elec_type_name " +
                                 "from sv_fxreport " +
                               "where town_id = %s " + //供电所id
                            "and year_month = '%s' " +
                                  "and fee_sum > 0 " +
"group by village_id, card_book_id, elec_type_name) b " +
             "where a.village_id = b.village_id(+) " +
           "and a.card_book_id = b.card_book_id(+) " +
  "and a.elec_type_name = b.elec_type_name(+)) t1, " +
                                      "sl_town t2, " +
                                   "sl_village t3, " +
                                 "sl_card_book t4, " +
                                  "sl_elec_type t5 " +
                                 "where t2.id = %s " + //供电所id
                              "and t3.town_id = %s " + //供电所id
                        "and t3.id = t1.village_id " +
                              "and t4.town_id = %s " + //供电所id
                      "and t4.id = t1.card_book_id " +
        "and t5.elec_type_name = t1.elec_type_name " +
              "order by t2.id, t3.id, t4.id, t5.id " , year, month, townId,
              year_month, townId, year_month, townId, townId, townId);
       //准备生成的数据(当前)
       List dataList = this.dbo.executeSqlQuery(sql, true);
       if(dataList!=null && dataList.size() > 0) {
           if(this.save(dataList, dest)){
              //保存生成access数据库名
              String sql_access = String.format("insert into sl_access(id,town_id,year,month,access_name,access_path) values(slq_access.nextval,%s,'%s','%s','%s','%s')", townId, year, month, access_name, "/access/"+access_name);
              this.dbo.executeSqlUpdate(sql_access);
           } else {
               //删除垃圾数据
               this.deleteFile(dest);
               return "【生成数据库失败】";
           }   
       } else {
            String sql_history = String.format("select '%s' nf, " +
                                               "       '%s' yf, " +
                                             "t2.town_code tid, " +
                                            "t2.town_name town, " +
                                          "t3.village_code vid, " +
                                      "t3.village_name village, " +
                                        "t4.card_book_code cid, " +
                                       "t4.card_book_name card, " +
                                       "              '-' line, " +
                                      "t5.type_order_no typeid, " +
                                       "t5.elec_type_name type, " +
                                       "        t5.price price, " +
                                       "            t1.zhs zhs, " +
                                       "          t1.fxhs fxhs, " +
                                       "          t1.elec elec, " +
                                       "t1.elec_back elec_back, " +
                                       "  t1.elec_sum elec_sum, " +
                                       "    t1.fee_sum fee_sum, " +
                                      "t1.replace_fee4 fee_wgf, " +
                                 "t1.replace_fee_sum fee_dsfhj, " +
                                                     "0 fee_pq, " +
                                                     "0 fee_fq, " +
                                                     "0 fee_gq, " +
                                       "t1.replace_fee1 fee_sx, " +
                                       "t1.replace_fee2 fee_dj, " +
                                       "t1.replace_fee3 fee_fj, " +
                                    "t1.replace_fee6 fee_kzsny, " +
                                     "t1.replace_fee5 fee_skjj, " +
                                      "t1.replace_fee7 fee_dfjj " +
                                    "from (select a.village_id, " +
                                               "a.card_book_id, " +
                                             "a.elec_type_name, " +
                                                         "a.zhs, " +
                                          "nvl(b.fxhs, 0) fxhs, " +
                                                         "a.elec, " +
                                                    "a.elec_back, " +
                                                     "a.elec_sum, " +
                                                      "a.fee_sum, " +
                                            "a.replace_fee_sum, " +
                                               "a.replace_fee1, " +
                                               "a.replace_fee2, " +
                                               "a.replace_fee3, " +
                                               "a.replace_fee4, " +
                                               "a.replace_fee5, " +
                                               "a.replace_fee6, " +
                                               "a.replace_fee7  " +
                                                             "from " +
                                         "(select count(1) zhs, " + //当月发行
                                                   "village_id, " +
                                                 "card_book_id, " +
                                               "elec_type_name, " +
                                               "sum(elec) elec, " +
                                     "sum(elec_back) elec_back, " +
                                       "sum(elec_sum) elec_sum, " +
                                         "sum(fee_sum) fee_sum, " +
                         "sum(replace_fee_sum) replace_fee_sum, " +
                               "sum(replace_fee1) replace_fee1, " +
                               "sum(replace_fee2) replace_fee2, " +
                               "sum(replace_fee3) replace_fee3, " +
                               "sum(replace_fee4) replace_fee4, " +
                               "sum(replace_fee5) replace_fee5, " +
                               "sum(replace_fee6) replace_fee6, " +
                               "sum(replace_fee7) replace_fee7  " +
                                             "from sl_elec_history " +
                                               "where town_id = %s " + //供电所id
                                         "and year_month = '%s' " +
         "group by village_id, card_book_id, elec_type_name) a, " +
                                        "(select count(1) fxhs, " + //发行户数
                                                   "village_id, " +
                                                 "card_book_id, " +
                                                "elec_type_name " +
                                          "from sl_elec_history " +
                                            "where town_id = %s " + //供电所id
                                         "and year_month = '%s' " +
                                               "and fee_sum > 0 " +
          "group by village_id, card_book_id, elec_type_name) b " +
                          "where a.village_id = b.village_id(+) " +
                        "and a.card_book_id = b.card_book_id(+) " +
               "and a.elec_type_name = b.elec_type_name(+)) t1, " +
                                                   "sl_town t2, " +
                                                "sl_village t3, " +
                                              "sl_card_book t4, " +
                                               "sl_elec_type t5 " +
                                              "where t2.id = %s " + //供电所id
                                           "and t3.town_id = %s " + //供电所id
                                     "and t3.id = t1.village_id " +
                                           "and t4.town_id = %s " + //供电所id
                                   "and t4.id = t1.card_book_id " +
                     "and t5.elec_type_name = t1.elec_type_name " +
                         "order by t2.id, t3.id, t4.id, t5.id " , year, month, townId,
                         year_month, townId, year_month, townId, townId, townId);
           //准备生成的数据(历史)
           List datahistoryList = this.dbo.executeSqlQuery(sql_history, true);
           if(datahistoryList!=null&&datahistoryList.size()>0) {
               if(this.save(datahistoryList, dest)){
                  //保存生成access数据库名
                  String sql_access = String.format("insert into sl_access(id,town_id,year,month,access_name,access_path) values(slq_access.nextval,%s,'%s','%s','%s','%s')", townId, year, month, access_name, "/access/"+access_name);
                  this.dbo.executeSqlUpdate(sql_access);
               } else {
                   //删除垃圾数据
                   this.deleteFile(dest);
                   return "【生成数据库失败】";
               }
           } else {
               //删除垃圾数据
               this.deleteFile(dest);
               return "【无生成的数据】";
           }
       }
       return "ok";
    }
    
    /**
     * 生成access数据库
     * @param data  数据
     * @param dest  数据源    
     */
    private boolean save(List dataList, String dest) {
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = this.getConn(dest);
            String sql="insert into db_collect(nf,yf,tid,town,vid,village,cid,card,line,typeid,type,price,zhs,fxhs,elec,elec_back,elec_sum,fee_sum,fee_wgf,fee_dsfhj,fee_pq,fee_fq,fee_gq,fee_sx,fee_dj,fee_fj,fee_kzsny,fee_skjj,fee_dfjj) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            for(int i = 0; i < dataList.size(); i++) {
                Map data = (Map)dataList.get(i);
                ps.setString(1, data.get("nf").toString());
                ps.setString(2, data.get("yf").toString());
                ps.setString(3, data.get("tid").toString());
                ps.setString(4, data.get("town").toString());
                ps.setString(5, data.get("vid").toString());
                ps.setString(6, data.get("village").toString());
                ps.setString(7, data.get("cid").toString());
                ps.setString(8, data.get("card").toString());
                ps.setString(9, data.get("line").toString());
                ps.setString(10, data.get("typeid").toString());
                ps.setString(11, data.get("type").toString());
                ps.setString(12, data.get("price").toString());
                ps.setInt(13, Integer.valueOf(data.get("zhs").toString()));
                ps.setInt(14, Integer.valueOf(data.get("fxhs").toString()));
                ps.setInt(15, Integer.valueOf(data.get("elec").toString()));
                ps.setInt(16, Integer.valueOf(data.get("elecBack").toString()));
                ps.setInt(17, Integer.valueOf(data.get("elecSum").toString()));
                ps.setString(18, data.get("feeSum").toString());
                ps.setString(19, data.get("feeWgf").toString());
                ps.setString(20, data.get("feeDsfhj").toString());
                ps.setString(21, data.get("feePq").toString());
                ps.setString(22, data.get("feeFq").toString());
                ps.setString(23, data.get("feeGq").toString());
                ps.setString(24, data.get("feeSx").toString());
                ps.setString(25, data.get("feeDj").toString());
                ps.setString(26, data.get("feeFj").toString());
                ps.setString(27, data.get("feeKzsny").toString());
                ps.setString(28, data.get("feeSkjj").toString());
                ps.setString(29, data.get("feeDfjj").toString());
                ps.addBatch();
            }
            ps.executeBatch();
            return true;
        }catch(Exception e){
            throw new DBAccessException(e);
        }finally{
            try{
                ps.close();
                if(conn != null) {
                    conn.close();
                }
            }catch(Exception e){
                return false;
            }
        }
    }
    
    //获取数据库连接
    private Connection getConn(String dest){
        try{
            //取连接配置
            String driver = "sun.jdbc.odbc.JdbcOdbcDriver";            
            //建立连接
            String url = "jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb);DBQ="+dest;
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url);            
            return connection;
        } catch(Exception e) {
            throw new ProjectException("数据库连接错误 :",e);
        }
    }
    
    /**
     * 删除文件目录先
     * 判断文件目录是否存在
     *
     * @param  dest 文件目的目录
     */
    private void deleteFile(String dest) {
        try {
            File file = new File(dest);
            if(file.exists()&&file.isFile()) {
                file.delete();
            }
        } catch (Exception e) {
            throw new DBAccessException(e);
        }
    }
    
    /**
     * 以文件流的方式复制文件
     *
     * @param  src 文件源目录
     * @param  dest 文件目的目录
     */
    private void copyFile(String src, String dest) {
        
        FileInputStream fin = null;
        FileOutputStream fout = null;
        try {
            fin = new FileInputStream(src);
            File file = new File(dest);
            if(!file.exists()&&file.isFile()) {
                file.createNewFile();
            }
            fout = new FileOutputStream(file);
            byte[] buffer = new byte[8192];
            int bytesRead = 0;
            while ((bytesRead = fin.read(buffer, 0, 8192)) != -1) {
                fout.write(buffer, 0, bytesRead);
            }
        } catch (Exception e) {
            throw new DBAccessException(e);
        } finally {
            try {
                fin.close();
                fout.close();
            } catch (Exception e) {}
        }
    
    }