结合JDBC和Oracle ROWNUM实现分页查询

这两天兴趣大发，突然想复习一下JDBC对数据库的操作，刚好一个同事问我：Oracle如何实现分页查询？基于此前提，就将二者结合复习。

首先，对于oracle的分页，本人熟练的目前有两种，这两种都还是结合网上各位同行的经验而掌握的。

我们知道：rownum是oracle根据你查询的结果给每行自动虚拟添加的一个行号。

第一种：利用rownum和between and进行分页

select * from(select rownum as rn, t.* from table_name t) temp where temp.rn between 0 and 1000;

这就可以查询出0到1000行的结果；

第二种：利用rownum和三层查询模式进行分页

select * from (select rownum as rn, t.* from table_name t where rownum<2000) temp where temp.rn>1000;

这可以查询到1000到2000行的数据

针对上面两种sql语句：我们可以结合jdbc实现分页：请看代码：

首先新建UDUtil.java，用于获得Connection，以及定义一些常量：

package org.cmcc.util;

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

import org.apache.log4j.Logger;

/**
 * @Desc 		数据库连接工具类
 * @Author 		Administrator
 * @Datetime 	2012-9-24下午06:07:10
 * @Version 	1.0
 */
public class DBUtil {
	private static Logger logger = Logger.getLogger(DBUtil.class);
	
	//数据库连接信息：数据库驱动、主机地址：端口：实例、用户名、密码
	public static String db_driver = "oracle.jdbc.driver.OracleDriver";
	public static String db_url = "jdbc:oracle:thin:@localhost:1521:ORCL";
	public static String db_user = "gd";
	public static String db_password = "gd123";
	
	//每页多少行
	public static final int pageSize = 10000;
	
	/**
	 * 获得数据库连接
	 * @param driver
	 * @param url
	 * @param user
	 * @param password
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_user, db_password);
		} catch (ClassNotFoundException e) {
			logger.error("数据库驱动加载失败！", e);
		} catch (SQLException e) {
			logger.error("连接数据库失败！", e);
		}
		
		return conn;
	}
	
}

第二步：创建一个数据库操作类：DBDAO.java

package org.cmcc.dao;

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 org.apache.log4j.Logger;
import org.cmcc.entity.DGwap201208;
import org.cmcc.util.DBUtil;
/**
 * @Desc 		数据库操作类
 * @Author 		Administrator
 * @Datetime 	2012-9-24下午05:50:37
 * @Version 	1.0
 */
public class DGwapDAO {
	private static Logger logger = Logger.getLogger(DGWapDAO.class);
	
	private Connection conn = null;
	
	private Statement stm = null;
	
	private ResultSet rs = null;
	
	/**
	 * 分页获得数据
	 * @param pageNum	页码
         * @param pageSize      每页多少行
         * @return
	 */
	public List<DGwap201208> getGdWapByPage(int pageNum, int pageSize) {
		List<DGwap201208> results = new ArrayList<DGwap201208>();;
		DGwap201208 wap = null;
		
		int start = pageSize * pageNum;
		int end = pageSize * (pageNum + 1);
		
		//分页查询语句：利用ROWNUM
		String sql = "SELECT * FROM (SELECT ROWNUM AS rn, T.MSISDN as msisdn,T.USER_AGENT as userAgent," +
				"T.URL as url,T.USE_TIME as useTime,T.STATUS_CODE as statusCode," +
				"T.DOWN_TRAFFIC as downTraffic,T.UP_TRAFFIC as upTraffic," +
				"T.GETWAY_DELAY as getwayDelay,T.SP_DELAY as spDelay " +
				"FROM DG_WAP_201208 T) WHERE rn>=" + start + " AND rn<" + end;
                
               /*String sql = "SELECT * FROM (SELECT ROWNUM AS rn, T.MSISDN as msisdn,T.USER_AGENT as userAgent," +
		"T.URL as url,T.USE_TIME as useTime,T.STATUS_CODE as statusCode," +
		"T.DOWN_TRAFFIC as downTraffic,T.UP_TRAFFIC as upTraffic," +
		"T.GETWAY_DELAY as getwayDelay,T.SP_DELAY as spDelay " +
		"FROM DG_WAP_201208 T) WHERE rn BETWEEN " + start + " AND " + end;*/

                long startTime = System.currentTimeMillis();
		logger.info("执行第" + (pageNum + 1) + "次分页查询！当前毫秒时间是：" + System.currentTimeMillis());
		logger.info("JDBC分页查询语句：" + sql.toString());
		try {
			conn = DBUtil.getConnection();
			stm = conn.createStatement();
			
			rs = stm.executeQuery(sql);
			
			while(rs.next()) {
				wap = new DGwap201208();
				
				wap.setMsisdn(rs.getString("msisdn"));
				wap.setUserAgent(rs.getString("userAgent"));
				wap.setUrl(rs.getString("url"));
				wap.setUseTime(rs.getString("useTime"));
				wap.setStatusCode(rs.getString("statusCode"));
				wap.setDownTraffic(Long.valueOf(rs.getString("downTraffic")));
				wap.setUpTraffic(Long.valueOf(rs.getString("upTraffic")));
				wap.setGetwayDelay(Long.valueOf(rs.getString("getwayDelay")));
				wap.setSpDelay(Long.valueOf(rs.getString("spDelay")));
				
				results.add(wap);
			}
		} catch (SQLException e) {
			logger.error("分页获取数据失败！", e);
		} finally {
			this.closeResouce();
		}
		long endTime = System.currentTimeMillis();
		logger.info("第" + (pageNum + 1) + "次将分页查询结果返回！" + "当前毫秒时间是：" + System.currentTimeMillis());
		logger.info("本次获取 " + DBUtil.pageSize + "条数据，消耗时间：" + (endTime - startTime)/1000 + " 秒");
		return results;
	}
	
	/*public static void main(String[] args) {
		DGWapDao wapDao = new DGWapDAO();
		System.out.println(wapDao.getGdWapByPage(0).size());
	}*/
	
        /*
         *关闭连接，释放资源
         */
        public void closeResouce() {
		logger.info("开始关闭资源连接，以释放空间...");
		try {
			if(rs != null) {
				rs.close();
			}
			if(stm != null) {
				stm.close();
			}
			if(conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			logger.error("关闭连接失败！", e);
		}
	}
}

第三步：测试，分页数据的正确性：PageData.java

package org.cmcc.http;

import java.util.List;

import org.cmcc.dao.DGWapDAO;
import org.cmcc.entity.DGwap201208;

/**
 * @Desc 		测试
 * @Author 		Administrator
 * @Datetime 	2012-9-26下午04:37:41
 * @Version 	1.0
 */
public class PageDataTest {
	public static void main(String[] args) {
		DGWapDAO wapDao = new DGWapDAO();
		List<DGwap201208> results = wapDao.getGdWapByPage(1, 10);
		for(DGwap201208 wap : results) {
			System.out.println(wap.getMsisdn() + " | " + wap.getStatusCode() + " | " + wap.getUrl().substring(1, 20) + "... | " +wap.getUserAgent() + " | " + wap.getUseTime() + " | " + wap.getDownTraffic()+ " | " + wap.getUpTraffic()+ " | " + wap.getGetwayDelay()+ " | " + wap.getSpDelay());
		}
	}
}

这样基本实现分页查询数据的目的了。

由于本人是个java菜鸟，所有，代码和思路上难以有瑕疵，欢迎各位拍砖指正！