道: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()); } } }