(5)用户管理实现 P35
-
导入分页工具类 (PageSupport.java)
-
导入用户列表页面
获取用户数量
-
UserDao
//查询用户总数 -- 根据用户名或角色查询总数 public int getUserCount(Connection connection, String userName, int userRole)throws Exception;
-
UserDaoImpl
实现类//查询用户总数 -- 根据用户名或角色查询总数 @Override public int getUserCount(Connection connection, String userName, int userRole) throws Exception { PreparedStatement pstm = null; ResultSet rs = null; int count = 0; if(connection != null){ StringBuffer sql = new StringBuffer(); sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id"); //存放参数 List<Object> list = new ArrayList<Object>(); //如果传入的用户名不为空 -- 拼接查询name的SQL语句 -- 记得加空格 if(!StringUtils.isNullOrEmpty(userName)){ sql.append(" and u.userName like ?"); list.add("%"+userName+"%"); } //如果传入的userRole>0 -- 拼接查询 if(userRole > 0){ sql.append(" and u.userRole = ?"); list.add(userRole); } //将list转换为数组 Object[] params = list.toArray(); System.out.println("sql --> " + sql.toString()); rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params); if(rs.next()){ count = rs.getInt("count"); } BaseDao.closeResource(null, pstm, rs); } return count; }
-
UserService
//查询用户总数 -- 根据用户名或角色查询总数 public int getUserCount(String queryUserName, int queryUserRole);
-
UserServiceImpl
//查询用户总数 -- 根据用户名或角色查询总数 @Override public int getUserCount(String queryUserName, int queryUserRole) { Connection connection = null; int count = 0; System.out.print("UserName -- > " + queryUserName+" | "+"UserRole -- > " + queryUserRole); try { connection = BaseDao.getConnection(); count = userDao.getUserCount(connection, queryUserName,queryUserRole); } catch (Exception e) { e.printStackTrace(); }finally{ BaseDao.closeResource(connection, null, null); } return count; }
获取用户列表
-
UserDao
//获取用户列表 -- userList public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;
-
UserDaoImpl
实现类//通过条件查询 -- userList @Override public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception { PreparedStatement pstm = null; ResultSet rs = null; List<User> userList = new ArrayList<User>(); if(connection != null){ StringBuffer sql = new StringBuffer(); sql.append("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 u.userRole = ?"); list.add(userRole); } //在数据库中分页 -- 使用limit sql.append(" order by creationDate DESC limit ?,?"); //当前页 = (当前页-1)*页面大小 currentPageNo = (currentPageNo-1)*pageSize; list.add(currentPageNo); list.add(pageSize); ? Object[] params = list.toArray(); System.out.println("sql --> " + sql.toString()); rs = BaseDao.execute(connection, pstm, 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(null, pstm, rs); } return userList; }
-
UserService
//获取用户列表 -- userList public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
-
UserServiceImpl
实现类//获取用户列表 -- userList @Override public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) { Connection connection = null; List<User> userList = null; System.out.println("UserName -- > " + queryUserName); System.out.println("UserRole -- > " + queryUserRole); System.out.println("PageNo -- > " + currentPageNo); System.out.println("pageSize -- > " + pageSize); try { connection = BaseDao.getConnection(); userList = userDao.getUserList(connection, queryUserName,queryUserRole,currentPageNo,pageSize); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ BaseDao.closeResource(connection, null, null); } return userList; }
用户分页管理实现Role P36
-
dao/role/RoleDao
public interface RoleDao { public List<Role> getRoleList(Connection connection)throws Exception; ? }
? -
dao/role/RoleDaoImpl
实现类public class RoleDaoImpl implements RoleDao{ @Override public List<Role> getRoleList(Connection connection) throws Exception { PreparedStatement pstm = null; ResultSet rs = null; List<Role> roleList = new ArrayList<Role>(); if(connection != null){ String sql = "select * from smbms_role"; Object[] params = {}; rs = BaseDao.execute(connection, pstm, rs, sql, params); while(rs.next()){ Role _role = new Role(); _role.setId(rs.getInt("id")); _role.setRoleCode(rs.getString("roleCode")); _role.setRoleName(rs.getString("roleName")); roleList.add(_role); } BaseDao.closeResource(null, pstm, rs); } return roleList; } }
-
service/role/RoleService
public interface RoleService { public List<Role> getRoleList(); }
-
service/role/RoleServiceImpl
实现类public class RoleServiceImpl implements RoleService { //引入Dao private RoleDao roleDao; public RoleServiceImpl(){ roleDao = new RoleDaoImpl(); } @Override public List<Role> getRoleList() { Connection connection = null; List<Role> roleList = null; try { connection = BaseDao.getConnection(); roleList = roleDao.getRoleList(connection); } catch (Exception e) { e.printStackTrace(); }finally{ BaseDao.closeResource(connection, null, null); } return roleList; } }
Servlet
/*重点 : 查询用户*/ private void query(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { //查询用户列表 -- 从前端获取数据 String queryUserName = request.getParameter("queryname"); String temp = request.getParameter("queryUserRole"); String pageIndex = request.getParameter("pageIndex"); //默认为空,所以显示得赋值为0,取到的值temp int queryUserRole = 0; //获取用户列表 UserService userService = new UserServiceImpl(); //第一次走页面一定是第一页,页面大小固定的 List<User> userList = null; //设置页面容量,配置文件获取 int pageSize = Constants.pageSize; //当前页码,默认为1 int currentPageNo = 1; System.out.println("UserName servlet -- > "+queryUserName); System.out.println("UserRole servlet -- > "+queryUserRole); System.out.println("pageIndex -- > " + pageIndex); //查询的名字为空 if(queryUserName == null){ queryUserName = ""; } //temp有值(代表选择)则赋值 if(temp != null && !temp.equals("")){ queryUserRole = Integer.parseInt(temp);//给查询赋值:0,1,2,3 } //解析页面:第几页 if(pageIndex != null){ try{ currentPageNo = Integer.valueOf(pageIndex); }catch(NumberFormatException e){ response.sendRedirect("error.jsp"); } } //获取用户总数 int totalCount = userService.getUserCount(queryUserName,queryUserRole); //总页数 PageSupport pages=new PageSupport(); pages.setCurrentPageNo(currentPageNo); pages.setPageSize(pageSize); pages.setTotalCount(totalCount); int totalPageCount = pages.getTotalPageCount(); //控制首页和尾页 if(currentPageNo < 1){ currentPageNo = 1; }else if(currentPageNo > totalPageCount){ currentPageNo = totalPageCount; } /*获取用户列表展示*/ userList = userService.getUserList(queryUserName,queryUserRole,currentPageNo, pageSize); request.setAttribute("userList", userList); //获取角色列表 List<Role> roleList = null; RoleService roleService = new RoleServiceImpl(); roleList = roleService.getRoleList(); //传入角色列表及页数等数据至前端 request.setAttribute("roleList", roleList); request.setAttribute("queryUserName", queryUserName); request.setAttribute("queryUserRole", queryUserRole); request.setAttribute("totalPageCount", totalPageCount); request.setAttribute("totalCount", totalCount); request.setAttribute("currentPageNo", currentPageNo); //返回前端 request.getRequestDispatcher("userlist.jsp").forward(request, response); }
#项目架构