1、总体说明
现在java读取mdb数据(也就是链接access数据库读取数据)对jdk的版本有要求,jdk1.8以前的可以直接链接access数据 库读取数据,而jdk1.8以后的都需要加载一个连接驱动,我用的就是jdk1.8,而驱动呢 我用的是ucanaccess.jar。话不多说直接上代码:
private static Connection getConnection(String path) {
if (null == connection) {
resetConnection(path);
return connection;
} else {
return connection;
}
}
private static void resetConnection(String path) {
try {
String mdbPath = "jdbc:ucanaccess://" + path + ";skipIndexes=true";
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver").newInstance();
long startTime = System.currentTimeMillis();
connection = DriverManager.getConnection(mdbPath,"user","123");
long endTime = System.currentTimeMillis();
System.out.println("连接数据库时间: " + (endTime - startTime) + "ms");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 连接mdb并执行sql
*/
public static JSONArray linkMDB(String path, String sql, boolean update) throws Exception {
if (path.isEmpty() && sql.isEmpty()) {
throw new Exception("mdbd的路径和sql操作不能为空");
}
Connection connection = getConnection(path);
Statement statement = connection.createStatement();
JSONArray result = null;
if (!update) {
ResultSet rs = statement.executeQuery(sql);
result = resultSetToJson(rs);
} else {
statement.executeUpdate(sql);
}
statement.close();
return result;
}
/**
* 转换mdb查询结果
*/
private static JSONArray resultSetToJson(ResultSet rs) throws SQLException, UnsupportedEncodingException {
// json数组
JSONArray array = new JSONArray();
// 获取列数
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 遍历ResultSet中的每条数据
while (rs.next()) {
JSONObject jsonObj = new JSONObject();
// 遍历每一列
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
String columnTypeName = metaData.getColumnTypeName(i);
if (null != columnName) {
String gbk = null;
Object query = null;
try {
query = rs.getObject(i);
} catch (Exception e) {
e.printStackTrace();
}
if (query != null) {
jsonObj.put(columnName, query);
}
if (gbk != null) {
jsonObj.put(columnName, gbk);
}
}
}
array.add(jsonObj);
}
rs.close();
return array;
}