JavaWEB快速入门之分页【09】

一、回顾oracle中的伪列

            rownum：伪列

           需求：根据伪列实现查询tb_news表中的第一条到第五条记录

           方案：将rownum伪列字段变成明列字段

           代码： select b.* from ( select a.*,rownum as rid from tb_news a ) b where b.rid between 1 and 5;

           具体显示：

二、javaweb分页

          实现javaweb中的数据分页将依赖于伪列

  （1）分析每一页显示多少条数据

  （2）找到每一页与条数之间的关系

  （3）将找到的sql规律利用到javaweb中

  （4）编写分页和显示条数的方法

          4.1 简单的分页和显示条数的方法功能

	public List<News> queryNewsAll3(int pageIndex,int pageSize) {
		//根据参数pageIndex和pageSize来计算区间查询的规律
		int start = (pageIndex-1)*pageSize +1;
		int end = pageIndex * pageSize;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> list = new ArrayList<News>();
		try {
			conn = DBHelper.getConn();
			String sql = "select b.* from ( select a.*,rownum as rid from tb_news a ) b where b.rid between "+start+" and "+end+"";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				//获取分类编号rs.getInt("ntid");
				Theme theme = new ThemeDaoImpl().getThemeByTid(rs.getInt("ntid"));
				list.add(new News(rs.getInt("nid"), theme, rs.getString("ntitle"), rs.getString("nauthor"),
						rs.getString("nsummary"), rs.getString("ncontent"),
						rs.getString("nimage"), rs.getString("ndate"), rs.getInt("ncount")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(conn, ps, rs);
		}
		return list;
	}


	@Override
	public int getNewsCount() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;//保存表的总记录数
		try {
			conn = DBHelper.getConn();
			String sql = "select count(*) from tb_news";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(conn, ps, rs);
		}
		return count;
	
	}

       4.2 携带模糊查询的分页和显示条数的方法功能

@Override
	public int getNewsCount(String strName) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;//保存表的总记录数
		try {
			conn = DBHelper.getConn();
			String sql = "select count(*) from tb_news where ntitle like '%"+strName+"%'";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(conn, ps, rs);
		}
		return count;
	
	}
	
	
	
	@Override
	public List<News> queryNewsAll5(int pageIndex,int pageSize,String strName) {
		//根据参数pageIndex和pageSize来计算区间查询的规律
		int start = (pageIndex-1)*pageSize +1;
		int end = pageIndex * pageSize;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> list = new ArrayList<News>();
		try {
			conn = DBHelper.getConn();
			String sql = "select b.* from (\r\n" + 
					"       select a.*,rownum as rid from (\r\n" + 
					"               select * from tb_news where ntitle like '%"+strName+"%'\r\n" + 
					"        )a\r\n" + 
					")b where b.rid between "+start+" and "+end+"";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				//获取分类编号rs.getInt("ntid");
				Theme theme = new ThemeDaoImpl().getThemeByTid(rs.getInt("ntid"));
				list.add(new News(rs.getInt("nid"), theme, rs.getString("ntitle"), rs.getString("nauthor"),
						rs.getString("nsummary"), rs.getString("ncontent"),
						rs.getString("nimage"), rs.getString("ndate"), rs.getInt("ncount")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(conn, ps, rs);
		}
		return list;
	}

  （5）主页代码显示（携带模糊查询）

                 <ul class="classlist">
					<%	
						request.setCharacterEncoding("utf-8");
						INewsDao ind = new NewsDaoImpl();
						int pageIndex = 1;
						int pageSize = 5;
						
						String pIndex = request.getParameter("pageIndex");
						if(null!=pIndex){
							pageIndex = Integer.valueOf(pIndex);
						}
						String strName = request.getParameter("strName");
						if(strName==null){
							strName="";
						}
						int count = ind.getNewsCount(strName); 
						int pageMax = 0;
						if(count % pageSize == 0){
							pageMax = count / pageSize;
						}else{
							pageMax = count / pageSize +1;
						}
						List<News> listNews = ind.queryNewsAll(pageIndex,pageSize,strName);
						for(News news:listNews){
					%>
						<li> 
							<!-- 新闻标题 -->
							<a href='#'><%=news.getNtitle() %></a> 
							<span> 
								作者：<%=news.getNauthor() %> 
								&#160;&#160;&#160;&#160; 
								<a href='#'>修改</a> 
								&#160;&#160;&#160;&#160; 
								<a href='javascript:void(0)' onclick='clickdel()'>删除</a>
							</span> </li>
					<%
						}
					%>
					
					<li class='space'></li>
					<p align="right" style = "font-size:15px;font-weight: bold"> 
						当前页数:[<%=pageIndex %>/<%=pageMax %>]&nbsp; 
						<a href="admin.jsp?pageIndex=1&strName=<%=null!=strName?strName:""%>">首页</a>
						<a href="admin.jsp?pageIndex=<%=pageIndex-1<0?1:pageIndex-1%>&strName=<%=null!=strName?strName:""%>">上一页</a>
						<a href="admin.jsp?pageIndex=<%=pageIndex+1>pageMax?pageMax:pageIndex+1%>&strName=<%=null!=strName?strName:""%>">下一页</a>
						<a href="admin.jsp?pageIndex=<%=pageMax%>&strName=<%=null!=strName?strName:""%>">末页</a> 
					</p>
				</ul>

   （6）结果界面显示