Posted on 2011-05-17 00:21
簡簡單單 閱讀(4381)
評論(0) 編輯 收藏 所屬分類:
DataBase
1、數據庫分頁技術的基本思想：
(1)、確定記錄跨度。即確定每頁顯示數據的條數。
(2)、獲取記錄總數。即獲取要顯示在頁面中的總記錄數。其目的是依據總記錄數來技術得到總頁數。
(3)、確定分頁后的總頁數。依據公式“總記錄數/跨度”計算分頁后的總頁數。要注意的是：如果總頁數有余數，要去掉余數，將總頁數加1。修改為:（總記錄數-1）/跨度+1
(4)、根據當前頁數顯示數據。注意：如果該頁數小于1，則使其等于1；如果頁數大于最大頁數，則使其等于最大頁數。
(5)、通過For循環語句分頁顯示數據。
2、使用標準的SQL語句實現數據分頁：
(1)、獲取前n條數據：
SELECT TOP n[PERCENT] *
FROM TABLE
WHERE ......
ORDER BY......
(2)、獲取分頁數據：
在java中通過參數來獲取分頁數據：
String sql="select top "+pageSize+"* from table where id not in (select top"+(page-1)*pageSize+" id from table order by id ASC ) order by id ASC";
其中：
pageSize：英語指定分頁后每頁顯示的數據
page:用于指定當前頁數
3、MySQL數據庫分頁：
MySQL數據庫提供了LIMIT函數課一方便實現分頁：
SELECT [DISTINCT|UNIQUE]( *,columname [as alias])
FROM TABLE
WHERE ......
ORDER BY......
LIMIT [offset],rows
eg:
String strSql = "select * from tb_orderform order by id limit "+(page-1)*pagesize+","+pagesize+"";
4、Hibernate分頁：
Hibernate對分頁也提供了很好的支持，可以利用HQL和QBC檢索方式實現數據分頁。
eg:從索引位置3開始的6條記錄。
(1)、HQL方式：
Query query=session.createQuery("from User");
query.setFirstResult(3);
query.setMaxResult(6);
(2)、QBC方式：
Criteria criteria=session.createCriteria(User.class);
createria.setFirstResult(3);
createria.setMaxResult(6);
5、實踐：
(1)、實體類：
1
package com.domain;
2
3import org.apache.struts.action.ActionForm;
4
5
public class PeopleForm extends ActionForm 
{
6
private int id;
7 private String name;
8 private String sex ;
9 private int age;
10 private String job;
11
public int getId() {
12 return id;
13
}
14 public void setId(int id) {
15 this.id = id;
16 }
17 public String getName() {
18 return name;
19 }
20 public void setName(String name) {
21 this.name = name;
22 }
23 public String getSex() {
24 return sex;
25 }
26 public void setSex(String sex) {
27 this.sex = sex;
28 }
29 public int getAge() {
30 return age;
31 }
32 public void setAge(int age) {
33 this.age = age;
34 }
35 public String getJob() {
36 return job;
37 }
38 public void setJob(String job) {
39 this.job = job;
40 }
41
42
43
}
44
(2)、Dao:
數據庫操作：
1package com.dao;
2import java.sql.*;
3public class JDBConnection {
4Connection connection = null;
5static {
6 try {
7 Class.forName("com.mysql.jdbc.Driver"); // 靜態塊中實現加載數據庫驅動
8 } catch (ClassNotFoundException e) {
9 e.printStackTrace();
10 }
11}
12public Connection creatConnection(){
13 //創建數據庫連接對象
14 String url = "jdbc:mysql://localhost:3306/db_database20"; //指定數據庫連接URL
15 String userName = "root"; //連接數據庫用戶名
16 String passWord = "111"; //連接數據庫密碼
17 try {
18 connection = DriverManager.getConnection(url,userName, passWord); //獲取數據庫連接
19 } catch (SQLException e) {
20 e.printStackTrace();
21 }
22 return connection;
23}
24 //對數據庫的查詢操作
25public ResultSet executeQuery(String sql) {
26 ResultSet rs; //定義查詢結果集
27 try {
28 if (connection == null) {
29 creatConnection(); //創建數據庫連接
30 }
31 Statement stmt = connection.createStatement(); //創建Statement對象
32 rs = stmt.executeQuery(sql); //執行查詢SQL語句
33 } catch (SQLException e) {
34 System.out.println(e.getMessage());
35 return null; //有異常發生返回null
36 }
37 return rs; //返回查詢結果集對象
38}
39//關閉數據庫連接
40public void closeConnection() {
41 if (connection != null) { //如果Connection對象
42 try {
43 connection.close(); //關閉連接
44 } catch (SQLException e) {
45 e.printStackTrace();
46 } finally {
47 connection = null;
48 }
49 }
50}
51
52}
53
業務邏輯：
1package com.dao;
2import java.util.*;
3import com.domain.PeopleForm;
4import java.sql.ResultSet;
5import java.sql.*;
6public class PeopleDao {
7 private JDBConnection connection = null;
8 public PeopleDao() {
9 connection = new JDBConnection();
10 }
11 //查詢所有員工信息方法
12public List selectPeople() {
13 List list = new ArrayList(); //創建保存查詢結果集集合對象
14 PeopleForm form = null;
15 String sql = "select * from tb_emp"; //定義查詢tb_emp表中全部數據SQL語句
16 ResultSet rs = connection.executeQuery(sql); //執行查詢
17 try {
18 while (rs.next()) { //循環遍歷查詢結果集
19 form = new PeopleForm(); //創建ActionForm實例
20 form.setId(Integer.valueOf(rs.getString(1))); //獲取查詢結果
21 form.setName(rs.getString(2));
22 form.setSex(rs.getString(3));
23 form.setAge(rs.getInt(4));
24 form.setJob(rs.getString(5));
25 list.add(form); //向集合中添加對象
26 }
27 } catch (SQLException ex) {
28 }
29 connection.closeConnection(); //關閉數據連接
30 return list; //返回查詢結果
31}
32}
33
(3)Action:
1package com.action;
2
3import org.apache.struts.action.*;
4import javax.servlet.http.*;
5import com.dao.PeopleDao;
6import java.util.List;
7public class PeopleAction extends Action {
8private PeopleDao dao = null;
9public ActionForward execute(ActionMapping mapping, ActionForm form,
10 HttpServletRequest request, HttpServletResponse response) {
11 dao = new PeopleDao(); // 創建保存有數據查詢類對象
12 List list = dao.selectPeople(); // 調用數據查詢方法
13 int pageNumber = list.size(); // 計算出有多少條記錄
14 int maxPage = pageNumber; // 計算有多少頁數
15 String number = request.getParameter("i"); // 獲取保存在request對象中變量
16 if (maxPage % 4 == 0) { // “4”代表每頁顯示有4條記錄
17 maxPage = maxPage / 4; // 計算總頁數
18 } else { // 如果總頁數除以4不整除
19 maxPage = maxPage / 4 + 1; // 將總頁數加1
20 }
21 if (number == null) { // 如果保存在request范圍內的當前頁數為null
22 number = "0"; // 將number為0
23 }
24 request.setAttribute("number", String.valueOf((number))); // 將number保存在request范圍內
25 request.setAttribute("maxPage", String.valueOf(maxPage)); // 將分的總頁數保存在request范圍內
26 int nonce = Integer.parseInt(number) + 1;
27 request.setAttribute("nonce", String.valueOf(nonce));
28 request.setAttribute("pageNumber", String.valueOf(pageNumber));
29 request.setAttribute("list", list);
30 return mapping.findForward("peopleAction"); // 請求轉發地址
31}
32}
33
(4)、頁面代碼：
index.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<meta http-equiv="refresh" content="0;URL=peopleAction.do">
</head>
<body>
</body>
</html>
===============================================
pagenation.jsp
<%@ page contentType="text/html; charset=gbk" %>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<%@page import="com.domain.PeopleForm"%>
<html>
<meta http-equiv="Content-Type" content="text/html;charset=gbk">
<%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean"%>
<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html"%>
<%@ taglib uri="/WEB-INF/struts-logic.tld" prefix="logic"%>
<link href="css/style.css" rel="stylesheet" type="text/css">
<%
List list = (List)request.getAttribute("list"); //獲取保存在request范圍內的數據
int number = Integer.parseInt((String)request.getAttribute("number"));
int maxPage = Integer.parseInt((String)request.getAttribute("maxPage"));
int pageNumber = Integer.parseInt((String)request.getAttribute("pageNumber"));
int start = number*4;//開始條數
int over = (number+1)*4;//結束條數
int count=pageNumber-over;//還剩多少條記錄
if(count<=0){
over=pageNumber;
}
%>
<head>
<title>利用查詢結果集進行分頁</title>
</head>
<body >
<table width="756" height="650" border="0" align="center" cellpadding="0"
cellspacing="0" >
<tr>
<td height="280">
<table width="635" border="1" align="center">
<tr align="center" bgcolor="#FFFFFF">
<td width="112" height="17"><span class="style4">編號</span></td>
<td width="112"><span class="style4">姓名</span></td>
<td width="112"><span class="style4">性別</span></td>
<td width="112"><span class="style4">年齡</span></td>
<td width="142"><span class="style4">職位</span></td>
</tr>
<logic:iterate id="element" indexId="index" name="list"
offset="<%=String.valueOf(start)%>"
length="4"> <!-- 通過迭代標簽將員工信息輸出 -->
<tr align="center" bgcolor="#FFFFFF">
<td height="22">
<bean:write name="element" property="id"/>
</td>
<td>
<bean:write name="element" property="name"/>
</td>
<td><bean:write name="element" property="sex"/>
</td>
<td><bean:write name="element" property="age"/>歲</td>
<td><bean:write name="element" property="job"/>
</td>
</tr>
</logic:iterate>
</table>
</td>
</tr>
<tr>
<td valign="top">
<form name="form" method="post" action="peopleAction.do">
<table width="400" height="20" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="400" valign="middle" bgcolor="#CCCCCC">
共為<bean:write name="maxPage"/> <!-- 輸出總記錄數 -->
頁 共有<bean:write name="pageNumber"/> <!-- 輸出總分頁 -->
條 當前為第<bean:write name="nonce"/>頁 <!-- 輸出當前頁數 -->
<logic:equal name="nonce" value="1">
首頁
</logic:equal>
<logic:notEqual name="nonce" value="1"> <!-- 如果當前頁碼不等于1 -->
<a href="peopleAction.do?i=0">首頁</a> <!-- 提供首頁超鏈接 -->
</logic:notEqual>
<logic:lessEqual name="maxPage" value="${nonce}"> <!-- 如果當前頁碼不小于總頁數 -->
尾頁 <!-- 不提供尾頁超鏈接 -->
</logic:lessEqual>
<logic:greaterThan name="maxPage" value="${nonce}"> <!-- 如果當前頁碼小于總頁數 -->
<a href="peopleAction.do?i=<%=maxPage-1%>">尾頁</a> <!-- 提供尾頁超鏈接 -->
</logic:greaterThan>
<logic:equal name="nonce" value="1"> <!-- 如果當前頁碼等于1 -->
上一頁 <!-- 不提供上一頁超鏈接 -->
</logic:equal>
<logic:notEqual name="nonce" value="1"> <!-- 如果當前頁碼不等于1 -->
<a href="peopleAction.do?i=<%=number-1%>">上一頁</a> <!-- 提供上一頁超鏈接 -->
</logic:notEqual>
<logic:lessEqual name="maxPage" value="${nonce}">
下一頁
</logic:lessEqual>
<logic:greaterThan name="maxPage" value="${nonce}"> <!-- 如果當前頁面小于總頁數 -->
<a href="peopleAction.do?i=<%=number+1%>">下一頁</a> <!-- 提供下一頁超鏈接 -->
</logic:greaterThan>
</td>
</tr>
</table> </form></td>
</tr>
</table>
</body>
</html>
(5)、Struts-config.xml
1<?xml version="1.0" encoding="UTF-8"?>
2<!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.2//EN"
3
4"http://struts.apache.org/dtds/struts-config_1_2.dtd">
5
6<struts-config>
7 <data-sources />
8<form-beans>
9 <form-bean name="peopleForm" type="com.domain.PeopleForm" />
10</form-beans>
11 <global-exceptions />
12 <global-forwards />
13<action-mappings >
14 <action name="peopleForm" path="/peopleAction" scope="request"
15 type="com.action.PeopleAction" validate="true">
16 <forward name="peopleAction" path="/pagination.jsp" />
17 </action>
18</action-mappings>
19 <message-resources parameter="com.yourcompany.struts.ApplicationResources" />
20</struts-config>
21