SMBMS超市订单管理系统（四）

项目资源可以从我的github上获取

https://github.com/chiyu999/smbms.git

六、用户管理页面实现

思路：

分别实现上面的三条从dao层到service层的功能，才能实现用户管理的页面

6.1、获取用户数量

1、UserDao

/**
     * 根据用户名或用户角色查询用户总数
     * @param connection
     * @return
     * @throws Exception
     */
    public int getUserCount(Connection connection,String username,int userRole) throws Exception;

2、UserDaoImpl

/**
     * 根据用户名或者用户角色查询用户总数
     * @param connection
     * @param username
     * @param userRole
     * @return
     * @throws Exception
     */
    @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");

            ArrayList<Object> list = new ArrayList<>();//用于存放参数

            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("UserServiceImpl---->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

/**
     * 查询获得用户数量
     * @param username
     * @param userRole
     * @return count
     */
    public int getUserCount(String username,int userRole);

4、UserServiceImpl

/**
     * 获取用户总数
     * @param username
     * @param userRole
     * @return
     */
    @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 (Exception e) {
   
            e.printStackTrace();
        }finally {
   
            BaseDao.closeResource(connection,null,null);
        }
        return count;
    }

5、测试

@Test
    public void test(){
   
        UserServiceImpl userService = new UserServiceImpl();
        int userCount = userService.getUserCount(null, 0);
        System.out.println(userCount);
    }

6.2、获取用户列表

1、UserDao(getUserList())

/**
     * 获取用户列表
     * @param connection
     * @param username
     * @param userRole
     * @param currentPageNo
     * @param pageSize
     * @return
     * @throws Exception
     */
    public List<User> getUserList(Connection connection,String username,int userRole,int currentPageNo,int pageSize) throws Exception;

2、UserDaoImpl

根据传入的用户名，用户角色是否符合条件，进行sql的拼接，实现多条件查询，一个方法复用

/**
     * 获取用户列表
     * @param connection
     * @param username
     * @param userRole
     * @param currentPageNo
     * @param pageSize
     * @return
     * @throws Exception
     */
    @Override
    public List<User> getUserList(Connection connection, String username, int userRole,int currentPageNo,int pageSize) throws Exception {
   
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Object> list = new ArrayList<>(); //存放参数
        List<User> userList = new ArrayList<>();
        System.out.println("UserDao获得的要查询的用户名=====>"+username);
        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");
            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 startIndex pageSize
            sql.append(" order by creationDate DESC limit ?,?");
            System.out.println("currentPageNo:"+currentPageNo);
            currentPageNo = (currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            //打印sql
            System.out.println("UserDaoImpl---->getUserList:"+sql.toString());
            //把查询的结果赋值给list
            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

/**
     * 获取用户列表
     * @param username
     * @param userRole
     * @param currentPageNo
     * @param pageSize
     * @return
     * @throws Exception
     */
    public List<User> getUserList(String username, int userRole,int currentPageNo,int pageSize) throws Exception;

4、UserServiceImpl

/**
     * 获取用户列表
     * @param username
     * @param userRole
     * @param currentPageNo
     * @param pageSize
     * @return
     * @throws Exception
     */
    @Override
    public List<User> getUserList(String username, int userRole, int currentPageNo