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

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

【Model1分页查询用户】

             

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);

<font color="#FFFFFF"> 共 <%=pageModel.getTotalPages() %> 页</font>     
<font color="#FFFFFF">当前第</font> 
<font color="#FF0000"><%=pageModel.getPageNo() %></font> 
<font color="#FFFFFF">页</font>

【Model2分页查询物料】

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】——工厂模式

 感谢您的阅读！