定义:pageStart 起始页,pageEnd 终止页,pageSize页面容量
oracle分页:
select * from ( select mytable.*,rownum num from (实际传的SQL) where rownum<=pageEnd) where num>=pageStart
sqlServer分页:
select * from ( select top 页面容量 from( select top 页面容量*当前页码 * from 表 where 条件 order by 字段A) as temptable1 order by
字段A desc) as temptable2 order by 字段A
Mysql分页:
select * from mytable where 条件 limit 当前页码*页面容量-1 to 页面容量
Java分页接口和实现类:
package com.qg.demo.util;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
public class OracleUtil {
private String dataSourceName;
private DataSource ds;
public OracleUtil(String dataSourceName){
this.dataSourceName = dataSourceName;
}
public OracleUtil(){
}
public void setDataSourceName(String dataSourceName){
this.dataSourceName = dataSourceName;
}
public void init(){
Context initContext;
try {
initContext = new InitialContext();
ds = (DataSource)initContext.lookup(dataSourceName);
} catch (NamingException e) {
e.printStackTrace();
}
}
public int update(String sql,String[] param){
int result = 0;
QueryRunner qr = new QueryRunner(ds);
try {
result = qr.update(sql,param);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
public Object query(String sql,String[] param,ResultSetHandler rsh){
QueryRunner qr = new QueryRunner(ds);
Object result = null;
try {
result = qr.query(sql, param,rsh);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
public static Connection getConnection(){
Connection conn = null;
try {
Context context = new InitialContext();
DataSource ds = (DataSource)context.lookup("java:/comp/env/jdbc/oracleds");
conn = ds.getConnection();
QueryRunner qr = new QueryRunner(ds);
// PreparedStatement pstmt = conn.prepareStatement("select * from guestbook");
// ResultSet rs = pstmt.executeQuery();
// while(rs.next()){
// System.out.println(rs.getInt("g_id"));
// System.out.println(rs.getString("title"));
// System.out.println(rs.getString("remark"));
// }
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
return conn;
}
}
package com.qg.demo.util;
import java.util.List;
public interface Pagination {
public boolean isLast();
public boolean isFirst();
public boolean hasNext();
public boolean hasPrevious();
public int getMaxElements();//最大记录数
public int getMaxPage();//最大页码
public int getNext();
public int getPrevious();
public int getPageSize();
public int getPageNumber();
public List<Object> getList();
public void setPageSize(int pageSize);
public void setPageNumber(int pageNumber);
}
package com.qg.demo.util;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.regex.Pattern;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
public class OraclePaginationImpl implements Pagination {
private int pageSize = 20;
private int pageNumber = 1;
private int maxElements;
private int maxPage;
private String sql;
private OracleUtil db;
public OraclePaginationImpl(String sql){
this.sql = sql;
init();
}
public OraclePaginationImpl(String sql,int pageSize, int pageNumber){
this.sql = sql;
this.pageSize = pageSize;
this.pageNumber = pageNumber;
init();
setPageNumber(pageNumber);
}
private void init(){
db = new OracleUtil("java:/comp/env/jdbc/oracleds");
db.init();
setMaxElements();
setmaxPage();
}
private void setMaxElements() {
//select * from xxx order by xx desc
//select count(1) from xxx order by xx desc
String regex = "select((.)+)from";
Pattern p = Pattern.compile(regex,Pattern.CASE_INSENSITIVE);
String[] s = p.split(this.sql);
String newSql = "select count(1) as total from "+s[1];
ResultSetHandler handler = new ResultSetHandler(){
public Object handle(ResultSet rs) throws SQLException{
if(rs.next()){
return new Integer(rs.getInt("total"));
}else{
return null;
}
}
};
this.maxElements = (Integer)db.query(newSql, null, handler);
}
private void setmaxPage(){
this.maxPage = (maxElements%pageSize == 0 ? maxElements/pageSize : (maxElements/pageSize +1));
}
private String sqlModify(String sql,int begin ,int end){
StringBuffer buffer = new StringBuffer();
buffer.append("select * from ( select rownum num,a.* from (")
.append(sql)
.append(") a where rownum <= ")
.append(end)
.append(") where num >= ")
.append(begin);
return buffer.toString();
}
private int getBeginElement() {
return (pageNumber-1) * pageSize +1;
}
private int getEndElement() {
return (pageNumber*pageSize >=maxElements ? maxElements : pageNumber*pageNumber);
}
public List<Object> getList() {
String newSql = this.sqlModify(sql, getBeginElement(), getEndElement());
return (List)db.query(sql, null, new MapListHandler());
}
public int getMaxElements() {
return maxElements;
}
public int getMaxPage() {
return maxPage;
}
public int getNext() {
return pageNumber+1 >= maxPage ? maxPage : pageNumber+1;
}
public int getPageNumber() {
return pageNumber;
}
public int getPageSize() {
return pageSize;
}
public int getPrevious() {
return pageNumber-1 <=1 ? 1 :pageNumber -1;
}
public boolean hasNext() {
return pageNumber < maxPage;
}
public boolean hasPrevious() {
return pageNumber > 1;
}
public boolean isFirst() {
return pageNumber == 1;
}
public boolean isLast() {
return pageNumber == maxPage;
}
public void setPageNumber(int pageNumber) {
if(pageNumber>maxPage){
this.pageNumber = maxPage;
}else if(pageNumber<1){
this.pageNumber = 1;
}else{
this.pageNumber = pageNumber;
}
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
}
分享到:
相关推荐
mysql,oracle,sql server分页总结与比较
sql 分页 针对oracle mysql sqlserver 等数据库的通用类
oracle分页 mysql分页 sqlserver分页
sqlserver,mysql,oracle sql分页
mysql,oracle,sqlserver分页
这里只是总结了MySQL,SQL Server,Oracle分页语句。供大家参考
用于oracle,mysql,sqlserver,数据库分页联合查询
sqlserver mysql oracle 三种数据库的分页查询比较
此文件里面包含一些常用的数据库分页方法,这是我在学习的过程中和查阅一些资料总结出来的,希望能够帮助到你,欢迎下载哦。
Sqlserver、Oracle、MySql、PostgreSql、SqlLite常用数据库的对比 1、分页 2、时间函数 3、自增列 4、表名规范 5、字符串连接 6、变量定义 7、其它
最近简单的对oracle,mysql,sqlserver2005的数据分页查询作了研究,把各自的查询的语句贴出来供大家学习….. (一)、mysql的分页查询 mysql的分页查询是最简单的,借助关键字limit即可实现查询,查询语句通式: ...
主流数据库的分页技术,解析注解等等 为什么需要分页 1. 方便用户查看,提高客户端体验 2. 减轻数据库资源管理器的开销,提高程序运行速度 分页查询的本质 查看某一页就只返回这一页的数据
数据库分页大全,oracle,sqlserver,mysql
看过此博文后Oracle、MySql、SQLServer 数据分页查询,在根据公司的RegionRes表格做出了 SQLserver的分页查询语句: SELECT DISTINCT TOP 500 rr.ID AS id ,rr.strName AS name ,rr.nType AS res_type ,...
主要是mysql、sqlserver、oracle、db2、分页 及规律
先说下 intersect和 minus intersect运算 返回查询结果中相同的部分。 eg:各个部门中有哪些相同的工种 select job from account intersect select job from research .......................
mybatis 分页插件,支持kingbase、tdengine、达梦、mysql、oracle、sqlserver等数据库
行式引擎按页取数只适用于Oracle,mysql,hsql和sqlserver2008及以上数据库,其他数据库,如access,sqlserver2005,sqlite等必须编写分页SQL。今天我们以Access数据库为例介绍需要写分页SQL的数据库怎样利用行式的...
几条常见的数据库分页SQL 语句,针对oracle,sqlserver,mysql三种常见数据库的分页显示。