JDBC分页查询
来源:互联网 发布:c 后台调用前台js 编辑:程序博客网 时间:2024/05/22 04:32
分页语句
取出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)";
0 0
- jdbc 分页查询
- JDBC分页查询
- jdbc分页查询
- Java jdbc分页查询
- jdbc之分页查询
- 分页查询-JDBC版
- JDBC实现分页查询
- JDBC中的分页查询
- JDBC的分页查询
- JDBC中的分页查询
- JDBC利用游标分页查询
- JDBC利用游标分页查询
- JDBC实例-分页查询-limit
- 使用JDBC实现分页查询
- JDBC分页查询及实现
- 使用JDBC实现分页查询
- JDBC 实效数据分页查询
- JDBC的分页查询以及hibernate高级查询分页
- 写给自己的javaweb细节
- android WebView总结
- Some Oracle products are currently in use. Please stop all Windows NT services and quit applications
- 装系统的小问题
- Windows 64位 安装Oracle instantclient 官方绿色版和PL/SQL Developer 总结(转)
- JDBC分页查询
- 把数字格式化成带有分号的字符串
- multipath多路径设备input/output error读写错误
- linux sort 命令详解
- MingW上编译WinSocket程序undefined reference to `WSAStartup@8'报错的解决办法
- OCP-1Z0-053-V12.02-568题
- 自己写一个库函数strcpy(转)
- debug_objects_early_init
- if语句应用输入月份输出对应季节举例