一、分析
> 按图名查询（模糊）（分页）
> 按作者查询（分页）
> 按出版社查询（分页）
> 按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> </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 }