重构后的JdbcUtil

JDBC数据库连接工具类
package util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtil {
   //连接数据库的四要素
  private static Properties p=new Properties();
    static { //当JdbcUtil这份字节码被加载进JVM就立刻执行
    	try {
    		//加载和读取db.properties文件
    		InputStream instream=Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
    		p.load(instream);//加载
    		//--------------------------------------
    		Class.forName(p.getProperty("driverClassName"));
    	}catch(Exception e) {
    		e.printStackTrace();
    	}
    }
   //创建并返回一个Connection对象
    public static Connection getConn() {
    	try {
			return DriverManager.getConnection(p.getProperty("url"),
					                           p.getProperty("username"),
					                           p.getProperty("password"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return null;
    }
    //释放资源
    public static void close(ResultSet rs,PreparedStatement pst,Connection conn){
    	try {
			if(rs!=null) {
				rs.close();
			}
		}catch(Exception e) {
		}finally {
		try {
			if(pst!=null) {
				pst.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(conn!=null) {
					conn.close();
				}
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
    	
    }
}


再根据BookManager类代码修改BookSearchController类代码(不能增添额外java类) 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; } } BookSearchController类代码为: package opera; import dao.BookManager; import entity.Book; import javafx.collections.FXCollections; import javafx.collections.ObservableList; import javafx.fxml.FXML; import javafx.scene.control.ComboBox; import javafx.scene.control.TableColumn; import javafx.scene.control.TableView; import javafx.scene.control.TextField; import javafx.scene.control.cell.PropertyValueFactory; public class BookSearchController extends Controller { @FXML private TextField searchField; @FXML private ComboBox<String> searchTypeCombo; @FXML private TableView<Book> resultTable; @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, Integer> totalCopiesColumn; @FXML private TableColumn<Book, Integer> borrowedCopiesColumn; private ObservableList<Book> searchResults = FXCollections.observableArrayList(); @FXML public void initialize() { // 初始化搜索类型下拉框 searchTypeCombo.getItems().addAll("ISBN", "书名", "作者", "出版社", "分类"); searchTypeCombo.setValue("书名"); // 配置表格列 isbnColumn.setCellValueFactory(new PropertyValueFactory<>("isbn")); titleColumn.setCellValueFactory(new PropertyValueFactory<>("title")); authorColumn.setCellValueFactory(new PropertyValueFactory<>("author")); publisherColumn.setCellValueFactory(new PropertyValueFactory<>("publisher")); totalCopiesColumn.setCellValueFactory(new PropertyValueFactory<>("totalCopies")); borrowedCopiesColumn.setCellValueFactory(new PropertyValueFactory<>("borrowedCopies")); resultTable.setItems(searchResults); } @FXML private void handleSearch() { String keyword = searchField.getText().trim(); if (keyword.isEmpty()) { showErrorAlert("请输入搜索关键词"); return; } String searchType = searchTypeCombo.getValue(); searchResults.clear(); switch (searchType) { case "ISBN": searchResults.addAll(BookManager.searchByISBN(keyword)); break; case "书名": searchResults.addAll(BookManager.searchByTitle(keyword)); break; case "作者": searchResults.addAll(BookManager.searchByAuthor(keyword)); break; case "出版社": searchResults.addAll(BookManager.searchByPublisher(keyword)); break; case "分类": searchResults.addAll(BookManager.searchByCategory(keyword)); break; } if (searchResults.isEmpty()) { showInfoAlert("未找到匹配的图书"); } } }
06-13
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值