Mdb文件工具类,UCanAccess使用,Access数据库操作
程序员文章站
2022-03-18 14:08:58
©Copyright 蕃薯耀 2020-01-09 https://www.cnblogs.com/fanshuyao/ 使用Ocbc连接是区分电脑是32位还是64位的,需要安装相应的驱动文件,不方便,所以采用第三方的Jar包(UCanAccess) UCanAccess-4.0.4-bin.zip ......
================================
©copyright 蕃薯耀 2020-01-09
使用ocbc连接是区分电脑是32位还是64位的,需要安装相应的驱动文件,不方便,所以采用第三方的jar包(ucanaccess)
ucanaccess-4.0.4-bin.zip(自行搜索)
需要的jar包:
ucanaccess-4.0.4.jar
在lib文件的jar包:
commons-lang-2.6.jar
commons-logging-1.1.3.jar
hsqldb.jar
jackcess-2.1.11.jar
import java.io.file; 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.statement; import java.util.arraylist; import java.util.list; import java.util.properties; import org.apache.log4j.logger; import com.plan.commons.row; import com.plan.commons.rowimpl; public class mdbutils { private static logger log = logger.getlogger(mdbutils.class); //odbc方式区分32位和64位系统 /* private final static string jdbc_driver = "sun.jdbc.odbc.jdbcodbcdriver"; private final static string jdbc_url = "jdbc:odbc:driver={microsoft access driver (*.mdb, *.accdb)};dbq="; */ //使用ucanaccess private final static string jdbc_driver = "net.ucanaccess.jdbc.ucanaccessdriver"; private final static string jdbc_url = "jdbc:ucanaccess://"; public static void close(resultset resultset, statement statement, connection connection){ try { if(resultset != null){ resultset.close(); //log.info("关闭mdb resultset连接。"); //system.out.println("关闭mdb resultset连接。"); } if(statement != null){ statement.close(); //log.info("关闭mdb statement连接。"); //system.out.println("关闭mdb statement连接。"); } if(connection != null){ connection.close(); //log.info("关闭mdb connection连接。"); //system.out.println("关闭mdb connection连接。"); } } catch (sqlexception e) { e.printstacktrace(); log.error("关闭mdb连接出错。" + e); } } /** * mdb文件获取连接 * @param absolutefilepath * @return */ public static connection getconn(string absolutefilepath){ log.info("mdb文件路径absolutefilepath=" + absolutefilepath); properties prop = new properties(); prop.put("charset", "utf-8");//解决中文乱码(gb2312/gbk) //prop.put("user", ""); //prop.put("password", ""); string url = jdbc_url + absolutefilepath; connection connection = null; try { connection = drivermanager.getconnection(url, prop); } catch (sqlexception e) { e.printstacktrace(); log.info("mdb文件获取连接出错。exception=" + e); } return connection; } /** * 查询mdb文件的表数据 * @param absolutefilepath mdb文件绝对路径 * @param sql 查询的sql语句 * @return */ public static list<row> read(string absolutefilepath, string sql){ log.info("mdb文件路径absolutefilepath=" + absolutefilepath); log.info("mdb查询sql=" + sql); list<row> rowlist = new arraylist<row>(); properties prop = new properties(); prop.put("charset", "utf-8");//解决中文乱码(gb2312/gbk) //prop.put("user", ""); //prop.put("password", ""); string url = jdbc_url + absolutefilepath; //preparedstatement preparedstatement = null; statement statement = null; resultset resultset = null; connection connection = null; try{ class.forname(jdbc_driver); connection = drivermanager.getconnection(url, prop); statement = connection.createstatement(); resultset = statement.executequery(sql); resultsetmetadata resultsetmetadata = resultset.getmetadata(); while(resultset.next()){ row row = new rowimpl(); for(int i=1; i<= resultsetmetadata.getcolumncount(); i++){ string columnname = resultsetmetadata.getcolumnname(i);//列名 object columnvalue = resultset.getobject(i); row.addcolumn(columnname, columnvalue); } rowlist.add(row); } }catch (exception e) { e.printstacktrace(); log.info("mdb文件读取sql出错。exception=" + e); throw new runtimeexception(e); }finally{ close(resultset, statement, connection); } return rowlist; } /** * 查询mdb文件的表数据 * @param file file * @param sql 查询的sql语句 * @return */ public static list<row> read(file file, string sql){ return read(file.getabsolutepath(), sql); } /** * 更新mdb文件的表数据,返回更新的记录数量,0表示没有更新(id不允许更新) * @param absolutefilepath mdb文件绝对路径 * @param sql 查询的sql语句 * @return */ public static int update(string absolutefilepath, string sql){ log.info("mdb文件绝对路径,absolutefilepath=" + absolutefilepath); log.info("mdb文件更新,sql=" + sql); properties prop = new properties(); prop.put("charset", "utf-8");//解决中文乱码(gb2312/gbk) string url = jdbc_url + absolutefilepath; statement statement = null; connection connection = null; int updatesize = 0; try{ class.forname(jdbc_driver); connection = drivermanager.getconnection(url, prop); statement = connection.createstatement(); updatesize = statement.executeupdate(sql); }catch (exception e) { e.printstacktrace(); log.info("mdb文件更新sql出错。exception=" + e); throw new runtimeexception(e); }finally{ close(null, statement, connection); } log.info("mdb更新数量,updatesize=" + updatesize + "。sql="+sql); return updatesize; } /** * 更新mdb文件的表数据,返回更新的记录数量,0表示没有更新 * @param absolutefilepath mdb文件绝对路径 * @param sql 查询的sql语句 * @return */ public static int update(string absolutefilepath, string sql, list<object> params){ log.info("mdb文件路径absolutefilepath=" + absolutefilepath); log.info("mdb更新sql=" + sql); properties prop = new properties(); prop.put("charset", "utf-8");//解决中文乱码(gb2312/gbk) string url = jdbc_url + absolutefilepath; preparedstatement preparedstatement = null; connection connection = null; int updatesize = 0; try{ class.forname(jdbc_driver); connection = drivermanager.getconnection(url, prop); preparedstatement = connection.preparestatement(sql); if(params != null && params.size() > 0){ for(int i=0; i<params.size(); i++){ preparedstatement.setobject(i + 1, params.get(i)); } } updatesize = preparedstatement.executeupdate(); }catch (exception e) { e.printstacktrace(); log.info("mdb文件更新sql出错。exception=" + e); throw new runtimeexception(e); }finally{ close(null, preparedstatement, connection); } log.info("mdb更新数量,updatesize=" + updatesize + "。sql="+sql); return updatesize; } /** * mdb文件sql执行(如新增、删除字段),成功返回true * @param absolutefilepath mdb文件绝对路径 * @param sql 查询的sql语句 * @return */ public static boolean execute(string absolutefilepath, string sql){ log.info("mdb文件绝对路径,absolutefilepath=" + absolutefilepath); log.info("mdb文件sql执行,sql=" + sql); properties prop = new properties(); prop.put("charset", "utf-8");//解决中文乱码(gb2312/gbk) string url = jdbc_url + absolutefilepath; statement statement = null; connection connection = null; boolean result = false; try{ class.forname(jdbc_driver); connection = drivermanager.getconnection(url, prop); statement = connection.createstatement(); statement.execute(sql); result = true; log.info("mdb文件执行sql成功。sql=" + sql); }catch (exception e) { e.printstacktrace(); log.info("mdb文件执行sql出错。exception=" + e); throw new runtimeexception(e); }finally{ close(null, statement, connection); } return result; } public static void main(string[] args) { /* string sql = "select * from cu_proj_zxgh_land"; //list<map<string, object>> listmap = read("c:/db/test.mdb", sql); list<row> rowlist = read("c:/db/02-地块划分与指标控制图.mdb", sql); if(rowlist != null && rowlist.size() > 0){ system.out.println("=====listmap.size()="+rowlist.size()); for (row row : rowlist) { system.out.println(row.tostring()); system.out.println(""); } } */ /* //更新数据 string sql = "update t_user set age=199 where id=1"; system.out.println(update("c:/db/test.mdb", sql)); */ //preparedstatement /* string sql = "update t_user set age=?,email=? where id=?"; list<object> params = new arraylist<object>(); params.add(99); params.add("bbb@qq.com"); params.add(1); system.out.println(update("c:/db/test.mdb", sql, params)); */ //增加列 /* string sql = "alter table t_user add column gh_id int"; //string sql = "alter table t_user add column my_id datetime not null default now()"; system.out.println(execute("c:/db/test.mdb", sql)); */ } }
(如果你觉得文章对你有帮助,欢迎捐赠,^_^,谢谢!)
================================
©copyright 蕃薯耀 2020-01-09