SMBMS商超管理系统

Smbms

1. 项目搭建准备工作

1. 搭建一个maven web项目

2. 配置Tomcat

3. 测试项目是否能够跑起来

4. 导入项目中会遇到的jar包：jsp、Servlet-api、jstl、standard、jstl-api

5. 创建项目包结构

   

6. 编写实体类

   pojo–>数据库表结构一一对应

7. 编写基础公共类

   1. 数据库配置文件

      driver=com.mysql.jdbc.Driver
      url=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf8&useSSL=true
      username=root
      password=123456
      

   2. 编写数据库公共类

      package com.jian.dao;
      
      import sun.plugin2.os.windows.FLASHWINFO;
      
      import java.io.IOException;
      import java.io.InputStream;
      import java.sql.*;
      import java.util.Properties;
      
      //操作数据库的公共类
      public class BaseDao {
          private static String driver;
          private static String url;
          private static String username;
          private static String password;
      
          static {
              //通过类加载器获得对应的资源
              InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
              Properties properties = new 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");
              try {
                  Class.forName(driver);
              } catch (ClassNotFoundException e) {
                  e.printStackTrace();
              }
          }
          //获取连接公共类
          public static Connection getConnection() {
              Connection connection = null;
              try {
                  connection = DriverManager.getConnection(url, username, password);
              } catch (SQLException e) {
                  e.printStackTrace();
              }
              return connection;
          }
          //查询公共类
          public static ResultSet executeQuery(Connection connection,String sql,Object[] params,ResultSet rs,PreparedStatement ps) throws SQLException {
              ps=connection.prepareStatement(sql);
              for (int i=0;i<params.length;i++){
                  ps.setObject(i+1,params[i]);
              }
              rs=ps.executeQuery();
              return rs;
          }
          //增删改公共类
          public static int execute(Connection connection,String sql,Object[] params,PreparedStatement ps) throws SQLException {
              ps=connection.prepareStatement(sql);
              for (int i=0;i<params.length;i++){
                  ps.setObject(i+1,params[i]);
              }
              return ps.executeUpdate();
          }
          //关闭连接
          public static boolean release(Connection connection,PreparedStatement ps,ResultSet rs){
              boolean flag=true;
              if (rs!=null){
                  try {
                      rs.close();
                      //GC回收
                      rs=null;
                  } catch (SQLException e) {
                      e.printStackTrace();
                      flag=false;
                  }
              }
              if (ps!=null){
                  try {
                      ps.close();
                      ps=null;
                  } catch (SQLException e) {
                      e.printStackTrace();
                      flag=false;
                  }
              }
              if (connection!=null){
                  try {
                      connection.close();
                      connection=null;
                  } catch (SQLException e) {
                      e.printStackTrace();
                      flag=false;
                  }
              }
              return flag;
          }
      }
      

   3. 编写字符编码过滤器

      package com.jian.filter;
      
      import javax.servlet.*;
      import java.io.IOException;
      
      public class CharacterEncodingFilter implements Filter {
          public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
              servletRequest.setCharacterEncoding("utf-8");
              servletResponse.setCharacterEncoding("utf-8");
              filterChain.doFilter(servletRequest,servletResponse);
          }
      
          public void init(FilterConfig filterConfig) throws ServletException {
              
          }
      
          public void destroy() {
              
          }
      }
      

          <filter>
              <filter-name>CharacterEncodingFilter</filter-name>
              <filter-class>com.jian.filter.CharacterEncodingFilter</filter-class>
          </filter>
          <filter-mapping>
              <filter-name>CharacterEncodingFilter</filter-name>
              <url-pattern>/smbms/*</url-pattern>
          </filter-mapping>
      

8. 导入以下静态资源，注意导入到webapp目录下

   

2. 登录功能实现

1. 编写前段页面代码

2. 设置首页(web.xml中配置)

       <!--设置首页-->
       <welcome-file-list>
           <welcome-file>login.jsp</welcome-file>
       </welcome-file-list>
   

3. 编写Dao层登录用户的接口

   public interface UserDao {
       User getLoginUser(Connection connection,String userCode) throws SQLException;
   }
   

4. 编写Dao接口的实现类

   package com.jian.dao.user;
   
   import com.jian.dao.BaseDao;
   import com.jian.pojo.User;
   
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   
   public class UserDaoImpl implements UserDao {
       public User getLoginUser(Connection connection, String userCode) throws SQLException {
           PreparedStatement ps = null;
           ResultSet rs = null;
           User user = null;
           if (connection != null) {
               String sql = "select * from smbms_user where userCode=?";
               Object[] params = {userCode};
               rs= BaseDao.executeQuery(connection,sql,params,rs,ps);
               while (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.getDate("creationDate"));
                   user.setModifyBy(rs.getInt("modifyBy"));
                   user.setModifyDate(rs.getDate("modifyDate"));
               }
               BaseDao.release(null,ps,rs);
           }
           return user;
       }
   }
   

5. 业务层service层接口

   public interface UserService {
       User getLoginUser(String userCode,String password);
   }
   

6. 业务层实现类serviceImpl

   package com.jian.service.user;
   
   import com.jian.dao.BaseDao;
   import com.jian.dao.user.UserDao;
   import com.jian.dao.user.UserDaoImpl;
   import com.jian.pojo.User;
   import org.junit.Test;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   public class UserServiceImpl implements UserService {
       private UserDao userDao;
       public UserServiceImpl(){
           userDao=new UserDaoImpl();
       }
       public User getLoginUser(String userCode, String password) {
           Connection connection = null;
           User user = null;
           try {
               connection = BaseDao.getConnection();
               user = userDao.getLoginUser(connection, userCode);
           } catch (SQLException e) {
               e.printStackTrace();
           }finally {
               BaseDao.release(connection,null,null);
           }
           return user;
       }
   }
   

7. 编写LoginServlet

   package com.jian.servlet.user;
   
   import com.jian.pojo.User;
   import com.jian.service.user.UserService;
   import com.jian.service.user.UserServiceImpl;
   import com.jian.utils.Constants;
   
   import javax.servlet.ServletException;
   import javax.servlet.http.HttpServlet;
   import javax.servlet.http.HttpServletRequest;
   import javax.servlet.http.HttpServletResponse;
   import java.io.IOException;
   
   public class LoginServlet extends HttpServlet {
       @Override
       protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
           String userCode = req.getParameter("userCode");
           String userPassword = req.getParameter("userPassword");
           UserService userService=new UserServiceImpl();
           User user=userService.getLoginUser(userCode,userPassword);
           if (user!=null){
               //将用户信息存放在Session中
               req.getSession().setAttribute(Constants.USER_SESSION,user);
               resp.sendRedirect("jsp/frame.jsp");
           }else {
               //请求转发时携带参数，可以前段页面获取error值
               req.setAttribute("error","用户名或者密码错误!");
               req.getRequestDispatcher("/login.jsp").forward(req,resp);
           }
       }
   
       @Override
       protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
           doGet(req,resp);
       }
   }
   

8. 注册Servlet

       <servlet>
           <servlet-name>LoginServlet</servlet-name>
           <servlet-class>com.jian.servlet.user.LoginServlet</servlet-class>
       </servlet>
       <servlet-mapping>
           <servlet-name>LoginServlet</servlet-name>
           <url-pattern>/login.do</url-pattern>
       </servlet-mapping>
   

9. 测试访问

3. 登录功能优化

注销：移除Session，重定向登录页面

LogoutServlet

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.getSession().removeAttribute(Constants.USER_SESSION);
        resp.sendRedirect(req.getContextPath()+"/login.jsp");
    }

web.xml

    <servlet>
        <servlet-name>LogoutServlet</servlet-name>
        <servlet-class>com.jian.servlet.user.LogoutServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>LogoutServlet</servlet-name>
        <url-pattern>/jsp/logout.do</url-pattern>
    </servlet-mapping>

登录拦截功能：编写过滤器，注册

SysFilter

    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        HttpServletRequest req=(HttpServletRequest) servletRequest;
        HttpServletResponse resp=(HttpServletResponse) servletResponse;
        User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);
        if (user!=null){
            filterChain.doFilter(servletRequest,servletResponse);
        }else {
            resp.sendRedirect("/smbms/error.jsp");
        }
    }

web.xml

    <filter>
        <filter-name>SysFilter</filter-name>
        <filter-class>com.jian.filter.SysFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>SysFilter</filter-name>
        <url-pattern>/jsp/*</url-pattern>
    </filter-mapping>

4. 修改密码实现

1. 导入静态资源（前段页面）

2. 写项目建议从底层往上写

   

3. UserDao接口

   //修改当前用户密码
       int updatePwd(Connection connection,int id,String password) throws SQLException;
   

4. UserDao接口实现类

       public int updatePwd(Connection connection, int id, String password) throws SQLException {
           PreparedStatement ps=null;
           int rows=0;
           if (connection!=null){
               String sql="update smbms_user set userPassword=? where id=?";
               Object[] params={password,id};
               rows = BaseDao.execute(connection, sql, params, ps);
               BaseDao.release(null,ps,null);
           }
           return rows;
       }
   

5. UserService接口

       //修改密码
       boolean updatePwd(int id, String password);
   

6. UserServiceImpl实现类

       //修改当前用户密码
       public boolean updatePwd(int id, String password) {
           Connection connection = null;
           boolean flag = false;
           try {
               connection = BaseDao.getConnection();
               if (userDao.updatePwd(connection, id, password) > 0) {
                   flag = true;
               }
           } catch (SQLException e) {
               e.printStackTrace();
           } finally {
               BaseDao.release(connection, null, null);
           }
           return flag;
       }
   

7. 实现Servlet复用并注册Servlet

   package com.jian.servlet.user;
   
   import com.jian.pojo.User;
   import com.jian.service.user.UserService;
   import com.jian.service.user.UserServiceImpl;
   import com.jian.utils.Constants;
   import com.mysql.cj.util.StringUtils;
   
   import javax.servlet.ServletException;
   import javax.servlet.http.HttpServlet;
   import javax.servlet.http.HttpServletRequest;
   import javax.servlet.http.HttpServletResponse;
   import java.io.IOException;
   
   //实现Servlet复用
   public class UserServlet extends HttpServlet {
       @Override
       protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
           String method = req.getParameter("method");
           if ("savepwd".equals(method) && method != null) {
               updatePwd(req, resp);
           }
       }
   
       @Override
       protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
           doGet(req, resp);
       }
   
       public void updatePwd(HttpServletRequest req, HttpServletResponse resp) {
           String newpassword = req.getParameter("newpassword");
           User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);
           if (user != null && !StringUtils.isNullOrEmpty(newpassword)) {
               UserService userService = new UserServiceImpl();
               boolean flag = userService.updatePwd(user.getId(), newpassword);
               if (flag) {
                   req.setAttribute("message", "密码修改成功，请重新登录");
                   req.getSession().removeAttribute(Constants.USER_SESSION);
               } else {
                   req.setAttribute("message", "密码修改失败，请重试");
               }
           } else {
               req.setAttribute("message", "新密码有问题");
           }
           try {
               req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req, resp);
           } catch (ServletException e) {
               e.printStackTrace();
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
   }
   

       <servlet>
           <servlet-name>UserServlet</servlet-name>
           <servlet-class>com.jian.servlet.user.UserServlet</servlet-class>
       </servlet>
       <servlet-mapping>
           <servlet-name>UserServlet</servlet-name>
           <url-pattern>/jsp/user.do</url-pattern>
       </servlet-mapping>
   

8. 测试

旧密码判断是否正确使用Ajax

1. 导包

   • 阿里巴巴的fastjson

   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>fastjson</artifactId>
       <version>1.2.80</version>
   </dependency>
   

2. pwdModify（UserServlet）复用

       //验证旧密码，session中保存有直接对比
       public void pwdModify(HttpServletRequest req,HttpServletResponse resp){
           User user= (User) req.getSession().getAttribute(Constants.USER_SESSION);
           String oldpassword = req.getParameter("oldpassword");
           //万能Map:结果集
           Map<String,String> resultMap=new HashMap<String,String>();
           if (user==null){
               resultMap.put("result","sessionerror");  //Session为空
           }else if (StringUtils.isNullOrEmpty(oldpassword)){
               resultMap.put("result","error");  //旧密码为空
           }else{
               if (user.getUserPassword().equals(oldpassword)){
                   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. 用户管理实现

1. 导入分页工具类
2. 导入用户管理页面：userlist.jsp、rollpage.jsp

5.1 获取用户数量

UserDao

    //获取用户数量
    int getUserCount(Connection connection,String username,int userRole) throws SQLException;

UserDaoImpl

    //获取用户数量
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        if (connection != null) {
            StringBuffer sql = new StringBuffer("select count(1) as rowCount 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 userName like ?");
                list.add("%" + username + "%");
            }
            if (userRole > 0) {
                sql.append(" and userRole=?");
                list.add(userRole);
            }
            //将list转换为数组
            Object[] params = list.toArray();
            rs = BaseDao.executeQuery(connection, sql.toString(), params, rs, ps);
            while (rs.next()) {
                count = rs.getInt("rowCount");
            }
        }
        BaseDao.release(null, ps, rs);
        return count;
    }

UserService

    int getUserCount(String username, int userRole);

UserServiceImpl

    public int getUserCount(String username, int userRole) {
        Connection connection = null;
        int counts = 0;
        try {
            connection = BaseDao.getConnection();
            counts = userDao.getUserCount(connection, username, userRole);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.release(connection, null, null);
        }
        return counts;
    }

5.2 获取用户列表

UserDao

    //获取用户列表
    List<User> getUsers(Connection connection,String username,int userRole,int currentPageNo,int pageSize) throws SQLException;

UserDaoImpl

    //获取用户列表
    public List<User> getUsers(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<User> lists=new ArrayList<User>();
        if (connection!=null){
            StringBuffer sb=new StringBuffer("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where r.id=u.userRole");
            ArrayList<Object> plists=new ArrayList<Object>();
            if (username!=null){
                sb.append(" and userName like ?");
                plists.add("%"+username+"%");
            }
            if (userRole>0){
                sb.append(" and userRole=?");
                plists.add(userRole);
            }
            sb.append(" order by creationDate desc limit ?,?");
            currentPageNo=(currentPageNo-1)*pageSize;
            plists.add(currentPageNo);
            plists.add(pageSize);
            Object[] params = plists.toArray();
            rs=BaseDao.executeQuery(connection,sb.toString(),params,rs,ps);
            while (rs.next()){
                User _user=new User();
                _user.setId(rs.getInt("id"));
                _user.setUserName(rs.getString("userName"));
                _user.setGender(rs.getInt("gender"));
                _user.setUserCode(rs.getString("userCode"));
                _user.setBirthday(rs.getDate("birthday"));
                _user.setPhone(rs.getString("phone"));
                _user.setUserRole(rs.getInt("userRole"));
                _user.setUserRoleName(rs.getString("userRoleName"));
                lists.add(_user);
            }
        }
        BaseDao.release(null,ps,rs);
        return lists;
    }

UserService

    List<User> getUsers(String username,int userRole,int currentPageNo,int pageSize);

UserServiceImpl

    public List<User> getUsers(String username, int userRole, int currentPageNo, int pageSize) {
        Connection connection=null;
        List<User> user_list=null;
        try {
            connection=BaseDao.getConnection();
            user_list = userDao.getUsers(connection, username, userRole, currentPageNo, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(connection,null,null);
        }
        return user_list;
    }

5.3 获取角色列表

RoleDao

    //获取角色列表
    List<Role> getRoles(Connection connection) throws SQLException;

RoleDaoImpl

    public List<Role> getRoles(Connection connection) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Role> roleList = new ArrayList<Role>();
        if (connection != null) {
            String sql = "select * from smbms_role";
            Object[] params = {};
            rs = BaseDao.executeQuery(connection, sql, params, rs, ps);
            while (rs.next()) {
                Role role = new Role();
                role.setId(rs.getInt("id"));
                role.setRoleCode(rs.getString("roleCode"));
                role.setRoleName(rs.getString("roleName"));
                roleList.add(role);
            }
        }
        BaseDao.release(null, ps, rs);
        return roleList;
    }

RoleService

    //获取角色列表
    List<Role> getRoles();

RoleServiceImpl

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

5.4 获取用户列表展示Servlet

    //用户列表展示
    public void query(HttpServletRequest req,HttpServletResponse resp){
        String queryname = req.getParameter("queryname");
        String queryUserRole = req.getParameter("queryUserRole");
        String pageIndex = req.getParameter("pageIndex");
        //第一次走这个请求，一定是第一页，页面大小是固定的
        int pageSize=5;
        int currentPageNO=1;
        //默认设置roleId是0，防止前端没有传过来
        int queryRole=0;
        if (queryname==null){
            queryname="";
        }
        if (queryUserRole!=null&&!queryUserRole.equals("")){
            queryRole=Integer.parseInt(queryUserRole);
        }
        if (pageIndex!=null){
            currentPageNO=Integer.parseInt(pageIndex);
        }
        //Service层
        UserService userService=new UserServiceImpl();
        //获取用户列表
        List<User> userList=null;
        //获取用户总数（分页：上一页，下一页）
        int totalCount = userService.getUserCount(queryname, queryRole);
        PageSupport pageSupport=new PageSupport();
        pageSupport.setPageSize(pageSize);
        pageSupport.setCurrentPageNo(currentPageNO);
        pageSupport.setTotalCount(totalCount);
        int totalPageCount = pageSupport.getTotalPageCount();
        //首页和尾页控制
        if (currentPageNO<1){
            currentPageNO=1;
        }else if (currentPageNO>totalPageCount){
            currentPageNO=totalPageCount;
        }
        userList = userService.getUsers(queryname, queryRole, currentPageNO, pageSize);
        req.setAttribute("userList",userList);
        //获取角色列表
        RoleService roleService=new RoleServiceImpl();
        List<Role> roleList = roleService.getRoles();
        req.setAttribute("roleList",roleList);
        req.setAttribute("totalPageCount",totalPageCount);
        req.setAttribute("totalCount",totalCount);
        req.setAttribute("currentPageNo",currentPageNO);
        //保持查询框和下拉列表的值
        req.setAttribute("queryUserName",queryname);
        req.setAttribute("queryUserRole",queryRole);
        try {
            req.getRequestDispatcher("userlist.jsp").forward(req,resp);
        } catch (ServletException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }