javaweb-36：smbms用户管理分页OK

3、获取角色操作

为了我们职责统一，可以把角色的操作单独放在一个包中，和POJO类对应

初始化sql:

 INSERT INTO `smbms`.`smbms_role` (`roleCode`, `roleName`, `createBy`, `creationDate`, `modifyBy`, `modifyDate`) VALUES ('SMBMS_ADMIN', '系统管理员', '1', '2021-10-29 06:56:48', '1', '2021-10-29 06:56:52'),
('SMBMS_MANAGER', '经理', '1', '2021-10-29 06:56:48', '1', '2021-10-29 06:56:52'),
('SMBMS_EMPLOYEE', '普通员工', '1', '2021-10-29 06:56:48', '1', '2021-10-29 06:56:52');



 INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, `phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongyi4', '工一4', '2', '1', '2021-10-29', '13716267461','北京4',3,1,now(),1,now()); 
 INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, `phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongyi5', '工一5', '2', '1', '2021-10-29', '13716267461','北京5',3,1,now(),1,now()); 
 INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, `phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongyi6', '工一6', '2', '1', '2021-10-29', '13716267461','北京6',3,1,now(),1,now()); 
 INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, `phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongyi7', '工一7', '2', '1', '2021-10-29', '13716267461','北京7',3,1,now(),1,now()); 
 INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, `phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongyi8', '工一8', '2', '1', '2021-10-29', '13716267461','北京8',3,1,now(),1,now()); 
 INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, `phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongy9', '工一9', '2', '1', '2021-10-29', '13716267461','北京9',3,1,now(),1,now()); 
INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, 
`phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongy10', '工一10', '2', '1', '2021-10-29', '13716267461','北京10',3,1,now(),1,now());
INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, 
`phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongy11', '工一11', '2', '1', '2021-10-29', '13716267461','北京11',3,1,now(),1,now());
INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, `birthday`, 
`phone`,`address`,`userRole`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`) VALUES ('gongy12', '工一12', '2', '1', '2021-10-29', '13716267461','北京12',3,1,now(),1,now());

RoleDao

package com.gongyi.dao.role;


import com.gongyi.pojo.Role;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public interface RoleDao {
    //获取角色列表
    List<Role> getRoleList(Connection connection) throws SQLException;
}

RoleDaoImpl

package com.gongyi.dao.role;

import com.gongyi.dao.BaseDao;
import com.gongyi.pojo.Role;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class RoleDaoImpl implements RoleDao {
    //获取角色列表
    @Override
    public List<Role> getRoleList(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet resultSet = null;
        ArrayList<Role> roleList = new ArrayList<Role>();
        if (connection != null) {
            String sql = "select * from smbms_role";
            Object[] params = {};
            resultSet = BaseDao.execute(connection, pstm, resultSet, sql, params);
            while (resultSet.next()) {
                Role _role = new Role();
                _role.setId(resultSet.getInt("id"));
                _role.setRoleCode(resultSet.getString("roleCode"));
                _role.setRoleName(resultSet.getString("roleName"));
                roleList.add(_role);
            }
            BaseDao.closeResource(null, pstm, resultSet);
        }

        return roleList;
    }
}

RoleService

package com.gongyi.service.role;


import com.gongyi.pojo.Role;

import java.util.List;

public interface RoleService {
    //获取角色列表
    List<Role> getRoleList();
}

RoleServiceImpl

package com.gongyi.service.role;

import com.gongyi.dao.BaseDao;
import com.gongyi.dao.role.RoleDao;
import com.gongyi.dao.role.RoleDaoImpl;
import com.gongyi.pojo.Role;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;


public class RoleServiceImpl implements RoleService {
    //引入Dao
    private RoleDao roleDao;

    public RoleServiceImpl() {
        roleDao = new RoleDaoImpl();
    }

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

    @Test
    public void testGetRoleList() {
        RoleServiceImpl roleService = new RoleServiceImpl();
        List<Role> roleList = roleService.getRoleList();
        for (Role role : roleList) {
            System.out.println(role.getRoleName());
        }
    }
}

4、用户显示的Servlet

1.获取用户前端的数据（查询）

2.判断请求是否需要执行，看参数的值判断

3.为了实现分页，需要计算出当前页面和总页面，页面大小…

4.用户列表展示

5.返回前端

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();
        List<User> userList = null;


        //第一次走这个请求，一定是第一页，页面大小是固定的
        int pageSize = 5;//可以把这个写到配置文件中，方便后期修改
        int currentPageNo = 1;
        if (queryUserName == null) {
            queryUserName = "";
        }
        if (temp != null && !temp.equals("")) {
            queryUserRole = Integer.parseInt(temp);//给查询赋值！0,1,2,3...
        }
        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 = pageSupport.getTotalCount();//总共有几页
        int totalPageCount = ((int)(totalCount/pageSize))+1;
        //控制首页和尾页
        //如果页数要小于1了，就显示第一页的东西
        if (currentPageNo < 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();
        }

    }

小黄鸭调试法：自言自语

彩蛋

1.每张表对应一个dao，一个package，职责分明

2.idea先写方法名，快捷键快速构建方法