BookDao
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 //按分类查询 2 public PageBean<Book> findByCategory(String cid,int pc) throws SQLException 3 { 4 List<Expression> exprList=new ArrayList<Expression>(); 5 exprList.add(new Expression("cid", "=", cid)); 6 return findByCriteria(exprList, pc); 7 8 } 9 10 //按书名做一个模糊查询 11 public PageBean<Book> findByBname(String bname,int pc) throws SQLException 12 { 13 List<Expression> exprList=new ArrayList<Expression>(); 14 exprList.add(new Expression("bname", "like", "%"+bname+"%")); 15 return findByCriteria(exprList, pc); 16 17 } 18 19 //按作者查询 20 public PageBean<Book> findByAuthor(String author,int pc) throws SQLException 21 { 22 List<Expression> exprList=new ArrayList<Expression>(); 23 exprList.add(new Expression("author", "like", "%"+author+"%")); 24 return findByCriteria(exprList, pc); 25 26 } 27 28 29 //按出版社查询 30 public PageBean<Book> findByPress(String press,int pc) throws SQLException 31 { 32 List<Expression> exprList=new ArrayList<Expression>(); 33 exprList.add(new Expression("press", "like", "%"+press+"%")); 34 return findByCriteria(exprList, pc); 35 36 } 37 //多条件组合查询 book对象本身就是条件 38 public PageBean<Book> findByCombination(Book criteria,int pc) throws SQLException 39 { 40 List<Expression> exprList=new ArrayList<Expression>(); 41 exprList.add(new Expression("bname", "like", "%"+criteria.getBname()+"%")); 42 exprList.add(new Expression("author", "like", "%"+criteria.getAuthor()+"%")); 43 exprList.add(new Expression("press", "like", "%"+criteria.getPress()+"%")); 44 return findByCriteria(exprList, pc); 45 46 } 47 48 // 49 50 /* 51 * 通用的查询方法 52 */ 53 private PageBean<Book> findByCriteria(List<Expression> exprList,int pc) throws SQLException 54 { 55 /* 56 * 1、得到ps 57 * 2\得到tr 总记录数 通过 通过exprList生成where子句 58 * 3\得到beanlist 59 * 4\创建PageBean 返回 60 */ 61 //得到ps 62 int ps=PageConstants.BOOK_PAGE_SIZE; 63 /* 64 *总记录数 通过exprList生成where子句 select * from t_book where 1=1 and .. 65 *条件语句不好控制所以为了后面统一 在前面先给个1=1 后面统一用and 开头 拼凑sql语句的一重要技巧 66 */ 67 StringBuilder whereSql=new StringBuilder("where 1=1"); 68 List<Object> params=new ArrayList<Object>(); //它是对应问号的值 69 70 for(Expression expr:exprList) 71 { 72 whereSql.append(" and ").append(expr.getName()).append(" "). 73 append(expr.getOpertator()).append(" "); 74 75 if(!expr.getOpertator().equals("is null")) 76 { 77 whereSql.append("?"); 78 params.add(expr.getValue()); 79 } 80 } 81 82 //总记录数 83 String sql="select count(*) from t_book "+whereSql; 84 Number number=(Number) qr.query(sql, new ScalarHandler(),params); 85 int tr=number.intValue(); //得到了总记录数 86 87 //得到beanList 即当前页记录 88 sql = "select * from t_book " + whereSql + " order by orderBy limit ?,?"; 89 params.add((pc-1)*ps); //第一个问号 (2-1)*8 当前页首行记录的下标 90 params.add(ps); //一共查询几行 91 List<Book> beanList=qr.query(sql, new BeanListHandler<Book>(Book.class),params.toArray()); 92 //丢了cid 若需要cid 改map 自己映射 93 94 //创建pageBean 设置参数 其中pageBean 没有url这一项 这个任务由servlet来得到 95 PageBean<Book> pb=new PageBean<Book>(); 96 pb.setPc(pc); 97 pb.setPs(ps); 98 pb.setTr(tr); 99 pb.setBeanList(beanList); 100 101 return pb; 102 } 103 104 105 }
BookService
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 /* 2 * 图书模块业务层 3 */ 4 public class BookService { 5 private BookDao bookDao = new BookDao(); 6 7 public PageBean<Book> findByCategory(String cid, int pc) { 8 9 try { 10 return bookDao.findByCategory(cid, pc); 11 } catch (SQLException e) { 12 throw new RuntimeException(e); 13 } 14 15 } 16 17 // 按书名查询 18 19 public PageBean<Book> findByBname(String bname, int pc) { 20 21 try { 22 return bookDao.findByBname(bname, pc); 23 } catch (SQLException e) { 24 throw new RuntimeException(e); 25 } 26 27 } 28 29 // 按作者查询 30 public PageBean<Book> findByAuthor(String author, int pc) { 31 32 try { 33 return bookDao.findByAuthor(author, pc); 34 } catch (SQLException e) { 35 throw new RuntimeException(e); 36 } 37 38 } 39 40 // 按出版社查询 41 public PageBean<Book> findByPress(String press, int pc) { 42 43 try { 44 return bookDao.findByAuthor(press, pc); 45 } catch (SQLException e) { 46 throw new RuntimeException(e); 47 } 48 49 } 50 51 //多条件组合查询 52 public PageBean<Book> findByCombination(Book criteria, int pc) 53 throws SQLException { 54 try { 55 return bookDao.findByCombination(criteria, pc); 56 } catch (SQLException e) { 57 throw new RuntimeException(e); 58 } 59 60 } 61 62 }
BookServlet
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 //获取当前页码值 2 private int getPc(HttpServletRequest req) 3 { 4 int pc=1; 5 String param=req.getParameter("pc"); 6 if(param!=null&&!param.trim().isEmpty()) 7 { 8 try{ 9 pc=Integer.parseInt(param); 10 }catch(RuntimeException e){} 11 } 12 return pc; 13 } 14 15 //获取url 截取url 页面中的分页导航中使用它作为超链接的目标 还带了条件 保证条件不丢 16 private String getUrl(HttpServletRequest req) 17 { //http://localhost:8080//goods/BookServlet 18 //getRequestURI()获取/goods/BookServlet 19 //req.getQueryString()获取method=findByCategory&cid=xxx 20 String url=req.getRequestURI()+"?"+req.getQueryString(); 21 //如果url中存在pc参数 截取掉 如果不存在则不用截取 22 int index=url.lastIndexOf("&pc="); 23 if(index!=-1) 24 { 25 url=url.substring(0,index); 26 } 27 return url; 28 } 29 30 public String findByCategory(HttpServletRequest req, HttpServletResponse resp) 31 throws ServletException, IOException { 32 33 //得到pc 如果页面传递使用页面的 如果没传pc=1 34 int pc=getPc(req); 35 36 //2得到url 37 String url=getUrl(req); 38 //3获取查询条件 本方法是cid 即分类id 39 String cid=req.getParameter("cid"); 40 41 //使用pc cid 调用service 42 PageBean<Book> pb=bookService.findByCategory(cid,pc); 43 //使用pageBean设置url 保存pageBean 转发到/jsps/book/list.jsp页面 44 pb.setUrl(url); 45 req.setAttribute("pb", pb); 46 return "f:/jsps/book/list.jsp"; 47 48 49 }
left.jsp
1 <ul> 2 <c:forEach items="${pb.beanList}" var="book"> 3 <li> 4 <div class="inner"> 5 <a class="pic" href="<c:url value='/jsps/book/desc.jsp'/>"><img 6 src="<c:url value='/${book.image_b }'/>" border="0" /> </a> 7 <p class="price"> 8 <span class="price_n">¥${book.currPrice}</span> 9 <span class="price_r">¥${book.price}</span> 10 (<span class="price_s">${book.discount}折</span>) 11 </p> 12 <p> 13 <a id="bookname" 14 title="${book.bname }" 15 href="<c:url value='/jsps/book/desc.jsp'/>">${book.bname }</a> 16 </p> 17 <p> 18 <a href="<c:url value='/jsps/book/list.jsp'/>" name='P_zz' 19 title='Craig Walls'>${book.author }</a> 20 </p> 21 <p class="publishing"> 22 <span>出 版 社:</span><a href="<c:url value='/jsps/book/list.jsp'/>">${book.press} }</a> 23 </p> 24 <p class="publishing_time"> 25 <span>出版时间:</span>${book.publishtime} 26 </p> 27 </div></li> 28 </c:forEach> 29 30 31 </ul>