Mdb文件工具类,UCanAccess使用,Access数据库操…
2020-01-09 16:04:26来源:博客园 阅读 ()
Mdb文件工具类,UCanAccess使用,Access数据库操作
================================
©Copyright 蕃薯耀 2020-01-09
https://www.cnblogs.com/fanshuyao/
使用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
https://www.cnblogs.com/fanshuyao/
原文链接:https://www.cnblogs.com/fanshuyao/p/12169669.html
如有疑问请与原作者联系
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- 代码对比工具,我就用这 6 个! 2020-06-10
- 2020最新IDEA插件大集合,一款能帮助你写代码的工具是多么重 2020-06-09
- 「starter推荐」简单高效Excel 导出工具 2020-06-08
- Spring Boot 实现配置文件加解密原理 2020-06-08
- Java跨平台原理(字节码文件、虚拟机) 以及Java安全性 2020-06-07
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash