JavaWeb（15）超市订单管理系统smbms——用户管理实现

一、思路

准备工作：导入分页的工具类rollpage.jsp、用户列表页面导入userlist.jsp；

二、获取用户数量
1、UserDao

//根据用户名、角色查询用户总数
    public int getUserCount(Connection connection , String username , int userRole) throws SQLException;

2、UserDaoImpl

 //根据用户名、角色查询用户总数【最难理解的sql】
    @Override
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
            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");
                ArrayList<Object> list = new ArrayList<Object>();           //存放参数

                if(!StringUtils.isNullOrEmpty(username)) {
                    sql.append(" and u.userName like ?");
                    list.add("%"+username+"%");                             //index=0
                }

                if(userRole > 0){
                    sql.append(" and u.userRole = ?");
                    list.add(userRole);                                     //index=1
                }
                //将list转化为数组
                Object[] params = list.toArray();
                System.out.println("UserDaoImp->getUserCount:"+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;
    }

3、UserService

 //查询记录数
    public int getUsercount(String username,int userRole);

4、UserServiceImpl

//查询记录数
 @Override
    public int getUsercount(String username, int userRole)  {
        Connection connection = null;
        int count = 0;
        try {
            connection = BaseDao.getConnection();
            count = userDao.getUserCount(connection,username,userRole);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return count;
    }

三、获取用户列表
1、UserDao

//通过条件查询userList
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception;

2、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 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 userName like ?");
                list.add("%" + userName + "%");
            }

            if (userRole > 0) {
                    sql.append(" and u.userRole = ?");
                    list.add(userRole);
            }

            sql.append(" order by creationDate DESC limit ? , ?");
            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;

3、UserService

 //根据条件查询用户列表
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);

4、UserServiceImpl

//根据条件查询用户列表
    @Override
    public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
       Connection connection = null;
       List<User> userList = null;

        System.out.println("queryUserName----->"+queryUserName);
        System.out.println("queryUserRole----->"+queryUserRole);
        System.out.println("currentPageNo----->"+currentPageNo);
        System.out.println("pageSize----->"+pageSize);

        connection = BaseDao.getConnection();
        try {
            userList = userDao.getUserList(connection,queryUserName,queryUserRole,currentPageNo,pageSize);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return userList;
    }

四、用户显示的Servlet
1、获取用户前端的数据（查询）
2、判断请求是否需要执行，看参数的值判断
3、为了实现分页，需要计算出当前页面、总页面、页面的大小
4、用户列表展示
5、返回前端

//查询用户列表
    public void query(HttpServletRequest req, HttpServletResponse resp){
        //从前端获取数据
        String queryUserName = req.getParameter("queryname");
        String temp = req.getParameter("queryUserRole");
        String pageIndex = req.getParameter("pageIndex");
        int queryUserRole = 0;

        //获取用户列表
        UserServiceImpl userService = new UserServiceImpl();
        List<User> userList = null;

        //第一次走这个请求，一定是第一页，页面大小固定
        int pageSize = 5;
        int currentPageNo = 1;

        if(queryUserName == null){
            queryUserName = "";
        }
        if(temp != null && !temp.equals("")){
            queryUserRole = Integer.parseInt(temp);//给查询赋值
        }
        if(pageIndex != null){
            currentPageNo = Integer.parseInt(pageIndex);
        }

        //获取用户总数
        int totalCount = userService.getUsercount(queryUserName,queryUserRole);

        //总页数支持
        PageSupport pageSupport = new PageSupport();
        pageSupport.setCurrentPageNo(currentPageNo);
        pageSupport.setPageSize(pageSize);
        pageSupport.setTotalCount(totalCount);

        //总共有几页
        int totalPageCount = ((int)(totalCount/pageSize))+1;

        //控制首页和尾页
        if(currentPageNo < 1){//当前页面小于1
            currentPageNo = 1;
        }else if(currentPageNo > totalPageCount){//当前页面大于总页面数
            currentPageNo = totalPageCount;
        }

        //获取用户列表展示
        userList = userService.getUserList(queryUserName,queryUserRole,currentPageNo,pageSize);
        req.setAttribute("userList",userList);

        RoleServiceImpl roleService = new RoleServiceImpl();
        List<Role> roleList = roleService.getRoleList();
        req.setAttribute("roleList",roleList);
        req.setAttribute("totalCount",totalCount);
        req.setAttribute("currentPageNo",currentPageNo);
        req.setAttribute("totalPageCount",totalPageCount);
        req.setAttribute("queryUserName",queryUserName);
        req.setAttribute("queryUserRole",queryUserRole);

        try {
            req.getRequestDispatcher("userlist.jsp").forward(req,resp);
        } catch (ServletException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }