Oracle 数据分页显示详解

 

Oracle 数据分页显示详解

1. oracle的分页语句：

   在t_activity 中查出address towhere nick的第2－10条的记录：

   1)

SELECT t2.* FROM(

SELECT t1.*,ROWNUM rr FROM(

SELECT t.address,t.towhere,t.nick FROM t_activity t)t1 WHERE ROWNUM<=10)t2

WHERE t2.rr>1

2)

SELECT t.address,t.towhere,t.nick FROM t_activity t WHERE ROWNUM<=10

minus

SELECT t.address,t.towhere,t.nick FROM t_activity t WHERE ROWNUM<=1

DAO:

package com.soft136.kf.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import oracle.jdbc.rowset.OracleCachedRowSet;

public class MessageDAO {

public OracleCachedRowSet reMess(String nick, int pageSize, int startRow)

throws ClassNotFoundException, SQLException {

int end = pageSize + startRow;

System.out.println("###startRow:" + startRow + " endRow:" + end);

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

String sql = "SELECT t.id,t.fromwhere,t.towhere,t.con,t.time FROM T_LOG t";

if (nick != null && !(nick.equals(""))) {

sql = sql + " WHERE t.towhere='" + nick + "'";

}

sql = "SELECT t2.* FROM(SELECT t1.*,ROWNUM rr FROM(" + sql

+ ")t1 WHERE ROWNUM<='" + end + "')t2 WHERE t2.rr>'" + startRow

+ "'";

try {

conn = DataBaseConnection.getInstance().getConnection();

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

OracleCachedRowSet ocrs = new OracleCachedRowSet();

ocrs.populate(rs);

return ocrs;

} finally {

if (ps != null) {

ps.close();

}

if (conn != null) {

conn.close();

}

}

}

public OracleCachedRowSet countReMess() throws SQLException {

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

String sql = "SELECT count(*) FROM T_LOG t";

try {

conn = DataBaseConnection.getInstance().getConnection();

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

OracleCachedRowSet ocrs = new OracleCachedRowSet();

ocrs.populate(rs);

return ocrs;

} finally {

if (ps != null) {

ps.close();

}

if (conn != null) {

conn.close();

}

}

}

}

DTO:

package com.soft136.kf.dto;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import oracle.jdbc.rowset.OracleCachedRowSet;

import com.soft136.kf.dao.*;

import com.soft136.kf.pojo.*;

public class MessageDTO {

public List reMess(String nick, int pageSize, int startRow)

throws SQLException, Exception {

List list = new ArrayList();

OracleCachedRowSet ocrs = new MessageDAO().reMess(nick, pageSize,

startRow);

while (ocrs != null && ocrs.next()) {

list.add(new ReMessBean(ocrs.getInt(1), ocrs.getString(2), ocrs

.getString(3), ocrs.getString(4), ocrs.getString(5)));

}

return list;

}

public int countReMess() throws SQLException {

int count = 0;

OracleCachedRowSet ocrs = new MessageDAO().countReMess();

while (ocrs != null && ocrs.next()) {

count = ocrs.getInt(1);

}

return count;

}

}

ACTION:

package com.soft136.kf.struts.action;

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

import com.soft136.kf.struts.form.MessForm;

import com.soft136.kf.pojo.*;

import com.soft136.kf.dto.*;

public class MessAction extends Action {

public ActionForward execute(ActionMapping mapping, ActionForm form,

HttpServletRequest request, HttpServletResponse response) {

MessForm messForm = (MessForm) form;

String nick = messForm.getNick();

String messType = messForm.getMessType();

List list = null;

int totalRows = 0;

PageBean pager = null;

try {

if (messType.equals("receive")) {

totalRows = new MessageDTO().countReMess();

} else if (messType.equals("send")) {

//

//

}

pager = new PageBean(totalRows);

String currentPage = request.getParameter("currentPage");

if (currentPage != null) {

pager.refresh(Integer.parseInt(currentPage));

}

String pagerMethod = request.getParameter("pageMethod");

if (pagerMethod != null) {

if (pagerMethod.equals("first")) {

pager.first();

} else if (pagerMethod.equals("previous")) {

pager.previous();

} else if (pagerMethod.equals("next")) {

pager.next();

} else if (pagerMethod.equals("last")) {

pager.last();

}

}

if (messType.equals("receive")) {

list = new MessageDTO().reMess(nick, pager.getPageSize(), pager

.getStartRow());

} else if (messType.equals("send")) {

//

//

//

}

} catch (Exception e) {

e.printStackTrace();

}

request.setAttribute("count", totalRows);

request.setAttribute("pagging", pager);

request.setAttribute("list", list);

return mapping.findForward("view");

}

}

VIEW:

<%@ page language="java" pageEncoding="UTF-8"%>

<%@ taglib uri="" prefix="bean"%>

<%@ taglib uri="" prefix="html"%>

<%@ taglib uri="" prefix="logic"%>

<%@ taglib uri="" prefix="tiles"%>

<html:html lang="true">

<head>

<html:base />

<title>短信查询title>

head>

<body>

<html:form action="/mess">

<html:select property="messType">

<html:option value="send">发送查询html:option>

<html:option value="receive">接收查询html:option>

html:select>

<html:text property="nick">手机号html:text>

<html:submit>html:submit>

html:form>

<table>

<tr>

<td background="home_images/prize_titlebg.gif">

<strong>接收手机号strong>

td>

<td background="home_images/prize_titlebg.gif">

<strong>发送手机号strong>

td>

<td background="home_images/prize_titlebg.gif">

<strong>接收时间strong>

td>

tr>

<logic:iterate id="reMess" name="list">

<tr>

<td class="m">

${reMess.towhere }

td>

<td class="m">

${reMess.fromwhere }

td>

<td class="m">

${reMess.time }

td>

tr>

<tr>

<td height="1" colspan="9" background="home_images/point.gif">td>

tr>

logic:iterate>

table>

第

<bean:write name="pagging" property="currentPage" scope="request" />

页共

<bean:write name="pagging" property="totalPages" scope="request" />

页

<html:link action="/mess.do?pageMethod=first" paramName="pagging"

paramProperty="currentPage" paramId="currentPage" scope="request">首页html:link>

<logic:notEqual value="1" name="pagging" property="currentPage"

scope="request">

<html:link action="/mess.do?pageMethod=previous" paramName="pagging"

paramProperty="currentPage" paramId="currentPage" scope="request">上一页html:link>

logic:notEqual>

<html:link action="/mess.do?pageMethod=next" paramName="pagging"

paramProperty="currentPage" paramId="currentPage" scope="request">下一页html:link>

<html:link action="/mess.do?pageMethod=last" paramName="pagging"

paramProperty="currentPage" paramId="currentPage">尾页html:link>

共${count }条记录

body>

html:html>

PageBean:

package com.soft136.kf.pojo;

public class PageBean {

private int totalRows;

private int pageSize = 10;

private int currentPage;

private int totalPages;

private int startRow;

public PageBean() {

}

public PageBean(int _totalRows) {

totalRows = _totalRows;

totalPages = totalRows / pageSize;

int mod = totalRows % pageSize;

if (mod > 0) {

totalPages++;

}

currentPage = 1;

startRow = 0;

}

public int getStartRow() {

return startRow;

}

public int getTotalPages() {

return totalPages;

}

public int getCurrentPage() {

return currentPage;

}

public int getPageSize() {

return pageSize;

}

public void setTotalRows(int totalRows) {

this.totalRows = totalRows;

}

public void setStartRow(int startRow) {

this.startRow = startRow;

}

public void setTotalPages(int totalPages) {

this.totalPages = totalPages;

}

public void setCurrentPage(int currentPage) {

this.currentPage = currentPage;

}

public void setPageSize(int pageSize) {

this.pageSize = pageSize;

}

public int getTotalRows() {

return totalRows;

}

public void first() {

currentPage = 1;

startRow = 0;

}

public void previous() {

if (currentPage == 1) {

return;

}

currentPage--;

startRow = (currentPage - 1) * pageSize;

}

public void next() {

if (currentPage < totalPages) {

currentPage++;

}

startRow = (currentPage - 1) * pageSize;

}

public void last() {

currentPage = totalPages;

startRow = (currentPage - 1) * pageSize;

}

public void refresh(int _currentPage) {

currentPage = _currentPage;

if (currentPage > totalPages) {

last();

}

}

}