MVC图书管理系统.zip

package dao.impl; import dao.BookDao; import entity.BookInfo; import util.DBHelper; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class BookDaoImpl implements BookDao { @Override public int addBook(BookInfo bookInfo) { Connection connection = null; PreparedStatement ps = null; int n = 0; try { connection = DBHelper.getConnection(); String sql = "insert into bookinfo (bookid,bookname,editor,price,publishingHouse,publishingDate) " + "values (?,?,?,?,?,?)"; ps = connection.prepareStatement(sql); ps.setString(1, bookInfo.getBookid()); ps.setString(2, bookInfo.getBookname()); ps.setString(3, bookInfo.getEditor()); ps.setString(4, bookInfo.getPrice()); ps.setString(5, bookInfo.getPublishingHouse()); ps.setString(6, bookInfo.getPublishingDate()); n = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } return n; } @Override public BookInfo findBookById(String bookid) { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; BookInfo bookInfo = null; try{ connection = DBHelper.getConnection(); String sql = "select * from bookinfo where bookid = ?"; ps = connection.prepareStatement(sql); ps.setString(1,bookid); rs = ps.executeQuery(); while(rs.next()){ bookid = rs.getString("bookid"); String bookname = rs.getString("bookname"); String editor = rs.getString("editor"); String price = rs.getString("price"); String publishingHouse = rs.getString("publishingHouse"); String publishingDate = rs.getString("publishingDate"); bookInfo =new BookInfo(bookid,bookname,editor,price,publishingHouse,publishingDate); } } catch (SQLException e) { e.printStackTrace(); }finally { if(connection!=null){ try{ DBHelper.closeAll(rs,ps,connection); } catch (Exception e) { e.printStackTrace(); } } } return bookInfo; } @Override public List<BookInfo> findPageBook(int start, int end, String bookname, String editor) { List<BookInfo> bookInfoList = new ArrayList<BookInfo>(); Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; BookInfo bookInfo = null; // 加载驱动 // 获取连接 try { connection = DBHelper.getConnection(); String sql="select * from bookinfo where bookname like '%"+bookname+"%' and editor like '%"+editor+ "' order by bookid limit "+start+","+(end-start)+""; ps = connection.prepareStatement(sql); rs = ps.executeQuery(); // 处理结果 while (rs.next()) { String bookid =rs.getString("bookid"); bookname = rs.getString("bookname"); editor = rs.getString("editor"); String price = rs.getString("price"); String publishingHouse = rs.getString("publishingHouse"); String publishingDate = rs.getString("publishingDate"); // 封装到对象中 bookInfo = new BookInfo(bookid,bookname,editor,price,publishingHouse,publishingDate); bookInfoList.add(bookInfo); } } catch (SQLException e) { e.printStackTrace(); } finally {// 关闭资源 DBHelper.closeAll(rs, ps, connection); } return bookInfoList; } @Override public List<BookInfo> findBook(String bookname, String editor) { List<BookInfo> bookInfoList = new ArrayList<BookInfo>(); Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; BookInfo bookInfo = null; // 加载驱动 // 获取连接 try { connection = DBHelper.getConnection(); // 建立命令发送器（prepareStatement） StringBuilder sql=new StringBuilder("select * from bookinfo where bookid = ? "); if(bookname!=null && !"".equals(bookname)){ sql.append(" and bookname like '%").append(bookname).append("%'"); } if(editor!=null && !"".equals(editor)){ sql.append(" and editor >= '").append(editor).append("'"); } ps = connection.prepareStatement(sql.toString()); rs = ps.executeQuery(); // 处理结果 while (rs.next()) { // 获取各个字段的值 String bookid = rs.getString("bookid"); bookname = rs.getString("bookname"); editor = rs.getString("editor"); String price = rs.getString("price"); String publishingHouse = rs.getString("publishingHouse"); String publishingDate = rs.getString("publishingDate"); // 封装到对象中 bookInfo = new BookInfo(bookid,bookname,editor,price,publishingHouse,publishingDate); bookInfoList.add(bookInfo); } } catch (SQLException e) { e.printStackTrace(); } finally {// 关闭资源 DBHelper.closeAll(rs, ps, connection); } return bookInfoList; } @Override public List<BookInfo> findAllBook() { Connection connection =null; PreparedStatement ps = null; ResultSet rs= null; List<BookInfo> bookInfoList = new ArrayList<BookInfo>(); BookInfo bookInfo = null; try{ connection = DBHelper.getConnection(); String sql = "select * from bookinfo"; ps = connection.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()){ String bookid = rs.getString("bookid"); String bookname = rs.getString("bookname"); String editor = rs.getString("editor"); String price = rs.getString("price"); String publishingHouse = rs.getString("publishingHouse"); String publishingDate = rs.getString("publishingDate"); bookInfo = new BookInfo(bookid,bookname,editor,price,publishingHouse,publishingDate); bookInfoList.add(bookInfo); } } catch (SQLException e) { e.printStackTrace(); }finally { try{ DBHelper.closeAll(rs,ps,connection); } catch (Exception e) { e.printStackTrace(); } } return bookInfoList; } @Override public boolean deleteBook(String bookid) { boolean statuscode = false; Connection connection = null; PreparedStatement ps2 = null; try { connection = DBHelper.getConnection(); String sql2 = "delete from bookinfo where bookid=?"; ps2 = connection.prepareStatement(sql2); ps2.setString(1,bookid); ps2.executeUpdate(); statuscode = true; }catch(Exception e) { e.printStackTrace(); }finally { DBHelper.closeAll(null, ps2, connection); } return statuscode; } @Override public int updateBook(BookInfo bookInfo) { Connection connection = nul