實戰項目：基于控制臺與數據庫的圖書管理系統開發指南

一、項目概述與設計思路

1.1 為什么選擇圖書管理系統

圖書管理系統是學習編程的經典項目，它涵蓋了：

控制臺交互：學習用戶輸入輸出處理
數據庫操作：掌握CRUD核心功能
業務邏輯：理解實際應用場景
系統架構：實踐分層設計思想

1.2 系統功能設計

核心功能模塊：

1. 圖書管理- 添加新書- 刪除圖書- 修改圖書信息- 查詢圖書（按ID/書名/作者）2. 借閱管理- 圖書借出- 圖書歸還- 借閱記錄查詢3. 用戶管理- 讀者注冊- 讀者信息修改- 讀者注銷4. 統計報表- 圖書庫存統計- 借閱排行榜- 逾期未還清單

1.3 技術選型

技術組件	選擇方案	備注
開發語言	Java 8+	兼顧教學與實用
數據庫	MySQL 8.0	免費開源，應用廣泛
數據庫連接	JDBC	學習原生數據庫操作
控制臺框架	-	純Java實現
單元測試	JUnit 5	保證代碼質量
日志系統	SLF4J + Logback	記錄系統運行狀態

二、數據庫設計與實現

2.1 數據庫表結構設計

ER圖關鍵實體：

圖書(Book) ---< 借閱記錄(BorrowRecord) >--- 讀者(Reader)

建表SQL：

-- 圖書表
CREATE TABLE books (book_id INT AUTO_INCREMENT PRIMARY KEY,isbn VARCHAR(20) NOT NULL UNIQUE,title VARCHAR(100) NOT NULL,author VARCHAR(50) NOT NULL,publisher VARCHAR(50),publish_date DATE,price DECIMAL(10,2),stock INT DEFAULT 1 COMMENT '庫存數量',create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 讀者表
CREATE TABLE readers (reader_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,gender CHAR(1) CHECK (gender IN ('M', 'F')),phone VARCHAR(20),email VARCHAR(100),register_date DATE DEFAULT (CURRENT_DATE),membership_level INT DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 借閱記錄表
CREATE TABLE borrow_records (record_id INT AUTO_INCREMENT PRIMARY KEY,book_id INT NOT NULL,reader_id INT NOT NULL,borrow_date DATETIME DEFAULT CURRENT_TIMESTAMP,due_date DATETIME GENERATED ALWAYS AS (borrow_date + INTERVAL 30 DAY) STORED,return_date DATETIME,status TINYINT DEFAULT 1 COMMENT '1-借出 2-已還 3-逾期',FOREIGN KEY (book_id) REFERENCES books(book_id),FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 索引優化設計

-- 提高查詢性能的索引
CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_books_author ON books(author);
CREATE INDEX idx_borrow_records_status ON borrow_records(status);
CREATE INDEX idx_borrow_records_due_date ON borrow_records(due_date);

2.3 初始化測試數據

-- 插入示例圖書
INSERT INTO books (isbn, title, author, publisher, price, stock)
VALUES 
('9787111636667', 'Java核心技術 卷I', 'Cay S. Horstmann', '機械工業出版社', 119.00, 5),
('9787115523660', 'Effective Java', 'Joshua Bloch', '機械工業出版社', 129.00, 3),
('9787302515421', 'Python編程：從入門到實踐', 'Eric Matthes', '人民郵電出版社', 89.00, 7);-- 插入示例讀者
INSERT INTO readers (name, gender, phone, email)
VALUES 
('張三', 'M', '13800138001', 'zhangsan@example.com'),
('李四', 'F', '13900139001', 'lisi@example.com');

三、Java核心代碼實現

3.1 數據庫連接層

DBUtil.java?- 數據庫工具類：

public class DBUtil {private static final String URL = "jdbc:mysql://localhost:3306/library_db?useSSL=false&serverTimezone=UTC";private static final String USER = "root";private static final String PASSWORD = "123456";static {try {Class.forName("com.mysql.cj.jdbc.Driver");} catch (ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException {return DriverManager.getConnection(URL, USER, PASSWORD);}public static void close(Connection conn, Statement stmt, ResultSet rs) {try {if (rs != null) rs.close();if (stmt != null) stmt.close();if (conn != null) conn.close();} catch (SQLException e) {e.printStackTrace();}}
}

3.2 實體類設計

Book.java?- 圖書實體：

public class Book {private Integer bookId;private String isbn;private String title;private String author;private String publisher;private Date publishDate;private BigDecimal price;private Integer stock;// 構造方法、getter和setter省略// 建議使用Lombok @Data注解簡化代碼
}

3.3 數據訪問層(DAO)

BookDAO.java?- 圖書數據訪問：

public class BookDAO {// 添加新書public boolean addBook(Book book) {String sql = "INSERT INTO books (isbn, title, author, publisher, publish_date, price, stock) " +"VALUES (?, ?, ?, ?, ?, ?, ?)";try (Connection conn = DBUtil.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql)) {pstmt.setString(1, book.getIsbn());pstmt.setString(2, book.getTitle());pstmt.setString(3, book.getAuthor());pstmt.setString(4, book.getPublisher());pstmt.setDate(5, new java.sql.Date(book.getPublishDate().getTime()));pstmt.setBigDecimal(6, book.getPrice());pstmt.setInt(7, book.getStock());return pstmt.executeUpdate() > 0;} catch (SQLException e) {e.printStackTrace();return false;}}// 按ID查詢圖書public Book getBookById(int bookId) {String sql = "SELECT * FROM books WHERE book_id = ?";Book book = null;try (Connection conn = DBUtil.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql)) {pstmt.setInt(1, bookId);try (ResultSet rs = pstmt.executeQuery()) {if (rs.next()) {book = new Book();book.setBookId(rs.getInt("book_id"));book.setIsbn(rs.getString("isbn"));// 設置其他屬性...}}} catch (SQLException e) {e.printStackTrace();}return book;}// 其他CRUD方法...
}

3.4 業務邏輯層(Service)

LibraryService.java?- 核心業務邏輯：

public class LibraryService {private BookDAO bookDAO = new BookDAO();private ReaderDAO readerDAO = new ReaderDAO();private BorrowRecordDAO recordDAO = new BorrowRecordDAO();// 借書業務方法public boolean borrowBook(int bookId, int readerId) {// 檢查圖書庫存Book book = bookDAO.getBookById(bookId);if (book == null || book.getStock() <= 0) {System.out.println("圖書不存在或庫存不足");return false;}// 檢查讀者是否存在Reader reader = readerDAO.getReaderById(readerId);if (reader == null) {System.out.println("讀者不存在");return false;}// 檢查是否已借過同一本書未還if (recordDAO.hasUnreturnedRecord(bookId, readerId)) {System.out.println("您已借閱該書且未歸還");return false;}// 開啟事務Connection conn = null;try {conn = DBUtil.getConnection();conn.setAutoCommit(false);// 1. 減少庫存bookDAO.updateStock(conn, bookId, -1);// 2. 創建借閱記錄BorrowRecord record = new BorrowRecord();record.setBookId(bookId);record.setReaderId(readerId);recordDAO.addRecord(conn, record);conn.commit();return true;} catch (SQLException e) {if (conn != null) {try {conn.rollback();} catch (SQLException ex) {ex.printStackTrace();}}e.printStackTrace();return false;} finally {if (conn != null) {try {conn.setAutoCommit(true);conn.close();} catch (SQLException e) {e.printStackTrace();}}}}// 其他業務方法...
}

3.5 控制臺界面實現

ConsoleUI.java?- 用戶交互界面：

public class ConsoleUI {private Scanner scanner = new Scanner(System.in);private LibraryService libraryService = new LibraryService();public void start() {while (true) {showMainMenu();int choice = getIntInput("請選擇操作：");switch (choice) {case 1:manageBooks();break;case 2:manageReaders();break;case 3:manageBorrowing();break;case 4:generateReports();break;case 0:System.out.println("感謝使用圖書管理系統，再見！");return;default:System.out.println("無效選擇，請重新輸入");}}}private void showMainMenu() {System.out.println("\n===== 圖書管理系統 =====");System.out.println("1. 圖書管理");System.out.println("2. 讀者管理");System.out.println("3. 借閱管理");System.out.println("4. 統計報表");System.out.println("0. 退出系統");}private void manageBooks() {while (true) {System.out.println("\n===== 圖書管理 =====");System.out.println("1. 添加新書");System.out.println("2. 查詢圖書");System.out.println("3. 修改圖書信息");System.out.println("4. 刪除圖書");System.out.println("0. 返回上級菜單");int choice = getIntInput("請選擇操作：");switch (choice) {case 1:addNewBook();break;case 2:searchBooks();break;// 其他case...case 0:return;default:System.out.println("無效選擇");}}}private void addNewBook() {System.out.println("\n--- 添加新書 ---");String isbn = getStringInput("ISBN：");String title = getStringInput("書名：");String author = getStringInput("作者：");Book book = new Book();book.setIsbn(isbn);book.setTitle(title);book.setAuthor(author);// 設置其他屬性...if (libraryService.addBook(book)) {System.out.println("添加圖書成功！");} else {System.out.println("添加圖書失敗");}}// 其他方法...private int getIntInput(String prompt) {while (true) {try {System.out.print(prompt);return Integer.parseInt(scanner.nextLine());} catch (NumberFormatException e) {System.out.println("請輸入有效數字！");}}}private String getStringInput(String prompt) {System.out.print(prompt);return scanner.nextLine();}
}

四、項目擴展與優化

4.1 功能擴展建議

預約功能：
- 允許讀者預約已被借出的圖書
- 圖書歸還時通知預約讀者
逾期罰款：
- 計算逾期天數
- 按規則自動計算罰款金額
圖書推薦：
- 基于借閱歷史的簡單推薦
- 熱門圖書推薦

4.2 代碼優化方向

引入連接池：

// 使用HikariCP替代原生JDBC連接
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/library_db");
config.setUsername("root");
config.setPassword("123456");
HikariDataSource dataSource = new HikariDataSource(config);

使用DAO接口：

public interface BookDAO {boolean addBook(Book book);Book getBookById(int bookId);// 其他方法...
}public class BookDAOImpl implements BookDAO {// 實現方法...
}

日志記錄：

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;public class BookDAOImpl implements BookDAO {private static final Logger logger = LoggerFactory.getLogger(BookDAOImpl.class);public boolean addBook(Book book) {logger.debug("嘗試添加圖書：{}", book.getTitle());// 實現代碼...}
}

4.3 異常處理改進

自定義異常類：

public class LibraryException extends Exception {public LibraryException(String message) {super(message);}public LibraryException(String message, Throwable cause) {super(message, cause);}
}// 使用示例
public void borrowBook(int bookId, int readerId) throws LibraryException {try {// 業務邏輯...} catch (SQLException e) {throw new LibraryException("借書操作失敗", e);}
}

五、項目部署與測試

5.1 單元測試示例

BookDAOTest.java：

public class BookDAOTest {private BookDAO bookDAO = new BookDAOImpl();@Testpublic void testAddAndGetBook() {Book book = new Book();book.setIsbn("978-3-16-148410-0");book.setTitle("測試圖書");book.setAuthor("測試作者");boolean added = bookDAO.addBook(book);assertTrue(added);Book retrieved = bookDAO.getBookByIsbn("978-3-16-148410-0");assertNotNull(retrieved);assertEquals("測試圖書", retrieved.getTitle());}// 其他測試方法...
}