狂神说 javaweb 35集：smbms用户管理底层实现_SMBMS 项目搭建（六）

35.smbms用户管理底层实现

用户管理实现

1. 导入分页的工具类

   -PageSupport

2. 用户列表页面导入

   -userlist.jsp

   -rollpage.jsp

1、获取用户数量

1. UserDao

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

2. UserDaoImpl

    //根据用户名或者角色查询用户总数【最难理解的SQL】
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        ResultSet rs = null;
        PreparedStatement pstm = null;
        int count = 0;
    ​
        if (null != connection) {
            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("UserDaoImpl--> getUserCount:" + sql.toString());//输出最后完整的SQL语句
    ​
            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

    //查询记录数
    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 throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource( connection,null,null );
        }
        return count;
    }
    ​
    @Test
    public void test(){
        UserServiceImpl userService = new UserServiceImpl();
        int userCount = userService.getUserCount( null, 1 );
        System.out.println(userCount);
    }

 

1. UserDao

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

2. UserDaoImpl

    //通过条件查询-userList
    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);
            }
            //在mysql数据库中，分页使用 limit startIndex，pageSize ; 总数
            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

    //根据条件查询用户列表
    public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
        Connection connection = null;
        List<User> userList = null;
        try {
            connection = BaseDao.getConnection();
            userList = userDao.getUserList(connection, queryUserName, queryUserRole, currentPageNo, pageSize);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeResource(connection, null, null);
        }
        return userList;
    }

5.