Question

[oracle]一个oracle分页sql语句的类 有问题 麻烦各位前辈给个指教

java操作oracle分页sqlSELECT * FROM     (SELECT A.*, ROWNUM RN      FROM          (SELECT *           FROM tb_news           where isload=0           order by isload desc) A      WHERE ROWNUM < 15) WHERE RN >=10;  package com.whelp.system.util;public class ShowSQL {/**** 获取oracle分页SQL语句* @param tableName 要查询的表,不可以带where* @param start 开始条数* @param end 结束条数* @param orderBy 排序方式* @param andwhere 如 typeid=5* @return 一条相应的oracle sql语句*/public static String getSqlStartEnd(String tableName,int start,int end,String orderBy,String andwhere){   String sql = "";   sql = "select * from ( select rownum r,a.* from "+tableName+" a where rownum<="+end+" ) b where b.r>"+start;     if(orderBy!=null)   {    sql = sql+" order by b."+orderBy;   }     if(start==-1 && end==-1)   {    sql = "select * from " +tableName;   }     if(andwhere!=null)   {    sql=sql+" and b."+andwhere;   }   return sql;}/*** 获取oracle分页SQL语句* @param tableName 要查询的表,不可以带where* @param start 开始条数* @param end 结束条数* @param orderBy 排序方式* @return 一条相应的oracle sql语句*/public static String getSqlStartEnd(String tableName,int start,int end,String orderBy){     return getSqlStartEnd(tableName, start, end, orderBy, null);}/**** 获取oracle分页SQL语句* @param tableName 要查询的表,不可以带where* @param start 开始条数* @param end 结束条数* @return 一条相应的oracle sql语句*/public static String getSqlStartEnd(String tableName,int start,int end){   return getSqlStartEnd(tableName, start, end, null);}/**** 获取oracle分页SQL语句* @param tableName 要查询的表,不可以带where* @param pagesize 每页显示多少条* @param pagenum 当前页* @param orderBy 排序方式* @param andwhere 如 typeid=5* @return 一条相应的oracle sql语句*/public static String getSql(String tableName,int pagesize,int pagenum,String orderBy,String andwhere){   int first = (pagenum-1)*pagesize;   int last = pagenum * pagesize;   String sql = "";   sql = "select * from ( select rownum r,a.* from "+tableName+" a where rownum<="+last+" ) b where b.r>"+first;     if(orderBy!=null)   {    sql = sql+" order by b."+orderBy;   }     if(pagenum==-1 && pagesize==-1)   {    sql = "select * from " +tableName;   }     if(andwhere!=null)   {    sql=sql+" and b."+andwhere;   }   return sql;}/**比4个参数的多一个条

Answer 1

建议使用3层封装+rownum函数实现分页：
select *
from (select * from (select * from prod) t1 where rownum <= 20) t2
where rownum <= 10

please note:
1. select * from prod 是处理业务逻辑的sql,直接替换成你的就可以了
2. “20”代表最大的数目
3. “10”表示开始的数目

Answer 2

俺也学习一下哈

Answer 3

ALTER TABLE aaa ADD ccc int NULL CONSTRAINT ccc DEFAULT 0 WITH VALUES ; GO 蓝屏

Answer 4

为什么不用select t.* from tableName t where rownum > ? and rownum < ?