超市订单管理系统 ---用户管理实现

用户管理实现

思路：

1. 导入分页的工具类
2. 用户列表页面导入

1、获取用户数量

1. UserDao

   //查询用户总数
   public int getUserCount(Connection connection, String userName, int userRole) throws SQLException;
   

2. UserDaoImpl

   @Override
   public int getUserCount(Connection connection, String userName, int userRole) throws SQLException {
       PreparedStatement st = 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>();//存放我们的参数
   
           if (!StringUtils.isNullOrEmpty(userName)) {
               sql.append(" and username like ?");
               list.add("%" + userName + "%");//index:0
           }
   
           if (userRole > 0) {
               sql.append(" and userRole = ?");
               list.add(userRole);//index :1
           }
   
           //
           Object[] params = list.toArray();
   
           System.out.println("UserDaoImpl -> getUserCount:" + sql.toString());//输出最后完整的sql语句
   
           rs = BaseDao.execute(connection, st, sql.toString(), params);
           if (rs.next()){
               count = rs.getInt("count");
           }
   
           BaseDao.release(connection,st,rs);
       }
       return count;
   }
   

3. UserService

   //根据用户名或者角色查询用户总数
   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 throwables) {
           throwables.printStackTrace();
       } finally {
           BaseDao.release(connection,null,null);
       }
       return count;
   }
   

2、获取用户列表

1. UserDao

       //查询用户列表
       public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo,int pageSize) throws SQLException;
   

2. UserDaoImpl

   @Override
   public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
       PreparedStatement st = 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 username like ?");
               list.add("%" + userName + "%");//index:0
           }
   
           if (userRole > 0) {
               sql.append(" and userRole = ?");
               list.add(userRole);//index :1
           }
   
           sql.append(" order by u.userName limit ?,?");
   
           //在数据库中，分页使用 limit startIndex,pageSize;  总数
           //当前页 （当前页-1）*页面大小
           list.add((currentPageNo - 1) * pageSize);
           list.add(pageSize);
   
           System.out.println("UserDaoImpl:getUserList" + sql.toString());
   
           Object[] params = list.toArray();
   
           rs = BaseDao.execute(connection, st, sql.toString(), params);
   
   
           //在结果集中  if(rs.next())只是判断是否还有数据  只做一次判断  
           //而  while(rs.next())  是将所有的结果遍历出来  只有还有数据就会继续下去
           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.setAddress(rs.getString("address"));
               user.setUserRole(rs.getInt("userRole"));
               user.setUserRoleName(rs.getString("userRoleName"));
               userList.add(user);
           }
   
           BaseDao.release(connection,st,rs);
       }
   
       System.out.println(userList.size());
       return userList;
   }
   

3. UserService

   //根据用户名或者角色查询用户总数
   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 throwables) {
           throwables.printStackTrace();
       } finally {
           BaseDao.release(connection,null,null);
       }
       return count;
   }