使用JSP+Servlet+JavaBean的MVC开发模式实现Web图书管理后台系统

package dao; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import beans.*; public class BookDAO { public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String DBURL = "jdbc:mysql://localhost:3306/testdb"; public static final String DBUSER = "root"; public static final String DBPASS = "123456"; private Connection conn = null; private PreparedStatement pStat = null; private ResultSet rs = null; public Connection getConnectionn() { try { Class.forName(DRIVER).newInstance(); return DriverManager.getConnection(DBURL, DBUSER, DBPASS); } catch (Exception e) { return null; } } public void close() { try { if (rs != null) rs.close(); if (pStat != null) pStat.close(); if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } // end close public boolean addBook(Book book) { conn = getConnectionn(); try { pStat = conn.prepareStatement("insert into books values(null,?,?,?,?,?,?,?)"); pStat.setString(1, book.getISBNid()); pStat.setString(2, book.getBookname()); pStat.setString(3, book.getAuthor()); pStat.setDouble(4, book.getPrice()); pStat.setDate(5, book.getTime()); pStat.setDouble(6, Double.parseDouble(book.getDiscount())); pStat.setInt(7, book.getStock()); int cnt = pStat.executeUpdate(); if (cnt > 0) return true; else return false; } catch (Exception e) { return false; } finally { close(); } } public boolean isISBNidExists(String ISBNid) { conn = getConnectionn(); try { pStat = conn.prepareStatement("select * from books where ISBNid=?"); pStat.setString(1, ISBNid); rs = pStat.executeQuery(); if (rs.next()) return true; else return false; } catch (Exception e) { return false; } finally { close(); } } public ArrayList<Book> findBook() { conn = getConnectionn(); ArrayList<Book> list = new ArrayList<Book>(); try { pStat = conn.prepareStatement("select * from books "); rs = pStat.executeQuery(); while( rs.next() ){ //循环遍历结果集 Book book=new Book(rs.getString("ISBNid"),rs.getString("bookname"),rs.getString("author"), rs.getDouble("price"),rs.getDate("time"),rs.getString("discount"),rs.getInt("stock")); list.add(book); } return list; } catch (Exception e) { return null; } finally { close(); } } public boolean deleteBook(String iSBNid) { conn = getConnectionn(); try { pStat = conn.prepareStatement("delete from books where ISBNid=?"); pStat.setString(1, iSBNid); int cnt=pStat.executeUpdate(); if (cnt>0) return true; else return false; } catch (Exception e) { return false; } finally { close(); } } public Book findBookByISBNid(String iSBNid) { conn = getConnectionn(); ArrayList<Book> list = new ArrayList<Book>(); try { pStat = conn.prepareStatement("select * from books where ISBNid=? "); pStat.setString(1, iSBNid); rs = pStat.executeQuery(); Book book=new Book(); while( rs.next() ){ //循环遍历结果集 book=new Book(rs.getString("ISBNid"),rs.getString("bookname"),rs.getString("author"), rs.getDouble("price"),rs.getDate("time"),rs.getString("discount"),rs.getInt("stock")); } return book; } catch (Exception e) { return null; } finally { close(); } } public boolean update(Book book) { conn = getConnectionn(); try { pStat = conn.prepareStatement("update books set bookname = ?,author = ?,price = ?,time= ? ," + "discount= ? ,stock= ? where ISBNid= ? "); pStat.setString(1, book.getBookname()); pStat.setString(2, book.getAuthor()); pStat.setDouble(3, book.getPrice()); pStat.setDate(4, book.getTime()); pStat.setDouble(5, Double.parseDouble(book.getDiscount())); pStat.setInt(6, book.getStock()); pStat.setString(7, book.getISBNid()); int cnt=pStat.executeUpdate(); if (cnt>0) return true; else return false; } catch (Exception e) { return false; } finally { close(); } } public ArrayList<Book> findBookbytime(String starttime, String endtime) { conn = getConnectionn(); ArrayList<Book> list = new ArrayList<Book>(); try { pStat = conn.prepareStatement("select * from books where time > ? and time < ? "); pStat.setDate(1, Date.valueOf(starttime)); pStat.setDate(2, Date.valueOf(endtime)); rs = pStat.executeQuery(); Book book=new Book(); while( rs.next() ){ //循环遍历结果集 book=new Book(rs.getString("ISBNid"),rs.getString("bookname"),rs.getString("author"), rs.getDouble("price"),rs.getDate("time"),rs.getString("discount"),rs.getInt("stock")); list.add(book); } return list; } catch (Exception e) { return null; } finally { close(); } } public ArrayList<Book> findBookbyprice(String startprice, String endprice) { conn = getConnectionn(); ArrayList<Book> list = new ArrayList<Book>(); try { pStat = conn.prepareStatement("select * from books where price > ? and price < ? "); pStat.setDouble(1, Double.parseDouble(startprice)); pStat.setDouble(2, Double.parseDouble(endprice)); rs = pStat.executeQuery(); Book book=new Book(); while( rs.next() ){ //循环遍历结果集 book=new Book(rs.getString("ISBNid"),rs.getString("bookname"),rs.getString("author"), rs.getDouble("price"),rs.getDate("time"),rs.getString("discount"),rs.getInt("stock")); list.add(book); } return list; } catch (Exception e) { return null; } finally { close(); } } public boolean deleteBook(String[] iSBNid) { conn = getConnectionn(); try { String sql="delete from books where ISBNid=? "; if(iSBNid.length>1) { for(int i=1;i<iSBNid.length;i++) { sql=sql+" or ISBNid=?"; } } pStat = conn.prepareStatement(sql); for(int i=0;i<iSBNid.length;i++) { pStat.setString(i+1, iSBNid[i]); } int cnt=pStat.executeUpdate(); if (cnt>0) return true; else return false; } catch (Exception e) { return false; } finally { close(); } } public ArrayList<Book> findBookbykey(String keyvalue) { conn = getConnectionn(); ArrayList<Book> list = new ArrayList<Book>(); try { String sql="select * from books where ISBNid like '%"+keyvalue+"%' or author like '%"+keyvalue+"%' or bookname like '%"+keyvalue+"%'"; ; pStat = conn.prepareStatement(sql); rs = pStat.executeQuery(); Book book=new Book(); while( rs.next() ){ //循环遍历结果集 book=new Book(rs.getString("ISBNid"),rs.getString("bookname"),rs.getString("author"), rs.getDouble("price"),rs.getDate("time"),rs.getString("discount"),rs.getInt("stock")); list.add(book); } return list; } catch (Exception e) { return null; } finally { close(); } } }