一、分析
> 按图名查询（模糊）（分页）
> 按作者查询（分页）
> 按出版社查询（分页）
> 按id查询
> 多条件组合查询（分页）
二、代码
1.view层
(1）gj.jsp等
<form action="<c:url value='/BookServlet'/>" method="get">
<input type="hidden" name="method" value="findByCombination" />
<table align="center">
<tr>
<td>书名：</td>
<td>
<input type="text" name="bname" />
</td>
</tr>
<tr>
<td>作者：</td>
<td>
<input type="text" name="author" />
</td>
</tr>
<tr>
<td>出版社：</td>
<td>
<input type="text" name="press" />
</td>
</tr>
<tr>
<td> </td>
<td>
<input type="submit" value="搜 索" />
<input type="reset" value="重新填写" />
</td>
</tr>
</table>
</form>
2.servlet层
(1）BookServlet.java
/**
* 按作者查
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String findByAuthor(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
int currentPage = getCurrentPage(req);
String url = getUrl(req);
String author = req.getParameter("author");
PageBean<Book> pb = bookService.findByAuthor(author, currentPage);
pb.setUrl(url);
req.setAttribute("pb", pb);
return "f:/jsps/book/list.jsp";
} /**
* 按书名查
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String findByBname(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
int currentPage = getCurrentPage(req);
String url = getUrl(req);
String bname = req.getParameter("bname");
PageBean<Book> pb = bookService.findByBname(bname, currentPage);
pb.setUrl(url);
req.setAttribute("pb", pb);
return "f:/jsps/book/list.jsp";
} /**
* 按出版社查询
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String findByPress(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
int currentPage = getCurrentPage(req);
String url = getUrl(req);
String press = req.getParameter("press");
PageBean<Book> pb = bookService.findByPress(press, currentPage);
pb.setUrl(url);
req.setAttribute("pb", pb);
return "f:/jsps/book/list.jsp";
} /**
* 多条件组合查询
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String findByCombination(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
int currentPage = getCurrentPage(req);
String url = getUrl(req);
Book criteria = CommonUtils.toBean(req.getParameterMap(), Book.class);
PageBean<Book> pb = bookService.findByCombination(criteria, currentPage);
pb.setUrl(url);
req.setAttribute("pb", pb);
return "f:/jsps/book/list.jsp";
} /**
* 按bid查询
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String load(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String bid = req.getParameter("bid");
Book book = bookService.load(bid);
req.setAttribute("book", book);
return "f:/jsps/book/desc.jsp";
}
3.service层
(1）BookService.java
/**
* 按书名查
* @param bname
* @param currentPage
* @return
*/
public PageBean<Book> findByBname(String bname, int currentPage) {
try {
return bookDao.findByBname(bname, currentPage);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} /**
* 按作者查
* @param author
* @param currentPage
* @return
*/
public PageBean<Book> findByAuthor(String author, int currentPage) {
try {
return bookDao.findByAuthor(author, currentPage);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} /**
* 按出版社查
* @param author
* @param currentPage
* @return
*/
public PageBean<Book> findByPress(String press, int currentPage) {
try {
return bookDao.findByPress(press, currentPage);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} /**
* 多条件组合查询
* @param criteria
* @param currentPage
* @return
*/
public PageBean<Book> findByCombination(Book criteria, int currentPage) {
try {
return bookDao.findByCombination(criteria, currentPage);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} /**
* 加载图书
* @param bid
* @return
*/
public Book load(String bid) {
try {
return bookDao.findById(bid);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
4.dao层
(1）BookDao.java
/**
* 按书名模糊查询
* @param bname
* @param currentPage
* @return
* @throws SQLException
*/
public PageBean<Book> findByBname(String bname, int currentPage) throws SQLException {
List<Expression> exprList = new ArrayList<Expression>();
exprList.add(new Expression("bname", "like", "%" + bname + "%"));
return findByCriteria(exprList, currentPage);
} /**
* 按作者查
* @param author
* @param currentpage
* @return
* @throws SQLException
*/
public PageBean<Book> findByAuthor(String author, int currentpage) throws SQLException {
List<Expression> exprList = new ArrayList<Expression>();
exprList.add(new Expression("author", "like", "%" + author + "%"));
return findByCriteria(exprList, currentpage);
} /**
* 按出版社查
* @param press
* @param currentpage
* @return
* @throws SQLException
*/
public PageBean<Book> findByPress(String press, int currentpage) throws SQLException {
List<Expression> exprList = new ArrayList<Expression>();
exprList.add(new Expression("press", "like", "%" + press + "%"));
return findByCriteria(exprList, currentpage);
} /**
* 多条件组合查询
* @param criteria
* @param currentPage
* @return
* @throws SQLException
*/
public PageBean<Book> findByCombination(Book criteria, int currentPage) 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, currentPage);
} /**
* 通用的查询方法
* @param exprList
* @param currentPage
* @return
* @throws SQLException
*/
private PageBean<Book> findByCriteria(List<Expression> exprList,
int currentPage) throws SQLException {
/*
* 1. 得到pageSize
* 2. 得到totalRecords
* 3. 得到beanList
* 4. 创建PageBean，返回
*/
/*
* 1. 得到pageSize
*/
int pageSize = PageConfig.BOOK_PAGE_SIZE;
/*
* 2. 通过exprList来生成where子句
*/
StringBuilder whereSql = new StringBuilder(" where 1=1");
List<Object> params = new ArrayList<Object>();
for(Expression expr : exprList) {
/*
* 添加一个条件上，
* 1) 以and开头
* 2) 条件的名称
* 3) 条件的运算符，可以是=、!=、>、< ... is null，is null没有值
* 4) 如果条件不是is null，再追加问号，然后再向params中添加一与问号对应的值
*/
whereSql.append(" and ").append(expr.getName())
.append(" ").append(expr.getOperator()).append(" ");
// where 1=1 and bid = ?
if(!expr.getOperator().equalsIgnoreCase("is null")) {
whereSql.append("?");
params.add(expr.getValue());
}
} /*
* 3. 总记录数
*/
String sql = "select count(*) from t_book" + whereSql;
Number count = (Number) qr.query(sql, new ScalarHandler(), params.toArray());
int totalRecords = count.intValue();//得到了总记录数
/*
* 4. 得到beanList，即当前页记录
*/
sql = "select * from t_book" + whereSql + " order by orderBy limit ?,?";
params.add((currentPage - 1) * pageSize);//当前页首行记录的下标
params.add(pageSize);//每页记录数 List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class), params.toArray()); /*
* 5. 创建PageBean，设置参数
*/
PageBean<Book> pb = new PageBean<Book>();
/*
* 其中PageBean没有url，这个任务由Servlet完成
*/
pb.setBeanList(beanList);
pb.setCurrentPage(currentPage);
pb.setPageSize(pageSize);
pb.setTotalRecords(totalRecords); return pb;
} /**
* 按bid查询
* @param bid
* @return
* @throws SQLException
*/
public Book findById(String bid) throws SQLException {
String sql = "select * from t_book where bid=?";
return qr.query(sql, new BeanHandler<Book>(Book.class), bid);
}