Oracle分页例子

package test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cc.yiyao.dbConn.JdbcUtil;

public class Test2 {

 //Oracle分页  //Oracle分页 //Oracle分页
 
 //分页调用例子
 public static void main(String[] args) {
  List list = new ArrayList();
  int totalPage = splitPage(list,3,3);
  System.out.println("totalPage="+totalPage);
     for(int i=0; i<list.size();i++){
      ChannelBean bean = (ChannelBean)list.get(i);
      System.out.println("channelId="+bean.getChannelID()+", channel name="+bean.getChannelName());
     }
 }
 
    // 返回总共的页数,list为输出参数(即将list装满后返回,在程序中可以直接使用)
    public  static int splitPage(List list,int pageNo,int pageSize){
   /*取2-4条数据例子:
    * String sql = "select * from (select ROWNUM r,t1.* from  (select * from coUser order by coUserId desc) t1 where rownum<=4) t2  where t2.r>=2 and t2.r<=4";
    *
    */
     if(pageNo<1){
      pageNo = 1;    
     }
     String sql1 = "select count(*) from channel";
     Connection conn = JdbcUtil.getConnection();
     Statement stmt = null;
     ResultSet rs = null;
     int count = 0;
     try {
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql1);
   if(rs.next()){
       count = rs.getInt(1);
      }
    } catch (SQLException e) {
   // TODO 自动生成 catch 块
   e.printStackTrace();
    }finally{
     try {
    if(rs != null){
       rs.close();
       rs = null;
      }
    if(stmt != null){
     stmt.close();
     stmt = null;
      }
   } catch (SQLException e) {
    // TODO 自动生成 catch 块
    e.printStackTrace();
   }
    }
     int totalPage = (count%pageSize==0)? count/pageSize:count/pageSize+1;  //数据库的总页数
    
     if(pageNo > totalPage){
      pageNo = totalPage;
     }
    
     // 判断开开始位置
     int startPos = (pageNo-1)*pageSize + 1;
    
     //判断结束位置
     int endPos = pageNo*pageSize;
     if(endPos > count){  //大于总记录数
      endPos = count;
     }
    String sql2 = "select * from  "+
                                "(select rownum r,t1.* from  "+
                                    "   (select * from channel order by channelID desc) t1 "+
                                  " where rownum<= "+ endPos+") t2 "+   //4= pageNo*pageSize
                     " where t2.r>="+startPos+" and t2.r<="+endPos;      //
   
    Statement stmt2 = null;
    ResultSet rs2 = null;
   
    try {
     stmt2 = conn.createStatement();   
     rs2 = stmt2.executeQuery(sql2);
     while(rs2.next()){
       //将对象插入到list中
    
        ChannelBean bean = new ChannelBean();
              bean.setChannelID(rs2.getInt("channelID"));  //取出包含r(rownum)了,所以要指定字段名
        bean.setChannelCode(rs2.getString("ChannelCode"));
        bean.setChannelName(rs2.getString("ChannelName"));
        bean.setParentIDCode(rs2.getString("ParentIDCode"));
        bean.setChannelInfo(rs2.getString("ChannelInfo"));
        bean.setCreateDate(rs2.getString("CreateDate"));
     
      list.add(bean);
       }
     } catch (SQLException e) {
    // TODO 自动生成 catch 块
    e.printStackTrace();
     }finally{
      try {
      if(rs2 != null){
         rs2.close();
         rs2 = null;
        }
      if(stmt2 != null){
       stmt2.close();
       stmt2 = null;
       }
      if(conn != null){
       conn.close();
       conn = null;
       }
     } catch (SQLException e) {
      // TODO 自动生成 catch 块
      e.printStackTrace();
     }
      }
   
    return totalPage;
    }
}