简介

使用UCanAccess并进行封装，通过反射、注解的方式构建目标数据，便捷创建表、写入数据、查询数据。

Maven依赖

<dependency> <groupId>net.sf.ucanaccess</groupId> <artifactId>ucanaccess</artifactId> <version>5.0.1</version> </dependency>

使用实例

建表

AccessUtil.createTable(conn, tableName, HonyeeAccessDO.class);

插入数据

AccessUtil.insert(conn, tableName, HonyeeAccessDO.class, list);

查询数据

List<HonyeeAccessDO> all = AccessUtil.listAll(conn, tableName, HonyeeAccessDO.class);

完整代码

HonyeeAccessDO

import lombok.Data; import org.apache.ibatis.type.JdbcType; @Data public class HonyeeAccessDO { @AccessTableField(value = "ID") private String id; @AccessTableField(value = "Name") private String name; @AccessTableField(value = "Remark", jdbcType = JdbcType.LONGVARCHAR) private String remark; }

AccessTableField

import org.apache.ibatis.type.JdbcType; import java.lang.annotation.*; /** * Access 字段标识 */ @Documented @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.FIELD, ElementType.ANNOTATION_TYPE}) public @interface AccessTableField { String value() default ""; JdbcType jdbcType() default JdbcType.UNDEFINED; }

AccessUtil

import com.jhtech.cloud.server.boot.exception.ApplicationException; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.type.JdbcType; import java.lang.reflect.Field; import java.math.BigDecimal; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; @Slf4j public class AccessUtil { /** * 创建表结构 * @param conn access连接 * @param tableName 表名 * @param clz 表模型对应的类 */ public static <T> void createTable(Connection conn, String tableName, Class<T> clz) throws SQLException { try (Statement stmt = conn.createStatement()) { String sql = formatCreateSql(tableName, clz); stmt.executeUpdate(sql); } } private static <T> String formatCreateSql(String tableName, Class<T> clz) { List<String> fieldSqlList = new ArrayList<>(); for (Field field : clz.getDeclaredFields()) { AccessTableField ann = field.getAnnotation(AccessTableField.class); if (ann == null) { throw new ApplicationException(String.format("字段缺少注解@TableField: %s.%s", clz.getSimpleName(), field.getName())); } // 默认包含自增ID，无需手动添加 if ("ID".equalsIgnoreCase(ann.value())) { continue; } String fieldType = ""; Class<?> type = field.getType(); JdbcType jdbcType = ann.jdbcType(); if (type == String.class) { fieldType = "TEXT(255)"; if (jdbcType == JdbcType.LONGNVARCHAR) { fieldType = "MEMO"; } } else if (type == Integer.class) { fieldType = "INTEGER"; } else if (type == Long.class) { fieldType = "LONG"; } else if (type == Float.class) { fieldType = "DOUBLE"; } else if (type == Double.class) { fieldType = "DOUBLE"; } else if (type == BigDecimal.class) { fieldType = "DOUBLE"; } else { log.warn("字段输出类型未定义: {}.{}", clz.getSimpleName(), field.getName()); fieldType = "VARCHAR(255)"; } fieldSqlList.add(String.format("%s %s", ann.value(), fieldType)); } if (fieldSqlList.isEmpty()) { throw new ApplicationException("没有字段可输出"); } String createTableSql = String.format( "CREATE TABLE [%s] (\n" + " ID COUNTER PRIMARY KEY,\n" + "\t%s\n" + ");", tableName, String.join(",\n\t", fieldSqlList)); return createTableSql; } /** * 插入数据 * @param conn access连接 * @param tableName 表名 * @param clz 表模型对应的类 * @param list 数据 */ public static <T> void insert(Connection conn, String tableName, Class<T> clz, List<T> list) throws SQLException, IllegalAccessException { List<String> fieldNameList = new ArrayList<>(); List<String> fieldNamePlaceholderList = new ArrayList<>(); for (Field field : clz.getDeclaredFields()) { AccessTableField ann = field.getAnnotation(AccessTableField.class); if (ann == null) { throw new ApplicationException(String.format("字段缺少注解@AccessTableField: %s.%s", clz.getSimpleName(), field.getName())); } fieldNameList.add(ann.value()); fieldNamePlaceholderList.add("?"); } String insertSql = String.format("INSERT INTO [%s] (%s) VALUES (%s)", tableName, String.join(", ", fieldNameList), String.join(", ", fieldNamePlaceholderList)); try (PreparedStatement ps = conn.prepareStatement(insertSql)) { for (T t : list) { int i = 1; for (Field field : clz.getDeclaredFields()) { Class<?> type = field.getType(); field.setAccessible(true); if (type == String.class) { ps.setString(i++, (String) field.get(t)); } else if (type == Integer.class) { ps.setInt(i++, (Integer) field.get(t)); } else if (type == Long.class) { ps.setLong(i++, (Long) field.get(t)); } else if (type == Float.class) { ps.setFloat(i++, (Float) field.get(t)); } else if (type == Double.class) { ps.setDouble(i++, (Double) field.get(t)); } else if (type == BigDecimal.class) { ps.setBigDecimal(i++, (BigDecimal) field.get(t)); } else { log.warn("字段输出类型未定义: {}.{}", clz.getSimpleName(), field.getName()); Object val = field.get(t); if (val == null) { ps.setString(i++, null); } else { ps.setString(i++, field.get(t).toString()); } } } ps.executeUpdate(); } } } /** * 查询全部 */ public static <T> List<T> listAll(Connection conn, String tableName, Class<T> clz) throws SQLException, IllegalAccessException, InstantiationException { List<T> list = new ArrayList<>(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from " + tableName); Map<Field, AccessTableField> fieldMap = new HashMap<>(); for (Field field : clz.getDeclaredFields()) { AccessTableField ann = field.getAnnotation(AccessTableField.class); if (ann == null) { throw new ApplicationException(String.format("字段缺少注解@AccessTableField: %s.%s", clz.getSimpleName(), field.getName())); } fieldMap.put(field, ann); } T t = clz.newInstance(); while (rs.next()) { for (Map.Entry<Field, AccessTableField> entry : fieldMap.entrySet()) { Field field = entry.getKey(); AccessTableField ann = entry.getValue(); Object value = getValue(rs, field, ann); field.setAccessible(true); field.set(t, value); } list.add(t); } return list; } /** * 获取值 */ private static <T> Object getValue(ResultSet rs, Field field, AccessTableField ann) throws SQLException { Class<?> type = field.getType(); if (type == String.class) { return rs.getString(ann.value()); } else if (type == Integer.class) { return rs.getInt(ann.value()); } else if (type == Long.class) { return rs.getLong(ann.value()); } else if (type == Float.class) { return rs.getFloat(ann.value()); } else if (type == Double.class) { return rs.getDouble(ann.value()); } else if (type == BigDecimal.class) { return rs.getBigDecimal(ann.value()); } log.warn("字段输出类型未定义: {}", field.getName()); return null; } }

完整调用示例

import com.healthmarketscience.jackcess.Database; import com.healthmarketscience.jackcess.DatabaseBuilder; import java.io.File; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class Main { public static void main(String[] args) throws Exception { String dir = "d://"; String fileName = "honyee.accdb"; File file = new File(dir, fileName); // 加载驱动（其实不太需要，有自动加载机制） Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); // 创建空的 Access 数据库文件，选择合适的版本 DatabaseBuilder.create(Database.FileFormat.V2016, file).close(); String connectPath = String.format("jdbc:ucanaccess://%s", file.getAbsolutePath()); // 连接到新创建的数据库 try (Connection conn = DriverManager.getConnection(connectPath)) { String tableName = "Honyee_Table_Name"; // 1.建表 AccessUtil.createTable(conn, tableName, HonyeeAccessDO.class); List<HonyeeAccessDO> list = new ArrayList<>(); HonyeeAccessDO d = new HonyeeAccessDO(); d.setName("Honyee"); d.setRemark("备注"); list.add(d); // 2. 插入数据 AccessUtil.insert(conn, tableName, HonyeeAccessDO.class, list); // 3. 查询数据 { List<HonyeeAccessDO> all = AccessUtil.listAll(conn, tableName, HonyeeAccessDO.class); System.out.println(all.get(0)); } // 3. 查询数据-基础方式 Statement stmt = conn.createStatement(); { ResultSet rs = stmt.executeQuery("select * from " + tableName); while (rs.next()) { // 假设表中有ID和Name列，根据实际情况调整 int id = rs.getInt("id"); String name = rs.getString("Name"); String remark = rs.getString("Remark"); System.out.println("ID: " + id + ", Name: " + name + ", Remark: " + remark); } } } } }