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;

}

}