java图书管理系统项目源码

/** * */ package demo.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import demo.entity.Borrow; import demo.util.DBUtil; /** * 对接borrow表的类 * */ public class BorrowDao { /** * 查询该借阅者当前是否有超期图书未归还 * @param readerId 借阅者id * @return 当前有超期图书未归还：1 没有：-1 */ public int selectNowTimeoutById(String readerId) { Connection con = DBUtil.getConnection(); String sql = " select * from borrow where timeout=1 and time_return_real is null and id_reader = ?"; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, readerId); ResultSet rs = ps.executeQuery(); if (rs.next()) { return 1; } } catch (SQLException e) { } finally { DBUtil.closeConnection(con); } } return -1; } /** * 归还图书时，根据num填写实际还书时间 * @param bookId * @return 失败：-1 成功：1 */ public int updateRealById(String bookId) { Connection con = DBUtil.getConnection(); String sql = " update borrow set time_return_real = CURDATE() where id_book=? and time_return_real is NULL"; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, bookId); return ps.executeUpdate(); } catch (SQLException e) { return -1; } finally { DBUtil.closeConnection(con); } } return -1; } /** * 续借时，修改应还时间，将应还时间修改为当前时间加一个月 * @param bookId * @return */ public int updateShouldById(String bookId) { Connection con = DBUtil.getConnection(); String sql = " update borrow set time_return_should = date_add(CURTATE(), interval 1 MONTH) where num = (SELECT a.num from(select num from borrow where id_book = ? and timeout!=1 and time_return_real is NULL) a )"; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, bookId); return ps.executeUpdate(); } catch (SQLException e) { return -1; } finally { DBUtil.closeConnection(con); } } return -1; } /** * 续借次数加1 * @param bookId * @return */ public int renew (String bookId) { Connection con = DBUtil.getConnection(); String sql = " update borrow set renew = renew+1 where num = (SELECT a.num from(select num from borrow where id_book = ? and timeout!=1 and time_return_real is NULL) a )"; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, bookId); return ps.executeUpdate(); } catch (SQLException e) { return -1; } finally { DBUtil.closeConnection(con); } } return -1; } /** * 返回续借次数，起始未1，续借两次后为3 * @param bookId * @return */ public int returnRenewById(String bookId) { Connection con = DBUtil.getConnection(); String sql = " SELECT renew from borrow where num = (select num from borrow where id_book = ? and timeout!=1 and time_return_real is NULL)"; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, bookId); ResultSet rs = ps.executeQuery(); int renew = 0; if (rs.next()) { renew = rs.getInt(1); } return renew; } catch (SQLException e) { return 0; } finally { DBUtil.closeConnection(con); } } return 0; } /** * 管理员当前借阅模糊查询 * @param bookId * @param readId * @param readerName * @param bookName * @return */ public List<Borrow> selectByOthers( String readerName, String bookName){ Connection con = DBUtil.getConnection(); String sql = "select id_book, book.name as bookname, id_reader, user.name as username, time_borrow, time_return_should from user,book,borrow where user.id=id_reader and book.id=id_book and time_return_real IS NULL and user.name like \"%\"?\"%\" and book.name like \"%\"?\"%\""; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, readerName); ps.setString(2, bookName); ResultSet rs = ps.executeQuery(); List<Borrow> borrowList = rsToBorrows(rs); return borrowList; } catch (SQLException e) { return null; } finally { DBUtil.closeConnection(con); } } return null; } /** * 管理员历史借阅模糊查询 * @param bookId * @param readId * @param readerName * @param bookName * @return */ public List<Borrow> selectPastByOthers( String readerName, String bookName){ Connection con = DBUtil.getConnection(); String sql = "select id_book, book.name as bookname, id_reader, user.name as username, time_borrow, time_return_real from user,book,borrow where user.id=id_reader and book.id=id_book and time_return_real IS NOT NULL and user.name like \"%\"?\"%\" and book.name like \"%\"?\"%\""; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, readerName); ps.setString(2, bookName); ResultSet rs = ps.executeQuery(); List<Borrow> borrowList = rsToBorrows(rs); return borrowList; } catch (SQLException e) { return null; } finally { DBUtil.closeConnection(con); } } return null; } /** * 管理员超时借阅模糊查询 * @param bookId * @param readId * @param readerName * @param bookName * @return */ public List<Borrow> selectOverByOthers( String readerName, String bookName){ Connection con = DBUtil.getConnection(); String sql = "select id_book, book.name as bookname, id_reader, user.name as username, time_return_should, time_return_real from user,book,borrow where user.id=id_reader and book.id=id_book and timeout=1 and user.name like \"%\"?\"%\" and book.name like \"%\"?\"%\""; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, readerName); ps.setString(2, bookName); ResultSet rs = ps.executeQuery(); List<Borrow> borrowList = rsToBorrows(rs); return borrowList; } catch (SQLException e) { return null; } finally { DBUtil.closeConnection(con); } } return null; } /** * 管理员当前借阅查询，根据读者id和图书id和其他 * @param bookId * @param readId * @param readerName * @param bookName * @return */ public List<Borrow> selectByBookIdReaderId( String bookId, String readId, String readerName, String bookName){ Connection con = DBUtil.getConnection(); String sql = "select id_book, book.name as bookname, id_reader, user.name as username, time_borrow, time_return_should from user,book,borrow where user.id=id_reader and book.id=id_book and time_return_real IS NULL and book.id=? and user.id=? and user.name like \"%\"?\"%\" and book.name like \"%\"?\"%\""; PreparedStatement ps = null; if (con != null) { try { ps = con.prepareStatement(sql); ps.setString(1, bookId); ps.setString(2, readId); ps.setString(3, readerName); ps.setString(4, bookName); ResultSet rs = ps.executeQuery(); List<Borrow> borrowList = rsToBorrows(rs); return borrowList; } catch (SQLException e) { return null; } finally { DBUtil.closeConnection(con); } } return null; } /** * 管理员历史借阅查询，根据读者id和图书id和其他 * @param bookId * @param readId * @param readerName * @param bookName * @return */ public List<Borrow> selectPastByBookIdReaderId( String bookId, String readId, String readerName, String bookName){ Connection con = DBUtil.getConnection(); String sql = "select id_book, book.name as bookname, id_reader, user.name as username, time_borrow