java oracle分页查询语句_结合JDBC和Oracle ROWNUM实现分页查询

道：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.javapackage 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 getGdWapByPage(int pageNum, int pageSize) {

List results = new ArrayList();;

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

 /*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.javapackage 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 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());

}

}

}