最近项目里非要加一个生成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) {}
}
}
/**
* 生成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) {}
}
}