mysql、sqlserver、oracle分页，java分页统一接口实现
定义：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;
}
}