超市订单管理系统，用户管理（查询功能，分页）实现

思路

补充：oop三大特性：封装，多态，继承

　　封装：属性私有，利用get/set获得，（注：在set中限定不安全情况）

1 实现各角色人数统计

　　1.1 dao层接口

public int getUserCount(Connection connection,String username,int userRole) throws SQLException;

　　1.2 dao接口是实现类

 //根据用户名或用户角色查询总数
    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
            }
            //将链表转换成数组
            Object[] params=list.toArray();

            //输出完整的sql语句
            System.out.println("UserDao->impl->"+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;
    }

　　

　　1.3 服务层接口

int getUserCount(String username,int userRole);c

　　1.4 服务层接口实现类

    //查询记录数
    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;
    
    }

2  通过条件和联表查询，获取user表和role表的数据，最终合成为张用户表

dao层

2.1 获取用户表信息接口

    //通过条件查询，获取用户列表
    List<User> getUserList  (Connection connection, String userName, int userRole,int currentPageNo,int pageSize) throws SQLException;

2.1 user表接口实现类

//实现类，实现接口的方法
public class UserDaoImpl implements UserDao{
    public User getLoginUser(Connection connection, String userCode) throws SQLException {

        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;


        if (connection!=null){
            String sql="select * from smbms_user where userCode=?";
            Object[] params={userCode};

                rs = BaseDao.execute(connection, pstm, rs, sql, params);

                if(rs.next()){
                    user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUserCode(rs.getString("userCode"));
                    user.setUserName(rs.getString("userName"));
                    user.setUserPassword(rs.getString("userPassword"));
                    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.setCreatedBy(rs.getInt("createdBy"));
                    user.setCreationDate(rs.getTimestamp("creationDate"));
                    user.setModifyBy(rs.getInt("modifyBy"));
                    user.setModifyDate(rs.getTimestamp("modifyDate"));
                    user.getAge();
                }
                BaseDao.closeResource(null,pstm,rs);

        }


        return user;


    }

2.3 获取角色表信息接口、

    //角色列表，
    List<Role> getRoleList(Connection connection)throws SQLException;

2.4  role表结构实现类

public class RoleDaoImpl implements RoleDao{
    public List<Role> getRoleList(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList<Role> roleList= new ArrayList<Role>();
        if(connection!=null){
            String sql="SELECT * from smbms_role";
            Object[] params={};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);

            while(rs.next()){
                Role _role = new Role();
                _role.setId(rs.getInt("id"));
                _role.setRoleName( rs.getString("roleCode"));
                _role.setRoleName(rs.getString("roleName"));
                roleList.add(_role);

            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return roleList;
    }
}

service 层

2.5　（1）引用 dao层获取【user】表信息接口

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

　　　（2）实现类

  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);

        try {
            connection = BaseDao.getConnection();
            userList = userDao.getUserList(connection,queryUserName,queryUserRole,currentPageNo,pageSize);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return userList;
    }

　　　（3）引用 dao层获取 【role】表信息接口

public interface RoleService {
    List<Role> getRoleList ();
}

　　　（4）实现类

public class RoleServiceImpl implements RoleService{

    //引入dao层
    private RoleDao roledao;

    //无参构造，（为什么用无参，而不用有参）
    //有参在每次使用时，需指指明参数，无参不需要
    public RoleServiceImpl(){
        roledao = new RoleDaoImpl();
    }

    public List<Role> getRoleList() {
        Connection connection = null;
        List<Role> roleList = null;
        try {
            connection = BaseDao.getConnection();
            roleList = roledao.getRoleList(connection);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return roleList;
    }

2.6 servlet层

　　将统计人数，【role】表，【user】表整合到一个servlet里

//实现servlet复用
public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method = req.getParameter("method");
        if (method.equals("savepwd")&&method!=null){
            this.updatePwd(req,resp);
        }else if (method.equals("pwdmodify")&&method!= null){
            this.pwdModify(req, resp);
        }else if (method.equals("query")&&method!= null){
            this.query(req, resp);
        }
    }

   //重点，难点
    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();

        //第一次走这个页面，一定是第一页，页面大小是固定的
        int pageSize = 5;//可以把这个写到配置文件中，方便后期修改
        int currentPageNo=1;


        if (queryUserName == null){
            queryUserName = "";
        }

        if (temp!=null&& !temp.equals("")){
            queryUserRole =Integer.parseInt(temp);//解析页面，转换成整数型
        }
        if(pageIndex!=null){
            try {
                currentPageNo = Integer.parseInt(pageIndex);
            } catch (Exception e) {
                try {
                    resp.sendRedirect("error.jsp");
                } catch (IOException ioException) {
                    ioException.printStackTrace();
                }
            }
        }

        //获取用户总数(分页：上一页，下一页情况)
        int totalCount = userService.getUserCount(queryUserName, queryUserRole);
        //总页数支持
        PageSupport pageSupport = new PageSupport();
        pageSupport.setCurrentPageNo(currentPageNo);
        pageSupport.setPageSize(pageSize);
        pageSupport.setTotalCount(totalCount);

        //通过分页支持的公共类，查出一共有几页
        int totalPageCount=pageSupport.getTotalPageCount();

        //控制尾页和首页
        if (currentPageNo<1){
            currentPageNo = 1;
        }else if (currentPageNo>totalPageCount){//当页面大于最后一页
            currentPageNo=totalCount;
        }

        //获取用户展示
        List<User> 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();
        }


    }