Goods:图书模块按分类查询各层实现

BookDao

 //按分类查询
      public PageBean<Book>  findByCategory(String cid,int pc) throws SQLException
      {
          List<Expression> exprList=new ArrayList<Expression>();
          exprList.add(new Expression("cid", "=", cid));
          return findByCriteria(exprList, pc);
          
      }
      
      //按书名做一个模糊查询
      public PageBean<Book>  findByBname(String bname,int pc) throws SQLException
      {
          List<Expression> exprList=new ArrayList<Expression>();
          exprList.add(new Expression("bname", "like", "%"+bname+"%"));
          return findByCriteria(exprList, pc);
          
      }
      
      //按作者查询
      public PageBean<Book>  findByAuthor(String author,int pc) throws SQLException
      {
          List<Expression> exprList=new ArrayList<Expression>();
          exprList.add(new Expression("author", "like", "%"+author+"%"));
          return findByCriteria(exprList, pc);
          
      }
      
      
      //按出版社查询
      public PageBean<Book>  findByPress(String press,int pc) throws SQLException
      {
          List<Expression> exprList=new ArrayList<Expression>();
          exprList.add(new Expression("press", "like", "%"+press+"%"));
          return findByCriteria(exprList, pc);
          
      }
      //多条件组合查询  book对象本身就是条件
      public PageBean<Book>  findByCombination(Book criteria,int pc) throws SQLException
      {
          List<Expression> exprList=new ArrayList<Expression>();
          exprList.add(new Expression("bname", "like", "%"+criteria.getBname()+"%"));
          exprList.add(new Expression("author", "like", "%"+criteria.getAuthor()+"%"));
          exprList.add(new Expression("press", "like", "%"+criteria.getPress()+"%"));
          return findByCriteria(exprList, pc);
          
      }
      
      //
      
      /*
       * 通用的查询方法
       */
      private PageBean<Book> findByCriteria(List<Expression> exprList,int pc) throws SQLException
      {  
          /*
           * 1、得到ps
           * 2得到tr 总记录数 通过 通过exprList生成where子句
           * 3得到beanlist
           * 4创建PageBean 返回
           */
          //得到ps
          int ps=PageConstants.BOOK_PAGE_SIZE;
          /*
           *总记录数 通过exprList生成where子句  select * from t_book where 1=1 and ..
           *条件语句不好控制所以为了后面统一  在前面先给个1=1 后面统一用and 开头 拼凑sql语句的一重要技巧
           */
          StringBuilder whereSql=new StringBuilder("where 1=1");
          List<Object> params=new ArrayList<Object>(); //它是对应问号的值
          
          for(Expression expr:exprList)
          {  
             whereSql.append(" and ").append(expr.getName()).append(" ").
             append(expr.getOpertator()).append(" "); 
              
             if(!expr.getOpertator().equals("is null"))
             {
                     whereSql.append("?");
                     params.add(expr.getValue());
             }
          }
          
          //总记录数
          String sql="select count(*) from t_book "+whereSql;
          Number number=(Number) qr.query(sql, new ScalarHandler(),params);
          int tr=number.intValue(); //得到了总记录数
          
          //得到beanList 即当前页记录
          sql = "select * from t_book " + whereSql + " order by orderBy limit ?,?";
          params.add((pc-1)*ps); //第一个问号 （2-1）*8 当前页首行记录的下标
          params.add(ps);  //一共查询几行
          List<Book> beanList=qr.query(sql, new BeanListHandler<Book>(Book.class),params.toArray());
          //丢了cid  若需要cid 改map 自己映射
          
          //创建pageBean 设置参数 其中pageBean 没有url这一项 这个任务由servlet来得到
          PageBean<Book> pb=new PageBean<Book>(); 
          pb.setPc(pc);
          pb.setPs(ps);
          pb.setTr(tr);
          pb.setBeanList(beanList);
     
          return pb;
      }
    

}

BookService

/*
 * 图书模块业务层
 */
public class BookService {
    private BookDao bookDao = new BookDao();

    public PageBean<Book> findByCategory(String cid, int pc) {

        try {
            return bookDao.findByCategory(cid, pc);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    // 按书名查询

    public PageBean<Book> findByBname(String bname, int pc) {

        try {
            return bookDao.findByBname(bname, pc);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    // 按作者查询
    public PageBean<Book> findByAuthor(String author, int pc) {

        try {
            return bookDao.findByAuthor(author, pc);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    // 按出版社查询
    public PageBean<Book> findByPress(String press, int pc) {

        try {
            return bookDao.findByAuthor(press, pc);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
   
    //多条件组合查询
    public PageBean<Book> findByCombination(Book criteria, int pc)
            throws SQLException {
        try {
            return bookDao.findByCombination(criteria, pc);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

}

BookServlet

//获取当前页码值
    private int getPc(HttpServletRequest req)
    {
        int pc=1;
        String param=req.getParameter("pc");
        if(param!=null&&!param.trim().isEmpty())
        {   
            try{
            pc=Integer.parseInt(param);
            }catch(RuntimeException e){}
        }
        return pc;
    }

    //获取url  截取url 页面中的分页导航中使用它作为超链接的目标  还带了条件 保证条件不丢
    private String getUrl(HttpServletRequest req)
    {    //http://localhost:8080//goods/BookServlet
        //getRequestURI()获取/goods/BookServlet
        //req.getQueryString()获取method=findByCategory&cid=xxx
        String url=req.getRequestURI()+"?"+req.getQueryString();
        //如果url中存在pc参数 截取掉 如果不存在则不用截取
        int index=url.lastIndexOf("&pc=");
        if(index!=-1)
        {
            url=url.substring(0,index);
        }
        return url;
    }

    public String findByCategory(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        
        //得到pc 如果页面传递使用页面的 如果没传pc=1
        int pc=getPc(req);
        
        //2得到url
        String url=getUrl(req);
        //3获取查询条件 本方法是cid 即分类id
        String cid=req.getParameter("cid");
        
        //使用pc cid 调用service 
        PageBean<Book> pb=bookService.findByCategory(cid,pc);
        //使用pageBean设置url 保存pageBean 转发到/jsps/book/list.jsp页面
         pb.setUrl(url); 
         req.setAttribute("pb", pb);
         return "f:/jsps/book/list.jsp";
       
        
    }

left.jsp

<ul>
        <c:forEach items="${pb.beanList}" var="book">
        <li>
                <div class="inner">
                    <a class="pic" href="<c:url value='/jsps/book/desc.jsp'/>"><img
                        src="<c:url value='/${book.image_b }'/>" border="0" /> </a>
                    <p class="price">
                        <span class="price_n">&yen;${book.currPrice}</span> 
                        <span class="price_r">&yen;${book.price}</span>
                        (<span class="price_s">${book.discount}折</span>)
                    </p>
                    <p>
                        <a id="bookname"
                            title="${book.bname }"
                            href="<c:url value='/jsps/book/desc.jsp'/>">${book.bname }</a>
                    </p>
                    <p>
                        <a href="<c:url value='/jsps/book/list.jsp'/>" name='P_zz'
                            title='Craig Walls'>${book.author }</a>
                    </p>
                    <p class="publishing">
                        <span>出 版 社：</span><a href="<c:url value='/jsps/book/list.jsp'/>">${book.press} }</a>
                    </p>
                    <p class="publishing_time">
                        <span>出版时间：</span>${book.publishtime}
                    </p>
                </div></li>
        </c:forEach>


    </ul>