使用jQuery，AJAX，JSON和MySQL的JSP分页示例
In this tutorial you will learn how to implement pagination in JSP.
在本教程中，您将学习如何在JSP中实现分页。
Pagination is a technique of dividing the content into several pages. Fetching all the data at a time is time consuming and it also results in poor user experience as the user has to scroll down to view data. So, pagination is used to speed up data fetching because only selected amount of data is fetched form server by user request.
分页是一种将内容分为几页的技术。 一次获取所有数据非常耗时，并且由于用户必须向下滚动才能查看数据，因此还会导致糟糕的用户体验。 因此，由于用户请求仅从服务器中提取了选定数量的数据，因此使用分页来加快数据提取的速度。
Below I have shared JSP pagination example. It is created using Eclipse IDE and the technologies I have used are JSP, jQuery, AJAX, JSON and MySQL.
下面我共享了JSP分页示例。 它是使用Eclipse IDE创建的，我使用的技术是JSP，jQuery，AJAX，JSON和MySQL。
JSP分页示例 (JSP Pagination Example)
Note: To use jQuery you have to add the jQuery library. You can download it from here. To use JSON here I have used json.simple library. You can download it from here. Make sure you include both of these libraries in your project.
注意：要使用jQuery，您必须添加jQuery库。 您可以从这里下载。 要在这里使用JSON，我使用了json.simple库。 您可以从这里下载。 确保在项目中包括这两个库。
Create a dynamic web project in Eclipse and add following code in respective files.
在Eclipse中创建一个动态Web项目，并在相应文件中添加以下代码。
index.jsp (index.jsp)
This page displays the data to user. All pagination logic is written here.
该页面向用户显示数据。 所有分页逻辑都在此处编写。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
<html>
<head>
<title>JSP Pagination Example</title>
<script src="jquery-1.11.3.js"></script>
</head>
<body>
<script type="text/javascript">
$(document).ready(function(){
var totalRecords;
var recordsPerPage=5;
var recordsToFetch=recordsPerPage;
var totalPages;
var currentPage=1;
var currentIndex=0;
$.get("processRequest.jsp?requestType=countRecords",function(data){
var JSONData=JSON.parse(data);
totalRecords=JSONData.count;
totalPages=Math.floor(totalRecords/recordsPerPage);
if(totalRecords%recordsPerPage!=0){
totalPages++;
}
if(totalRecords<recordsPerPage){
recordsToFetch=totalRecords%recordsPerPage;
}
else{
recordsToFetch=recordsPerPage;
}
$("#page").html("Page "+currentPage+" of "+totalPages);
});
$.get("processRequest.jsp?requestType=getRecords¤tIndex="+currentIndex+"&recordsToFetch="+recordsToFetch,function(data){
var JSONData=JSON.parse(data);
for(i=0;i<recordsToFetch;++i){
$("#div1").append("<p>"+(currentIndex+1)+". "+JSONData.record[i]+"</p>");
currentIndex++;
}
if(currentPage==totalPages){
$("#next").hide();
}
else{
$("#next").show();
}
if(currentPage==1){
$("#back").hide();
}
else{
$("#back").show();
}
});
$("#next").click(function(){
$("#div1").html("");
currentPage++;
if(currentPage==totalPages){
$("#next").hide();
if(totalRecords%recordsPerPage!=0){
recordsToFetch=totalRecords%recordsPerPage;
}
else{
recordsToFetch=recordsPerPage;
}
}
else{
$("#next").show();
recordsToFetch=recordsPerPage;
}
if(currentPage==1){
$("#back").hide();
}
else{
$("#back").show();
}
$.get("processRequest.jsp?requestType=getRecords¤tIndex="+currentIndex+"&recordsToFetch="+recordsToFetch,function(data){
var JSONData=JSON.parse(data);
for(i=0;i<recordsToFetch;++i){
$("#div1").append("<p>"+(currentIndex+1)+". "+JSONData.record[i]+"</p>");
currentIndex++;
}
});
$("#page").html("Page "+currentPage+" of "+totalPages);
});
$("#back").click(function(){
$("#div1").html("");
currentPage--;
currentIndex=currentIndex-recordsToFetch-recordsPerPage;
if(currentPage==totalPages){
$("#next").hide();
recordsToFetch=totalRecords%recordsPerPage;
}
else{
$("#next").show();
recordsToFetch=recordsPerPage;
}
if(currentPage==1){
$("#back").hide();
}
else{
$("#back").show();
}
$.get("processRequest.jsp?requestType=getRecords¤tIndex="+currentIndex+"&recordsToFetch="+recordsToFetch,function(data){
var JSONData=JSON.parse(data);
for(i=0;i<recordsToFetch;++i){
$("#div1").append("<p>"+(currentIndex+1)+". "+JSONData.record[i]+"</p>");
currentIndex++;
}
});
$("#page").html("Page "+currentPage+" of "+totalPages);
});
});
</script>
<div id="div1"></div><br/>
<button id="back">Back</button>
<button id="next">Next</button>
<p id="page"></p>
</body>
</html>
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
< html >
< head >
< title > JSP Pagination Example < / title >
<script src = "jquery-1.11.3.js" > </script>
< / head >
< body >
<script type = "text/javascript" >
$ ( document ) . ready ( function ( ) {
var totalRecords ;
var recordsPerPage = 5 ;
var recordsToFetch = recordsPerPage ;
var totalPages ;
var currentPage = 1 ;
var currentIndex = 0 ;
$ . get ( "processRequest.jsp?requestType=countRecords" , function ( data ) {
var JSONData = JSON . parse ( data ) ;
totalRecords = JSONData . count ;
totalPages = Math . floor ( totalRecords / recordsPerPage ) ;
if ( totalRecords % recordsPerPage != 0 ) {
totalPages ++ ;
}
if ( totalRecords < recordsPerPage ) {
recordsToFetch = totalRecords % recordsPerPage ;
}
else {
recordsToFetch = recordsPerPage ;
}
$ ( "#page" ) . html ( "Page " + currentPage + " of " + totalPages ) ;
} ) ;
$ . get ( "processRequest.jsp?requestType=getRecords¤tIndex=" + currentIndex + "&recordsToFetch=" + recordsToFetch , function ( data ) {
var JSONData = JSON . parse ( data ) ;
for ( i = 0 ; i < recordsToFetch ; ++ i ) {
$ ( "#div1" ) . append ( "<p>" + ( currentIndex + 1 ) + ". " + JSONData . record [ i ] + "</p>" ) ;
currentIndex ++ ;
}
if ( currentPage == totalPages ) {
$ ( "#next" ) . hide ( ) ;
}
else {
$ ( "#next" ) . show ( ) ;
}
if ( currentPage == 1 ) {
$ ( "#back" ) . hide ( ) ;
}
else {
$ ( "#back" ) . show ( ) ;
}
} ) ;
$ ( "#next" ) . click ( function ( ) {
$ ( "#div1" ) . html ( "" ) ;
currentPage ++ ;
if ( currentPage == totalPages ) {
$ ( "#next" ) . hide ( ) ;
if ( totalRecords % recordsPerPage != 0 ) {
recordsToFetch = totalRecords % recordsPerPage ;
}
else {
recordsToFetch = recordsPerPage ;
}
}
else {
$ ( "#next" ) . show ( ) ;
recordsToFetch = recordsPerPage ;
}
if ( currentPage == 1 ) {
$ ( "#back" ) . hide ( ) ;
}
else {
$ ( "#back" ) . show ( ) ;
}
$ . get ( "processRequest.jsp?requestType=getRecords¤tIndex=" + currentIndex + "&recordsToFetch=" + recordsToFetch , function ( data ) {
var JSONData = JSON . parse ( data ) ;
for ( i = 0 ; i < recordsToFetch ; ++ i ) {
$ ( "#div1" ) . append ( "<p>" + ( currentIndex + 1 ) + ". " + JSONData . record [ i ] + "</p>" ) ;
currentIndex ++ ;
}
} ) ;
$ ( "#page" ) . html ( "Page " + currentPage + " of " + totalPages ) ;
} ) ;
$ ( "#back" ) . click ( function ( ) {
$ ( "#div1" ) . html ( "" ) ;
currentPage -- ;
currentIndex = currentIndex - recordsToFetch - recordsPerPage ;
if ( currentPage == totalPages ) {
$ ( "#next" ) . hide ( ) ;
recordsToFetch = totalRecords % recordsPerPage ;
}
else {
$ ( "#next" ) . show ( ) ;
recordsToFetch = recordsPerPage ;
}
if ( currentPage == 1 ) {
$ ( "#back" ) . hide ( ) ;
}
else {
$ ( "#back" ) . show ( ) ;
}
$ . get ( "processRequest.jsp?requestType=getRecords¤tIndex=" + currentIndex + "&recordsToFetch=" + recordsToFetch , function ( data ) {
var JSONData = JSON . parse ( data ) ;
for ( i = 0 ; i < recordsToFetch ; ++ i ) {
$ ( "#div1" ) . append ( "<p>" + ( currentIndex + 1 ) + ". " + JSONData . record [ i ] + "</p>" ) ;
currentIndex ++ ;
}
} ) ;
$ ( "#page" ) . html ( "Page " + currentPage + " of " + totalPages ) ;
} ) ;
} ) ;
</script>
< div id = "div1" > < / div > < br / >
< button id = "back" > Back < / button >
< button id = "next" > Next < / button >
< p id = "page" > < / p >
< / body >
< / html >
|
processRequest.jsp (processRequest.jsp)
This page process request by fetching data.
此页面通过获取数据来处理请求。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<%@page import="com.PaginationDAO"%>
<%
String req=request.getParameter("requestType");
String data="";
if(req.equals("countRecords")){
data=PaginationDAO.countRecords();
}
if(req.equals("getRecords")){
String start=request.getParameter("currentIndex");
String total=request.getParameter("recordsToFetch");
data=PaginationDAO.getRecords(start, total);
}
out.print(data);
%>
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<% @page import = "com.PaginationDAO" %>
<%
String req = request . getParameter ( "requestType" ) ;
String data = "" ;
if ( req . equals ( "countRecords" ) ) {
data = PaginationDAO . countRecords ( ) ;
}
if ( req . equals ( "getRecords" ) ) {
String start = request . getParameter ( "currentIndex" ) ;
String total = request . getParameter ( "recordsToFetch" ) ;
data = PaginationDAO . getRecords ( start , total ) ;
}
out . print ( data ) ;
%>
|
DBConnection.java (DBConnection.java)
Contains code for database connection.
包含用于数据库连接的代码。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
package com;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
final static String URL="localhost:3306/";
final static String DATABASE="pagination";
final static String USER="root";
final static String PASS="root";
final static String DATA_TABLE="data";
final static String ID_COL="id";
final static String NAME_COL="name";
public static Connection getCon(){
Connection con=null;
try{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://"+URL+DATABASE,USER,PASS);
}catch(Exception e){
e.printStackTrace();
}
return con;
}
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
package com ;
import java . sql . Connection ;
import java . sql . DriverManager ;
public class DBConnection {
final static String URL = "localhost:3306/" ;
final static String DATABASE = "pagination" ;
final static String USER = "root" ;
final static String PASS = "root" ;
final static String DATA_TABLE = "data" ;
final static String ID_COL = "id" ;
final static String NAME_COL = "name" ;
public static Connection getCon ( ) {
Connection con = null ;
try {
Class . forName ( "com.mysql.jdbc.Driver" ) ;
con = DriverManager . getConnection ( "jdbc:mysql://" + URL + DATABASE , USER , PASS ) ;
} catch ( Exception e ) {
e . printStackTrace ( ) ;
}
return con ;
}
}
|
分页DAO.java (PaginationDAO.java)
Fetch data from database and convert it into JSON format.
从数据库获取数据并将其转换为JSON格式。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
package com;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
public class PaginationDAO {
public static String countRecords(){
String query="select count(*) from "+DBConnection.DATA_TABLE;
int count=0;
JSONObject obj=new JSONObject();
try{
Connection con=DBConnection.getCon();
PreparedStatement ps=con.prepareStatement(query);
ResultSet rs=ps.executeQuery();
if(rs.next()){
count=rs.getInt(1);
obj.put("count",count);
}
}catch(Exception e){
e.printStackTrace();
}
return obj.toString();
}
public static String getRecords(String start,String total){
String query="select * from "+DBConnection.DATA_TABLE+" limit "+start+","+total;
JSONObject obj=new JSONObject();
JSONArray arr=new JSONArray();
try{
Connection con=DBConnection.getCon();
PreparedStatement ps=con.prepareStatement(query);
ResultSet rs=ps.executeQuery();
while(rs.next()){
arr.add(rs.getString(DBConnection.NAME_COL));
}
obj.put("record",arr);
}catch(Exception e){
e.printStackTrace();
}
return obj.toString();
}
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
package com ;
import java . sql . Connection ;
import java . sql . PreparedStatement ;
import java . sql . ResultSet ;
import org . json . simple . JSONArray ;
import org . json . simple . JSONObject ;
public class PaginationDAO {
public static String countRecords ( ) {
String query = "select count(*) from " + DBConnection . DATA_TABLE ;
int count = 0 ;
JSONObject obj = new JSONObject ( ) ;
try {
Connection con = DBConnection . getCon ( ) ;
PreparedStatement ps = con . prepareStatement ( query ) ;
ResultSet rs = ps . executeQuery ( ) ;
if ( rs . next ( ) ) {
count = rs . getInt ( 1 ) ;
obj . put ( "count" , count ) ;
}
} catch ( Exception e ) {
e . printStackTrace ( ) ;
}
return obj . toString ( ) ;
}
public static String getRecords ( String start , String total ) {
String query = "select * from " + DBConnection . DATA_TABLE + " limit " + start + "," + total ;
JSONObject obj = new JSONObject ( ) ;
JSONArray arr = new JSONArray ( ) ;
try {
Connection con = DBConnection . getCon ( ) ;
PreparedStatement ps = con . prepareStatement ( query ) ;
ResultSet rs = ps . executeQuery ( ) ;
while ( rs . next ( ) ) {
arr . add ( rs . getString ( DBConnection . NAME_COL ) ) ;
}
obj . put ( "record" , arr ) ;
} catch ( Exception e ) {
e . printStackTrace ( ) ;
}
return obj . toString ( ) ;
}
}
|
数据库 (Database)
The database table that I have used has following structure.
我使用的数据库表具有以下结构。
In this example I am fetching only 5 records at a time from the database and then displaying them. You can change the number of records per page according to you.
在此示例中，我一次仅从数据库中获取5条记录，然后显示它们。 您可以根据需要更改每页的记录数。
Download the project from below link. It also contains the database backup file.
从下面的链接下载项目。 它还包含数据库备份文件。
下载专案 (Download Project)
If you are facing any difficulty then feel free to ask it by commenting below.
如果您遇到任何困难，请随时在下面的评论中提问。
翻译自: https://www.thecrazyprogrammer.com/2016/05/jsp-pagination-example-using-jquery-ajax-json-mysql.html