当前位置:首页 > 开发 > 数据库 > 正文

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

发表于: 2012-04-18   作者:aijuans   来源:转载   浏览:
摘要: 定义:pageStart 起始页,pageEnd 终止页,pageSize页面容量 oracle分页:     select * from ( select mytable.*,rownum num from (实际传的SQL) where rownum<=pageEnd) where num>=pageStart sqlServer分页:  

定义: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;

 }
}

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
环境 SQLServer 2008 R2 问题 SQLServer分页 解决 use test; --创建测试表 create table test ( id
环境 MySQL 5.1 + 命令行工具 问题 MySQL分页 解决 --创建测试表 create table test ( id int(11) p
环境 Oracle 11gR2 + SQLPlus 问题 Oracle分页 解决 --创建测试表 SQL> create table test 2 ( 3
最近简单的对oracle,mysql,sqlserver2005的数据分页查询作了研究,把各自的查询的语句贴出来供大家
一个通用的分页存储过程实现-SqlServer(附上sql源码,一键执行即刻搭建运行环境) 使用前提   查
常用关系数据库分页SQL都是不相同的,不过大同小异 下面是Oracle分页简单事例图片以及代码: 1、普
先给截图,再说代码: 1、ExtJs的代码如下: /* paging.js * / Ext.onReady(function() { Ext.defin
先给截图,再说代码: 1、ExtJs的代码如下: /* paging.js * / Ext.onReady(function() { Ext.defin
BAIDU的搜索 我的实现,跟google的是一样的,没有像百度一样加载20条 要修改的地方分为三处:Pager.
BAIDU的搜索 我的实现,跟google的是一样的,没有像百度一样加载20条 要修改的地方分为三处:Pager.
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号