【DRP】Model1与Model2分别实现分页查询

我不是女神ヾ 2022-06-18 10:49 197阅读 0赞

小编在上一篇博客中已经说了对Model1与Model2有了一个初步的了解，这一篇内容就是我们对他们关于分页查询的实现。

# 【Model1分页查询用户】 #

![Center][]

## UserManager ##

/** * 分页查询 * @param pageNo 第几页 * @param pageSize 每页多少条数据 * @return pageModel */ public PageModel<User> findUserList(int pageNo, int pageSize) { StringBuffer sbSql = new StringBuffer(); sbSql.append("select user_id, user_name, password, contact_tel, email, create_date ") .append("from ") .append("( ") .append("select rownum rn, user_id, user_name, password, contact_tel, email, create_date ") .append("from ") .append("( ") .append("select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id <> 'root' order by user_id ") .append(") where rownum <= ? ") .append(") where rn > ? "); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; PageModel<User> pageModel = null; try { conn = DbUtil.getConnection(); pstmt = conn.prepareStatement(sbSql.toString()); pstmt.setInt(1, pageNo * pageSize); pstmt.setInt(2, (pageNo - 1) * pageSize); rs = pstmt.executeQuery(); List<User> userList = new ArrayList<User>(); while (rs.next()) { User user = new User(); user.setUserId(rs.getString("user_id")); user.setUserName(rs.getString("user_name")); user.setPassword(rs.getString("password")); user.setContactTel(rs.getString("contact_tel")); user.setEmail(rs.getString("email")); user.setCreateDate(rs.getTimestamp("create_date")); userList.add(user); } pageModel = new PageModel<User>(); pageModel.setList(userList); pageModel.setTotalRecords(getTotalRecords(conn)); pageModel.setPageSize(pageSize); pageModel.setPageNo(pageNo); }catch(SQLException e) { e.printStackTrace(); }finally { DbUtil.close(rs); DbUtil.close(pstmt); DbUtil.close(conn); } return pageModel; } /** * 取得总记录数 * @param conn * @return */ private int getTotalRecords(Connection conn) throws SQLException { String sql = "select count(*) from t_user where user_id <> 'root'"; PreparedStatement pstmt = null; ResultSet rs = null; int count = 0; try { pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); rs.next(); count = rs.getInt(1); }finally { DbUtil.close(rs); DbUtil.close(pstmt); } return count; }

## user\_maint.jsp ##

int pageNo=1; //页号 int pageSize=4; //每页记录数 String pageNoString = request.getParameter("pageNo");//取得分页页号 if (pageNoString != null) { pageNo = Integer.parseInt(pageNoString); //转换成int } PageModel<User> pageModel=UserManager.getInstance().findUserList(pageNo, pageSize);

共 <%=pageModel.getTotalPages() %> 页      当前第  <%=pageModel.getPageNo() %>  页

# 【Model2分页查询物料】 #

![Center 1][]

## ItemDao ##

/** * 根据条件分页查询 * @param pageNo * @param pageSize * @param condation * @return */ public PageModel findItemList(Connection conn, int pageNo, int pageSize, String condation);

## ItemDao4OracleImpl ##

public PageModel findItemList(Connection conn, int pageNo, int pageSize, String condation) { StringBuffer sbSql = new StringBuffer(); sbSql.append("select * ") .append("from (") .append("select i.*, rownum rn from (") .append("select a.item_no, a.item_name, a.spec, a.pattern, a.item_category_id, ") .append("b.name as item_category_name, a.item_unit_id, c.name as item_unit_name, a.file_name ") .append("from t_items a, t_data_dict b, t_data_dict c ") .append("where a.item_category_id=b.id and a.item_unit_id=c.id "); if (condation != null && !"".equals(condation)) { sbSql.append(" and (a.item_no like '" + condation + "%' or a.item_name like '" + condation + "%') "); } sbSql.append(" order by a.item_no") .append(") i where rownum<=? ") .append(") ") .append("where rn >? "); System.out.println("sql=" + sbSql.toString()); //通常采用日志组件记录，如log4j, 级别：info,debug,error... PreparedStatement pstmt = null; ResultSet rs = null; PageModel pageModel = null; try { pstmt = conn.prepareStatement(sbSql.toString()); pstmt.setInt(1, pageNo * pageSize); pstmt.setInt(2, (pageNo - 1) * pageSize); rs = pstmt.executeQuery(); List itemList = new ArrayList(); while (rs.next()) { Item item = new Item(); item.setItemNo(rs.getString("item_no")); item.setItemName(rs.getString("item_name")); item.setSpec(rs.getString("spec")); item.setPattern(rs.getString("pattern")); //构造ItemCategory ItemCategory ic = new ItemCategory(); ic.setId(rs.getString("item_category_id")); ic.setName(rs.getString("item_category_name")); item.setItemCategory(ic); //构造ItemUnit ItemUnit iu = new ItemUnit(); iu.setId(rs.getString("item_unit_id")); iu.setName(rs.getString("item_unit_name")); item.setItemUnit(iu); item.setFileName(rs.getString("file_name")); itemList.add(item); } pageModel = new PageModel(); pageModel.setPageNo(pageNo); pageModel.setPageSize(pageSize); pageModel.setList(itemList); //根据条件取得记录数 int totalRecords = getTotalRecords(conn, condation); pageModel.setTotalRecords(totalRecords); }catch(SQLException e) { e.printStackTrace(); //记录到日志文件 error throw new ApplicationException("分页查询失败"); }finally { DbUtil.close(rs); DbUtil.close(pstmt); } return pageModel; } /** * 根据条件取得记录数 * @param conn * @param queryStr * @return */ private int getTotalRecords(Connection conn, String condation) throws SQLException { String sql = "select count(*) from t_items "; if (condation != null && !"".equals(condation)) { sql+="where item_no like '" + condation + "%' or item_name like '" + condation + "%' "; } PreparedStatement pstmt = null; ResultSet rs = null; int temp = 0; try { pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); rs.next(); temp = rs.getInt(1); }finally { DbUtil.close(rs); DbUtil.close(pstmt); } return temp; }

## ItemDaoFactory ##

/** * 创建物料Dao * @return */ public ItemDao createItemDao();

## ItemDaoFactory4Oracle ##

public ItemDao createItemDao() { return new ItemDao4OracleImpl(); }

## ItemManager ##

/** * 根据条件分页查询 * @param pageNo * @param pageSize * @param condation * @return */ public PageModel findItemList(int pageNo, int pageSize, String condation);

## ItemManagerImpl ##

public PageModel findItemList(int pageNo, int pageSize, String condation) { Connection conn = null; try { conn = DbUtil.getConnection(); return itemDao.findItemList(conn, pageNo, pageSize, condation); }finally { DbUtil.close(conn); } }

## SearchItemServlet ##

@Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { int pageNo = 1; int pageSize = Integer.parseInt(this.getServletContext().getInitParameter("page-size")); String pageNoString = request.getParameter("pageNo"); if (pageNoString != null && !"".equals(pageNoString)) { pageNo = Integer.parseInt(pageNoString); } //取得条件 String itemNoOrName = request.getParameter("itemNoOrName"); //ItemManager itemManager = new ItemManagerImpl(); PageModel pageModel = itemManager.findItemList(pageNo, pageSize, itemNoOrName); request.setAttribute("pageModel", pageModel); request.getRequestDispatcher("/basedata/item_maint.jsp").forward(request, response); }

小编在model2中用到了工厂方法模式如果有看不懂得地方请参考[【DRP】——工厂模式][DRP]

感谢您的阅读！

[Center]: /images/20220618/21c075af027f48e3807644fc7cb5070c.png
[Center 1]: /images/20220618/ef07615550374a98a69651410e5d6dc0.png
[DRP]: http://blog.csdn.net/tengliu6/article/details/63688201