如何不依赖Spring、Hibernate，用纯Java基于JDBC实现自定义Crud Repository

你不需要继承任何第三方提供的Repository接口，整套实现完全基于JDK原生API和JDBC完成，核心逻辑是通过反射解析实体与数据库表的映射关系，自动生成CRUD SQL并执行，最终实现你定义的MyOwnRepository接口的所有功能。

核心实现步骤 #

• 自定义映射注解：用来标注实体对应的表名、主键字段、普通字段名，解决实体和数据库表的映射问题
• 封装JDBC公共工具类：统一管理数据库连接、资源释放、SQL执行、事务控制的公共逻辑，减少重复代码
• 实现通用抽象Repository类：实现你定义的MyOwnRepository<T, ID>接口，通过反射解析泛型T的实际类型、实体的映射配置，自动生成增删改查SQL，调用JDBC工具类执行并完成结果和实体的映射
• 业务Repository复用：具体业务的Repository只需要继承这个通用抽象类，传入对应的实体和主键类型，即可直接使用所有基础CRUD能力

具体代码实现 #

1. 自定义映射注解 #

// 标注表名
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Table {
    String value();
}

// 标注主键字段
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Id {
}

// 标注字段名
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {
    String value();
}

2. JDBC工具类封装 #

public class JdbcUtils {
    private static final String URL = "jdbc:mysql://localhost:3306/你的库名?useUnicode=true&characterEncoding=utf8";
    private static final String USERNAME = "数据库账号";
    private static final String PASSWORD = "数据库密码";

    // 获取数据库连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    // 释放资源
    public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (pstmt != null) pstmt.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }

    // 执行更新类SQL（增删改）
    public static int executeUpdate(String sql, Object... params) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            return pstmt.executeUpdate();
        } finally {
            close(conn, pstmt, null);
        }
    }

    // 执行查询类SQL，返回映射后的实体列表
    public static <T> List<T> executeQuery(String sql, RowMapper<T> rowMapper, Object... params) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
            List<T> list = new ArrayList<>();
            int rowNum = 0;
            while (rs.next()) {
                list.add(rowMapper.mapRow(rs, rowNum++));
            }
            return list;
        } finally {
            close(conn, pstmt, rs);
        }
    }

    // 结果映射接口
    public interface RowMapper<T> {
        T mapRow(ResultSet rs, int rowNum) throws SQLException;
    }
}

3. 通用抽象Repository实现 #

public abstract class AbstractJdbcRepository<T, ID> implements MyOwnRepository<T, ID> {
    private final Class<T> entityClass;
    private String tableName;
    private Field idField;
    private List<Field> allFields;
    private List<String> columnNames;

    @SuppressWarnings("unchecked")
    public AbstractJdbcRepository() {
        // 解析泛型T的实际类型
        ParameterizedType pt = (ParameterizedType) this.getClass().getGenericSuperclass();
        this.entityClass = (Class<T>) pt.getActualTypeArguments()[0];
        // 解析实体的映射信息
        parseEntityMapping();
    }

    // 解析实体的表名、主键、字段信息
    private void parseEntityMapping() {
        // 解析表名
        if (entityClass.isAnnotationPresent(Table.class)) {
            this.tableName = entityClass.getAnnotation(Table.class).value();
        } else {
            this.tableName = entityClass.getSimpleName().toLowerCase();
        }
        // 解析字段和主键
        allFields = Arrays.asList(entityClass.getDeclaredFields());
        columnNames = new ArrayList<>();
        for (Field field : allFields) {
            field.setAccessible(true);
            if (field.isAnnotationPresent(Id.class)) {
                idField = field;
            }
            if (field.isAnnotationPresent(Column.class)) {
                columnNames.add(field.getAnnotation(Column.class).value());
            } else {
                columnNames.add(field.getName());
            }
        }
    }

    @Override
    public <S extends T> S save(S entity) {
        try {
            Object idValue = idField.get(entity);
            // 主键为空执行新增，不为空执行更新
            if (idValue == null) {
                return insert(entity);
            } else {
                return update(entity);
            }
        } catch (Exception e) {
            throw new RuntimeException("保存实体失败", e);
        }
    }

    private <S extends T> S insert(S entity) throws Exception {
        // 生成insert SQL
        String columns = String.join(", ", columnNames);
        String placeholders = String.join(", ", Collections.nCopies(columnNames.size(), "?"));
        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns, placeholders);
        // 组装参数
        Object[] params = new Object[allFields.size()];
        for (int i = 0; i < allFields.size(); i++) {
            params[i] = allFields.get(i).get(entity);
        }
        JdbcUtils.executeUpdate(sql, params);
        return entity;
    }

    private <S extends T> S update(S entity) throws Exception {
        // 生成update SQL
        List<String> setClauses = new ArrayList<>();
        for (String column : columnNames) {
            setClauses.add(column + " = ?");
        }
        String idColumn = columnNames.get(allFields.indexOf(idField));
        String sql = String.format("UPDATE %s SET %s WHERE %s = ?", tableName, String.join(", ", setClauses), idColumn);
        // 组装参数
        Object[] params = new Object[allFields.size() + 1];
        for (int i = 0; i < allFields.size(); i++) {
            params[i] = allFields.get(i).get(entity);
        }
        params[allFields.size()] = idField.get(entity);
        JdbcUtils.executeUpdate(sql, params);
        return entity;
    }

    @Override
    public Optional<T> findById(ID id) {
        try {
            String idColumn = columnNames.get(allFields.indexOf(idField));
            String sql = String.format("SELECT * FROM %s WHERE %s = ?", tableName, idColumn);
            List<T> list = JdbcUtils.executeQuery(sql, this::mapRow, id);
            return list.isEmpty() ? Optional.empty() : Optional.of(list.get(0));
        } catch (Exception e) {
            throw new RuntimeException("查询实体失败", e);
        }
    }

    @Override
    public boolean existsById(ID id) {
        return findById(id).isPresent();
    }

    @Override
    public Iterable<T> findAll() {
        try {
            String sql = String.format("SELECT * FROM %s", tableName);
            return JdbcUtils.executeQuery(sql, this::mapRow);
        } catch (Exception e) {
            throw new RuntimeException("查询所有实体失败", e);
        }
    }

    @Override
    public long count() {
        try {
            String sql = String.format("SELECT COUNT(*) FROM %s", tableName);
            return JdbcUtils.executeQuery(sql, (rs, rowNum) -> rs.getLong(1)).get(0);
        } catch (Exception e) {
            throw new RuntimeException("统计实体数量失败", e);
        }
    }

    @Override
    public void deleteById(ID id) {
        try {
            String idColumn = columnNames.get(allFields.indexOf(idField));
            String sql = String.format("DELETE FROM %s WHERE %s = ?", tableName, idColumn);
            JdbcUtils.executeUpdate(sql, id);
        } catch (Exception e) {
            throw new RuntimeException("删除实体失败", e);
        }
    }

    // 剩余的saveAll、findAllById、deleteAll等批量方法逻辑类似，都是批量组装参数执行SQL即可，可自行扩展
    // 结果集映射为实体
    private T mapRow(ResultSet rs, int rowNum) throws SQLException {
        try {
            T entity = entityClass.newInstance();
            for (int i = 0; i < allFields.size(); i++) {
                Field field = allFields.get(i);
                String columnName = columnNames.get(i);
                Object value = rs.getObject(columnName);
                field.set(entity, value);
            }
            return entity;
        } catch (Exception e) {
            throw new SQLException("结果映射失败", e);
        }
    }
}

4. 使用示例 #

首先定义实体类：

@Table("user")
public class User {
    @Id
    @Column("id")
    private Long id;
    @Column("username")
    private String username;
    @Column("age")
    private Integer age;
    // 省略getter、setter、构造方法
}

然后定义业务Repository：

public class UserRepository extends AbstractJdbcRepository<User, Long> {
    // 不需要写任何基础CRUD代码，直接继承就拥有所有对应能力
    // 复杂查询可以自己加方法，写自定义SQL调用JdbcUtils执行即可
}

调用示例：

public class Main {
    public static void main(String[] args) {
        UserRepository userRepository = new UserRepository();
        // 新增
        User user = new User();
        user.setUsername("张三");
        user.setAge(20);
        userRepository.save(user);
        // 查询
        User findUser = userRepository.findById(1L).orElse(null);
        // 更新
        findUser.setAge(21);
        userRepository.save(findUser);
        // 删除
        userRepository.deleteById(1L);
    }
}

说明 #

• 基础单表CRUD不需要手动编写SQL，通用抽象类会自动生成对应SQL执行
• 复杂的多表关联查询、自定义条件查询需要你手动编写SQL，调用JdbcUtils的方法执行即可
• 可以按需扩展事务控制、主键自增回写、缓存、分页等能力，完全自主可控

内容的提问来源于stack exchange，提问作者Mary