使用Oracle中的rownum实现分页

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import jdbc.util.JDBCUtil;

public class Page {
private int start = 1;//页面的起始位置
    private int size = 5;//每页显示的记录数
    private int end = 5;//页面的终止位置
    private int count = 0;//数据库中的记录数
    private Connection con = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
private JDBCUtil jd;//数据库连接的工具包
    
public Page(){
        jd = new JDBCUtil();
        con = jd.getCon();
    }

public void getCount(){
        String sqlCount = "select count(*) from student";
        String sql = "select id,name,rnum from(select id,name,rownum rnum from student)" +
"where rnum between ? and ?";//为了第一次显示页面
        try {
            ps = con.prepareStatement(sqlCount);
            rs = ps.executeQuery();
while(rs.next()){
                count = rs.getInt(1);
            }
            ps = con.prepareStatement(sql);
            ps.setInt(1,start);
            ps.setInt(2,end);
            rs = ps.executeQuery();
            jd.printResultSet(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
public void showPage(String command) throws SQLException{
        String sql = "select id,name,rnum from(select id,name,rownum rnum from student)" +
"where rnum between ? and ?";
        ps = con.prepareStatement(sql);
if(command.equals("up")){
if(start - size <= 1){
                System.out.println("已经是第一页了");
                start = 1;
                end = 5;
                ps.setInt(1,start);
                ps.setInt(2,end);
                rs = ps.executeQuery();
                jd.printResultSet(rs);
            }else{
                start = start - size;
                end = start +  size - 1;
                ps.setInt(1,start);
                ps.setInt(2, end);
                rs = ps.executeQuery();
                jd.printResultSet(rs);
            }

        }else if(command.equals("next")){
if(count - start <= size ){
                System.out.println("已经是最后一页");
                end = count;
                start = end - count%size + 1;
                ps.setInt(1,start);
                ps.setInt(2,end);
                rs = ps.executeQuery();
                jd.printResultSet(rs);
            }else{
                start = start + size;
                end = end + size;
                ps.setInt(1, start);
                ps.setInt(2,end);
                rs = ps.executeQuery();
                jd.printResultSet(rs);
            }
        }else if(command.equals("exit")){
            System.exit(-1);
        }
    }
public void insertData() throws SQLException{
int j = 0;
for (int i = 1; i < 27; i++) {
            String sql = "insert into student values("+i+","+"'zhangsan"+i+"')";
            ps = con.prepareStatement(sql);
            j += ps.executeUpdate();
        }
        System.out.println(j+"条记录被执行");
    }
public static void main(String[] args) throws SQLException {
        Page page = new Page();
        page.getCount();
//        page.insertData();
        Scanner scan = new Scanner(System.in);
while(true){
            System.out.println("输入next up exit指令");
            String command = scan.next();
            page.showPage(command.trim());
        }
    }
}

　　上面是控制显示页面的，下面提供JDBCUtil的工具包

package jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtil {

static{
        String driverName = "oracle.jdbc.driver.OracleDriver";
try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
public Connection getCon(){
        Connection con = null;
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "hr";
        String password = "admin";
try {
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
return con;
    }
public void printResultSet(ResultSet rs){
        ResultSetMetaData rsmd = null;
if(rs != null){
try {
                rsmd = rs.getMetaData();
                System.out.println("rsmd ====== >"+rsmd.getColumnCount());
            } catch (SQLException e) {
                e.printStackTrace();
            }
try {
while(rs.next()){
for(int i=1;i <= rsmd.getColumnCount();i++){
                        System.out.print(rsmd.getColumnName(i)+"=");
                        System.out.print(rs.getString(i)+"\t");
                    }
                    System.out.println();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
public void release(ResultSet rs,Statement smt,Connection con){
if(rs != null)
try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
if(smt != null)
try {
                smt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
if(con != null)
try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
public void release(Object...obj){
for (Object object : obj) {
if(object instanceof ResultSet)
try {
                    ((ResultSet)object).close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
if(object instanceof Statement)
try {
                    ((Statement)object).close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
if(object instanceof Connection)
try {
                    ((Connection)object).close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }    
        }
    }
}

　　对于释放资源使用了两种方法，release(ResultSet rs,Statement smt,Connection con)和release(Object...obj)，随便使用。

转载于:https://www.cnblogs.com/yuzhongge/archive/2011/07/31/2122532.html