Servlet、jsp和javabean图书管理系统

package com.tsgl.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Collection; import com.tsgl.bean.BookBean; import com.tsgl.util.DBUtils; public class BookDAO { private Connection con = null; private PreparedStatement pstmt = null; private ResultSet rs = null; //显示数据的查询 public Collection<BookBean> query(int id){ BookBean book = null; Collection<BookBean> col = new ArrayList<BookBean>(); String sql = ""; if(id != 0){ sql = "select b.*,bc.strCaseName as caseName from tblbookinfo b left join tblbookcase bc on b.intBookCase=bc.intAutoID where b.intAutoID="+id+""; }else{ sql = "select b.*,bc.strCaseName as caseName from tblbookinfo b left join tblbookcase bc on b.intBookCase=bc.intAutoID"; } try{ con = DBUtils.getConnection(); pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ book = new BookBean(); book.setAutoID(rs.getInt(1)); book.setBookID(rs.getInt(2)); book.setBookName(rs.getString(3)); book.setAuthor(rs.getString(4)); book.setTranslator(rs.getString(5)); book.setISBN(rs.getString(6)); book.setPrice(rs.getFloat(7)); book.setPress(rs.getString(8)); book.setInTime(rs.getString(9)); book.setBookCase(rs.getInt(10));//所在书架ID book.setIfBorrow(rs.getInt(11)); book.setCaseName(rs.getString(12));//所在书架名 col.add(book); } }catch(Exception e){ e.printStackTrace(); } try{ rs.close(); pstmt.close(); con.close(); }catch(Exception e){ e.printStackTrace(); } return col; } //用于图书查询的查询 public Collection<BookBean> query2(String str){ BookBean book = null; Collection<BookBean> col = new ArrayList<BookBean>(); String sql = ""; try{ if(str != null){ sql = "select * from (select b.*,bc.strCaseName as caseName from tblbookinfo b left join tblbookcase bc on b.intBookCase=bc.intAutoID) as book where book."+str+""; con = DBUtils.getConnection(); pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ book = new BookBean(); book.setAutoID(rs.getInt(1)); book.setBookID(rs.getInt(2)); book.setBookName(rs.getString(3)); book.setAuthor(rs.getString(4)); book.setTranslator(rs.getString(5)); book.setISBN(rs.getString(6)); book.setPrice(rs.getFloat(7)); book.setPress(rs.getString(8)); book.setInTime(rs.getString(9)); book.setBookCase(rs.getInt(10));//所在书架ID book.setIfBorrow(rs.getInt(11)); book.setCaseName(rs.getString(12));//所在书架名 col.add(book); } } }catch(Exception e){ e.printStackTrace(); } try{ if(rs != null) rs.close(); if(pstmt != null) pstmt.close(); if(con != null) con.close(); }catch(Exception e){ e.printStackTrace(); } return col; } //添加数据 public int insert(BookBean book){ int temp = 0; String sql1 = "select * from tblbookinfo where intBookID="+book.getBookID(); String sql2 = ""; try{ con = DBUtils.getConnection(); pstmt = con.prepareStatement(sql1); rs = pstmt.executeQuery(); if(rs.next()){ temp = 2; }else{ sql2 = "insert into tblbookinfo(intBookID,strBookName,strAuthor,strTranslator,strISBN,floatPrice,strPress,dateInTime,intBookCase,intIfBorrow)" +" values(?,?,?,?,?,?,?,?,?,?)"; pstmt = con.prepareStatement(sql2); pstmt.setInt(1, book.getBookID()); pstmt.setString(2, book.getBookName()); pstmt.setString(3, book.getAuthor()); pstmt.setString(4, book.getTranslator()); pstmt.setString(5, book.getISBN()); pstmt.setFloat(6, book.getPrice()); pstmt.setString(7, book.getPress()); pstmt.setString(8, book.getInTime()); pstmt.setInt(9, book.getBookCase()); pstmt.setInt(10, book.getIfBorrow()); temp = pstmt.executeUpdate(); } }catch(Exception e){ temp = 0; e.printStackTrace(); } try{ if(rs != null) rs.close(); if(pstmt != null) pstmt.close(); if(con != null) con.close(); }catch(Exception e){ e.printStackTrace(); } return temp; } //用于修改的查询 public BookBean queryM(int id){ BookBean book = null; String sql = "select b.*,bc.strCaseName as caseName from tblbookinfo b left join tblbookcase bc on b.intBookCase=bc.intAutoID where b.intAutoID="+id+""; try{ con = DBUtils.getConnection(); pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ book = new BookBean(); book.setAutoID(rs.getInt(1)); book.setBookID(rs.getInt(2)); book.setBookName(rs.getString(3)); book.setAuthor(rs.getString(4)); book.setTranslator(rs.getString(5)); book.setISBN(rs.getString(6)); book.setPrice(rs.getFloat(7)); book.setPress(rs.getString(8)); book.setInTime(rs.getString(9)); book.setBookCase(rs.getInt(10));//所在书架ID book.setIfBorrow(rs.getInt(11)); book.setCaseName(rs.getString(12));//所在书架名 } }catch(Exception e){ e.printStackTrace(); } try{ if(rs != null) rs.close(); if(pstmt != null) pstmt.close(); if(con != null) con.close(); }catch(Exception e){ e.printStackTrace(); } return book; } //修改信息 public int update(BookBean book){ int temp = 0; String sql = "update tblbookinfo set strBookName=?,strAuthor=?,strTranslator=?,strISBN=?,floatPrice=?,strPress=?,intBookCase=? where intAutoID="+book.getAutoID()+""; try{ con = DBUtils.getConnection(); pstmt = con.prepareStatement(sql); pstmt.setString(1, book.getBookName()); pstmt.setString(2, book.getAuthor()); pstmt.setString(3, book.getTranslator()); pstmt.setString(4, book.getISBN()); pstmt.setFloat(5, book.getPrice()); pstmt.setString(6, book.getPress()); pstmt.setInt(7, book.getBookCase()); temp = pstmt.executeUpdate(); }catch(Exception e){ temp = 0; e.printStackTrace(); } try{ if(pstmt != null) pstmt.close(); if(con != null) con.close(); }catch(Exception e){ e.printStackTrace(); } return temp; } //删除数据 public int delete(int id){ int temp = 0; String sql = "delete from tblbookinfo where intAutoID=?"; try{ con = DBUtils.getConnection(); pstmt = con.prepareStatement(sql); pstmt.setInt(1, id); temp = pstmt.executeUpdate(); }catch(Exception e){ temp = 0; e.printStackTrace(); } try{ if(pstmt != null) pstmt.close(); if(con != null) con.close(); }catch(Exception e){ e.printStackTrace(); } return temp; } //验证图书是否存在 public boolean check(int id){ boolean flag; String sql = "select * from tblbookinfo where intBookID=?"; try{ con = DBUtils.getConnection(); pstmt = con.prepareStatement(sql); pstmt.setInt(1, id); rs = pstmt.executeQuery(); if(rs.next()) flag = true; else flag = false; }catch(Exception e){ flag = false; e.printStackTrace(); } try{ if(rs != null) rs.close(); if(pstmt != null) pstmt.close(); if(con != null) con.close(); }catch(Exception e){ e.printStackTrace(); } return flag; } //查询图书是否已借 public boolean checkB(int id){ boolean flag = false; String sql = "select intIfBorrow from tblbookinfo where intBookID="+id+""; try{ con = DBUtils.getConnection(); pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ int temp = rs.getInt(1); if(temp == 0) flag = false; else flag = true; } }catch(Exception e){ e.printStackTrace(); } try{ if(rs != null) rs.close(); if(pstmt != null) pstmt.close()