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){ 

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); 

   result = qr.update(sql,param); 

  } catch (sqlexception e) { 

   // todo auto-generated catch block 

  return result; 

public object query(string sql,string[] param,resultsethandler rsh){ 

  object result = null; 

   result = qr.query(sql, param,rsh); 

public static connection getconnection(){ 

  connection conn = null; 

   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(sqlexception e){ 

  return conn; 

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); 

import java.sql.resultset; 

import java.util.regex.pattern; 

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.pagesize = pagesize; 

  this.pagenumber = pagenumber; 

  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) {