Mdb文件工具类，UCanAccess使用，Access数据库操作
2024-08-31 09:08:55
================================
©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/
最新文章
- String对象方法扩展
- svm机器学习算法中文视频讲解
- python模块及包的导入
- Java NIO教程 Selector
- iOS7.0适配问题
- BZOJ-2326 数学作业 矩阵乘法快速幂+快速乘
- 比较compareTo与equals及==的区别
- Dtrace for Linux 2016
- 【Struts2学习笔记-6--】Struts2之拦截器
- C++拷贝对象
- Kaggle Bike Sharing Demand Prediction – How I got in top 5 percentile of participants?
- 【方法2】删除Map中Value反复的记录，而且仅仅保留Key最小的那条记录
- ID卡
- C# 操作IIS -App & AppPools
- win10 uwp 从StorageFile获取文件大小
- Vigenère 密码
- asp.net core 系列 13 日志
- JavaScript入门学习笔记(异常处理)
- (7)udp-socket
- 快乐的一天从JAVA第一课开始，生活美滋滋！！！