思路
补充: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(); } }