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;
?}
}
