您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 綜合教學

手把手打造輕量級 ORM 框架：DBUtils_DG 設計與實戰

編輯：Java 綜合教學

手把手打造輕量級 ORM 框架：DBUtils_DG 設計與實戰

ORM（Object-Relational Mapping）核心思想是用元數據描述對象與關係數據庫的映射，自動將對象持久化為數據庫記錄。主流框架（Hibernate、MyBatis）功能強大但複雜。本文演示從零構建一個極簡 ORM 工具類 DBUtils_DG，覆蓋查詢映射、CRUD 封裝、存儲過程、JSON 輸出等核心能力。

設計目標

零依賴（僅需 JDBC、Commons Logging、JSON-lib）
基於反射自動映射 ResultSet → POJO / List / Map / JSON
支持參數化 SQL 防注入
統一資源管理與異常處理
外部化配置數據庫連接信息

核心代碼：DBUtils_DG.java

package liuyanban.Utils;

import org.json.JSONArray;
import org.json.JSONObject;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

public abstract class DBUtils_DG {
    // ---- 靜態配置區 ----
    private static String driverClass, url, username, password;
    static {
        try (InputStream is = DBUtils_DG.class.getClassLoader().getResourceAsStream("db.properties")) {
            Properties props = new Properties();
            props.load(is);
            driverClass = props.getProperty("driver_class");
            url = props.getProperty("driver_url");
            username = props.getProperty("database_user");
            password = props.getProperty("database_password");
            Class.forName(driverClass);
        } catch (Exception e) {
            throw new ExceptionInInitializerError("DB 配置加載失敗: " + e.getMessage());
        }
    }

    // ---- 內部輔助方法 ----
    private static Connection getConn() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    private static void setParams(PreparedStatement ps, Object... params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }
    }

    private static void closeAll(ResultSet rs, Statement st, Connection conn) {
        closeQuietly(rs);
        closeQuietly(st);
        closeQuietly(conn);
    }
    private static void closeQuietly(AutoCloseable c) {
        if (c != null) try { c.close(); } catch (Exception ignored) {}
    }

    // ---- 核心執行模板 ----
    private static  T execute(ConnectionCallback callback) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConn();
            return callback.doInConnection(conn, ps, rs);
        } catch (SQLException e) {
            throw new RuntimeException("DB 操作異常", e);
        } finally {
            closeAll(rs, ps, conn);
        }
    }

    @FunctionalInterface
    private interface ConnectionCallback {
        T doInConnection(Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException;
    }

    // ---- 公共 API ----

    /** 執行 INSERT/UPDATE/DELETE，返回是否影響行數 > 0 */
    public static boolean executeUpdate(String sql, Object... params) {
        return execute(conn -> {
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                setParams(ps, params);
                return ps.executeUpdate() > 0;
            }
        });
    }

    /** 查詢單值（如 count(*), max(id)） */
    public static Object queryForObject(String sql, Object... params) {
        return execute(conn -> {
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                setParams(ps, params);
                try (ResultSet rs = ps.executeQuery()) {
                    return rs.next() ? rs.getObject(1) : null;
                }
            }
        });
    }

    /** 查詢單行映射為 POJO */
    public static  T queryForBean(Class clazz, String sql, Object... params) {
        return execute(conn -> {
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                setParams(ps, params);
                try (ResultSet rs = ps.executeQuery()) {
                    if (!rs.next()) return null;
                    return mapRow(rs, clazz);
                }
            }
        });
    }

    /** 查詢多行映射為 List */
    public static  List queryForList(Class clazz, String sql, Object... params) {
        return execute(conn -> {
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                setParams(ps, params);
                try (ResultSet rs = ps.executeQuery()) {
                    List list = new ArrayList<>();
                    while (rs.next()) list.add(mapRow(rs, clazz));
                    return list;
                }
            }
        });
    }

    /** 查詢單行映射為 JSONObject */
    public static JSONObject queryForJson(String sql, Object... params) {
        return execute(conn -> {
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                setParams(ps, params);
                try (ResultSet rs = ps.executeQuery()) {
                    if (!rs.next()) return null;
                    return mapRowToJson(rs);
                }
            }
        });
    }

    /** 查詢多行映射為 JSONArray */
    public static JSONArray queryForJsonArray(String sql, Object... params) {
        return execute(conn -> {
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                setParams(ps, params);
                try (ResultSet rs = ps.executeQuery()) {
                    JSONArray arr = new JSONArray();
                    while (rs.next()) arr.put(mapRowToJson(rs));
                    return arr;
                }
            }
        });
    }

    /** 執行存儲過程，支持 OUT 參數 */
    public static Object callProcedure(String sql, Object[] params, int outParamIndex, int sqlType) {
        return execute(conn -> {
            try (CallableStatement cs = conn.prepareCall(sql)) {
                if (params != null) {
                    for (int i = 0; i < params.length; i++) cs.setObject(i + 1, params[i]);
                }
                cs.registerOutParameter(outParamIndex, sqlType);
                cs.execute();
                return cs.getObject(outParamIndex);
            }
        });
    }

    // ---- 反射映射核心 ----
    private static  T mapRow(ResultSet rs, Class clazz) throws SQLException, ReflectiveOperationException {
        T bean = clazz.getDeclaredConstructor().newInstance();
        ResultSetMetaData meta = rs.getMetaData();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            String colName = meta.getColumnLabel(i);
            Object val = rs.getObject(i);
            try {
                Field field = clazz.getDeclaredField(colName);
                field.setAccessible(true);
                field.set(bean, val);
            } catch (NoSuchFieldException ignored) {
                // 忽略數據庫列與實體字段不匹配的情況
            }
        }
        return bean;
    }

    private static JSONObject mapRowToJson(ResultSet rs) throws SQLException {
        JSONObject json = new JSONObject();
        ResultSetMetaData meta = rs.getMetaData();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            json.put(meta.getColumnLabel(i), rs.getObject(i));
        }
        return json;
    }
}

配置文件 db.properties

driver_class=com.mysql.jdbc.Driver
driver_url=jdbc:mysql://localhost:3306/db_liuyanban?useSSL=false&serverTimezone=UTC
database_user=root
database_password=root

實戰：DAO 層調用示例

public class UserDaoImpl implements IUserDao {
    // 新增
    public boolean addUser(User user) {
        String sql = "INSERT INTO tb_user (loginId, pwd, name, jurisdiction, image) VALUES (?,?,?,?,?)";
        return DBUtils_DG.executeUpdate(sql, user.getLoginId(), user.getPwd(), user.getName(),
                user.getJurisdiction(), user.getImage());
    }

    // 修改
    public boolean updateUser(User user) {
        String sql = "UPDATE tb_user SET loginId=?, pwd=?, name=?, jurisdiction=?, image=? WHERE userId=?";
        return DBUtils_DG.executeUpdate(sql, user.getLoginId(), user.getPwd(), user.getName(),
                user.getJurisdiction(), user.getImage(), user.getUserId());
    }

    // 刪除
    public boolean deleteUser(int userId) {
        return DBUtils_DG.executeUpdate("DELETE FROM tb_user WHERE userId=?", userId);
    }

    // 查詢所有
    public List getAll() {
        return DBUtils_DG.queryForList(User.class, "SELECT userId, loginId, name, jurisdiction, image FROM tb_user");
    }

    // 按 ID 查詢單個
    public User getById(int userId) {
        return DBUtils_DG.queryForBean(User.class,
                "SELECT userId, loginId, name, jurisdiction, image FROM tb_user WHERE userId=? LIMIT 1", userId);
    }

    // 判斷用戶是否存在
    public boolean exists(String loginId) {
        Object obj = DBUtils_DG.queryForObject("SELECT loginId FROM tb_user WHERE loginId=? LIMIT 1", loginId);
        return obj != null && obj.toString().length() > 0;
    }
}