工具类：
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
/**
* Access数据库连接类
* @author zql
* @createTime 2020-11-29 21:31:53
* @version 1.1
* @modifyLog 1.1 优化代码
*
*/
public class AccessConnection {
/**
* Access数据库Connection
*/
private Connection connection;
static {
try {
// 加载ucanaccess驱动
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
/**
* 开启数据库连接
* @author zql
* @createTime 2020-11-29 21:31:28
*
* @param path Access文件的相对或者绝对路径(支持*.mdb和*.accdb数据库文件)
* @param user 用户账号(如果没有就写"")
* @param pwd 密码密码(如果没有就写"")
*/
public void openAccessConnection(String path, String user, String pwd) {
try {
if (Objects.nonNull(this.connection) && !this.connection.isClosed()) {
this.connection.close();
}
/*
* memory：驱动程序属性memory默认是true的，当处理大型数据库，如果JVM内存不足，将会造成JVM内存溢出，建议使用者
* 使用-Xms和-Xmx选项为JVM分配足够的内存。否则，必须将驱动程序的“memory”属性设置为“false”。
*
* Skipindexes：(UCanAccess 2.0.9.4版本以后):为了最小化内存占用，它允许跳过简单索引的创建。它对引用完整性约束
*
* ignorecase：文本的大小写敏感性。
*
* immediatelyReleaseResources：(取代UCanAccess 3.0.6版本以后被弃用的singleConnection):它用于ETL作业、计划
* 任务，或者在只打开一个连接的情况下“一次性”使用UCanAccess。所有资源(内存和文件系统)将在连接结束时释放。默认
* 设置为false。
*/
String dburl="jdbc:ucanaccess://" + path + ";memory=false;Skipindexes=true;ignorecase = true;immediatelyReleaseResources=true";
this.connection = DriverManager.getConnection(dburl, user, pwd);
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
}
}
/**
* 获取所有表名
* @author zql
* @createTime 2020-11-29 21:31:14
*
* @return 表名字符串数组
* @throws Exception
*/
public String[] selectTableName() throws Exception {
DatabaseMetaData dbmd = this.connection.getMetaData();
ResultSet result = dbmd.getTables(null, null, "%", null);
List<String> tableNameList = new ArrayList<String>();
while (result.next()) {
tableNameList.add(result.getString(3));
}
result.close();
String[] tableNames = new String[tableNameList.size()];
for (int i = 0; i < tableNames.length; i++) {
tableNames[i] = tableNameList.get(i).toString();
}
return tableNames;
}
/**
* 执行查询
* @author zql
* @createTime 2020-11-29 21:30:55
*
* @param tableName 表名
* @param condition 条件键值对，键为字段，值为条件，该方法仅支持and连接条件，如需其他请自行修改
* @return
* @throws Exception
*/
public List<Map<String, String>> select(String tableName,Map<String,Object> condition) throws Exception {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
/*
* []是为了防止表名是关键字
*/
String sql = "select * from [" + tableName + "] ";
List<Object> params = new ArrayList<Object>();
StringBuffer conditions = new StringBuffer();
if (Objects.nonNull(condition) && condition.size() > 0) {
int conCount = condition.size();
int index = 0;
conditions.append(" where 1=1 ");
for (Map.Entry<String, Object> m : condition.entrySet()) {
if (index != conCount) {
conditions.append(" and ");
}
conditions.append(m.getKey()).append("=? ");
params.add(m.getValue());
index++;
}
sql = sql + conditions.toString();
}
PreparedStatement stmt = this.connection.prepareStatement(sql);
// 设置参数类型
this.setParamsType(stmt, params);
ResultSet result = stmt.executeQuery();
ResultSetMetaData data = result.getMetaData();
int columnCount = data.getColumnCount();
String[] columnNames = new String[columnCount];
for (int i = 1; i <= data.getColumnCount(); i++) {
// 获得列名
columnNames[i-1] = data.getColumnName(i);
}
while (result.next()) {
Map<String, String> map = new HashMap<String, String>(columnCount);
for (int i = 1; i <= columnCount; i++) {
map.put(columnNames[i - 1], result.getString(i));
}
list.add(map);
}
result.close();
stmt.close();
return list;
}
/**
* 执行添加
* @author zql
* @createTime 2020-11-29 21:30:29
*
* @param tableName 表名
* @param map 键值对，键为表的字段，值为字段对应的值（即要添加的值）
* @return 受影响的行数
* @throws Exception
*/
public int insert(String tableName,Map<String,Object> map) throws Exception {
StringBuffer tableFields = new StringBuffer();
List<Object> params = new ArrayList<Object>();
for (Map.Entry<String, Object> m : map.entrySet()) {
tableFields.append(m.getKey()).append(",");
params.add(m.getValue());
}
tableFields.deleteCharAt(tableFields.length() - 1);
String sql = this.getInsertSql(tableName, tableFields.toString());
PreparedStatement stmt = this.connection.prepareStatement(sql);
// 设置参数类型
this.setParamsType(stmt, params);
int r = stmt.executeUpdate();
stmt.close();
return r;
}
/**
* 执行更新
* @author zql
* @createTime 2020-11-29 21:30:07
*
* @param tableName 表名
* @param valueMap 键值对，键为要修改的字段，值为字段对应的值（即要修改的值）
* @param condition 条件键值对，键为字段，值为条件，该方法仅支持and连接条件，如需其他请自行修改
* @return
* @throws Exception
*/
public int update(String tableName,Map<String,Object> valueMap,Map<String,Object> condition) throws Exception {
StringBuffer tableFields = new StringBuffer();
List<Object> params = new ArrayList<Object>();
for (Map.Entry<String, Object> m : valueMap.entrySet()) {
tableFields.append(m.getKey()).append(",");
params.add(m.getValue());
}
tableFields.deleteCharAt(tableFields.length() - 1);
StringBuffer conditions = new StringBuffer();
int conCount = condition.size();
int index = 0;
for (Map.Entry<String, Object> m : condition.entrySet()) {
conditions.append(m.getKey()).append("=? ");
index++;
if (index != conCount) {
conditions.append(" and ");
}
params.add(m.getValue());
}
String sql = this.getUpdateSql(tableName, tableFields.toString(), conditions.toString());
PreparedStatement stmt = this.connection.prepareStatement(sql);
// 设置参数类型
this.setParamsType(stmt, params);
int r = stmt.executeUpdate();
stmt.close();
return r;
}
/**
* 执行删除
* @author zql
* @createTime 2020-11-29 21:29:43
*
* @param tableName 表名
* @param condition 条件键值对，键为字段，值为条件，该方法仅支持and连接条件，如需其他请自行修改
* @return 受影响的行数
* @throws Exception
*/
public int delete(String tableName,Map<String,Object> condition) throws Exception {
String sql ="delete from ["+tableName+"] ";
StringBuffer conditions = new StringBuffer();
int conCount = condition.size();
int index = 0;
conditions.append(" where ");
List<Object> params = new ArrayList<Object>();
for (Map.Entry<String, Object> m : condition.entrySet()) {
conditions.append(m.getKey()).append("=? ");
index++;
if (index != conCount) {
conditions.append(" and ");
}
params.add(m.getValue());
}
if (Objects.isNull(condition) || condition.size() == 0) {
return 0;
}
PreparedStatement stmt = this.connection.prepareStatement(sql+conditions.toString());
// 设置参数类型
this.setParamsType(stmt, params);
int r = stmt.executeUpdate();
stmt.close();
return r;
}
/**
* 关闭数据库连接
* @author zql
* @createTime 2020-11-29 21:29:26
*
*/
public void closeDbConnection(){
try {
if (Objects.nonNull(this.connection) && !this.connection.isClosed()) {
this.connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 获取插入sql语句
* @author zql
* @createTime 2020-11-29 21:28:58
*
* @param tableName 表名
* @param tableFields 字段名字符串，字段名以英文逗号隔开，例：a,b,c
* @return 返回 占位符方式的语句，?占位
*/
private final String getInsertSql(String tableName, String tableFields) {
return "insert into [" + tableName + "] (" + tableFields + ") values (" + this.getLoopSpellOn("?", tableFields.split(",").length) + ") ";
}
/**
* 获取更新sql语句
* @author zql
* @createTime 2020-11-29 21:28:25
*
* @param tableName 表名
* @param tableFields 字段名字符串，字段名以英文逗号隔开，例：a,b,c
* @param where 条件字符串
* @return 返回 占位符方式的语句 ，?占位
*/
private final String getUpdateSql(String tableName, String tableFields, String where) {
return "update [" + tableName + "] set " + this.getLoopSplit(tableFields.split(","), "=?") + " where " + where + " ";
}
/**
* 循环拼接字符串
* @author zql
* @createTime 2020-11-29 21:27:44
*
* @param str 字符串
* @param times 拼接的次数
* @return 拼接后的字符串
*/
private final String getLoopSpellOn(String str, int times) {
StringBuffer sb = new StringBuffer();
if (times > 0) {
for (int i = 0; i < times; i++) {
sb.append(str).append(",");
}
sb.deleteCharAt(sb.length() - 1);
}
return sb.toString();
}
/**
* 字符串组装
* <pre>
* 例： String tableFields = "a,b,c,d";
* getLoopSplit(tableFields.split(","), "c") = ac,bc,cc,dc
* </pre>
* @author zql
* @createTime 2020-11-29 21:26:49
*
* @param split 字符串数组
* @param mark 要组装的字符串
* @return 以逗号隔开的字符串
*/
private final String getLoopSplit(String[] split, String mark) {
if(Objects.isNull(split) || split.length <= 0) {
return "";
}
if (split.length == 1) {
return split[0] + mark;
}
StringBuffer sb = new StringBuffer();
for (int i = 0; i < split.length; i++) {
sb.append(split[i]).append(mark).append(",");
}
sb.deleteCharAt(sb.length() - 1);
return sb.toString();
}
/**
* 设置参数类型
* @author zql
* @createTime 2020-11-29 21:26:08
*
* @param stmt
* @param params
* @throws SQLException
*/
private void setParamsType(PreparedStatement stmt, List<Object> params) throws SQLException {
for(int i = 0, len = params.size(); i < len; i++) {
Object value = params.get(i);
if (value instanceof Integer) {
int integer = ((Integer) value).intValue();
stmt.setInt(i + 1, integer);
} else if (value instanceof String) {
String s = (String) value;
stmt.setString(i + 1, s);
} else if (value instanceof Double) {
double d = ((Double) value).doubleValue();
stmt.setDouble(i + 1, d);
} else if (value instanceof Float) {
float f = ((Float) value).floatValue();
stmt.setFloat(i + 1, f);
} else if (value instanceof Long) {
long l = ((Long) value).longValue();
stmt.setLong(i + 1, l);
} else if (value instanceof Boolean) {
boolean bl = ((Boolean) value).booleanValue();
stmt.setBoolean(i + 1, bl);
} else if (value instanceof Date) {
Date d = (Date) value;
stmt.setDate(i + 1, (Date) d);
} else if (value instanceof BigDecimal) {
BigDecimal bd = (BigDecimal) value;
stmt.setBigDecimal(i + 1, bd);
}
}
}
}测试类：
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Access数据库连接测试类
* @author zql
* @createTime 2020-11-29 21:38:39
* @version 1.1
*
*/
public class AccessConnectionTest {
private String dbPath = "E://access//test.accdb";
private AccessConnection db = new AccessConnection();
@Before
public void openAccessConnection() {
System.out.println("正在开启数据库连接...");
db.openAccessConnection(dbPath, "", "");
System.out.println("数据库连接已开启");
}
@Test
public void selectTableName() throws Exception {
String[] tableName = db.selectTableName();
for (int i = 0, len = tableName.length; i < len; i++) {
System.out.println("表名：" + tableName[i]);
List<Map<String, String>> list = db.select(tableName[i], null);
for (Map<String,String> map : list) {
for (Map.Entry<String, String> m : map.entrySet()) {
System.out.print(m.getValue() + " | ");
}
System.out.println();
}
}
System.out.println("执行结束！！！！！！");
}
@Test
public void select() throws Exception {
Map<String,Object> con0 = new HashMap<String,Object>();
con0.put("ID", 2);
List<Map<String, String>> list = db.select("学生表", con0);
list.forEach(map -> {
map.forEach((key,value) -> {
System.out.print(value + " | ");
});
System.out.println();
});
System.out.println("执行结束！！！！！！");
}
@Test
public void insert() throws Exception {
Map<String,Object> map1 = new HashMap<String,Object>();
map1.put("年龄", 12);
map1.put("性别", "男");
map1.put("年级", "四");
int r1 = db.insert("学生表", map1);
System.out.println("add:" + r1);
}
@Test
public void update() throws Exception {
Map<String,Object> val = new HashMap<String,Object>();
val.put("年级", "4年级");
Map<String,Object> con1 = new HashMap<String,Object>();
con1.put("ID", 2);
int r2 = db.update("学生表", val, con1);
System.out.println("update:" + r2);
}
@Test
public void delete() throws Exception {
Map<String,Object> con2 = new HashMap<String,Object>();
con2.put("ID", 3);
int r3 = db.delete("学生表", con2);
System.out.println("delete:" + r3);
System.out.println("执行结束！！！！！！");
}
@After
public void closeDbConnection() {
System.out.println("正在关闭数据库连接...");
db.closeDbConnection();
System.out.println("数据库连接已关闭");
}
}普通项目需要引入的包
ucanaccess-4.0.4.jar
hsqldb-2.3.1.jar
jackcess-2.1.11.jar
commons-lang-2.6.jar
commons-logging-1.1.3.jar
maven项目依赖
<dependency>
<groupId>net.sf.ucanaccess</groupId>
<artifactId>ucanaccess</artifactId>
<version>4.0.4</version>
</dependency>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
