JavaWeb_09 分页

文章目录

• 一、回顾oracle中伪列
• 二、JavaWeb分页

---

一、回顾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）编写分页和显示条数的方法

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

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

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

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