本文目录导读：
我来通过一个完整的图书管理系统案例，展示如何综合运用Java的CRUD（Create, Read, Update, Delete）操作。
项目结构
book-management-system/
├── src/
│ ├── model/ # 数据模型
│ ├── dao/ # 数据访问层
│ ├── service/ # 业务逻辑层
│ └── ui/ # 用户界面层
└── resources/
└── database.sql # 数据库脚本数据模型层 (Model)
// Book.java - 图书实体类
package model;
import java.util.Date;
public class Book {
private Long id;
private String isbn;
private String title;
private String author;
private String publisher;
private Date publishDate;
private Double price;
private Integer stock; // 库存数量
private String category; // 分类
private String status; // 状态：AVAILABLE/BORROWED/MAINTENANCE
// 构造函数
public Book() {}
public Book(String isbn, String title, String author, String publisher,
Date publishDate, Double price, Integer stock, String category) {
this.isbn = isbn;
this.title = title;
this.author = author;
this.publisher = publisher;
this.publishDate = publishDate;
this.price = price;
this.stock = stock;
this.category = category;
this.status = "AVAILABLE";
}
// Getter和Setter方法
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getIsbn() { return isbn; }
public void setIsbn(String isbn) { this.isbn = isbn; }
// ... 其他getter和setter方法
@Override
public String toString() {
return "Book{" +
"id=" + id +
", isbn='" + isbn + '\'' +
", title='" + title + '\'' +
", author='" + author + '\'' +
", price=" + price +
", stock=" + stock +
'}';
}
}
数据库工具类
// DBUtil.java
package util;
import java.sql.*;
public class DBUtil {
private static final String URL = "jdbc:mysql://localhost:3306/book_management";
private static final String USER = "root";
private static final String PASSWORD = "password";
// 获取数据库连接
public static Connection getConnection() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
throw new SQLException("数据库驱动加载失败", e);
}
}
// 关闭资源
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();
}
}
}
数据访问层 (DAO)
// BookDAO.java - 图书数据访问对象
package dao;
import model.Book;
import util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class BookDAO {
// CREATE - 添加图书
public boolean addBook(Book book) {
String sql = "INSERT INTO books (isbn, title, author, publisher, publish_date, " +
"price, stock, category, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
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.setDouble(6, book.getPrice());
pstmt.setInt(7, book.getStock());
pstmt.setString(8, book.getCategory());
pstmt.setString(9, book.getStatus());
int affectedRows = pstmt.executeUpdate();
if (affectedRows > 0) {
// 获取自增ID
ResultSet rs = pstmt.getGeneratedKeys();
if (rs.next()) {
book.setId(rs.getLong(1));
}
return true;
}
return false;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// READ - 查询所有图书
public List<Book> getAllBooks() {
List<Book> books = new ArrayList<>();
String sql = "SELECT * FROM books ORDER BY id DESC";
try (Connection conn = DBUtil.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
books.add(mapBook(rs));
}
} catch (SQLException e) {
e.printStackTrace();
}
return books;
}
// READ - 根据ID查询图书
public Book getBookById(Long id) {
String sql = "SELECT * FROM books WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setLong(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
return mapBook(rs);
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// READ - 根据关键字搜索图书
public List<Book> searchBooks(String keyword) {
List<Book> books = new ArrayList<>();
String sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
String searchPattern = "%" + keyword + "%";
pstmt.setString(1, searchPattern);
pstmt.setString(2, searchPattern);
pstmt.setString(3, searchPattern);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
books.add(mapBook(rs));
}
} catch (SQLException e) {
e.printStackTrace();
}
return books;
}
// UPDATE - 更新图书信息
public boolean updateBook(Book book) {
String sql = "UPDATE books SET isbn=?, title=?, author=?, publisher=?, " +
"publish_date=?, price=?, stock=?, category=?, status=? WHERE id=?";
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.setDouble(6, book.getPrice());
pstmt.setInt(7, book.getStock());
pstmt.setString(8, book.getCategory());
pstmt.setString(9, book.getStatus());
pstmt.setLong(10, book.getId());
return pstmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// DELETE - 删除图书
public boolean deleteBook(Long id) {
String sql = "DELETE FROM books WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setLong(1, id);
return pstmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 辅助方法：将ResultSet映射为Book对象
private Book mapBook(ResultSet rs) throws SQLException {
Book book = new Book();
book.setId(rs.getLong("id"));
book.setIsbn(rs.getString("isbn"));
book.setTitle(rs.getString("title"));
book.setAuthor(rs.getString("author"));
book.setPublisher(rs.getString("publisher"));
book.setPublishDate(rs.getDate("publish_date"));
book.setPrice(rs.getDouble("price"));
book.setStock(rs.getInt("stock"));
book.setCategory(rs.getString("category"));
book.setStatus(rs.getString("status"));
return book;
}
}
业务逻辑层 (Service)
// BookService.java
package service;
import dao.BookDAO;
import model.Book;
import java.util.List;
public class BookService {
private BookDAO bookDAO = new BookDAO();
// 添加图书（带业务验证）
public boolean addBook(Book book) {
// 验证ISBN是否重复
List<Book> existingBooks = bookDAO.searchBooks(book.getIsbn());
if (!existingBooks.isEmpty()) {
throw new RuntimeException("ISBN已存在");
}
// 验证必填字段
if (book.getTitle() == null || book.getTitle().trim().isEmpty()) {
throw new RuntimeException("书名不能为空");
}
if (book.getPrice() < 0) {
throw new RuntimeException("价格不能为负数");
}
return bookDAO.addBook(book);
}
// 借书操作（更新库存）
public boolean borrowBook(Long bookId) {
Book book = bookDAO.getBookById(bookId);
if (book == null) {
throw new RuntimeException("图书不存在");
}
if (book.getStock() <= 0) {
throw new RuntimeException("库存不足");
}
book.setStock(book.getStock() - 1);
return bookDAO.updateBook(book);
}
// 还书操作（增加库存）
public boolean returnBook(Long bookId) {
Book book = bookDAO.getBookById(bookId);
if (book == null) {
throw new RuntimeException("图书不存在");
}
book.setStock(book.getStock() + 1);
return bookDAO.updateBook(book);
}
// 其他业务方法...
public List<Book> getAllBooks() {
return bookDAO.getAllBooks();
}
public Book getBookById(Long id) {
return bookDAO.getBookById(id);
}
public boolean updateBook(Book book) {
return bookDAO.updateBook(book);
}
public boolean deleteBook(Long id) {
return bookDAO.deleteBook(id);
}
}
用户界面层 (UI) - 控制台版本
// MainUI.java
package ui;
import model.Book;
import service.BookService;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
public class MainUI {
private BookService bookService = new BookService();
private Scanner scanner = new Scanner(System.in);
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
public void start() {
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("5. 删除图书");
System.out.println("6. 借书");
System.out.println("7. 还书");
System.out.println("0. 退出");
System.out.print("请选择操作: ");
int choice = scanner.nextInt();
scanner.nextLine(); // 消耗换行符
switch (choice) {
case 1:
addBook();
break;
case 2:
viewAllBooks();
break;
case 3:
searchBooks();
break;
case 4:
updateBook();
break;
case 5:
deleteBook();
break;
case 6:
borrowBook();
break;
case 7:
returnBook();
break;
case 0:
System.out.println("感谢使用！");
return;
default:
System.out.println("无效选择！");
}
}
}
// CREATE - 添加图书
private void addBook() {
try {
System.out.println("\n=== 添加图书 ===");
System.out.print("ISBN: ");
String isbn = scanner.nextLine();
System.out.print("书名: ");
String title = scanner.nextLine();
System.out.print("作者: ");
String author = scanner.nextLine();
System.out.print("出版社: ");
String publisher = scanner.nextLine();
System.out.print("出版日期 (yyyy-MM-dd): ");
Date publishDate = sdf.parse(scanner.nextLine());
System.out.print("价格: ");
Double price = scanner.nextDouble();
scanner.nextLine();
System.out.print("库存数量: ");
Integer stock = scanner.nextInt();
scanner.nextLine();
System.out.print("分类: ");
String category = scanner.nextLine();
Book book = new Book(isbn, title, author, publisher,
publishDate, price, stock, category);
if (bookService.addBook(book)) {
System.out.println("图书添加成功，ID: " + book.getId());
} else {
System.out.println("图书添加失败");
}
} catch (Exception e) {
System.out.println("添加失败: " + e.getMessage());
}
}
// READ - 查看所有图书
private void viewAllBooks() {
System.out.println("\n=== 所有图书 ===");
List<Book> books = bookService.getAllBooks();
if (books.isEmpty()) {
System.out.println("暂无图书");
return;
}
for (Book book : books) {
displayBook(book);
}
}
// READ - 搜索图书
private void searchBooks() {
System.out.print("\n请输入搜索关键字: ");
String keyword = scanner.nextLine();
List<Book> books = bookService.getBookDAO().searchBooks(keyword);
if (books.isEmpty()) {
System.out.println("未找到相关图书");
return;
}
System.out.println("找到 " + books.size() + " 本图书:");
for (Book book : books) {
displayBook(book);
}
}
// UPDATE - 更新图书
private void updateBook() {
System.out.print("\n请输入要更新的图书ID: ");
Long id = scanner.nextLong();
scanner.nextLine();
Book book = bookService.getBookById(id);
if (book == null) {
System.out.println("图书不存在");
return;
}
System.out.println("当前图书信息:");
displayBook(book);
System.out.println("\n输入新信息 (直接回车保持不变):");
System.out.print("书名 (" + book.getTitle() + "): ");
String title = scanner.nextLine();
if (!title.isEmpty()) book.setTitle(title);
System.out.print("作者 (" + book.getAuthor() + "): ");
String author = scanner.nextLine();
if (!author.isEmpty()) book.setAuthor(author);
System.out.print("价格 (" + book.getPrice() + "): ");
String price = scanner.nextLine();
if (!price.isEmpty()) book.setPrice(Double.parseDouble(price));
if (bookService.updateBook(book)) {
System.out.println("更新成功");
} else {
System.out.println("更新失败");
}
}
// DELETE - 删除图书
private void deleteBook() {
System.out.print("\n请输入要删除的图书ID: ");
Long id = scanner.nextLong();
Book book = bookService.getBookById(id);
if (book == null) {
System.out.println("图书不存在");
return;
}
System.out.println("即将删除的图书:");
displayBook(book);
System.out.print("确认删除? (y/n): ");
String confirm = scanner.next();
if (confirm.equalsIgnoreCase("y")) {
if (bookService.deleteBook(id)) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
}
}
// 借书
private void borrowBook() {
System.out.print("\n请输入要借阅的图书ID: ");
Long id = scanner.nextLong();
try {
if (bookService.borrowBook(id)) {
System.out.println("借阅成功");
}
} catch (Exception e) {
System.out.println("借阅失败: " + e.getMessage());
}
}
// 还书
private void returnBook() {
System.out.print("\n请输入要归还的图书ID: ");
Long id = scanner.nextLong();
try {
if (bookService.returnBook(id)) {
System.out.println("归还成功");
}
} catch (Exception e) {
System.out.println("归还失败: " + e.getMessage());
}
}
// 显示图书信息
private void displayBook(Book book) {
System.out.println("-------------------");
System.out.println("ID: " + book.getId());
System.out.println("ISBN: " + book.getIsbn());
System.out.println("书名: " + book.getTitle());
System.out.println("作者: " + book.getAuthor());
System.out.println("出版社: " + book.getPublisher());
System.out.println("出版日期: " + sdf.format(book.getPublishDate()));
System.out.println("价格: " + book.getPrice());
System.out.println("库存: " + book.getStock());
System.out.println("分类: " + book.getCategory());
System.out.println("状态: " + book.getStatus());
System.out.println("-------------------");
}
// 主方法
public static void main(String[] args) {
new MainUI().start();
}
}
数据库脚本
-- database.sql
CREATE DATABASE IF NOT EXISTS book_management;
USE book_management;
CREATE TABLE books (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
isbn VARCHAR(20) NOT NULL UNIQUE,VARCHAR(200) NOT NULL,
author VARCHAR(100),
publisher VARCHAR(100),
publish_date DATE,
price DECIMAL(10, 2),
stock INT DEFAULT 0,
category VARCHAR(50),
status VARCHAR(20) DEFAULT 'AVAILABLE',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 插入测试数据
INSERT INTO books (isbn, title, author, publisher, publish_date, price, stock, category) VALUES
('978-7-111-12345-6', 'Java编程思想', 'Bruce Eckel', '机械工业出版社', '2020-01-01', 89.00, 10, '计算机'),
('978-7-302-54321-0', '深入理解Java虚拟机', '周志明', '清华大学出版社', '2019-12-01', 79.00, 5, '计算机');
Maven依赖配置
<!-- pom.xml -->
<dependencies>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
</dependencies>
这个完整的图书管理系统案例展示了：
- CREATE：添加新图书
- READ：查询图书（全部、按ID、按关键字）
- UPDATE：更新图书信息
- DELETE：删除图书
- 业务逻辑：借书（减少库存）、还书（增加库存）
- 分层架构：Model、DAO、Service、UI
- 异常处理：业务验证、数据库操作异常
系统采用了标准的Java分层架构，实现了完整的CRUD操作和业务逻辑,可以作为学习Java数据库编程的参考案例。
