需要的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
具体代码
//使用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<Map<String,String>> read(String absoluteFilePath, String sql){
// log.info("mdb文件路径absoluteFilePath=" + absoluteFilePath);
// log.info("mdb查询sql=" + sql);
List<Map<String,String>> rowList = new ArrayList<Map<String,String>>();
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<Map<String,String>> read(File file, String sql){
return read(file.getAbsolutePath(), sql);
}
具体调用
String filePath = "E:\\Leader II_Data.mdb;memory=false";
String sql = "select * from Data";
//List<Map<String, Object>> listMap = read("C:/db/test.mdb", sql);
List<Map<String,String>> rowList =JdbcUtil.read("E:/Leader II_Data.mdb", sql);
if(rowList != null && rowList.size() > 0){
System.out.println("=====listMap.size()="+rowList.size());
for (Map<String, String> row : rowList) {
System.out.println(row.toString());
System.out.println("");
}
}