ITPub博客

首页 > 数据库 > Oracle > 数据库分页问题

数据库分页问题

Oracle 作者:ongseeping 时间:2013-09-11 16:53:40 0 删除 编辑
1.分页语句怎么取出表中第31到40的记录(以自动增长ID为主键)
  sql server方案1:
   select top 10 * from t where id not in (select top 30 id from t order by id ) order 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 and r>=30)
2.分页技术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-1)*pageSize ;
   sqlserver:sql = "select top 10 * from id not in (select top "+(pageNo-1)*pageSize + "id from articles)"

   DataSpirce ds = new InitialContext().lookup(jndiurl);
   Connection cn = ds.getConnection();
   PreparedStatement pstmt = cn.prepareStatement(sql);
   ResultSet rs = pstmt.executeQuery();
   while(rs.next()){
    out.println(rs.getString(1));
   }
3.不可滚动的游标
   pageSize = 20 ;
   pageNo = 5 ;
   cn = null ;
   stmt = null ;
   rs = null ;
  try{
   sqlserver:sql = "select * from articles ";
   DataSource ds = ds.getConnection();
   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()&&i<10){
    i++ ;
    out.println(rs.getString(1));
  }
}catch(){}finally{
   if(rs!=null)try{
     rs.close();
    }catch(Excetion e){}
   if()
 }

3.可滚动的游标
  pageSize = 20 ;
  pageNo = 5 ;
  cn = null ;
  stmt = null ;
  rs = null ;
 try{
  sqlserver :sql = "select * from articles" ;
  DataSource ds = new InitialContext().lookup(jndiurl);
  Connectin cn = ds.getConnection();
  PreparedStatement pstmt = cn.prepareStatement(sql,ResultSet.TYPE_SCROLL.INSENSITIVE,...);
  ResultSet rs = pstm.executeQuery();
  rs.absolute((pageNo-1)*pageSize);
  int i = 0 ;
  while(rs.next()&&i
   int i = 0 ;
   out.println(rs.getString(1));
   }
 } catch(){}finally{
  if(rs!=null)try{rs.close();}catch(Exception e){}
  if(stm...
 }
<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24259879/viewspace-1116262/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-07-18