基于JSP的网上图书销售系统

package com.bookshop.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import javax.naming.NamingException; import com.bookshop.db.DBTools; import com.bookshop.entity.BookInfo; import com.bookshop.idao.BookIDAO; public class BookDAO implements BookIDAO { private Connection conn = null; private PreparedStatement ps = null; private ResultSet rs = null; DBTools dbTools = new DBTools(); /** * 搜索书籍时通过类别，关键字，通过pageSize得到总页数 * @param category * @param keyWord * @param pageSize * @return */ public int getPageCountByCategoryAndKeyWord(int category,String keyWord,int pageSize){ int pageCount = 0; String sql = "select count(*) from tb_bookinfo where category_id=? and book_name like '%" + keyWord + "%'"; try { conn = dbTools.getConn(); ps = conn.prepareStatement(sql); ps.setInt(1, category); rs = ps.executeQuery(); while(rs.next()){ int rowCount = rs.getInt(1); if(rowCount%pageSize == 0){ pageCount = rowCount/pageSize; }else{ pageCount = rowCount/pageSize+1; } } } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ try { dbTools.closeResource(); } catch (SQLException e) { e.printStackTrace(); } } return pageCount; } /** * 查询新书，打折书，畅销书时通过PageSize来确定分页总数 * @param isNewBook * @param isSellWell * @param isCutPrice * @param pageSize * @return */ public int getPageCountByIsSellWellOrIsCutPriceOrIsNewBook(String isNewBook, String isSellWell, String isCutPrice, int pageSize ){ int pageCount = 0; int condition = 0; String sql = ""; if(isNewBook != null){ condition = Integer.parseInt(isNewBook); sql = "select count(*) from tb_bookinfo where is_newbook=?"; }else if(isSellWell != null){ condition = Integer.parseInt(isSellWell); sql = "select count(*) from tb_bookinfo where is_sellwell=?"; }else if(isCutPrice != null){ condition = Integer.parseInt(isCutPrice); sql = "select count(*) from tb_bookinfo where is_cutprice=?"; } try { conn = dbTools.getConn(); ps = conn.prepareStatement(sql); ps.setInt(1, condition); rs = ps.executeQuery(); while(rs.next()){ int rowCount = rs.getInt(1); if(rowCount%pageSize == 0){ pageCount = rowCount / pageSize; }else{ pageCount = rowCount / pageSize + 1; } } } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ try { dbTools.closeResource(); } catch (SQLException e) { e.printStackTrace(); } } return pageCount; } /** * 通过给出的条件查询出需要的书籍 * * @see com.bookshop.idao.BookIDAO#find(int, java.lang.String) */ public ArrayList<BookInfo> find(int category, String keyWord ,int pageNow ,int pageSize) { ArrayList<BookInfo> bookInfoList = new ArrayList<BookInfo>(); String sql = "select * from tb_bookinfo where category_id=? and book_name like '%" + keyWord + "%' limit ?,?"; try { conn = dbTools.getConn(); ps = conn.prepareStatement(sql); ps.setInt(1, category); ps.setInt(2, (pageNow-1)*pageSize); ps.setInt(3, pageSize); rs = ps.executeQuery(); while (rs.next()) { BookInfo bookInfo = new BookInfo(); bookInfo = putResultIntoBookInfo(rs); bookInfoList.add(bookInfo); } } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { dbTools.closeResource(); } catch (SQLException e) { e.printStackTrace(); } } return bookInfoList; } /** * 添加一本新书 */ public boolean addBook(BookInfo bookInfo) { boolean flag = false; try { conn = dbTools.getConn(); String sql = "insert into tb_bookinfo(book_name,auth_name,category_id,publish," + "ISBN,imag,price,vipprice,number,is_newbook," + "is_sellwell,is_cutprice,description) values(?,?,?,?,?,?,?,?,?,?,?,?,?)"; ps = conn.prepareStatement(sql); ps.setString(1, bookInfo.getBookName()); ps.setString(2, bookInfo.getAuthName()); ps.setInt(3, bookInfo.getCategoryId()); ps.setString(4, bookInfo.getPublish()); ps.setString(5, bookInfo.getISBN()); ps.setString(6, bookInfo.getImag()); ps.setFloat(7, bookInfo.getPrice()); ps.setFloat(8, bookInfo.getVipPrice()); ps.setInt(9, bookInfo.getStorage()); ps.setInt(10, bookInfo.getIsNewBook()); ps.setInt(11, bookInfo.getIsSellWell()); ps.setInt(12, bookInfo.getIsCutPrice()); ps.setString(13, bookInfo.getDescribe()); int result = ps.executeUpdate(); if (result == 1) { flag = true; } } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { dbTools.closeResource(); } catch (SQLException e) { e.printStackTrace(); } } return flag; } /** * 查询所有的书籍 * * @return */ public ArrayList<BookInfo> findAll() { ArrayList<BookInfo> bookInfoList = new ArrayList<BookInfo>(); try { conn = dbTools.getConn(); ps = conn.prepareStatement("select * from tb_bookinfo"); rs = ps.executeQuery(); while (rs.next()) { BookInfo bookInfo = new BookInfo(); bookInfo = putResultIntoBookInfo(rs); bookInfoList.add(bookInfo); } } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { dbTools.closeResource(); } catch (SQLException e) { e.printStackTrace(); } } return bookInfoList; } public ArrayList<BookInfo> findByPageSize(int pageNow, int pageSize) { ArrayList<BookInfo> bookInfoList = new ArrayList<BookInfo>(); try { conn = dbTools.getConn(); ps = conn .prepareStatement("select * from tb_bookinfo order by book_id desc limit ?,?"); ps.setInt(1, (pageNow - 1) * pageSize); ps.setInt(2, pageSize); rs = ps.executeQuery(); while (rs.next()) { BookInfo bookInfo = new BookInfo(); bookInfo = putResultIntoBookInfo(rs); bookInfoList.add(bookInfo); } } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { dbTools.closeResource(); } catch (SQLException e) { e.printStackTrace(); } } return bookInfoList; } /** * 通过id查询书籍信息 */ public BookInfo findByBookId(int bookId) { BookInfo bookInfo = new BookInfo(); String sql = "select * from tb_bookinfo where book_id=?"; try { conn = dbTools.getConn(); ps = conn.prepareStatement(sql); ps.setInt(1, bookId); rs = ps.executeQuery(); while (rs.next()) { bookInfo = putResultIntoBookInfo(rs); } } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return bookInfo; } /** * 修改书本信息 */ public boolean modifyBookInfo(BookInfo bookInfo) { boolean flag = false; String sql = "update tb_bookinfo set category_id=?,price=?,vipprice=?,number=?,is_newbook=?,is_sellwell=?,is_cutprice=? where book_id=?"; try { conn = dbTools.getConn(); ps = conn.prepareStatement(sql); ps.setInt(1, bookInfo.getCategoryId()); ps.setFloat(2, bookInfo.getPrice()); ps.setFloat(3, bookInfo.getVipPrice()); ps.setInt(4, bookInfo.getStorage()); ps.setInt(5, bookInfo.getIsNewBook()); ps.setInt(6, bookInfo.getIsSellWell()); ps.setInt(7, bookInfo.getIsCutPrice()); ps.setInt(8, bookInfo.getBookId()); int result = ps.executeUpdate(); if (result == 1) { flag = true; } } c