数据分页
数据分页功能
protected void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//获取入参
String queryName = req.getParameter("queryname");
String temp = req.getParameter("queryUserRole");
String pageIndex = req.getParameter("pageIndex");
//角色
int queryUserRole = 0;
//每页最大容量可以前端传入,也可以写成配置项,这里暂时写死
int pageSize = 5;
int currentPageNo = 1;
//参数处理
if (temp != null && temp.length() > 0) {
queryUserRole = Integer.parseInt(temp);
}
if (pageIndex != null && pageIndex.length() > 0) {
currentPageNo = Integer.parseInt(pageIndex);
}
UserService userService = new UserServiceImpl();
//获取用户总数(上一页,下一页)
int totalCount = userService.getUserCount(queryName, queryUserRole);
//总页数支持
PageSupport pageSupport = new PageSupport();
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setPageSize(pageSize);
pageSupport.setTotalCount(totalCount);
int totalPageCount = pageSupport.getTotalPageCount();
//控制首页和尾页
if (currentPageNo < 0) {
currentPageNo = 0;
} else if (currentPageNo > totalPageCount) {
currentPageNo = totalPageCount;
}
//查询用户列表
List<User> userList = userService.getUserList(queryName, queryUserRole, currentPageNo, pageSize);
//获取角色列表
RoleService roleService = new RoleServiceImpl();
List<Role> roleList = roleService.getRoleList();
req.setAttribute("userList", userList);
req.setAttribute("roleList", roleList);
req.setAttribute("totalCount", totalCount);
req.setAttribute("currentPageNo", currentPageNo);
req.setAttribute("totalPageCount", totalPageCount);
req.setAttribute("queryUserName", queryName);
req.setAttribute("queryUserRole", queryUserRole);
req.getRequestDispatcher("userlist.jsp").forward(req, resp);
}
Dao层方法
public List<User> getUserList(Connection con, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
List<User> userList = new ArrayList<User>();
if (con != null) {
StringBuffer sql = new StringBuffer("select u.*,r.roleName as `userRoleName` from smbms_user u,smbms_role r where u.userRole=r.id");
List<Object> list = new ArrayList<Object>();
if (!StringUtils.isNullOrEmpty(userName)) {
sql.append(" and u.userName like ?");
list.add("%" + userName + "%");
}
if (userRole > 0) {
sql.append(" and r.id = ?");
list.add(userRole);
}
//mysql 分页使用limit startIndex, pageSize
//比如现在一共13条数据,每页最大容量是5
//0,5 01234 第一页
//5,5 56789 第二页
//10,3 10,11,12 第三页
sql.append(" order by u.creationDate desc limit ?,?");
//公式:开始的索引(该页第一个数据的起始位置)=(当前页码-1)*每页显示的条数
currentPageNo = (currentPageNo - 1) * pageSize;
list.add(currentPageNo);
list.add(pageSize);
Object[] params = list.toArray();
rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUserCode(rs.getString("userCode"));
user.setUserName(rs.getString("userName"));
user.setGender(rs.getInt("gender"));
user.setBirthday(rs.getDate("birthday"));
user.setPhone(rs.getString("phone"));
user.setUserRole(rs.getInt("userRole"));
user.setUserRoleName(rs.getString("userRoleName"));
userList.add(user);
}
BaseDao.closeResource(ps, rs);
}
return userList;
}
PageSupport
public class PageSupport {
//当前页码-来自于用户输入
private int currentPageNo = 1;
//总数量(表)
private int totalCount = 0;
//页面容量
private int pageSize = 0;
//总页数-totalCount/pageSize(+1)
private int totalPageCount = 1;
public int getCurrentPageNo() {
return currentPageNo;
}
public void setCurrentPageNo(int currentPageNo) {
if(currentPageNo > 0){
this.currentPageNo = currentPageNo;
}
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
if(totalCount > 0){
this.totalCount = totalCount;
//设置总页数
this.setTotalPageCountByRs();
}
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
if(pageSize > 0){
this.pageSize = pageSize;
}
}
public int getTotalPageCount() {
return totalPageCount;
}
public void setTotalPageCount(int totalPageCount) {
this.totalPageCount = totalPageCount;
}
public void setTotalPageCountByRs(){
if(this.totalCount % this.pageSize == 0){
this.totalPageCount = this.totalCount / this.pageSize;
}else if(this.totalCount % this.pageSize > 0){
this.totalPageCount = this.totalCount / this.pageSize + 1;
}else{
this.totalPageCount = 0;
}
}
}
本文来自博客园,作者:Cn_FallTime,转载请注明原文链接:https://www.cnblogs.com/CnFallTime/p/16002069.html