Posted on 2011-05-17 00:21
簡簡單單 閱讀(4380)
評論(0) 編輯 收藏 所屬分類:
DataBase
1、數(shù)據(jù)庫分頁技術(shù)的基本思想：
(1)、確定記錄跨度。即確定每頁顯示數(shù)據(jù)的條數(shù)。
(2)、獲取記錄總數(shù)。即獲取要顯示在頁面中的總記錄數(shù)。其目的是依據(jù)總記錄數(shù)來技術(shù)得到總頁數(shù)。
(3)、確定分頁后的總頁數(shù)。依據(jù)公式“總記錄數(shù)/跨度”計(jì)算分頁后的總頁數(shù)。要注意的是：如果總頁數(shù)有余數(shù)，要去掉余數(shù)，將總頁數(shù)加1。修改為:（總記錄數(shù)-1）/跨度+1
(4)、根據(jù)當(dāng)前頁數(shù)顯示數(shù)據(jù)。注意：如果該頁數(shù)小于1，則使其等于1；如果頁數(shù)大于最大頁數(shù)，則使其等于最大頁數(shù)。
(5)、通過For循環(huán)語句分頁顯示數(shù)據(jù)。
2、使用標(biāo)準(zhǔn)的SQL語句實(shí)現(xiàn)數(shù)據(jù)分頁：
(1)、獲取前n條數(shù)據(jù)：
SELECT TOP n[PERCENT] *
FROM TABLE
WHERE ......
ORDER BY......
(2)、獲取分頁數(shù)據(jù)：
在java中通過參數(shù)來獲取分頁數(shù)據(jù)：
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：英語指定分頁后每頁顯示的數(shù)據(jù)
page:用于指定當(dāng)前頁數(shù)
3、MySQL數(shù)據(jù)庫分頁：
MySQL數(shù)據(jù)庫提供了LIMIT函數(shù)課一方便實(shí)現(xiàn)分頁：
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檢索方式實(shí)現(xiàn)數(shù)據(jù)分頁。
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、實(shí)踐：
(1)、實(shí)體類：
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:
數(shù)據(jù)庫操作：
1package com.dao;
2import java.sql.*;
3public class JDBConnection {
4Connection connection = null;
5static {
6 try {
7 Class.forName("com.mysql.jdbc.Driver"); // 靜態(tài)塊中實(shí)現(xiàn)加載數(shù)據(jù)庫驅(qū)動
8 } catch (ClassNotFoundException e) {
9 e.printStackTrace();
10 }
11}
12public Connection creatConnection(){
13 //創(chuàng)建數(shù)據(jù)庫連接對象
14 String url = "jdbc:mysql://localhost:3306/db_database20"; //指定數(shù)據(jù)庫連接URL
15 String userName = "root"; //連接數(shù)據(jù)庫用戶名
16 String passWord = "111"; //連接數(shù)據(jù)庫密碼
17 try {
18 connection = DriverManager.getConnection(url,userName, passWord); //獲取數(shù)據(jù)庫連接
19 } catch (SQLException e) {
20 e.printStackTrace();
21 }
22 return connection;
23}
24 //對數(shù)據(jù)庫的查詢操作
25public ResultSet executeQuery(String sql) {
26 ResultSet rs; //定義查詢結(jié)果集
27 try {
28 if (connection == null) {
29 creatConnection(); //創(chuàng)建數(shù)據(jù)庫連接
30 }
31 Statement stmt = connection.createStatement(); //創(chuàng)建Statement對象
32 rs = stmt.executeQuery(sql); //執(zhí)行查詢SQL語句
33 } catch (SQLException e) {
34 System.out.println(e.getMessage());
35 return null; //有異常發(fā)生返回null
36 }
37 return rs; //返回查詢結(jié)果集對象
38}
39//關(guān)閉數(shù)據(jù)庫連接
40public void closeConnection() {
41 if (connection != null) { //如果Connection對象
42 try {
43 connection.close(); //關(guān)閉連接
44 } catch (SQLException e) {
45 e.printStackTrace();
46 } finally {
47 connection = null;
48 }
49 }
50}
51
52}
53
業(yè)務(wù)邏輯：
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(); //創(chuàng)建保存查詢結(jié)果集集合對象
14 PeopleForm form = null;
15 String sql = "select * from tb_emp"; //定義查詢tb_emp表中全部數(shù)據(jù)SQL語句
16 ResultSet rs = connection.executeQuery(sql); //執(zhí)行查詢
17 try {
18 while (rs.next()) { //循環(huán)遍歷查詢結(jié)果集
19 form = new PeopleForm(); //創(chuàng)建ActionForm實(shí)例
20 form.setId(Integer.valueOf(rs.getString(1))); //獲取查詢結(jié)果
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(); //關(guān)閉數(shù)據(jù)連接
30 return list; //返回查詢結(jié)果
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(); // 創(chuàng)建保存有數(shù)據(jù)查詢類對象
12 List list = dao.selectPeople(); // 調(diào)用數(shù)據(jù)查詢方法
13 int pageNumber = list.size(); // 計(jì)算出有多少條記錄
14 int maxPage = pageNumber; // 計(jì)算有多少頁數(shù)
15 String number = request.getParameter("i"); // 獲取保存在request對象中變量
16 if (maxPage % 4 == 0) { // “4”代表每頁顯示有4條記錄
17 maxPage = maxPage / 4; // 計(jì)算總頁數(shù)
18 } else { // 如果總頁數(shù)除以4不整除
19 maxPage = maxPage / 4 + 1; // 將總頁數(shù)加1
20 }
21 if (number == null) { // 如果保存在request范圍內(nèi)的當(dāng)前頁數(shù)為null
22 number = "0"; // 將number為0
23 }
24 request.setAttribute("number", String.valueOf((number))); // 將number保存在request范圍內(nèi)
25 request.setAttribute("maxPage", String.valueOf(maxPage)); // 將分的總頁數(shù)保存在request范圍內(nèi)
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"); // 請求轉(zhuǎn)發(fā)地址
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范圍內(nèi)的數(shù)據(jù)
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;//開始條數(shù)
int over = (number+1)*4;//結(jié)束條數(shù)
int count=pageNumber-over;//還剩多少條記錄
if(count<=0){
over=pageNumber;
}
%>
<head>
<title>利用查詢結(jié)果集進(jìn)行分頁</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"> <!-- 通過迭代標(biāo)簽將員工信息輸出 -->
<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"/> <!-- 輸出總記錄數(shù) -->
頁 共有<bean:write name="pageNumber"/> <!-- 輸出總分頁 -->
條 當(dāng)前為第<bean:write name="nonce"/>頁 <!-- 輸出當(dāng)前頁數(shù) -->
<logic:equal name="nonce" value="1">
首頁
</logic:equal>
<logic:notEqual name="nonce" value="1"> <!-- 如果當(dāng)前頁碼不等于1 -->
<a href="peopleAction.do?i=0">首頁</a> <!-- 提供首頁超鏈接 -->
</logic:notEqual>
<logic:lessEqual name="maxPage" value="${nonce}"> <!-- 如果當(dāng)前頁碼不小于總頁數(shù) -->
尾頁 <!-- 不提供尾頁超鏈接 -->
</logic:lessEqual>
<logic:greaterThan name="maxPage" value="${nonce}"> <!-- 如果當(dāng)前頁碼小于總頁數(shù) -->
<a href="peopleAction.do?i=<%=maxPage-1%>">尾頁</a> <!-- 提供尾頁超鏈接 -->
</logic:greaterThan>
<logic:equal name="nonce" value="1"> <!-- 如果當(dāng)前頁碼等于1 -->
上一頁 <!-- 不提供上一頁超鏈接 -->
</logic:equal>
<logic:notEqual name="nonce" value="1"> <!-- 如果當(dāng)前頁碼不等于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}"> <!-- 如果當(dāng)前頁面小于總頁數(shù) -->
<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