SMBMS
数据库:
项目如何搭建?
考虑使用不使用Maven?依赖,Jar
1、项目搭建准备工作
- 搭建一个maven web项目
- 配置Tomcat
- 测试项目是否能够跑起来
- 导入项目中会遇到的jar包;
jsp,Servlet,mysql驱动,jstl,standard...
- 创建项目包结构
- 编写实体类;
ORM映射:表-类映射
- 编写基础公共类
- 数据库配置文件
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8&useSSL=true&setServiceTime=GMT username=root password=123456
- 编写数据库的公共类
//操作数据库的公共类 public class BaseDao { private static String driver; private static String url; private static String username; private static String password; //静态代码块,类加载的时候就初始化了 static { Properties properties = new Properties(); //通过类加载器读取对应的资源 InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties"); try { properties.load(is); } catch (IOException e) { e.printStackTrace(); } driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); } //获取数据库的连接 public static Connection getConnection(){ Connection connection = null; try { Class.forName("driver"); connection = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return connection; } //编写查询公共类 public static ResultSet execute(Connection connection,String sql,Object[] params,ResultSet resultSet,PreparedStatement preparedStatement) throws SQLException { //预编译的sql,在后面直接执行就可以了 preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { //setObject,占位符从1开始,但是我们的数组是从0开始! preparedStatement.setObject(i+1,params[i]); } resultSet = preparedStatement.executeQuery(); return resultSet; } //编写增删改公共方法 public static int execute(Connection connection, String sql, Object[] params, PreparedStatement preparedStatement) throws SQLException { preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { //setObject,占位符从1开始,但是我们的数组是从0开始! preparedStatement.setObject(i+1,params[i]); } int updateRows = preparedStatement.executeUpdate(); return updateRows; } //释放资源 public static boolean closeResource(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){ boolean flag = true; if (resultSet!=null){ try { resultSet.close(); //GC回收 resultSet = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (preparedStatement!=null){ try { preparedStatement.close(); //GC回收 preparedStatement = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (connection!=null){ try { connection.close(); //GC回收 connection = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } return flag; } }
- 编写字符编码过滤器
- 导入静态资源
2、登录功能实现
- 编写前端页面
- 设置首页
<!--设置欢迎页面--> <welcome-file-list> <welcome-file>login.jsp</welcome-file> </welcome-file-list>
- 编写dao层得到用户登录的接口
DAO:data access object
//得到要登录的用户 public User getLoginUser(Connection connection,String userCode) throws SQLException;
- 编写dao接口的实现类
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")); } BaseDao.closeResource(null,pstm,rs); } return user; } }
- 业务层接口
//用户登录 public User login(String userCode,String password);
- 业务层实现类
public class UserServiceImpl implements UserService { //业务层都会调用dao层,所以我们要引入Dao层; private UserDao userDao; public UserServiceImpl() { userDao = new UserDaoImpl(); } public User login(String userCode, String password) { Connection connection = null; User user = null; connection = BaseDao.getConnection(); try { user = userDao.getLoginUser(connection, userCode); } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection, null, null); } return user; } }
- 编写Servlet
public class LoginServlet extends HttpServlet { //Servlet:控制层,调用业务层代码 @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { System.out.println("LoginServlet--start...."); //获取用户名和密码 String userCode = req.getParameter("userCode"); String userPassword = req.getParameter("userPassword"); //和数据库中的密码进行对比,调用业务层; UserService userService = new UserServiceImpl(); User user = userService.login(userCode, userPassword); //这里已经把登录的人给查出来了 if (user!=null){ //查有此人,可以登录 //将用户的信息放到Session中; req.getSession().setAttribute(Constants.USER_SESSION,user); //跳转到主页 resp.sendRedirect("jsp/frame.jsp"); }else {//查无此人,无法登录 //转发回登录页面,顺带提示它,用户名或者密码错误 req.setAttribute("error","用户名或者密码不正确"); req.getRequestDispatcher("login.jsp").forward(req,resp); } } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doGet(req, resp); } }
- 注册Servlet
<!--Servlet--> <servlet> <servlet-name>LoginServlet</servlet-name> <servlet-class>com.kuang.servlet.user.LoginServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>LoginServlet</servlet-name> <url-pattern>/login.do</url-pattern> </servlet-mapping>
- 测试访问,确保以上功能成功!
3、登录功能优化
注销功能:
思路:移除Session,返回登录页面
public class LogoutServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { //移除用户的Constants.USER_SESSION req.getSession().removeAttribute(Constants.USER_SESSION); resp.sendRedirect(req.getContextPath()+"/login.jsp");//返回登录页面 } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doGet(req, resp); } }
注册xml
<servlet> <servlet-name>LogoutServlet</servlet-name> <servlet-class>com.kuang.servlet.user.LogoutServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>LogoutServlet</servlet-name> <url-pattern>/jsp/logout.do</url-pattern> </servlet-mapping>
登录拦截优化
编写一个过滤器并注册
public class SysFilter implements Filter { public void init(FilterConfig filterConfig) throws ServletException { } public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException { HttpServletRequest request = (HttpServletRequest) req; HttpServletResponse response = (HttpServletResponse) resp; //过滤器,从Session中获取用户, User user = (User) request.getSession().getAttribute(Constants.USER_SESSION); if (user==null){ //已经被移除或者注销了,或者未登录 response.sendRedirect("/smbms/error.jsp"); }else { chain.doFilter(req,resp); } } public void destroy() { } }
<!--用户登录过滤器--> <filter> <filter-name>SysFilter</filter-name> <filter-class>com.kuang.filter.SysFilter</filter-class> </filter> <filter-mapping> <filter-name>SysFilter</filter-name> <url-pattern>/jsp/*</url-pattern> </filter-mapping>
测试,登录,注销权限,都要保证OK!
4、密码修改
- 导入前端素材
<li><a href="${pageContext.request.contextPath }/jsp/pwdmodify.jsp">密码修改</a></li>
- 写项目,建议从底层向上写
- UserDao接口
//修改当前用户密码 public int updatePwd(Connection connection,int id,int password) throws SQLException;
- UserDao接口实现类
//修改当前用户密码 public int updatePwd(Connection connection, int id, int password) throws SQLException { PreparedStatement pstm = null; int execute = 0; if (connection!=null){ String sql = "update smbms_user set userPassword = ? where id = ?"; Object params[] = {password,id}; execute = BaseDao.execute(connection, pstm, sql, params); BaseDao.closeResource(null,pstm,null); } return execute; }
- UserService层
//根据用户ID修改密码 public boolean updatePwd(int id, int pwd);
- UserService实现类
public boolean updatePwd(int id, int pwd) { Connection connection = null; boolean flag = false; //修改密码 try { connection = BaseDao.getConnection(); if (userDao.updatePwd(connection,id,pwd)>0){ flag = true; } } catch (SQLException e) { e.printStackTrace(); }finally { BaseDao.closeResource(connection,null,null); } return flag; }
- Servlet记得实现复用,需要提取出方法!
@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); } } public void updatePwd(HttpServletRequest req, HttpServletResponse resp){ //从Session里面拿ID; Object o = req.getSession().getAttribute(Constants.USER_SESSION); String newpassword = req.getParameter("newpassword"); System.out.println("UserServlet"+newpassword); boolean flag = false; //if (o!=null && !StringUtils.isNullOrEmpty(newpassword)){ if (o!=null && newpassword!=null){ UserService userService = new UserServiceImpl(); flag = userService.updatePwd(((User) o).getId(), newpassword); if (flag){ req.setAttribute("message","修改密码成功,请退出,使用新密码登录"); //密码修改成功,移除当前Session req.getSession().removeAttribute(Constants.USER_SESSION); }else { req.setAttribute("message","密码修改失败"); //密码修改成功,移除当前Session } }else { req.setAttribute("message","新密码有问题"); } try { req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp); } catch (ServletException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
<servlet> <servlet-name>UserServlet</servlet-name> <servlet-class>com.kuang.servlet.user.UserServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>UserServlet</servlet-name> <url-pattern>/jsp/user.do</url-pattern>
- 测试
优化密码修改使用Ajax;
- 阿里巴巴的fastjson
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.79</version> </dependency>
- 后台代码修改
//修改密码 public void updatePwd(HttpServletRequest req, HttpServletResponse resp){ //从Session里面拿ID; Object o = req.getSession().getAttribute(Constants.USER_SESSION); String newpassword = req.getParameter("newpassword"); System.out.println("UserServlet"+newpassword); boolean flag = false; //if (o!=null && !StringUtils.isNullOrEmpty(newpassword)){ if (o!=null && newpassword!=null){ UserService userService = new UserServiceImpl(); flag = userService.updatePwd(((User) o).getId(), newpassword); if (flag){ req.setAttribute("message","修改密码成功,请退出,使用新密码登录"); //密码修改成功,移除当前Session req.getSession().removeAttribute(Constants.USER_SESSION); }else { req.setAttribute("message","密码修改失败"); //密码修改成功,移除当前Session } }else { req.setAttribute("message","新密码有问题"); } try { req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp); } catch (ServletException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //验证旧密码,session中有用户的密码 public void pwdModify(HttpServletRequest req, HttpServletResponse resp){ //从Session里面拿oldpassword; Object o = req.getSession().getAttribute(Constants.USER_SESSION); String oldpassword = req.getParameter("oldpassword"); //万能的Map : 结果集 HashMap<String, String> resultMap = new HashMap<String,String>(); if (o==null){ //Session失效了,session过期了 resultMap.put("result","sessionerror"); }else if (StringUtils.isNullOrEmpty(oldpassword)){ //输入的密码为空 resultMap.put("result","error"); }else { String userPassword = ((User) o).getUserPassword(); //Session中用户的密码 if (oldpassword.equals(userPassword)){ resultMap.put("result","true"); }else { resultMap.put("result","false"); } } try { resp.setContentType("application/json"); PrintWriter writer = resp.getWriter(); //JSONArray 阿里巴巴的JSON工具类,转换格式 /* resultMap = ["result","sessionerror","result","error"] Json格式 = {key:value} */ writer.write(JSONArray.toJSONString(resultMap)); writer.flush(); writer.close(); } catch (IOException e) { e.printStackTrace(); } }
- 测试
5、用户管理实现
思路:
- 导入分页的工具类
- 用户列表页面导入
userlist.jsp
rollpage.jsp
1、获取用户数量
- UserDao
//根据用户名或者角色查询用户总数 public int getUserCount(Connection connection,String username,int userRole) throws SQLException;
- UserDaoImpl
//根据用户名或者角色查询用户总数【最难理解的SQL】 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语句 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, null, sql.toString(), params); if (rs.next()){ count = rs.getInt("count");//从结果集中获取最终的数量 } BaseDao.closeResource(null,pstm,rs); } return count; }
- UserService
//查询记录数 public int getUserCount(String username, int userRole);
- 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 e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection,null,null); } return count; }
2、获取用户列表
- userdao
//通过条件查询-userList public List<User> getUserList(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws Exception;
- userdaoImpl
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语句 sql.append(" and u.userName like ?"); list.add("%"+username+"%"); //index:0 } if (userRole>0){ sql.append(" and u.userRole = ?"); list.add(userRole); //index:1 } //在数据库中,分页使用 limit startIndex,pageSize; 总数 //当前页 (当前页-1)*页面大小 //0,5 1 0 01234 //5,5 2 5 56789 //10,5 3 10 sql.append(" order by creationDate DESC limit ?,?"); currentPageNo = (currentPageNo-1)*pageSize; list.add(currentPageNo); list.add(pageSize); //怎么把list转换为数组 Object[] params = list.toArray(); System.out.println("sql-->"+sql.toString()); //输出最后完整的SQL语句 rs = BaseDao.execute(connection, pstm, null, 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; }
- userService
//通过条件查询-userList public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
- 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; }
3、获取角色操作
为了我们职责统一,可以把角色的操作单独放在一个包中,和POJO类对应
RoleDao
public interface RoleDao { //获取角色列表 public List<Role> getRoleList(Connection connection) throws SQLException; }
RoleDaoImpl
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
//获取角色列表 public List<Role> getRoleList();
RoleServiceImpl
public class RoleServiceImpl implements RoleService{ //引入Dao private RoleDao roleDao = null; 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 e) { e.printStackTrace(); } finally { BaseDao.closeResource(connection,null,null); } return roleList; } }
4、用户显示的Servlet
- 获取用户前端的数据(查询)
- 判断请求是否需要执行,看参数的值判断
- 为了实现分页,需要计算出当前页面和总页面,页面大小...
- 用户列表展示
- 返回前端
//重点,难点 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.getTotalPageCount(); //总共有几页 //控制首页和尾页 //如果页面要小于1了,就显示第一页的东西 if (totalPageCount<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(); } }
小黄鸭调试法
6、SMBMS架构分析