根据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("请选择要删除的图书");
}
}
}
本文介绍了三种在Java中获取数据库自动生成主键的方法：使用JDBC 3.0的getGeneratedKeys()方法、利用特定数据库的SQL语句以及通过可更新的结果集实现。
扫一扫
1792
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
