mysql jdbc分页_JDBC分页查询 | 学步园

分页语句

取出sql表中第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) where r>30

分页技术

(直接利用sql语句进行分页，效率最高和最推荐的)

pageSize=20;

pageNo = 5；

1.mysql:sql = "select * from articles limit

" + (pageNo-1) * pageSize + "," + pageSize;"

2.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也是按顺序输出的，因为这时候生成原始的查询结果集时会参照索引表的顺序来构建。

3.sqlserver:sql = "select top 10 * from id not id(select top" + (pageNo-1)*pageSize + "id from articles"

================================================================================================================

sql server:String sql ="select top" + pageSize + " * from students where id not in (select top

"+ pageSize * (pageNumber-1) + " id from students order by id) order by id";

mysql:String sql ="select * from students order by id limit

"+ pageSize*(pageNumber-1)+"," + pageSize;"

oracle: String sql ="select * from " + (select *,rownum rid from (select * from students order by postime desc) where rid<=

"+ pageSize * pageNumber +") as t where t >

"+pageSize*(pageNumber-1)";