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;

}
}