分页设计:
#9条记录,每页最多只能显示4条 ,会有3页 第一页 0,4 第二页 4,4 第三页 8,4
select * from user limit 4,4
#n条记录 每页最多只能显示4条,会有整除n/4 n/4+1 页 ,第一页 0,4 ,第二页4,4 第三页 8,4 第m页 (m-1)*4 ,4
#n条记录 select count(*) from user
总记录数totalCount
页面大小pageSize
总页数pageCount
当前页currentPage
从第几行查询beginRow=(currentPage-1)*pageSize
工具类:
public class PageInfo {
private int totalCount;//总记录数 ----查出来了 select count(*) from 表名
private int pageSize=4;//页面大小(每页最多显示的记录数) ---用户指定
private int pageCount;//总页数 ---计算 totalCount/pageSize totalCount/pageSize+1
private int currentPage=1;//当前是第几页 ---用户选择(默认从第一页开始 =1)
private int beginRow;//从第几行开始查询 ---计算 beginRow=(currentPage-1)*pageSize
private boolean hasprev;//上一页
private boolean hasnext;//下一页
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
//总页数
public int getPageCount() {
if(totalCount%pageSize==0){
this.pageCount = totalCount/pageSize;
}else{
this.pageCount = totalCount/pageSize+1;
}
return pageCount;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
//开始查询的行
public int getBeginRow() {
beginRow=(currentPage-1)*pageSize;
return beginRow;
}
//是否有上一页:当前页为第一页,没有上一页,其他的都有上一页
public boolean isHasprev() {
if(currentPage==1){
hasprev=false;
}else{
hasprev=true;
}
return hasprev;
}
//是否有下一页:当前页为尾页pageCount,没有下一页,其他的都有下一页
public boolean isHasnext() {
if(currentPage==pageCount){
hasnext=false;
}else{
hasnext=true;
}
return hasnext;
}
}
===jsp页面:
<form action="SelectUserServlet">
用户名<input type="text" name="uname">
<input type="submit" value="查询">
</form>
<ul>
<c:forEach items="${userList }" var="user">
<li>${user.userid } ${user.username } ${user.password }</li>
</c:forEach>
</ul>
<c:if test="${ not empty PageObj}">
<div style="background:#ccc">
共有${PageObj.totalCount }条记录,共有${PageObj.pageCount }页数,当前是第${PageObj.currentPage }页
</div>
<c:choose>
<c:when test="${PageObj.hasprev==true }">
<span class="pindex" style="width:100px"><a href="SelectUserServlet?currentPage=${PageObj.currentPage-1 }">上一页</a></span>
</c:when>
<c:otherwise>
<span class="pindex" style="width:100px;background:gray"><a href="SelectUserServlet?currentPage=${PageObj.currentPage }">上一页</a></span>
</c:otherwise>
</c:choose>
<c:forEach begin="1" end="${PageObj.pageCount }" var="i">
<span class="pindex">
<a href="SelectUserServlet?currentPage=${i }">${i }</a>
</span>
</c:forEach>
<c:choose>
<c:when test="${PageObj.hasnext }">
<span class="pindex" style="width:100px"><a href="SelectUserServlet?currentPage=${PageObj.currentPage+1 }">下一页</a> </span>
</c:when>
<c:otherwise>
<span class="pindex" style="width:100px;background:gray"><a href="SelectUserServlet?currentPage=${PageObj.currentPage }">下一页</a> </span>
</c:otherwise>
</c:choose>
====servlet层
@WebServlet("/SelectUserServlet")
public class SelectUserServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//获取查询条件 (解决中文乱码)
String uname=request.getParameter("uname");
String username="";
if(uname!=null){
username=new String(uname.getBytes("iso-8859-1"),"utf8");
}
//获取当前页
String currentPage=request.getParameter("currentPage");
int currentPageIndex=0;
if(currentPage!=null){
currentPageIndex=Integer.parseInt(currentPage);
}else{
currentPageIndex=1;
}
PageInfo pageobj=new PageInfo();
//查询总条数 select count(*) from 表
UserDao dao=new UserDaoImpl();
int totalCount=dao.getUserCounts();
pageobj.setTotalCount(totalCount);//总条数
// //上一页小于1,默认为第一页
// if(currentPageIndex<=0){
// currentPageIndex=1;
// }
// //下一页大于尾页,默认为尾页
// if(currentPageIndex>=pageobj.getPageCount()){
// currentPageIndex=pageobj.getPageCount();
// }
pageobj.setCurrentPage(currentPageIndex);
pageobj.setPageSize(3);
System.out.println(pageobj.isHasnext()+"99999999999999"+pageobj.isHasprev());
//调用service层查询
UserService us=new UserService();
List<UserInfo> list=us.getAllUser(username,pageobj);
//根据查询的结果显示到页面中
request.setAttribute("userList", list);
//显示总页数,总条数
request.setAttribute("PageObj", pageobj);
request.getRequestDispatcher("selectUser.jsp").forward(request, response);
}
}
===dao层
public class UserDaoImpl implements UserDao{
//分页查询
@Override
public List<UserInfo> getAllUser(String username,PageInfo page) {
//查询
Connection con=null;
PreparedStatement pst=null;
ResultSet rs=null;
List<UserInfo> list=new ArrayList();
UserInfo user=null;
try {
con=DBUtil_c3p0.getConnection();
String sql="select * from user where username like ? limit ?,?"; // like '% fdd %'
pst=con.prepareStatement(sql);
pst.setString(1, "%"+username+"%");
pst.setInt(2, page.getBeginRow());
pst.setInt(3, page.getPageSize());
rs=pst.executeQuery();
while(rs.next()){
user=new UserInfo();
user.setUserid(rs.getInt("uid"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
list.add(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtil_c3p0.close(con, pst, rs);
}
return list;
}
//查询所有记录
@Override
public int getUserCounts() {
Connection con=null;
PreparedStatement pst=null;
ResultSet rs=null;
int res=0;
try {
con=DBUtil_c3p0.getConnection();
String sql="select count(*) from user ";
pst=con.prepareStatement(sql);
rs=pst.executeQuery();
if(rs.next()){
res=rs.getInt(1);
}
} catch (Exception e) {
}finally{
DBUtil_c3p0.close(con, pst, rs);
}
return res;
}
}