数据库分页语句.docVIP

1.分页语句 取出sql表中第31到40的记录（以自动增长ID为主键）select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id sql server方案2： select top 10 * from t where id in (select top 40 id from t order by id) order by id desc mysql方案：select * from t order by id limit 30,10 oracle方案：select * from (select rownum r,* from t where r=40) where r30 待整理进去的内容 pageSize=20; pageNo = 5; 1.分页技术1（直接利用sql语句进行分页，效率最高和最推荐的） mysql:sql = select * from articles limit + (pageNo-1)*pageSize + , + pageSize; oracle: sql = select * from + (select rownum r,* from + (select * from articles order by postime desc) + where rownum= + pageNo*pageSize +) tmp + where r + (pageNo-1)*pageSize; 注释：第7行保证rownum的顺序是确定的，因为oracle的索引会造成rownum返回不同的值 简洋提示：没有order by时，rownum按顺序输出，一旦有了order by，rownum不按顺序输出了，这说明rownum是排序前的编号。如果对order by从句中的字段建立了索引，那么，rownum也是按顺序输出的，因为这时候生成原始的查询结果集时会参照索引表的顺序来构建。 sqlserver:sql = select top 10 * from id not id(select top + (pageNo-1)*pageSize + id from articles) DataSource ds = new InitialContext().lookup(jndiurl); Connection cn = ds.getConnection(); //select * from user where id=? binary directive PreparedStatement pstmt = cn.prepareSatement(sql); ResultSet rs = pstmt.executeQuery() while(rs.next()) { out.println(rs.getString(1)); } 2.不可滚动的游标 pageSize=20; pageNo = 5; cn = null stmt = null; rs = null; try { sqlserver:sql = select * from articles; DataSource ds = new InitialContext().lookup(jndiurl); Connection cn = ds.getConnection(); //select * from user where id=? binary directive PreparedStatement pstmt = cn.prepareSatement(sql); ResultSet rs = pstmt.executeQuery() for(int j=0;j(pageNo-1)*pageSize;j++) { rs.next(); } int i=0; while(rs.next() i10) { i++; out.println(rs.getString(1)); } } cacth(){} finnaly { if(rs!=null)try{rs.close();}catch(Exception e){} if(stm if(cn } 3.可滚动的游标 pageSize=20; pageNo = 5; cn = null stmt = null; rs = null; try { sqlserver:sql = select * from articles; DataSource ds = new InitialContext().lookup(j