数据库分页技术
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
