物料分页查询

最新推荐文章于 2024-08-12 11:30:04 发布

北风剑客

最新推荐文章于 2024-08-12 11:30:04 发布

阅读量444

点赞数

分类专栏： project 文章标签：分页

本文链接：https://blog.csdn.net/xiongwt/article/details/38351949

版权

project 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

DaoImpl.java

public PageModel findItemList(Connection conn, int pageNo, int pageSize,
			String condition) {
		StringBuffer sbSql = new StringBuffer();
		//物料分页查询SQL
		sbSql.append("select * ")
				.append("from (")
					.append("select i.*,rownum rn") 
						.append("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")
							  .append(" from t_items a, t_data_dict b, t_data_dict c")
							 .append(" where a.item_category_id = b.id")
							   .append(" and a.item_unit_id = c.id")
							   .append(" order by a.item_no) i")
							   .append(" where rownum<=?) ")
							   .append("where rn>?");
		System.out.println("sql=" + sbSql.toString());
		
		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 ic = new ItemCategory();
				ic.setId(rs.getString("item_category_id"));
				ic.setName(rs.getString("item_category_name"));
				item.setItemCategory(ic);
				
				ItemUnit iu = new ItemUnit();
				iu.setId(rs.getString("item_unit_id"));
				iu.setName(rs.getString("item_unit_name"));
				item.setItemUnit(iu);
				
				itemList.add(item);
			}
			pageModel = new PageModel();
			pageModel.setPageNo(pageNo);
			pageModel.setPageSize(pageSize);
			pageModel.setList(itemList);
			
			//根据条件取得记录数
			int totalRecords = getTotalRecords(conn,condition);
			pageModel.setTotalRecords(totalRecords);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new ApplicationException("分页查询失败");
		}
		return pageModel;
	}

/**
	 * 根据条件取得记录数
	 * @param conn
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	private int getTotalRecords(Connection conn, String condition) throws SQLException {
		String sql = "select count(*) from t_items";
		if(condition!=null && !"".equals(condition)){
			sql = "where item_no like '%"+condition+"%' or item_name like '%"+condition+"%'";
		}
		int temp = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			temp = rs.getInt(0);
		} finally{
			DbUtil.close(rs);
			DbUtil.close(pstmt);
		}
		return temp;
	}

ManagerImpl.java

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

servlet

public class SearchItemServlet extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		int pageNo = 1;
		//从application范围内取得page-size,application指的是ServletContext对象
		int pageSize = Integer.parseInt(this.getServletContext().getInitParameter("page-size"));
		
		//点上、下页时传入的pageNo
		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);
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doGet(req,resp);
	}
	
}

<context-param>
<param-name>page-size</param-name>
<param-value>3</param-value>
</context-param>