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