JavaWeb分页显示内容和数据库分页查询

什么是分页查询？

从数据库中查询数据，在客户端显示出来。当数据少时，可以在一个页面内显示完成。然而，如果查询记录是几百条、上千条呢？直接一个页面显示完全的话，表格得多长啊。。。。。。这时，我们可以用分页技术。

效果图如下：

 分页效果的实现

原理：

在服务端分页。跳到第n页才查询、显示第n页内容。要点就是根据客户端表格的“页面”计算出数据库要查询的当前页面的第一条记录的位置。优点：实时性：跳页才查询。数据量小：只加载当前页的记录进行显示。

    重点在于两条语句：

select count(*) from ...:查询得到记录总条数

 select * from .. limit pageNo,rowsCount:查询从第pageNo条开始的rowsCount条数据。

然后把数据放到一个专门带数据的Page类，Page的类构造函数封装了计算页码的算法。

 

  跳页的实现：跳页是通过重定向来实现的，通过向当前网页传进待显示的pages，在跳转后根据pages重新算出页面显示的第一条，查limit条显示。

<div class="box-tools pull-right">
                        	
                            <ul class="pagination">
                           		<li>
                           		 	<div class="box-tools pull-right">
                           				<input onkeyup="this.value=this.value.replace(/\D/g,'')" onafterpaste="this.value=this.value.replace(/\D/g,'')" name="search" type ="text" placeholder="请输入页数">
                           				<input type="button" value="GO">
                        			</div>
                           		</li>
                                <li>
                                    <a href="${pageContext.request.contextPath }/ShowTeacherController?pageNum=1" aria-label="Previous">首页</a>
                                </li>
                                
                            		
                               	<c:if test="${page.pageNum>1 }">
                               		<li><a href="${pageContext.request.contextPath }/${page.url}?pageNum=${page.pageNum-1}">上一页</a></li>
                               	</c:if>   
                               	
                               	<c:forEach begin="${page.startPage }" end="${page.endPage }" var="pageNum">
                               		 <li><a href="${pageContext.request.contextPath }/${page.url}?pageNum=${pageNum}">${pageNum }</a></li>
                               	</c:forEach>
                                <c:if test="${page.pageNum<page.endPage}">
                                	<li><a href="${pageContext.request.contextPath }/${page.url}?pageNum=${page.pageNum+1}">下一页</a></li>
                                </c:if>
                                <li>
                                    <a href="${pageContext.request.contextPath }/${page.url}?pageNum=${page.totalPageNum}" aria-label="Next">尾页</a>
                                </li>
                                
                            </ul>
                        </div>

                    </div>

数据显示效果:

</div>
                            <!--工具栏/-->

                            <!--数据列表-->
                            <table id="dataList" class="table table-bordered table-striped table-hover dataTable">
                                <thead>
                                    <tr>
                                        <th class="" style="padding-right:0px;">
                                            <input id="selall" type="checkbox" class="icheckbox_square-blue">
                                        </th>
                                        <th class="sorting_asc">ID</th>
                                        <th class="sorting_desc">姓名</th>
                                        <th class="sorting_asc sorting_asc_disabled">密码 </th>
                                        <th class="sorting_desc sorting_desc_disabled">性别</th>
                                        <th class="sorting">邮箱 </th>
                                        <th class="text-center sorting">备注</th>
                                        <th class="text-center">操作</th>
                                    </tr>
                                </thead>
                                <tbody>
                                	<c:forEach items="${page.list }" var="teacher">
                                		<tr>
	                                        <td><input name="ids" type="checkbox"></td>
	                                        <td>${teacher.id }</td>
	                                        <td>${teacher.name }</td>
	                                        <td>${teacher.password }</td>
	                                        <td>${teacher.gender }</td>
	                                        <td>${teacher.email }</td>
	                                        <td class="text-center">${teacher.remark }</td>
	                                        <td class="text-center">
	                                            <a href="#"><button type="button" class="btn bg-olive btn-xs">编辑</button></a>
	                                            <a href="#"><button type="button" class="btn bg-olive btn-xs">删除</button></a>
	                                        </td>
                                   		 </tr>
                                	</c:forEach>
                                     

                                </tbody>
                                <!--
                            <tfoot>
                            <tr>
                            <th>Rendering engine</th>
                            <th>Browser</th>
                            <th>Platform(s)</th>
                            <th>Engine version</th>
                            <th>CSS grade</th>
                            </tr>
                            </tfoot>-->
                            </table>
                            <!--数据列表/-->

                           
                            <!--工具栏/-->

                        </div>

java代码:

controller层

 service层

 dao层

//查询总的列数
	@Override
	public int findTotalNum() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int totalNum = 0;
		conn = DBUtil.getConnection();
		String sql = "select count(*) from teacher";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				totalNum = rs.getInt(1);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}DBUtil.closeAll(conn, ps, rs);
		return totalNum;
	}
	
	//返回分页查询的每页信息
	@Override
	public List<Teacher> findTeacherBypage(int startIndex, int pageSize) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Teacher> list = new ArrayList<Teacher>();
		conn = DBUtil.getConnection();
		String sql = "select id,name,password,gender,email,remark from teacher limit ?,?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while(rs.next()){
				Teacher teacher = new Teacher();
				teacher.setId(rs.getInt(1));
				teacher.setName(rs.getString(2));
				teacher.setPassword(rs.getString(3));
				teacher.setGender(rs.getString(4));
				teacher.setEmail(rs.getString(5));
				teacher.setRemark(rs.getString(6));
				list.add(teacher);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}DBUtil.closeAll(conn, ps, rs);
		return list;
	}