Java 留言板教學（六）：Service 接口的 JDBC 實現

本篇落地Service 層業務邏輯，採用介面 + JDBC 實現類模式，實現留言板核心 CRUD：發文、回覆、分頁查詢、詳情、刪除、回覆統計。代碼採用 PreparedStatement 防注入，try-with-resources 自動關閉資源，異常轉為非受檢異常上拋。

Service 介面定義

public interface MessageService {
    // 發布主題貼
    void postTopic(Integer userId, String title, String content);
    // 回覆貼文
    void reply(Integer parentId, Integer userId, String title, String content);
    // 分頁查詢主題列表
    PageBean<Message> findTopics(int pageNo, int pageSize);
    // 查詢單條貼文詳情（含遞歸回覆樹）
    Message findById(Integer id);
    // 軟刪除
    void deleteById(Integer id);
    // 統計某主題的直接回覆數
    int countDirectReplies(Integer topicId);
}

JDBC 實現類核心代碼

public class MessageServiceJdbcImpl implements MessageService {
    private final DataSource dataSource; // 注入連接池

    @Override
    public void postTopic(Integer userId, String title, String content) {
        String sql = "INSERT INTO message_board (parent_id, user_id, title, content, post_time, status) VALUES (0, ?, ?, ?, NOW(), 1)";
        executeUpdate(sql, userId, title, content);
    }

    @Override
    public void reply(Integer parentId, Integer userId, String title, String content) {
        String sql = "INSERT INTO message_board (parent_id, user_id, title, content, post_time, status) VALUES (?, ?, ?, ?, NOW(), 1)";
        executeUpdate(sql, parentId, userId, title, content);
    }

    @Override
    public PageBean<Message> findTopics(int pageNo, int pageSize) {
        String countSql = "SELECT COUNT(*) FROM message_board WHERE parent_id = 0 AND status = 1";
        String dataSql = "SELECT * FROM message_board WHERE parent_id = 0 AND status = 1 ORDER BY post_time DESC LIMIT ?, ?";
        long total = queryCount(countSql);
        List<Message> list = queryList(dataSql, (pageNo-1)*pageSize, pageSize);
        // 組裝回覆數
        for (Message m : list) m.setReplyCount(countDirectReplies(m.getId()));
        return new PageBean<>(pageNo, pageSize, total, list);
    }

    @Override
    public Message findById(Integer id) {
        String sql = "SELECT * FROM message_board WHERE id = ?";
        Message msg = querySingle(sql, id);
        if (msg != null) {
            msg.setUser(userService.findById(msg.getUserId()));
            msg.setReplies(buildReplyTree(msg.getId())); // 遞歸構建回覆樹
        }
        return msg;
    }

    @Override
    public void deleteById(Integer id) {
        String sql = "UPDATE message_board SET status = 0 WHERE id = ?";
        executeUpdate(sql, id);
    }

    @Override
    public int countDirectReplies(Integer topicId) {
        String sql = "SELECT COUNT(*) FROM message_board WHERE parent_id = ? AND status = 1";
        return queryCount(sql, topicId);
    }

    // 通用 JDBC 模板方法
    private void executeUpdate(String sql, Object... params) {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            ps.executeUpdate();
        } catch (SQLException e) { throw new RuntimeException(e); }
    }
    private long queryCount(String sql, Object... params) {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? rs.getLong(1) : 0;
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
    }
    private List<Message> queryList(String sql, Object... params) { /* 映射 ResultSet -> List<Message> */ }
    private Message querySingle(String sql, Object... params) { /* 映射 ResultSet -> Message */ }
    private List<Message> buildReplyTree(Integer parentId) { /* 遞歸查詢子回覆 */ }
    private void setParams(PreparedStatement ps, Object[] params) throws SQLException {
        for (int i = 0; i < params.length; i++) ps.setObject(i+1, params[i]);
    }
}

設計亮點

1. 介面隔離實現：Controller 僅依賴 MessageService，未來可無縫切換 MyBatis、Hibernate、JPA 實現。
2. 參數化 SQL：全程 PreparedStatement，徹底防 SQL 注入。
3. 資源自動管理：try-with-resources 確保 Connection/Statement/ResultSet 正確關閉，無洩漏。
4. 異常統一包裝：捕獲 SQLException 拋出 RuntimeException，調用方無需處理受檢異常。
5. 分頁參數化：LIMIT ?, ? 交給資料庫執行，記憶體友好。

回覆樹構建遞歸邏輯

private List<Message> buildReplyTree(Integer parentId) {
    String sql = "SELECT * FROM message_board WHERE parent_id = ? AND status = 1 ORDER BY post_time";
    List<Message> children = queryList(sql, parentId);
    for (Message c : children) {
        c.setUser(userService.findById(c.getUserId()));
        c.setReplies(buildReplyTree(c.getId())); // 遞歸
    }
    return children;
}

下一篇將定義 MessageService 介面完整簽名，並介紹如何在 Servlet/Controller 中注入使用。