数据库sql分页

lyd2004888

　　

取出sql表中第31到40的记录（以自动增长ID为主键）
sql server方案1：
    selecttop 10 * from t where id not in (select top 30 id from t order by id ) orde byid
sql server方案2：
    selecttop 10 * from t where id in (select top 40 id from t order by id) order by iddesc
 
mysql方案：select * from t order by idlimit 30,10
 
oracle方案：select* from (select rownum r,* from t where r<=40) where r>30
 
----------------------------------案例-------------------------------
pageSize=20;
pageNo = 5;
 
1.分页技术1（直接利用sql语句进行分页，效率最高和最推荐的）
 
mysql:sql ="select * from articles limit " + (pageNo-1)*pageSize + ","+ pageSize;


oracle: sql ="select * from " +"(selectrownum r,* from " + "(select* from articles order by postime desc)" +"whererownum<= " + pageNo*pageSize +") tmp " +"wherer>" + (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 = newInitialContext().lookup(jndiurl);
Connection cn =ds.getConnection();
//"select * fromuser where id=?"  --->binarydirective
PreparedStatementpstmt = 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 = newInitialContext().lookup(jndiurl);
Connection cn =ds.getConnection();
//"select * fromuser where id=?"  --->binarydirective
PreparedStatementpstmt = cn.prepareSatement(sql);
ResultSet rs = pstmt.executeQuery()
for(intj=0;j<(pageNo-1)*pageSize;j++)
{
       rs.next();
}
 
int i=0;
 
while(rs.next()&& i<10)
{
       i++;
       out.println(rs.getString(1));
}
}
cacth(){}
finnaly
{
       if(rs!=null)try{rs.close();}catch(Exceptione){}
       if(stm.........
       if(cn............
}
 
3.可滚动的游标
pageSize=20;
pageNo = 5;
cn = null
stmt = null;
rs = null;
try
{
sqlserver:sql ="select  * from articles";
 
DataSource ds = newInitialContext().lookup(jndiurl);
Connection cn =ds.getConnection();
//"select * fromuser where id=?"  --->binarydirective
PreparedStatementpstmt = cn.prepareSatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,...);
//根据上面这行代码的异常SQLFeatureNotSupportedException，就可判断驱动是否支持可滚动游标
 
ResultSet rs =pstmt.executeQuery()
rs.absolute((pageNo-1)*pageSize)
int i=0;
while(rs.next()&& i<10)
{
       i++;
       out.println(rs.getString(1));
}
}
cacth(){}
finnaly
{
       if(rs!=null)try{rs.close();}catch(Exceptione){}
       if(stm.........
       if(cn............
}