如何实现Oracle中的分页查询

Oracle中的分页查询

---

以scott账户中的emp表和dept表为例

1. 最内层：主要负责排序

String sql = "select e.* from emp e inner join dept d on e.deptNo = d.deptNo where order by orderStd orderType";

orderStd :排序字段名
orderType：排序类型（desc asc）

2. 中间层：主要负责使用rownum关键字来进行分页 以及起别名、做小于项的设置

sql = "select rownum r ,t.* from("+ sql +") t where rownum < pageSize*pageNumber + 1";

pageSize:一页所呈现的数据量
pageNumber：页数

3. 外层：分页的大于项设置

sql = "select * from ("+ sql +") where r > pageSize*(pageNumber - 1)";

例如：查询emp表中的第二页数据，以降序显示，每页显示四条数据

orderStd ：empNo
orderType：desc
pageSize：4
pageNumber：2

sql语句如下

select * from(
	select rownum r,t.* from(
		select e.* from emp e inner join dept d on e.deptNo = d.deptNo order by e.empNo desc ) t 
		where rownum <  9)
		where r > 4;

查询结果：

在java中实现：

链接数据库的工具类：

package com.csdn.util;

import java.sql.Statement;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPUtil {

	private static DataSource data = null;
	static {
		// 利用类加载器 读取配置文件
		InputStream is = DBCPUtil.class.getClassLoader().getResourceAsStream("com/csdn/util/db.properties");
		Properties ps = new Properties();

		try {
			ps.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			// 给数据源对象赋值
			data = BasicDataSourceFactory.createDataSource(ps);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection getConn() {
		Connection conn;
		try {
			conn = data.getConnection();
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void closeDB(Connection conn, Statement st, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
		if(st!=null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				st=null;
			}
		}
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				conn=null;
			}
		}
	}
}

接口实现类：

package com.csdn.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.csdn.bean.Emp;
import com.csdn.dao.EmpDao;
import com.csdn.util.DBCPUtil;

public class EmpDaoImpl implements EmpDao {

	@Override
	public List<Emp> getEmpByPage(String orderType, String orderStd, int pageSize, int pageNumber) {
		Connection conn = DBCPUtil.getConn();
		PreparedStatement pst = null;
		ResultSet rs = null;
		// 编写SQL语句
		// 最内层
		String sql = "select e.* from emp e inner join dept d on e.deptNo = d.deptNo "

				+ "order by " + orderStd + " " + orderType + "";
		// 中间层
		sql = "select rownum r,t.* from(" + sql + ") t where rownum < ?";

		// 最外层
		sql = "select * from (" + sql + ") where r > ?";

		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, pageSize * pageNumber + 1);
			pst.setInt(2, pageSize * (pageNumber - 1));
			rs = pst.executeQuery();
			List<Emp> list = new ArrayList<Emp>();
			while (rs.next()) {
				Emp emp = new Emp(rs.getInt("empNo"), rs.getString("eName"), rs.getString("job"), rs.getInt("mgr"),
						rs.getTimestamp("hiredate"), rs.getDouble("sal"), rs.getDouble("comm"), rs.getInt("deptNo"));

				list.add(emp);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBCPUtil.closeDB(conn, pst, rs);
		}
		return null;
	}
	//测试方法
	@Test
	public void test() {
		EmpDao dao = new EmpDaoImpl();
		List<Emp> list = dao.getEmpByPage("desc", "empNo", 4, 2);
		for (Emp emp : list) {
			System.out.println(emp);
		}
	}

}

查询结果：