一.重构-提取公用方法
1.为了进行公共方法的抽取，需要找出上面实习中的可通用部分，和差异化部分
- .只要是分页，就会统计总记录数，而总记录数的统计是在业务sql外封装了一个select count(*)是有规律可循的，可以通用
- 只要是分页，则封装分页sql也是有规律可循的（在业务sql后加limit子句即可），可以通用
- 因为每个查询对应的业务实体（即模型）不同，所以ORM映射部分不能通用
2.公用方法封装思路
- 将可通用的部分封装到模板中
- 差异化部分（即不可通用部分），可以定义一个处理接口，以便于通过参数传入个性化的实现部分
3. 具体实现
通用分页查询模板类：
public final class DBTemplate { private DBTemplate() { } public static interface IORMConvert<T> { List<T> convert(ResultSet rs) throws SQLException; } public static <T> List<T> query(String sql, Object[] params, PageBean pageBean, IORMConvert<T> convert) { List<T> datas = new ArrayList<>(); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; //不需要分页 if (pageBean == null || !pageBean.isPagination()) { try { con = DBUtil.getConection(); ps = con.prepareStatement(sql); setParam(params, ps); rs = ps.executeQuery(); datas = convert.convert(rs); return datas; } catch(Exception e) { e.printStackTrace(); } finally { DBUtil.closeDB(rs, ps, con); } } else { //1. 查询总记录数 //2. 查询当前页数据 //1. 生成统计总记录数的SQL， 查询总记录数 try { String countSql = "select count(*) from (" + sql + ") tmp"; con = DBUtil.getConection(); ps = con.prepareStatement(countSql); setParam(params, ps); rs = ps.executeQuery(); while(rs.next()) { pageBean.setTotal(rs.getInt(1)); } /* * 如果统计的总记录数为0，则表示没有符合条件的记录，直接返回一个空结果集即可。 */ if(pageBean.getTotal() == 0) { return datas; } } catch (Exception e) { e.printStackTrace(); } finally { if(pageBean.getTotal() == 0) { DBUtil.closeDB(rs, ps, con); } DBUtil.closeDB(rs, ps); } //查询当前页数据 try { String pagingSql = sql + " limit " + pageBean.getStartRow() + ", " + pageBean.getRows(); ps = con.prepareStatement(pagingSql); setParam(params, ps); rs = ps.executeQuery(); datas = convert.convert(rs); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.closeDB(rs, ps, con); } } return datas; } private static void setParam(Object[] params, PreparedStatement ps) throws SQLException { if (params != null) { int i = 1; for (Object param : params) { ps.setObject(i, param); i++; } } } }
使用示例：
public class StudentDao2 { public List<Student> getStudents(String sname, PageBean pageBean) { String sql = "select * from t_student where sname like ?"; return DaoTemplate.query(sql, new Object[] {sname}, pageBean, new IORMConvert<Student>() { @Override public List<Student> convert(ResultSet rs) throws SQLException { List<Student> stus = new ArrayList<>(); while(rs.next()) { Student stu = new Student(); stu.setSid(rs.getInt("sid")); stu.setSname(rs.getString("sname")); stu.setAge(rs.getInt("age")); stu.setRemark(rs.getString("remark")); stus.add(stu); } return stus; } }); } public static void main(String[] args) { StudentDao2 dao = new StudentDao2(); PageBean pageBean = new PageBean(); pageBean.setPage(3); List<Student> students = dao.getStudents("张%", pageBean); students.forEach(s -> System.out.println(s)); } }
二.分页标签
2.1 准备一个Servlet
准备一个servlet用于处理请求，获取数据库中的数据，并转发到结果显示页面
@WebServlet(value = "/students") public class StudentAction extends HttpServlet { private static final long serialVersionUID = 3152900867611381148L; private StudentDao2 studentDao = new StudentDao2(); @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PageBean pageBean = new PageBean(); pageBean.setRequest(request); request.setAttribute("pageBean", pageBean); String sname = request.getParameter("sname"); List<Student> students = studentDao.getStudents(sname+"%", pageBean); request.setAttribute("students", students); System.out.println("dopost .......... "); request.getRequestDispatcher("/students/stuList.jsp").forward(request, response); } }
3.2 结果展示页面
创建一个页面，该页面用于显示结果， 使用jstl的c标签来展示结果，为正常使用c标签，需要引入jstl-1.2.jar和standard-1.1.2.jar。
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%> <%@taglib prefix="z" uri="/zking" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Insert title here</title> </head> <body> <h1>学生信息</h1> <form action="<%=request.getContextPath()%>/students" method="post"> <input type="text" name="sname"> <input type="submit" value="查询"> </form> <table border="1" style="width: 98%;"> <tr> <td>学号</td> <td>姓名</td> <td>年龄</td> <td>备注</td> </tr> <c:forEach items="${students}" var="student"> <tr> <td>${student.sid}</td> <td>${student.sname}</td> <td>${student.age}</td> <td>${student.remark}</td> </tr> </c:forEach> <z:paging pageBean="${pageBean}"/> </table> </body> </html>
