mysql、sqlserver、oracle分页，java分页统一接口实现

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; 

} 
}