Pagination in java jsp.

Monday, May 25, 2015

Pagination in java jsp.

The pagination is very useful when we have large number of records in a table. If we displaying all the record the at a time it will take more time to load all the data and taking more memory. So it's not a good for either memory or performance point of view. If we go for pagination it will load limited amount of record at a time so it will good for both memory and performance point of view.

In this example i'm going to display one of db table record in tabular format using pagination. I'm loading only 5 record at a time. Each request fetching  only 5 Record at a time by using below variable-
showRows = 5 (display the number of record per page)
totalRecords = 5 display the number of page at a time like 1,2,3,4,5 hyper link will come on each time



I'm using below query for fetching the data based on iPageNo (from which record we have to display the data) and showRows (Number of record)
String query1 = "SELECT SQL_CALC_FOUND_ROWS * FROM " + tablename + " limit " + iPageNo + "," + showRows + "";

I'm using below query for getting the total number of record in a particular table "paginationTest"
String query2 = "SELECT FOUND_ROWS() as cnt";

And displaying the record in Html table-

1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
<table>
 <tr>
  <th>Name</th>
  <th>Age</th>
  <th>Qualification</th>
 </tr>
 <%
  while (rs1.next()) {
 %>
 <tr>
  <td><%=rs1.getString("name")%></td>
  <td><%=rs1.getString("age")%></td>
  <td><%=rs1.getString("qualification")%></td>
 </tr>
 <%
   ques++;
  }
 %>
</table>

Also using some css to making better UI for pagination and table records.
 
I'm taking MySql as database please refer my previous post about mysql data base connection (http://atozjavatutorials.blogspot.in/2015/03/how-to-connect-mysql-database-in-java.html).

Steps for creating pagination in java

Step 1: Create table "paginationtest"

1
2
3
4
5
CREATE TABLE `paginationtest` (
  `name` varchar(20) default NULL,
  `age` int(3) default NULL,
  `qualification` varchar(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 2: Create paginationTest.jsp

paginationTest.jsp

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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
<%@ page language="java" %>
<%@ page import="java.sql.*" %>
<%!
    public int nullIntconvert(String str) {
        int num = 0;
        if (str == null) {
            str = "0";
        } else if ((str.trim()).equals("null")) {
            str = "0";
        } else if (str.equals("")) {
            str = "0";
        }
        try {
            num = Integer.parseInt(str);
        } catch (Exception e) {
        }
        return num;
    }
%>
<%
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8", "root", "root");
    ResultSet rs1 = null;
    ResultSet rs2 = null;
    PreparedStatement ps1 = null;
    PreparedStatement ps2 = null;

    int showRows = 5;
    int totalRecords = 5;
    int totalRows = nullIntconvert(request.getParameter("totalRows"));
    int totalPages = nullIntconvert(request.getParameter("totalPages"));
    int iPageNo = nullIntconvert(request.getParameter("iPageNo"));
    int cPageNo = nullIntconvert(request.getParameter("cPageNo"));

    int startResult = 0;
    int endResult = 0;
    int ques = 1;
    if (iPageNo == 0) {
        iPageNo = 0;
    } else {
        iPageNo = Math.abs((iPageNo - 1) * showRows);
    }
    String tablename = "paginationTest";
    String query1 = "SELECT SQL_CALC_FOUND_ROWS * FROM " + tablename + " limit " + iPageNo + "," + showRows + "";
    ps1 = connection.prepareStatement(query1);
    rs1 = ps1.executeQuery();

    String query2 = "SELECT FOUND_ROWS() as cnt";
    ps2 = connection.prepareStatement(query2);
    rs2 = ps2.executeQuery();
    if (rs2.next()) {
        totalRows = rs2.getInt("cnt");
    }
%>
<!DOCTYPE html>
<html>
    <head>
        <title>Pagination Test</title>
        <style>
            table{
                margin:20px 5px;
            }
            table{
                border-collapse: collapse;
            }
            table, td {
                border:1px solid buttonface;
                background: #ddd;
                color:black;
                width:400px;
            }
            table, tr, th{
                background: #0066CC;
                color:#ddd;
            }
            span{
                padding:5px 10px;
                background: #ddd;
                margin:15px 5px;
                border:1px solid #ccc;
            }
            span:hover{
                background: #ccc;
            }
        </style>
    </head>
    <body>

        <h1>Pagination Test</h1>
        <table>
            <tr>
                <th>Name</th>
                <th>Age</th>
                <th>Qualification</th>
            </tr>
            <%
                while (rs1.next()) {
            %>
            <tr>
                <td><%=rs1.getString("name")%></td>
                <td><%=rs1.getString("age")%></td>
                <td><%=rs1.getString("qualification")%></td>
            </tr>
            <%
                    ques++;
                }
            %>
        </table>
        <form>
            <input type="hidden" name="iPageNo" value="<%=iPageNo%>">
            <input type="hidden" name="cPageNo" value="<%=cPageNo%>">
            <input type="hidden" name="showRows" value="<%=showRows%>">
            <%
                try {
                    if (totalRows < (iPageNo + showRows)) {
                        endResult = totalRows;
                    } else {
                        endResult = (iPageNo + showRows);
                    }
                    startResult = (iPageNo + 1);
                    totalPages = ((int) (Math.ceil((double) totalRows / showRows)));
                } catch (Exception e) {
                    e.printStackTrace();
                }
                int i = 0;
                int cPage = 0;
                if (totalRows != 0) {
                    cPage = ((int) (Math.ceil((double) endResult / (totalRecords * showRows))));

                    int prePageNo = (cPage * totalRecords) - ((totalRecords - 1) + totalRecords);
                    if ((cPage * totalRecords) - (totalRecords) > 0) {
            %>

            <span id="previous"><a href="paginationTest.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>" class="numbersFont">Previous</a></span>
            <%
                }
                for (i = ((cPage * totalRecords) - (totalRecords - 1)); i <= (cPage * totalRecords); i++) {
                    if (i == ((iPageNo / showRows) + 1)) {%>
            <span class="numbers"><a href="paginationTest.jsp?iPageNo=<%=i%>" class="numbersFont"class="numbersFont"><%=i%></a></span>
                <%
                } else if (i <= totalPages) {
                %>
            <span class="numbers"><a href="paginationTest.jsp?iPageNo=<%=i%>" class="numbersFont" class="numbersFont"><%=i%></a></span>
                <%
                        }
                    }
                    if (totalPages > totalRecords && i < totalPages) {
                %>
            <span id="next"><a href="paginationTest.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>" class="numbersFont">Next</a></span>
            <%
                    }
                }
                connection.close();

                connection = null;
                rs1 = null;
                rs2 = null;
                ps1 = null;
                ps2 = null;
                query1 = null;
                query2 = null;
            %>
        </form>
        </br>
    </body>
</html>

Posted by
Labels: ,

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )