根据BookManager类和Book类，完善一下BookManagementController（只要完善BookManagementController类）
BookManager类：package dao;
import entity.Book;
import dao.JdbcUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BookManager {
/**
* 添加图书
* @param book 图书对象
* @return 添加成功返回生成的图书ID，失败返回-1
*/
public int addBook(Book book) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "INSERT INTO books (isbn, title, author, publisher, publish_date, price, stock, status, category) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
try {
conn = JdbcUtil.getConnection();
// 设置返回生成的主键
ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, book.getIsbn());
ps.setString(2, book.getTitle());
ps.setString(3, book.getAuthor());
ps.setString(4, book.getPublisher());
ps.setDate(5, new java.sql.Date(book.getPublishDate().getTime()));
ps.setDouble(6, book.getPrice());
ps.setInt(7, book.getStock());
ps.setString(8, book.getStatus());
ps.setString(9, book.getCategory());
int rows = ps.executeUpdate();
if (rows > 0) {
rs = ps.getGeneratedKeys();
if (rs.next()) {
return rs.getInt(1); // 返回生成的图书ID
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(rs, ps, conn);
}
return -1;
}
/**
* 更新图书信息
* @param book 图书对象
* @return 更新成功返回true，否则false
*/
public boolean updateBook(Book book) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "UPDATE books SET isbn=?, title=?, author=?, publisher=?, publish_date=?, " +
"price=?, stock=?, status=?, category=? WHERE book_id=?";
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, book.getIsbn());
ps.setString(2, book.getTitle());
ps.setString(3, book.getAuthor());
ps.setString(4, book.getPublisher());
ps.setDate(5, new java.sql.Date(book.getPublishDate().getTime()));
ps.setDouble(6, book.getPrice());
ps.setInt(7, book.getStock());
ps.setString(8, book.getStatus());
ps.setString(9, book.getCategory());
ps.setInt(10, book.getBookId());
int rows = ps.executeUpdate();
return rows > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(ps, conn);
}
return false;
}
/**
* 删除图书
* @param bookId 图书ID
* @return 删除成功返回true，否则false
*/
public boolean deleteBook(int bookId) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "DELETE FROM books WHERE book_id=?";
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, bookId);
int rows = ps.executeUpdate();
return rows > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(ps, conn);
}
return false;
}
/**
* 根据图书ID查询图书
* @param bookId 图书ID
* @return 图书对象，未找到返回null
*/
public Book getBookById(int bookId) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "SELECT * FROM books WHERE book_id=?";
Book book = null;
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, bookId);
rs = ps.executeQuery();
if (rs.next()) {
book = mapResultSetToBook(rs);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(rs, ps, conn);
}
return book;
}
/**
* 查询所有图书
* @return 图书列表
*/
public List<Book> getAllBooks() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = "SELECT * FROM books";
List<Book> books = new ArrayList<>();
try {
conn = JdbcUtil.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
books.add(mapResultSetToBook(rs));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(rs, stmt, conn);
}
return books;
}
/**
* 根据条件搜索图书（书名、作者、ISBN）
* @param keyword 搜索关键词
* @return 匹配的图书列表
*/
public List<Book> searchBooks(String keyword) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ?";
List<Book> books = new ArrayList<>();
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql);
String searchPattern = "%" + keyword + "%";
ps.setString(1, searchPattern);
ps.setString(2, searchPattern);
ps.setString(3, searchPattern);
rs = ps.executeQuery();
while (rs.next()) {
books.add(mapResultSetToBook(rs));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(rs, ps, conn);
}
return books;
}
/**
* 更新图书库存
* @param bookId 图书ID
* @param change 库存变化量（正数增加，负数减少）
* @return 更新成功返回true，否则false
*/
public boolean updateBookStock(int bookId, int change) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "UPDATE books SET stock = stock + ? WHERE book_id=?";
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, change);
ps.setInt(2, bookId);
int rows = ps.executeUpdate();
return rows > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(ps, conn);
}
return false;
}
/**
* 更新图书状态
* @param bookId 图书ID
* @param newStatus 新状态
* @return 更新成功返回true，否则false
*/
public boolean updateBookStatus(int bookId, String newStatus) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "UPDATE books SET status=? WHERE book_id=?";
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, newStatus);
ps.setInt(2, bookId);
int rows = ps.executeUpdate();
return rows > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(ps, conn);
}
return false;
}
/**
* 将ResultSet映射为Book对象
* @param rs 结果集
* @return Book对象
* @throws SQLException
*/
private Book mapResultSetToBook(ResultSet rs) throws SQLException {
Book book = new Book();
book.setBookId(rs.getInt("book_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.setStatus(rs.getString("status"));
book.setCategory(rs.getString("category"));
return book;
}
}
Book类：package entity;
import java.util.Date;
public class Book {
private int bookId; // 图书ID
private String isbn; // ISBN编号
private String title; // 书名
private String author; // 作者
private String publisher; // 出版社
private Date publishDate; // 出版日期
private double price; // 价格
private int stock; // 库存数量
private String status; // 状态（例如：可借阅/已借出）
private String category; // 分类
// 无参构造方法
public Book() {
}
// 全参构造方法
public Book(int bookId, String isbn, String title, String author,
String publisher, Date publishDate, double price,
int stock, String status, String category) {
this.bookId = bookId;
this.isbn = isbn;
this.title = title;
this.author = author;
this.publisher = publisher;
this.publishDate = publishDate;
this.price = price;
this.stock = stock;
this.status = status;
this.category = category;
}
// Getter 和 Setter 方法
public int getBookId() {
return bookId;
}
public void setBookId(int bookId) {
this.bookId = bookId;
}
public String getIsbn() {
return isbn;
}
public void setIsbn(String isbn) {
this.isbn = isbn;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getPublisher() {
return publisher;
}
public void setPublisher(String publisher) {
this.publisher = publisher;
}
public Date getPublishDate() {
return publishDate;
}
public void setPublishDate(Date publishDate) {
this.publishDate = publishDate;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public int getStock() {
return stock;
}
public void setStock(int stock) {
this.stock = stock;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
// 重写toString方法便于打印信息
@Override
public String toString() {
return "Book{" +
"bookId=" + bookId +
", isbn='" + isbn + '\'' +
", title='" + title + '\'' +
", author='" + author + '\'' +
", publisher='" + publisher + '\'' +
", publishDate=" + publishDate +
", price=" + price +
", stock=" + stock +
", status='" + status + '\'' +
", category='" + category + '\'' +
'}';
}
}
BookManagementController类：package opera;
import dao.BookManager;
import entity.Book;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import java.util.Date;
public class BookManagementController extends Controller {
@FXML private TableView<Book> bookTable;
@FXML private TableColumn<Book, String> isbnColumn;
@FXML private TableColumn<Book, String> titleColumn;
@FXML private TableColumn<Book, String> authorColumn;
@FXML private TableColumn<Book, String> publisherColumn;
@FXML private TableColumn<Book, Date> publishDateColumn;
@FXML private TableColumn<Book, Double> priceColumn;
@FXML private TableColumn<Book, Integer> stockColumn;
@FXML private TableColumn<Book, String> statusColumn;
@FXML private TableColumn<Book, String> categoryColumn;
private ObservableList<Book> bookList = FXCollections.observableArrayList();
private BookManager bookManager = new BookManager();
@FXML
public void initialize() {
// 配置表格列与Book属性的映射
isbnColumn.setCellValueFactory(new PropertyValueFactory<>("isbn"));
titleColumn.setCellValueFactory(new PropertyValueFactory<>("title"));
authorColumn.setCellValueFactory(new PropertyValueFactory<>("author"));
publisherColumn.setCellValueFactory(new PropertyValueFactory<>("publisher"));
publishDateColumn.setCellValueFactory(new PropertyValueFactory<>("publishDate"));
priceColumn.setCellValueFactory(new PropertyValueFactory<>("price"));
stockColumn.setCellValueFactory(new PropertyValueFactory<>("stock"));
statusColumn.setCellValueFactory(new PropertyValueFactory<>("status"));
categoryColumn.setCellValueFactory(new PropertyValueFactory<>("category"));
// 加载图书数据
loadBooks();
}
private void loadBooks() {
bookList.clear();
bookList.addAll(bookManager.getAllBooks());
bookTable.setItems(bookList);
}
@FXML
private void handleRefresh() {
loadBooks();
}
@FXML
private void handleDeleteBook() {
Book selectedBook = bookTable.getSelectionModel().getSelectedItem();
if (selectedBook != null) {
if (bookManager.deleteBook(selectedBook.getBookId())) {
showInfoAlert("图书删除成功");
loadBooks();
} else {
showErrorAlert("图书删除失败");
}
} else {
showErrorAlert("请选择要删除的图书");
}
}
}
本文探讨了SQL缓存的基本原理及其在数据库性能优化中的作用。通过分析不同类型的SQL缓存，如查询缓存和结果集缓存，文章详细解释了如何利用这些缓存来减少数据库负载并提高查询效率。
扫一扫
3655
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
