数据库分页

数据库分页技术

1.sql server分页是使用top来分页的，下面是是代码

public class SysAdminDao {
 
 public static final String SEARCHSQLPageall =
  "select"             + ENTER +
  "  count(*) "       + ENTER +
  "from "              + ENTER +
  "    administrator"       + ENTER +
  "where "    + ENTER +
  "   deleteFlg = 1";
 
 public static final String SEARCHSQLFenye =
  "select"             + ENTER  ;
 

 public int pageall(int pageno){
  ResultSet rs = DBmanager.executeQuery(SEARCHSQLPageall);
  int pageall=0;
  int allinfo=0;
  try {
   while(rs.next()) {   
    
    allinfo = rs.getInt(1);
     }
  } catch (SQLException e) {
   // TODO 自动生成 catch 块
   e.printStackTrace();
  }
  if((allinfo%pageno)==0){
  pageall = (allinfo/pageno);
  }else{
  pageall = (allinfo/pageno)+1;
  }
  return pageall;
 };
 

 
 public ArrayList<SysAdmin> findAllFenye(int nowpage, int pageno) {
  StringBuilder sql = new StringBuilder(SEARCHSQLFenye);
  sql.append(" top " + pageno);
  sql.append(" admin_ID,admin_Name"+ ENTER ) ;
  sql.append(" from administrator"+ ENTER ) ;
  sql.append(" where deleteFlg = 1"+ ENTER ) ;
  sql.append(" and admin_ID not in"+ ENTER ) ;
  sql.append(" (select top"+ ENTER )  ;
  sql.append( pageno*(nowpage-1)+  "admin_ID   from administrator  where deleteFlg = 1 order by admin_ID asc)");
  sql.append(" order by admin_ID asc");
  ArrayList<SysAdmin> admins = new ArrayList<SysAdmin>();
  ResultSet rs = DBmanager.executeQuery(sql.toString());
  System.out.println(sql.toString());
  try {
   while(rs.next()) {   
    SysAdmin admin = new SysAdmin();
    admin = new SysAdmin();
    admin.setAdmin_ID(rs.getString("admin_ID"));
    admin.setAdmin_Name(rs.getString("admin_Name"));
   
    
    admins.add(admin);
    
   }
  } catch (SQLException e) {
   // TODO 自动生成 catch 块
   e.printStackTrace();
  }
  return admins;
 }

 

2.mysql 使用limit进行分页

 

select * from table   limit (当前页数-1)*每页显示的条数，每页显示的条数

1. select * from tablename <条件语句> limit 100,15

从100条记录后开始取15条 (实际取取的是第101-115条数据)

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

2. select * from tablename <条件语句> limit 100,-1

从第100条后开始到最后一条记录

3. select * from tablename <条件语句> limit 15

相当于limit 0,15   查询结果取前15条数据

3.oracle 使用rownum进行分页

 

select * from (select rownum r,empno,ename from emp where rownum <10) t where t.r>5