网上图书商城项目学习笔记-012BOOK模块查询2

一、分析

> 按图名查询（模糊）（分页）
> 按作者查询（分页）
> 按出版社查询（分页）
> 按id查询
> 多条件组合查询（分页）

二、代码

1.view层

(1）gj.jsp等

 1 <form action="<c:url value='/BookServlet'/>" method="get">
 2     <input type="hidden" name="method" value="findByCombination" />
 3     <table align="center">
 4         <tr>
 5             <td>书名：</td>
 6             <td>
 7                 <input type="text" name="bname" />
 8             </td>
 9         </tr>
10         <tr>
11             <td>作者：</td>
12             <td>
13                 <input type="text" name="author" />
14             </td>
15         </tr>
16         <tr>
17             <td>出版社：</td>
18             <td>
19                 <input type="text" name="press" />
20             </td>
21         </tr>
22         <tr>
23             <td>&nbsp;</td>
24             <td>
25                 <input type="submit" value="搜　　索" />
26                 <input type="reset" value="重新填写" />
27             </td>
28         </tr>
29     </table>
30 </form>

2.servlet层

(1）BookServlet.java

 1 /**
 2      * 按作者查
 3      * @param req
 4      * @param resp
 5      * @return
 6      * @throws ServletException
 7      * @throws IOException
 8      */
 9     public String findByAuthor(HttpServletRequest req, HttpServletResponse resp)
10             throws ServletException, IOException {
11         int currentPage = getCurrentPage(req);
12         String url = getUrl(req);
13         String author = req.getParameter("author");
14         PageBean<Book> pb = bookService.findByAuthor(author, currentPage);
15         pb.setUrl(url);
16         req.setAttribute("pb", pb);
17         return "f:/jsps/book/list.jsp";
18     }
19     
20     /**
21      * 按书名查
22      * @param req
23      * @param resp
24      * @return
25      * @throws ServletException
26      * @throws IOException
27      */
28     public String findByBname(HttpServletRequest req, HttpServletResponse resp)
29             throws ServletException, IOException {
30         int currentPage = getCurrentPage(req);
31         String url = getUrl(req);
32         String bname = req.getParameter("bname");
33         PageBean<Book> pb = bookService.findByBname(bname, currentPage);
34         pb.setUrl(url);
35         req.setAttribute("pb", pb);
36         return "f:/jsps/book/list.jsp";
37     }
38     
39     /**
40      * 按出版社查询
41      * @param req
42      * @param resp
43      * @return
44      * @throws ServletException
45      * @throws IOException
46      */
47     public String findByPress(HttpServletRequest req, HttpServletResponse resp)
48             throws ServletException, IOException {
49         int currentPage = getCurrentPage(req);
50         String url = getUrl(req);
51         String press = req.getParameter("press");
52         PageBean<Book> pb = bookService.findByPress(press, currentPage);
53         pb.setUrl(url);
54         req.setAttribute("pb", pb);
55         return "f:/jsps/book/list.jsp";
56     }
57     
58     /**
59      * 多条件组合查询
60      * @param req
61      * @param resp
62      * @return
63      * @throws ServletException
64      * @throws IOException
65      */
66     public String findByCombination(HttpServletRequest req, HttpServletResponse resp)
67             throws ServletException, IOException {
68         int currentPage = getCurrentPage(req);
69         String url = getUrl(req);
70         Book criteria = CommonUtils.toBean(req.getParameterMap(), Book.class);
71         PageBean<Book> pb = bookService.findByCombination(criteria, currentPage);
72         pb.setUrl(url);
73         req.setAttribute("pb", pb);
74         return "f:/jsps/book/list.jsp";
75     }
76     
77     /**
78      * 按bid查询
79      * @param req
80      * @param resp
81      * @return
82      * @throws ServletException
83      * @throws IOException
84      */
85     public String load(HttpServletRequest req, HttpServletResponse resp)
86             throws ServletException, IOException {
87         String bid = req.getParameter("bid");
88         Book book = bookService.load(bid);
89         req.setAttribute("book", book);
90         return "f:/jsps/book/desc.jsp";
91     }

3.service层

(1）BookService.java

 1 /**
 2      * 按书名查
 3      * @param bname
 4      * @param currentPage
 5      * @return
 6      */
 7     public PageBean<Book> findByBname(String bname, int currentPage) {
 8         try {
 9             return bookDao.findByBname(bname, currentPage);
10         } catch (SQLException e) {
11             throw new RuntimeException(e);
12         }
13     }
14     
15     /**
16      * 按作者查
17      * @param author
18      * @param currentPage
19      * @return
20      */
21     public PageBean<Book> findByAuthor(String author, int currentPage) {
22         try {
23             return bookDao.findByAuthor(author, currentPage);
24         } catch (SQLException e) {
25             throw new RuntimeException(e);
26         }
27     }
28     
29     /**
30      * 按出版社查
31      * @param author
32      * @param currentPage
33      * @return
34      */
35     public PageBean<Book> findByPress(String press, int currentPage) {
36         try {
37             return bookDao.findByPress(press, currentPage);
38         } catch (SQLException e) {
39             throw new RuntimeException(e);
40         }
41     }
42     
43     /**
44      * 多条件组合查询
45      * @param criteria
46      * @param currentPage
47      * @return
48      */
49     public PageBean<Book> findByCombination(Book criteria, int currentPage) {
50         try {
51             return bookDao.findByCombination(criteria, currentPage);
52         } catch (SQLException e) {
53             throw new RuntimeException(e);
54         }
55     }
56     
57     /**
58      * 加载图书
59      * @param bid
60      * @return
61      */
62     public Book load(String bid) {
63         try {
64             return bookDao.findById(bid);
65         } catch (SQLException e) {
66             throw new RuntimeException(e);
67         }
68     }

4.dao层

(1）BookDao.java

  1     /**
  2      * 按书名模糊查询
  3      * @param bname
  4      * @param currentPage
  5      * @return
  6      * @throws SQLException
  7      */
  8     public PageBean<Book> findByBname(String bname, int currentPage) throws SQLException {
  9         List<Expression> exprList = new ArrayList<Expression>();
 10         exprList.add(new Expression("bname", "like", "%" + bname + "%"));
 11         return findByCriteria(exprList, currentPage);
 12     }
 13     
 14     /**
 15      * 按作者查
 16      * @param author
 17      * @param currentpage
 18      * @return
 19      * @throws SQLException
 20      */
 21     public PageBean<Book> findByAuthor(String author, int currentpage) throws SQLException {
 22         List<Expression> exprList = new ArrayList<Expression>();
 23         exprList.add(new Expression("author", "like", "%" + author + "%"));
 24         return findByCriteria(exprList, currentpage);
 25     }
 26     
 27     /**
 28      * 按出版社查
 29      * @param press
 30      * @param currentpage
 31      * @return
 32      * @throws SQLException
 33      */
 34     public PageBean<Book> findByPress(String press, int currentpage) throws SQLException {
 35         List<Expression> exprList = new ArrayList<Expression>();
 36         exprList.add(new Expression("press", "like", "%" + press + "%"));
 37         return findByCriteria(exprList, currentpage);
 38     }
 39     
 40     /**
 41      * 多条件组合查询
 42      * @param criteria
 43      * @param currentPage
 44      * @return
 45      * @throws SQLException
 46      */
 47     public PageBean<Book> findByCombination(Book criteria, int currentPage) throws SQLException {
 48         List<Expression> exprList = new ArrayList<Expression>();
 49         exprList.add(new Expression("bname", "like", "%" + criteria.getBname() + "%"));
 50         exprList.add(new Expression("author", "like", "%" + criteria.getAuthor() + "%"));
 51         exprList.add(new Expression("press", "like", "%" + criteria.getPress() + "%"));
 52         return findByCriteria(exprList, currentPage);
 53     }
 54     
 55     /**
 56      * 通用的查询方法
 57      * @param exprList
 58      * @param currentPage
 59      * @return
 60      * @throws SQLException 
 61      */
 62     private PageBean<Book> findByCriteria(List<Expression> exprList,
 63             int currentPage) throws SQLException {
 64         /*
 65          * 1. 得到pageSize
 66          * 2. 得到totalRecords
 67          * 3. 得到beanList
 68          * 4. 创建PageBean，返回
 69          */
 70         /*
 71          * 1. 得到pageSize
 72          */
 73         int pageSize = PageConfig.BOOK_PAGE_SIZE;
 74         /*
 75          * 2. 通过exprList来生成where子句
 76          */
 77         StringBuilder whereSql = new StringBuilder(" where 1=1");
 78         List<Object> params = new ArrayList<Object>();
 79         for(Expression expr : exprList) {
 80             /*
 81              * 添加一个条件上，
 82              * 1) 以and开头
 83              * 2) 条件的名称
 84              * 3) 条件的运算符，可以是=、!=、>、< ... is null，is null没有值
 85              * 4) 如果条件不是is null，再追加问号，然后再向params中添加一与问号对应的值
 86              */
 87             whereSql.append(" and ").append(expr.getName())
 88                 .append(" ").append(expr.getOperator()).append(" ");
 89             // where 1=1 and bid = ?
 90             if(!expr.getOperator().equalsIgnoreCase("is null")) {
 91                 whereSql.append("?");
 92                 params.add(expr.getValue());
 93             }
 94         }
 95         
 96         /*
 97          * 3. 总记录数 
 98          */
 99         String sql = "select count(*) from t_book" + whereSql;
100         Number count = (Number) qr.query(sql, new ScalarHandler(), params.toArray());
101         int totalRecords = count.intValue();//得到了总记录数
102         /*
103          * 4. 得到beanList，即当前页记录
104          */
105         sql = "select * from t_book" + whereSql + " order by orderBy limit ?,?";
106         params.add((currentPage - 1) * pageSize);//当前页首行记录的下标
107         params.add(pageSize);//每页记录数
108         
109         List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class), params.toArray());
110         
111         /*
112          * 5. 创建PageBean，设置参数
113          */
114         PageBean<Book> pb = new PageBean<Book>();
115         /*
116          * 其中PageBean没有url，这个任务由Servlet完成
117          */
118         pb.setBeanList(beanList);
119         pb.setCurrentPage(currentPage);
120         pb.setPageSize(pageSize);
121         pb.setTotalRecords(totalRecords);
122         
123         return pb;
124     }
125     
126     /**
127      * 按bid查询
128      * @param bid
129      * @return
130      * @throws SQLException
131      */
132     public Book findById(String bid) throws SQLException {
133         String sql = "select * from t_book where bid=?";
134         return qr.query(sql, new BeanHandler<Book>(Book.class), bid);
135     }