JDBC 实效数据分页查询

本案例使用到的Oracle数据库和数据表为Oracle自带

示例代码：

package com.hcq.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main2 {
	private static class DBProperties {
		// IP地址
		static final String IP = "127.0.0.1";
		// 端口
		static final String PORT = "1521";
		// 数据库名
		static final String DB_NAME = "test";
		// 用户名
		static final String USER = "scott";
		// 密码
		static final String PSW = "tiger";
		// 连接
		static final String URL = "jdbc:oracle:thin:@" + IP + ":" + PORT + ":" + DB_NAME;
	}

	// 得到连接
	public static Connection getConn() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(DBProperties.URL, DBProperties.USER, DBProperties.PSW);
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	// 分页方法 pageNUm表示查询单页数，pageSize表示每页显示的条数
	public static void page(int pageNum, int pageSize) {
		Connection conn = Main2.getConn();
		PreparedStatement ps = null;
		String sql = "SELECT * FROM (SELECT empno,ename,sal,deptno,ROWNUM rn FROM emp WHERE ROWNUM<=?) temp WHERE"
				+ " temp.rn BETWEEN ? AND ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pageNum * pageSize);
			ps.setInt(2, (pageNum - 1) * pageSize + 1);
			ps.setInt(3, pageNum * pageSize);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int empno = rs.getInt(1);
				String ename = rs.getString(2);
				double sal = rs.getDouble(3);
				int deptno = rs.getInt(4);
				int rn = rs.getInt(5);
				System.out
						.println("编号：" + empno + "\t姓名：" + ename + "\t薪水:" + sal + "\t部门:" + deptno + "\tROWNUM:" + rn);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	public static void main(String[] args) {
		// 查询第二页数据，每页显示3条数据
		page(2, 3);
	}

}

结果截图：