mysql、sqlserver、oracle分页,java分页统一接口实现-阿里云开发者社区

开发者社区> 数据库> 正文

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; 


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章