您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

Java JDBC 連接 MySQL 實戰：新聞管理系統 CRUD 完整代碼演示

編輯：MySQL 綜合教學

Java JDBC 連接 MySQL 實戰：新聞管理系統 CRUD 完整代碼演示

本文提供一個基於 Java JDBC、MySQL、Console 交互的完整新聞管理系統示例，涵蓋資料庫建表、連接配置、增刪改查 (CRUD)、預處理防注入、資源關閉等工程化要點，適合初學者快速上手 Java 數據庫開發。

一、資料庫準備

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8mb4;
USE test;

CREATE TABLE IF NOT EXISTS news (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  remark VARCHAR(200),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二、Maven 依賴

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.33</version>  <!-- 兼容 MySQL 5.7/8.0 -->
</dependency>

三、工具類：連接獲取與資源關閉

package com.example.util;

import java.sql.*;

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASS = "root";

    static {
        try { Class.forName("com.mysql.cj.jdbc.Driver"); }
        catch (ClassNotFoundException e) { throw new ExceptionInInitializerError(e); }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASS);
    }

    public static void close(ResultSet rs, Statement st, Connection conn) {
        try { if (rs != null) rs.close(); } catch (SQLException ignore) {}
        try { if (st != null) st.close(); } catch (SQLException ignore) {}
        try { if (conn != null) conn.close(); } catch (SQLException ignore) {}
    }
}

四、DAO 層：CRUD 核心邏輯 (使用 PreparedStatement)

package com.example.dao;

import com.example.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NewsDAO {
    // 新增
    public boolean add(String title, String content, String remark) {
        String sql = "INSERT INTO news (title, content, remark) VALUES (?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, title);
            ps.setString(2, content);
            ps.setString(3, remark);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) { e.printStackTrace(); return false; }
    }

    // 查詢全部
    public List findAll() {
        String sql = "SELECT id, title, content, remark, created_at FROM news ORDER BY id DESC";
        List list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(mapRow(rs));
            }
        } catch (SQLException e) { e.printStackTrace(); }
        return list;
    }

    // 按標題查詢
    public News findByTitle(String title) {
        String sql = "SELECT id, title, content, remark, created_at FROM news WHERE title = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, title);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return mapRow(rs);
            }
        } catch (SQLException e) { e.printStackTrace(); }
        return null;
    }

    // 刪除
    public boolean deleteByTitle(String title) {
        String sql = "DELETE FROM news WHERE title = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, title);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) { e.printStackTrace(); return false; }
    }

    // 更新
    public boolean update(String oldTitle, String newTitle, String content, String remark) {
        String sql = "UPDATE news SET title = ?, content = ?, remark = ? WHERE title = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newTitle);
            ps.setString(2, content);
            ps.setString(3, remark);
            ps.setString(4, oldTitle);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) { e.printStackTrace(); return false; }
    }

    private News mapRow(ResultSet rs) throws SQLException {
        News n = new News();
        n.setId(rs.getInt("id"));
        n.setTitle(rs.getString("title"));
        n.setContent(rs.getString("content"));
        n.setRemark(rs.getString("remark"));
        n.setCreatedAt(rs.getTimestamp("created_at"));
        return n;
    }
}

五、實體類

package com.example.entity;

import java.sql.Timestamp;

public class News {
    private int id;
    private String title;
    private String content;
    private String remark;
    private Timestamp createdAt;
    // Getter/Setter/ToString 省略
}

六、主程序：控制台交互菜單

package com.example;

import com.example.dao.NewsDAO;
import com.example.entity.News;
import java.util.List;
import java.util.Scanner;

public class NewsApp {
    private static final NewsDAO dao = new NewsDAO();
    private static final Scanner sc = new Scanner(System.in);

    public static void main(String[] args) {
        while (true) {
            printMenu();
            int choice = readInt("請選擇 (0-5): ");
            switch (choice) {
                case 1: listAll(); break;
                case 2: addNews(); break;
                case 3: deleteNews(); break;
                case 4: queryByTitle(); break;
                case 5: updateNews(); break;
                case 0: System.out.println("退出系統"); return;
                default: System.out.println("無效選項");
            }
        }
    }

    private static void printMenu() {
        System.out.println("\n===== 新聞管理系統 =====");
        System.out.println("1. 查詢全部新聞");
        System.out.println("2. 添加新聞");
        System.out.println("3. 刪除新聞");
        System.out.println("4. 按標題查詢");
        System.out.println("5. 按標題修改");
        System.out.println("0. 退出");
    }

    private static void listAll() {
        List list = dao.findAll();
        if (list.isEmpty()) { System.out.println("暫無數據"); return; }
        System.out.printf("%-5s %-20s %-30s %-20s %s%n", "ID", "標題", "內容", "備註", "創建時間");
        for (News n : list) {
            System.out.printf("%-5d %-20s %-30s %-20s %s%n",
                n.getId(), n.getTitle(), n.getContent(), n.getRemark(), n.getCreatedAt());
        }
    }

    private static void addNews() {
        System.out.print("標題: "); String title = sc.nextLine();
        System.out.print("內容: "); String content = sc.nextLine();
        System.out.print("備註: "); String remark = sc.nextLine();
        System.out.println(dao.add(title, content, remark) ? "添加成功" : "添加失敗");
    }

    private static void deleteNews() {
        System.out.print("要刪除的標題: "); String title = sc.nextLine();
        System.out.println(dao.deleteByTitle(title) ? "刪除成功" : "刪除失敗/不存在");
    }

    private static void queryByTitle() {
        System.out.print("查詢標題: "); String title = sc.nextLine();
        News n = dao.findByTitle(title);
        if (n != null) System.out.println(n);
        else System.out.println("未找到");
    }

    private static void updateNews() {
        System.out.print("原標題: "); String oldTitle = sc.nextLine();
        News exist = dao.findByTitle(oldTitle);
        if (exist == null) { System.out.println("原標題不存在"); return; }
        System.out.print("新標題: "); String newTitle = sc.nextLine();
        System.out.print("新內容: "); String content = sc.nextLine();
        System.out.print("新備註: "); String remark = sc.nextLine();
        System.out.println(dao.update(oldTitle, newTitle, content, remark) ? "修改成功" : "修改失敗");
    }

    private static int readInt(String prompt) {
        System.out.print(prompt);
        while (!sc.hasNextInt()) { System.out.print("請輸入數字: "); sc.next(); }
        int v = sc.nextInt(); sc.nextLine(); return v;
    }
}

七、關鍵工程化要點總結

PreparedStatement 防 SQL 注入：所有參數用 ? 佔位符，setString 等綁定。
try-with-resources 自動關閉：JDK 7+ 語法確保 Connection/Statement/ResultSet 無洩露。
連接池生產必備：實際項目請替換為 HikariCP/Druid，配置 maximumPoolSize、idleTimeout、connectionTestQuery。
異常處理分層：DAO 拋出自定義異常，Service 捕獲轉換業務錯誤碼，Controller 統一返回 HTTP 狀態。
事務控制：多表寫操作在 Service 層開啟 conn.setAutoCommit(false)，統一提交/回滾。