数据库记录分页的思想
程序员文章站
2024-03-18 13:44:40
...
今天写了段jsp页面分页的代码,这里我就只讲下分页的思想 。代码附带部分
分页种类;
逻辑分页
一次查询数据库所有记录,放入内存集合中。第一次查询比较慢,数据够多可能会溢出。只对数据库进行一次操作。
物理分页
每次都去查询数据库.,查一次放一次。不会溢出,我们基本上用的都是物理分页。
分页的原理
用户输入的参数,前台页面到后端的数据
CurrentPage 当前页
PageSize 每页显示的数量
Sql执行后出来的参数:
Total 总记录数:
SELECT COUNT(*) AS total FROM users
countPage总页数:
countPage = (total % pagesize == 0 ? total / pagesize : total/ pagesize + 1);
message查询结果集:
SELECT * FROM users order by id asc LIMIT ?,?
第一个参数为开始的索引 BeginIndex=(currentPage-1)*pagesize
第二个参数为每页的记录数:pagesize;
手动计算:
BeginPage:首页 默认1
countPage:末页/总页数
PrevPage:上一页 Prevpage>1? currentPage-1:1;(已经是第一页就减不了)
NextPage:下一页Nextpage< Totalpage?currentPage+1:countPage(注意countPage要定义在nextpage的前面)
以上分页所有用到的值都取到了,只需要数据交互就行了 下面是获取数值封装的方法。只需要在servlet中调用
public class Users {
private final int pagesize=2;//每页的记录
int total=0;
int countPage = 0;
public int getTotal() {
Connection conn=JdbcUtils.getConn();
PreparedStatement ps = null;
ResultSet rs = null;
int total = 0;
String sql = "SELECT COUNT(*) AS num FROM users"; //查询记录条数,然后把查询结果另外起一个别名,叫做num
try {
ps = (PreparedStatement) conn.prepareStatement(sql);
rs = ps.executeQuery();
if (rs.next()) {
total = rs.getInt("num"); //total为总条数
}
}catch (SQLException e) {
throw new RuntimeException(e.getMessage(), e);
}
return total;
}
public int getCountPage() throws SQLException {
total=this.getTotal();
// 总页数=总条数/每页显示最大留言数,能除尽时直接取结果,不能除尽时,结果加1,多加一页来显示
countPage = (total % pagesize == 0 ? total / pagesize : total
/ pagesize + 1);
if (countPage != 0)
return countPage;
return countPage + 1; //没有第0页,所以加1
}
public List<User> getMessage(int currentPage) throws SQLException {
//currentPage为当前页数
List<User> list = new ArrayList<User>();
String sql = "SELECT * FROM users order by id asc LIMIT ?,?";
/*
* order by id desc --按id列大小降序排列,不加desc就是升序排列
* LIMIT 100,15--从查询的结果中第100条开始取出15条数据
*/
try {
Connection conn=JdbcUtils.getConn();
PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
ps.setInt(1, (currentPage - 1) * pagesize);
ps.setInt(2, pagesize);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
User user=new User();
user.setId(rs.getInt("id"));
user.setAge(rs.getInt("age"));
user.setName(rs.getString("name"));
user.setTel(rs.getString("tel"));
list.add(user); //将找出的数据存入messageList
}
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
} finally {
}
return list;
}
}
@WebServlet("/FindServlet")
public class FindServlet extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
Users messageService = new Users();
int countPage = messageService.getCountPage();
System.out.println("总页数"+countPage);
//从jsp页面获取当前页数
int currentPage = 1;
try {
String scurrentPage =request.getParameter("currentPage");
System.out.println("jsp传递的页码"+scurrentPage);
//判断输入页码是否合法
if(scurrentPage==null||scurrentPage=="") {
currentPage=1;
}else {
currentPage = Integer.parseInt(scurrentPage);
if(currentPage<=0) {
currentPage=1;
}
if(currentPage>countPage) {
currentPage=countPage;
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//查询数据库获得数据计算出总页数
int total=messageService.getTotal();
//将当前页数,总页数,以及找出的数据返回给jsp页面
request.setAttribute("total", total);
request.setAttribute("countPage", countPage);
request.setAttribute("currentPage", currentPage);
request.setAttribute("list", messageService.getMessage(currentPage));
/*Connection conn=JdbcUtils.getConn();
String sql="select * from users";
Statement st=(Statement) conn.createStatement();
ResultSet resultSet = st.executeQuery(sql);
List<User> list = new ArrayList<User>();
while(resultSet.next()) {
User user=new User();
user.setId(resultSet.getInt("id"));
user.setAge(resultSet.getInt("age"));
user.setName(resultSet.getString("name"));
user.setTel(resultSet.getString("tel"));
list.add(user);
}
request.setAttribute("list", list);
resultSet.close();
st.close();*/
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
request.getRequestDispatcher("list.jsp").forward(request, response);
}
protected void doPost(String page1,HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
//doGet(req, resp);
}
}
前台代码
<div>
<form action="FindServlet">
第${requestScope.currentPage}页/共${requestScope.countPage}页,共${requestScope.total }条记录
<a
href="${pageContext.request.contextPath}/FindServlet?currentPage=1">首页</a><span> </span>
<c:choose>
<c:when test="${requestScope.currentPage==1}">
上一页
</c:when>
<c:otherwise>
<a
href="${pageContext.request.contextPath}/FindServlet?currentPage=${requestScope.currentPage-1}">上一页</a>
</c:otherwise>
</c:choose>
<%--计算begin和end --%>
<c:choose>
<%--如果总页数不足10,那么就把所有的页都显示出来 --%>
<c:when test="${requestScope.countPage<=10}">
<c:set var="begin" value="1" />
<c:set var="end" value="${requestScope.countPage}" />
</c:when>
<c:otherwise>
<%--如果总页数大于10,通过公式计算出begin和end --%>
<c:set var="begin" value="${requestScope.currentPage-5}" />
<c:set var="end" value="${requestScope.currentPage+4}" />
<%--头溢出 --%>
<c:if test="${begin<1}">
<c:set var="begin" value="1"></c:set>
<c:set var="end" value="10"></c:set>
</c:if>
<%--尾溢出 --%>
<c:if test="${end>requestScope.countPage}">
<c:set var="begin" value="${requestScope.countPage - 9}"></c:set>
<c:set var="end" value="${requestScope.countPage}"></c:set>
</c:if>
</c:otherwise>
</c:choose>
<%--循环显示页码列表 --%>
<c:forEach var="i" begin="${begin}" end="${end}">
<c:choose>
<c:when test="${i == requestScope.currentPage}">
[${i}]
</c:when>
<c:otherwise>
<a href="<c:url value ='/FindServlet
?currentPage=${i}'/>">[${i}]</a>
</c:otherwise>
</c:choose>
</c:forEach>
<c:choose>
<c:when test="${requestScope.currentPage==requestScope.countPage}">
下一页
</c:when>
<c:otherwise>
<a
href="${pageContext.request.contextPath}/FindServlet?currentPage=${requestScope.currentPage+1}"> 下一页</a>
</c:otherwise>
</c:choose>
<span> </span><a
href="${pageContext.request.contextPath}/FindServlet?currentPage=${requestScope.countPage}">尾页</a>
<input type="number" name="currentPage" style="width:50px" class="c" min="1" max="${requestScope.countPage }" value="${requestScope.currentPage }" >
<input type="submit" value="Go" onclick="Page1()" >
</form>
</div>
推荐阅读