package com.rain.dao;
import com.rain.bean.BooksBean;
import util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Booksdao {
/**
* 添加图书信息
* @throws ClassNotFoundException
*/
public boolean addBook(BooksBean book) throws ClassNotFoundException {
String sql = "INSERT INTO books (isbn, title, author, publisher, publication_date, category_id, total_copies, available_copies, STATUS, location, cover_image, introduction) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
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.getPublicationDate().getTime()));
pstmt.setInt(6, book.getCategoryId());
pstmt.setInt(7, book.getTotalCopies());
pstmt.setInt(8, book.getAvailableCopies());
pstmt.setString(9, book.getStatus());
pstmt.setString(10, book.getLocation());
pstmt.setString(11, book.getCoverImage());
pstmt.setString(12, book.getIntroduction());
int rowsAffected = pstmt.executeUpdate();
return rowsAffected > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
DBUtil.CloseDB(null, pstmt, conn);
}
}
/**
* 获取所有图书列表
* @throws ClassNotFoundException
*/
public List<BooksBean> getAllBooks() throws ClassNotFoundException {
List<BooksBean> bookList = new ArrayList<>();
String sql = "SELECT * FROM books";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
BooksBean book = new BooksBean();
book.setId(rs.getInt("id"));
book.setIsbn(rs.getString("isbn"));
book.setTitle(rs.getString("title"));
book.setAuthor(rs.getString("author"));
book.setPublisher(rs.getString("publisher"));
book.setPublicationDate(rs.getDate("publication_date"));
book.setCategoryId(rs.getInt("category_id"));
book.setTotalCopies(rs.getInt("total_copies"));
book.setAvailableCopies(rs.getInt("available_copies"));
book.setStatus(rs.getString("STATUS"));
book.setLocation(rs.getString("location"));
book.setCoverImage(rs.getString("cover_image"));
book.setIntroduction(rs.getString("introduction"));
book.setCreatedAt(rs.getTimestamp("created_at"));
book.setUpdatedAt(rs.getTimestamp("updated_at"));
bookList.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.CloseDB(rs, pstmt, conn);
}
return bookList;
}
/**
* 根据 ISBN 获取图书信息
* @throws ClassNotFoundException
*/
public BooksBean getBookByIsbn(String isbn) throws ClassNotFoundException {
String sql = "SELECT * FROM books WHERE isbn = ?";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
BooksBean book = new BooksBean();
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, isbn);
rs = pstmt.executeQuery();
if (rs.next()) {
book.setId(rs.getInt("id"));
book.setIsbn(rs.getString("isbn"));
book.setTitle(rs.getString("title"));
book.setAuthor(rs.getString("author"));
book.setPublisher(rs.getString("publisher"));
book.setPublicationDate(rs.getDate("publication_date"));
book.setCategoryId(rs.getInt("category_id"));
book.setTotalCopies(rs.getInt("total_copies"));
book.setAvailableCopies(rs.getInt("available_copies"));
book.setStatus(rs.getString("STATUS"));
book.setLocation(rs.getString("location"));
book.setCoverImage(rs.getString("cover_image"));
book.setIntroduction(rs.getString("introduction"));
book.setCreatedAt(rs.getTimestamp("created_at"));
book.setUpdatedAt(rs.getTimestamp("updated_at"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.CloseDB(rs, pstmt, conn);
}
return book;
}
/**
* 更新图书信息
* @throws ClassNotFoundException
*/
public boolean updateBook(BooksBean book) throws ClassNotFoundException {
String sql = "UPDATE books SET title = ?, author = ?, publisher = ?, publication_date = ?, " +
"category_id = ?, total_copies = ?, available_copies = ?, STATUS = ?, " +
"location = ?, cover_image = ?, introduction = ? WHERE isbn = ?";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, book.getTitle());
pstmt.setString(2, book.getAuthor());
pstmt.setString(3, book.getPublisher());
pstmt.setDate(4, new java.sql.Date(book.getPublicationDate().getTime()));
pstmt.setInt(5, book.getCategoryId());
pstmt.setInt(6, book.getTotalCopies());
pstmt.setInt(7, book.getAvailableCopies());
pstmt.setString(8, book.getStatus());
pstmt.setString(9, book.getLocation());
pstmt.setString(10, book.getCoverImage());
pstmt.setString(11, book.getIntroduction());
pstmt.setString(12, book.getIsbn());
int rowsAffected = pstmt.executeUpdate();
return rowsAffected > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
DBUtil.CloseDB(null, pstmt, conn);
}
}
/**
* 删除图书
* @throws ClassNotFoundException
*/
public boolean deleteBook(String isbn) throws ClassNotFoundException {
String sql = "DELETE FROM books WHERE isbn = ?";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, isbn);
int rowsAffected = pstmt.executeUpdate();
return rowsAffected > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
DBUtil.CloseDB(null, pstmt, conn);
}
}
/**
* 模糊搜索图书（书名、作者、ISBN）
* @throws ClassNotFoundException
*/
public List<BooksBean> searchBooks(String keyword) throws ClassNotFoundException {
List<BooksBean> bookList = new ArrayList<>();
String sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ?";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
String pattern = "%" + keyword + "%";
pstmt.setString(1, pattern);
pstmt.setString(2, pattern);
pstmt.setString(3, pattern);
rs = pstmt.executeQuery();
while (rs.next()) {
BooksBean book = new BooksBean();
book.setId(rs.getInt("id"));
book.setIsbn(rs.getString("isbn"));
book.setTitle(rs.getString("title"));
book.setAuthor(rs.getString("author"));
book.setPublisher(rs.getString("publisher"));
book.setPublicationDate(rs.getDate("publication_date"));
book.setCategoryId(rs.getInt("category_id"));
book.setTotalCopies(rs.getInt("total_copies"));
book.setAvailableCopies(rs.getInt("available_copies"));
book.setStatus(rs.getString("STATUS"));
book.setLocation(rs.getString("location"));
book.setCoverImage(rs.getString("cover_image"));
book.setIntroduction(rs.getString("introduction"));
book.setCreatedAt(rs.getTimestamp("created_at"));
book.setUpdatedAt(rs.getTimestamp("updated_at"));
bookList.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.CloseDB(rs, pstmt, conn);
}
return bookList;
}
/**
* 借阅图书（减少 available_copies）
* @throws ClassNotFoundException
*/
public boolean borrowBook(String isbn) throws ClassNotFoundException {
String sql = "UPDATE books SET available_copies = available_copies - 1 WHERE isbn = ? AND available_copies > 0";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, isbn);
int rowsAffected = pstmt.executeUpdate();
return rowsAffected > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
DBUtil.CloseDB(null, pstmt, conn);
}
}
/**
* 归还图书（增加 available_copies）
* @throws ClassNotFoundException
*/
public boolean returnBook(String isbn) throws ClassNotFoundException {
String sql = "UPDATE books SET available_copies = available_copies + 1 WHERE isbn = ?";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, isbn);
int rowsAffected = pstmt.executeUpdate();
return rowsAffected > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
DBUtil.CloseDB(null, pstmt, conn);
}
}
/**
* 自动更新图书状态（根据 available_copies）
* @throws ClassNotFoundException
*/
public void updateBookStatus() throws ClassNotFoundException {
String sql = "UPDATE books SET STATUS = CASE " +
"WHEN available_copies = 0 THEN 'unavailable' " +
"WHEN available_copies < total_copies THEN 'limited' " +
"ELSE 'available' END";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.CloseDB(null, pstmt, conn);
}
}
}
根据dao类，写出对应的servlet
在SpringBoot应用中，采用Hutool DB同步Mysql到Pg数据时，使用DbUtil分页查询导致Hikari连接泄露。源码分析显示pageForEntityList回调中未关闭连接，需手动调用session.close()解决。
扫一扫
2542
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
