您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

JDBC 輕量級封裝：從配置文件到通用 CRUD 的最佳實踐

編輯：Java 相關知識

設計目標

構建一個零依賴、易理解、可直接用於中小項目或教學演示的 JDBC 封裝層，核心功能包括：

屬性文件配置數據源
連接獲取與釋放自動化
通用增刪改查模板方法
PreparedStatement 防 SQL 注入
資源關閉保證 (try-with-resources)

項目結構

src/
├── db.properties          # 數據庫配置
├── util/
│   └── JdbcUtil.java      # 核心工具類
├── dao/
│   ├── BaseDao.java       # 通用 DAO 基類
│   └── UserDao.java       # 業務 DAO 示例
├── entity/
│   └── User.java          # 實體類
└── test/
    └── JdbcDemo.java      # 測試入口

核心代碼實現

1. db.properties - 外部化配置

# MySQL 8.0+ 配置示例
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo_db?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
username=root
password=your_password
# 連接池基礎設置 (可選，此處演示基本用法)
initialSize=5
maxActive=20

2. JdbcUtil.java - 核心工具類

package util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {
    private static final Properties PROPS = new Properties();
    private static final ThreadLocal CONN_THREAD_LOCAL = new ThreadLocal<>();

    // 靜態塊加載配置、註冊驅動
    static {
        try (InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties")) {
            if (is == null) throw new RuntimeException("找不到 db.properties 配置文件");
            PROPS.load(is);
            Class.forName(PROPS.getProperty("driver"));
        } catch (IOException | ClassNotFoundException e) {
            throw new ExceptionInInitializerError("JDBC 初始化失敗: " + e.getMessage());
        }
    }

    /** 獲取數據庫連接 (每線程一個，支持同線程事務) */
    public static Connection getConnection() throws SQLException {
        Connection conn = CONN_THREAD_LOCAL.get();
        if (conn == null || conn.isClosed()) {
            conn = DriverManager.getConnection(
                PROPS.getProperty("url"),
                PROPS.getProperty("username"),
                PROPS.getProperty("password")
            );
            CONN_THREAD_LOCAL.set(conn);
        }
        return conn;
    }

    /** 釋放當前線程綁定的連接 */
    public static void releaseConnection() {
        Connection conn = CONN_THREAD_LOCAL.get();
        if (conn != null) {
            try { conn.close(); } catch (SQLException ignore) {}
            CONN_THREAD_LOCAL.remove();
        }
    }

    /** 通用更新操作 (INSERT/UPDATE/DELETE) */
    public static int update(String sql, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParameters(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("執行更新失敗: " + sql, e);
        }
    }

    /** 通用單行查詢，自動映射到實體 */
    public static <T> T queryForObject(String sql, RowMapper<T> mapper, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = executeQuery(ps, params)) {
            if (rs.next()) {
                return mapper.mapRow(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new RuntimeException("查詢單行失敗: " + sql, e);
        }
    }

    /** 通用列表查詢 */
    public static <T> List<T> queryForList(String sql, RowMapper<T> mapper, Object... params) {
        List<T> list = new ArrayList<>();
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = executeQuery(ps, params)) {
            while (rs.next()) {
                list.add(mapper.mapRow(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("查詢列表失敗: " + sql, e);
        }
        return list;
    }

    /** 通用標量查詢 (count、max 等) */
    public static <T> T queryForScalar(String sql, Class<T> type, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = executeQuery(ps, params)) {
            if (rs.next()) {
                return type.cast(rs.getObject(1));
            }
            return null;
        } catch (SQLException e) {
            throw new RuntimeException("標量查詢失敗: " + sql, e);
        }
    }

    // --- 私有輔助方法 ---
    private static ResultSet executeQuery(PreparedStatement ps, Object... params) throws SQLException {
        setParameters(ps, params);
        return ps.executeQuery();
    }

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

    /** 行映射器函數式接口 */
    @FunctionalInterface
    public interface RowMapper<T> {
        T mapRow(ResultSet rs) throws SQLException;
    }
}

3. 實體類與 RowMapper 實現

package entity;

public class User {
    private Long id;
    private String username;
    private String email;
    private String password; // 實際項目不應直接存明文
    private java.time.LocalDateTime createTime;
    
    // 構造器、getter/setter、toString 省略
}

// UserRowMapper.java
package dao;

import entity.User;
import util.JdbcUtil;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserRowMapper implements JdbcUtil.RowMapper<User> {
    @Override
    public User mapRow(ResultSet rs) throws SQLException {
        User u = new User();
        u.setId(rs.getLong("id"));
        u.setUsername(rs.getString("username"));
        u.setEmail(rs.getString("email"));
        u.setPassword(rs.getString("password"));
        u.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
        return u;
    }
}

4. BaseDao - 通用 DAO 基類

package dao;

import util.JdbcUtil;
import java.util.List;

public abstract class BaseDao<T> {
    protected final JdbcUtil.RowMapper<T> rowMapper;

    protected BaseDao(JdbcUtil.RowMapper<T> rowMapper) {
        this.rowMapper = rowMapper;
    }

    public int insert(String sql, Object... params) {
        return JdbcUtil.update(sql, params);
    }

    public int update(String sql, Object... params) {
        return JdbcUtil.update(sql, params);
    }

    public int delete(String sql, Object... params) {
        return JdbcUtil.update(sql, params);
    }

    public T selectOne(String sql, Object... params) {
        return JdbcUtil.queryForObject(sql, rowMapper, params);
    }

    public List<T> selectList(String sql, Object... params) {
        return JdbcUtil.queryForList(sql, rowMapper, params);
    }

    public Long count(String sql, Object... params) {
        return JdbcUtil.queryForScalar(sql, Long.class, params);
    }
}

5. UserDao - 業務 DAO 實現

package dao;

import entity.User;
import java.util.List;

public class UserDao extends BaseDao<User> {
    public UserDao() {
        super(new UserRowMapper());
    }

    public int save(User user) {
        String sql = "INSERT INTO tb_user (username, email, password) VALUES (?, ?, ?)";
        return insert(sql, user.getUsername(), user.getEmail(), user.getPassword());
    }

    public int updateEmail(Long id, String newEmail) {
        return update("UPDATE tb_user SET email = ? WHERE id = ?", newEmail, id);
    }

    public int deleteById(Long id) {
        return delete("DELETE FROM tb_user WHERE id = ?", id);
    }

    public User findById(Long id) {
        return selectOne("SELECT * FROM tb_user WHERE id = ?", id);
    }

    public User findByUsername(String username) {
        return selectOne("SELECT * FROM tb_user WHERE username = ?", username);
    }

    public List<User> findAll() {
        return selectList("SELECT * FROM tb_user ORDER BY create_time DESC");
    }

    public long countAll() {
        return count("SELECT COUNT(*) FROM tb_user");
    }
}

6. 事務管理輔助 (可選)

public class TransactionTemplate {
    public static void execute(TransactionCallback callback) {
        Connection conn = null;
        try {
            conn = JdbcUtil.getConnection();
            conn.setAutoCommit(false);
            callback.doInTransaction(conn);
            conn.commit();
        } catch (Exception e) {
            if (conn != null) try { conn.rollback(); } catch (SQLException ignore) {}
            throw new RuntimeException("事務執行失敗", e);
        } finally {
            JdbcUtil.releaseConnection();
        }
    }

    @FunctionalInterface
    public interface TransactionCallback {
        void doInTransaction(Connection conn) throws SQLException;
    }
}

// 使用示例
TransactionTemplate.execute(conn -> {
    try (PreparedStatement ps1 = conn.prepareStatement("UPDATE account SET balance = balance - ? WHERE id = ?");
         PreparedStatement ps2 = conn.prepareStatement("UPDATE account SET balance = balance + ? WHERE id = ?")) {
        ps1.setObject(1, 100); ps1.setObject(2, 1);
        ps2.setObject(1, 100); ps2.setObject(2, 2);
        ps1.executeUpdate();
        ps2.executeUpdate();
    }
});

7. 測試驗證

package test;

import dao.UserDao;
import entity.User;
import java.time.LocalDateTime;
import java.util.List;

public class JdbcDemo {
    public static void main(String[] args) {
        UserDao dao = new UserDao();

        // 1. 插入
        User u = new User();
        u.setUsername("test_" + System.currentTimeMillis());
        u.setEmail("test@example.com");
        u.setPassword("encoded_pwd");
        u.setCreateTime(LocalDateTime.now());
        int rows = dao.save(u);
        System.out.println("插入行數: " + rows);

        // 2. 查詢
        User found = dao.findByUsername(u.getUsername());
        System.out.println("查詢結果: " + found);

        // 3. 列表
        List list = dao.findAll();
        System.out.println("總用戶數: " + list.size());
        list.forEach(System.out::println);

        // 4. 更新
        dao.updateEmail(found.getId(), "new_email@example.com");
        System.out.println("更新後: " + dao.findById(found.getId()));

        // 5. 刪除 (測試完清理)
        // dao.deleteById(found.getId());
    }
}

進階優化方向

維度	當前實現	生產級建議
連接管理	ThreadLocal + DriverManager	引入 HikariCP/Druid 連接池
SQL 管理	硬編碼在 DAO 中	外部化到 XML/MyBatis Mapper/注解
映射靈活性	手寫 RowMapper	ResultSetMapper 自動映射/反射/MapStruct
分頁支持	無	封裝 PageHelper/物理分頁方言
異常體系	RuntimeException 包裝	自定義分層異常 (DataAccessException 等)
監控指標	無	執行時間、慢 SQL、連接池狀態暴露

總結

這個輕量級封裝雖簡單，但涵蓋了 JDBC 開發的核心模式：配置外部化、資源自動管理、參數化查詢防注入、函數式行映射、DAO 模板化。理解並能手寫這套機制，是掌握 MyBatis、JdbcTemplate、Hibernate 等 ORM 框架原理的基石。中小項目可直接使用並逐步擴展；大型項目建議直接上成熟框架，避免重複造輪子。