用户管理实现
思路:
-
导入分页的工具类
-
用户列表页面导入
userlist.jsp
1.获取用户数量
-
UserDao
//根据用户名或者角色查询用户总数 public int getUserCount(Connection connection , String userName,int userRole) throws SQLException;
-
UserDaoImpl
public int getUserCount(Connection connection, String userName, int userRole) throws SQLException { PreparedStatement statement = null; ResultSet resultSet = null; int count = 0; //这里SQL语句是可变的,所以这里我们不用string类型 StringBuffer stringBuffer = new StringBuffer(); if (connection!=null){ stringBuffer.append("select count(1) as count from smbms_user as u ,smbms_role as r where u.userRole=r.id"); //这里需要一个集合来存放参数 ArrayList<Object> list = new ArrayList<Object>(); //在此sql基础上还可以添加,姓名查数据的功能 if (userName!=null){ //需要追加sql语句 stringBuffer.append(" and u.userName like ?"); //参数 list.add("%"+userName+"%"); } if (userRole>0){ stringBuffer.append(" and u.userRole =?"); list.add(userRole); } //将list转化为Object Object[] objects = list.toArray(); resultSet = BaseDao.queryResultSet(connection, stringBuffer.toString(), objects, statement, resultSet); if (resultSet.next()){ count = resultSet.getInt("count");//从结果集中获取数量 } //关闭 BaseDao.close(null,statement,resultSet); } return count; }
-
UserService
//获取用户数量 public int getUserCount(String userName,int userRole);
-
UserServiceIml
public int getUserCount(String userName, int userRole) { Connection connection = BaseDao.getConnection(); int userCount = 0; try { userCount = userDao.getUserCount(connection, userName, userRole); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { BaseDao.close(connection,null,null); } return userCount; }
2.获取用户列表
-
UserDao
//根据条件查询-userList public List<User> getUserList(Connection connection , String userName,int userRole,int currentPageNo ,int pageSize) throws SQLException;
-
UserDaoImpl
//根据条件查询-userList public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException { PreparedStatement statement = null; ResultSet resultSet = null; //查询的结果存放的方法 ArrayList<User> users = new ArrayList<User>(); //1.编写sql StringBuffer sql = new StringBuffer(); if (connection!=null){ sql.append("select u.*,r.roleName as userRoleName from smbms_user as u ,smbms_role as r where u.userRole=r.id"); //存放参数 ArrayList<Object> list = new ArrayList<Object>(); if (userName!=null){ sql.append(" and u.userName like ?"); list.add("%"+userName+"%"); } if (userRole>0){ sql.append(" and u.userRole =?"); list.add(userRole); } /* -- 第一页 limit 0,5 (1-1)*5=0 -- 第二页 limit 5,5 (2-1)*5=5 -- 第三页 limit 10,5 (3-1)*5=10 -- 第N页 limit (n-1)*pageSize, pageSize (n-1)*pageSize, pageSize -- [pageSize:页面是显示数据的条数] -- [总页数 = 数据总数/页面显示大小] 第N页 limit (n-1)*pageSize, pageSize ,我们现在要知道,每一页的第一个编号为几,还好用limit来显示 第一个编号 = (当前页面数-1)*页面显示大小 * */ sql.append("order by u.creationDate desc limit ?,?"); //第一个编号 = (当前页面数-1)*页面显示大小 int i = (currentPageNo - 1) * pageSize; list.add(i);//页面显示的起始编号 list.add(pageSize);//页面显示的总个数 //2.添加参数 //将list转换为Object Object[] objects = list.toArray(); //3.调用方法查询 resultSet = BaseDao.queryResultSet(connection, sql.toString(), objects, statement, resultSet); //将查询的结果存放到users中 while (resultSet.next()){ User user = new User(); //resultSet.getInt("id")括号里的值为查询结果后每列的列名 user.setId(resultSet.getInt("id")); user.setUserCode(resultSet.getString("userCode")); user.setUserName(resultSet.getString("userName")); user.setGender(resultSet.getInt("gender")); user.setPhone(resultSet.getString("phone")); user.setUserRole(resultSet.getInt("userRole")); user.setUserRoleName(resultSet.getString("userRoleName")); //将user添加到结果集list集合中 users.add(user); } //关闭 BaseDao.close(null,statement,resultSet); } return users; }
-
UserService
//查询结果集~userList public List<User> getUserList(String userName, int userRole, int currentPageNo, int pageSize);
-
UserServiceImpl
public List<User> getUserList(String userName, int userRole, int currentPageNo, int pageSize) { Connection connection = BaseDao.getConnection(); List<User> userList = null; try { userList = userDao.getUserList(connection, userName, userRole, currentPageNo, pageSize); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { BaseDao.close(connection,null,null); } return userList; }
3.获取角色操作
- 为了我们职责统一,可以把角色的操作单独放在一个包中,和POJO类对应
-
RoleDao
//获取角色列表 public List<Role> getRoleList(Connection connection) throws SQLException;
-
RoleDaoImpl
public List<Role> getRoleList(Connection connection) throws SQLException { //new 一个List用来装结果 ArrayList<Role> roles = new ArrayList<Role>(); PreparedStatement statement = null; ResultSet resultSet = null; Object[] objects = null; String sql = "select * from smbms_role"; resultSet = BaseDao.queryResultSet(connection, sql, objects, statement, resultSet); while (resultSet.next()){ Role role = new Role(); role.setId(resultSet.getInt("id")); role.setRoleCode(resultSet.getString("roleCode")); role.setRoleName(resultSet.getString("roleName")); roles.add(role); } //关闭资源 BaseDao.close(null,statement,resultSet); return roles; }
-
RoleService
//获取角色列表 public List<Role> getRoleList();
-
RoleServiceImpl
public List<Role> getRoleList() { Connection connection = BaseDao.getConnection(); List<Role> roleList = null; try { roleList = roleDao.getRoleList(connection); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { BaseDao.close(connection,null,null); } return roleList; }
4.用户显示的Servlet
- 获取前端的数据(查询)
- 判断请求是否需要执行,看参数的值判断
- 为了实现分页,需要计算出当前页面和总页面,页面大小…
- 用户列表展示
- 返回前端
//重点,难点
public void query(HttpServletRequest req, HttpServletResponse resp){
//首先从前端获取数据
String queryname = req.getParameter("queryname");//用户的姓名
String temp = req.getParameter("queryUserRole");//用户的角色(0,1,2,3)
String pageIndex = req.getParameter("pageIndex");//用户填入的页数
int queryUserRole = 0;//这里我们用户的角色,在加载完页面后,用户还没选择时,我们先让它默认是零
//第一次加载完页面,页面显示的数据一定是第一页,页面大小固定为5,
int pageSize = 5;//这些数据可以放在配置文件中方便以后修改
int currentPageNo = 1;//当前页面数为1
//判断前端传来的数据
if (queryname==null){
queryname="";
}
if (temp !=null&&!temp.equals("")){
//这里将前端传来用户的角色,赋值给我们定义的queryUserRole
queryUserRole = Integer.parseInt(temp);
}
if (pageIndex!=null){
currentPageNo = Integer.parseInt(pageIndex);//用户输入的页
}
//获取用户的总数
UserServiceImp1 userServiceImp1 = new UserServiceImp1();
int userCount = userServiceImp1.getUserCount(queryname, queryUserRole);
//总页数支持
PageSupport pageSupport = new PageSupport();
pageSupport.setPageSize(pageSize);
pageSupport.setCurrentPageNo(currentPageNo);
pageSupport.setTotalCount(userCount);
pageSupport.setTotalPageCountByRs();
//一共有几页
int totalPageCount = pageSupport.getTotalPageCount();
//控制首页和尾页
//用户输入的页面要小于1了,就显示第一页的东西
if (currentPageNo<1){
currentPageNo = 1;
}else if (currentPageNo>totalPageCount){
//用户输入的页面大于最后一页
currentPageNo=totalPageCount;
}
//获取用户列表
List<User> userList = userServiceImp1.getUserList(queryname, queryUserRole, currentPageNo, pageSize);
req.setAttribute("userList",userList);
System.out.println(userList==null);
//获取角色列表中的数据
RoleServiceImpl roleService = new RoleServiceImpl();
List<Role> roleList = roleService.getRoleList();
System.out.println(roleList==null);
req.setAttribute("roleList",roleList);
req.setAttribute("totalCount",userCount);
req.setAttribute("currentPageNo",currentPageNo);
req.setAttribute("totalPageCount",totalPageCount);
//返回前端
try {
req.getRequestDispatcher("/jsp/userlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}