SMBMS项目开发

SMBMS(超市管理项目)

目录

• SMBMS(超市管理项目)
  • 1. 搭建项目准备工作
  • 2.登录功能实现
  • 3. 登录功能优化
    • 3.1 注销功能
    • 3.2 登录拦截优化
  • 4. 密码修改
  • 5. 优化密码修改使用Ajax
  • 6. 用户管理实现
    • 6.1 获取用户数量
    • 6.2 获取用户列表
    • 6.3 获取角色信息
    • 6.4 用户显示的Servlet
  • 7.用户管理代码补齐

注意事项:js代码中文乱码问题，可以将编码改成UTF-8-bom，记得清除网站缓存Ctcl+Shift+Delete

SMBMS项目github下载

SMBMS项目百度网盘下载提取码rex3

记得修改smbms\WEB-INF\classes下的db.properties文件内容，该项目只完成到用户管理，可直接在Tomcat中跑

1. 搭建项目准备工作

1. 搭建一个maven web 项目

2. 配置Tomcat

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

4. 导入项目中需要的jar包;
   jsp，Servlet，mysql驱动，jstl，standard

5. 构建项目包结构

6. 编写实体类
   ROM映射:表-类映射

7. 编写基础公共类
   1、数据库配置文件（mysql5.xx和8.xx的编写有差异）

   driver = com.mysql.cj.jdbc.Driver
   url = jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true&characterEncoding =utf8&useSSL= true &serverTimezone=GMT%2B8
   username = root
   password = 12345678
   

   2、编写数据库的公共类

   package com.jezer.dao;
   
   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.*;
   import java.util.Properties;
   
   /**
    * @author Jay_Soul
    * 操作数据库的公共类
    */
   public class BaseDao {
       //静态代码块,在类加载的时候执行
       static{
           init();
       }
   
       private static String driver;
       private static String url;
       private static String user;
       private static String password;
   
       /**初始化连接参数,从配置文件里获得*/
       public static void init(){
           Properties properties =new Properties();
           String configFile = "db.properties";
           InputStream is= BaseDao.class.getClassLoader().getResourceAsStream(configFile);
           try {
               properties.load(is);
           } catch (IOException e) {
               e.printStackTrace();
           }
           driver=properties.getProperty("driver");
           url=properties.getProperty("url");
           user=properties.getProperty("username");
           password=properties.getProperty("password");
       }
       /**
        * 获取数据库连接
        */
       public static Connection getConnection(){
           Connection connection = null;
           try {
               Class.forName(driver);
               connection = DriverManager.getConnection(url, user, password);
           } catch (Exception e) {
               e.printStackTrace();
           }
           return connection;
       }
   
       /**编写查询公共方法*/
       public static ResultSet executeQuery(Connection connection, PreparedStatement preparedStatement,ResultSet resultSet, String sql, Object[] params) throws SQLException {
           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,PreparedStatement preparedStatement , String sql, Object[] params) throws SQLException {
           preparedStatement = connection.prepareStatement(sql);
           for (int i = 0; i < params.length; i++) {
               //setObject，占位符从1开始，但是数组下标从0开始
               preparedStatement.setObject(i + 1, params[i]);
           }
           return preparedStatement.executeUpdate();
       }
       /**释放资源*/
       public static boolean closeResource(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
           boolean isClose = true;
           if (resultSet != null){
               try {
                   resultSet.close();
                   //GC回收
                   resultSet = null;
               } catch (SQLException e) {
                   e.printStackTrace();
                   isClose = false;
               }
           }
   
           if (preparedStatement != null){
               try {
                   preparedStatement.close();
                   //GC回收
                   preparedStatement = null;
               } catch (SQLException e) {
                   e.printStackTrace();
                   isClose = false;
               }
           }
   
           if (connection != null){
               try {
                   connection.close();
                   //GC回收
                   connection = null;
               } catch (SQLException e) {
                   e.printStackTrace();
                   isClose = false;
               }
           }
           return isClose;
       }
   }
   
   

   3、编写字符编码过滤器

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

   <filter>
       <filter-name>characterEncodingFilter</filter-name>
       <filter-class>com.jezer.filter.CharacterEncodingFilter</filter-class>
   </filter>
   <filter-mapping>
       <filter-name>characterEncodingFilter</filter-name>
       <url-pattern>/*</url-pattern>
   </filter-mapping>
   

8. 导入静态资源

---

2.登录功能实现

1. 编写前端页面

2. 设置首页

   <%@ page language="java" contentType="text/html; charset=UTF-8"
       pageEncoding="UTF-8"%>
   <!DOCTYPE html>
   <html>
   <head lang="en">
       <meta charset="UTF-8">
       <title>系统登录 - 超市订单管理系统</title>
       <link type="text/css" rel="stylesheet" href="${pageContext.request.contextPath }/css/style.css" />
       <script type="text/javascript">
   	/* if(top.location!=self.location){
   	      top.location=self.location;
   	 } */
       </script>
   </head>
   <body class="login_bg">
       <section class="loginBox">
           <header class="loginHeader">
               <h1>超市订单管理系统</h1>
           </header>
           <section class="loginCont">
   	        <form class="loginForm" action="${pageContext.request.contextPath }/login.do"  name="actionForm" id="actionForm"  method="post" >
   				<div class="info">${error}</div>
   				<div class="inputbox">
                       <label for="userCode">用户名：</label>
   					<input type="text" class="input-text" id="userCode" name="userCode" placeholder="请输入用户名" required/>
   				</div>	
   				<div class="inputbox">
                       <label for="userPassword">密码：</label>
                       <input type="password" id="userPassword" name="userPassword" placeholder="请输入密码" required/>
                   </div>	
   				<div class="subBtn">
   					
                       <input type="submit" value="登录"/>
                       <input type="reset" value="重置"/>
                   </div>	
   			</form>
           </section>
       </section>
   </body>
   </html>
   
   

     <welcome-file-list>
       <welcome-file>login.jsp</welcome-file>
     </welcome-file-list>
   

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

   public interface UserDao {
       /**得到要登录的用户*/
       public User getLoginUser(Connection connection, String userCode) throws SQLException;
   }
   

   4. 编写dao层接口的实现类

   package com.jezer.dao.user;
   
   import com.jezer.dao.BaseDao;
   import com.jezer.pojo.User;
   
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   
   /**
    * @author Jay_Soul
    */
   public class UserDaoImpl implements UserDao{
       @Override
       public User getLoginUser(Connection connection, String userCode) throws SQLException {
           PreparedStatement preparedStatement = null;
           ResultSet resultSet = null;
           User user = null;
   
           if (connection != null){
               String sql = "select * from smbms_user where user userCode = ?";
               Object[] params = {userCode};
               resultSet = BaseDao.executeQuery(connection, preparedStatement, resultSet, sql, params);
               if (resultSet.next()){
                   user = new User();
                   user.setId(resultSet.getInt("id"));
                   user.setUserCode(resultSet.getString("userCode"));
                   user.setUserName(resultSet.getString("userName"));
                   user.setUserPassword(resultSet.getString("userPassword"));
                   user.setGender(resultSet.getInt("gender"));
                   user.setBirthday(resultSet.getDate("birthday"));
                   user.setPhone(resultSet.getString("phone"));
                   user.setAddress(resultSet.getString("address"));
                   user.setUserRole(resultSet.getInt("userRole"));
                   user.setCreatedBy(resultSet.getInt("createdBy"));
                   user.setCreationDate(resultSet.getTimestamp("creationDate"));
                   user.setModifyBy(resultSet.getInt("modifyBy"));
                   user.setModifyDate(resultSet.getTimestamp("modifyDate"));
               }
               BaseDao.closeResource(null, preparedStatement, resultSet);
   
           }
           return user;
       }
   }
   

   5. 业务层接口

   //业务层接口
   public User login(String userCode, String password);
   

   6. 业务层实现类

   package com.jezer.service.user;
   
   import com.jezer.dao.BaseDao;
   import com.jezer.dao.user.UserDao;
   import com.jezer.dao.user.UserDaoImpl;
   import com.jezer.pojo.User;
   import org.junit.Test;
   
   import java.sql.Connection;
   import java.sql.SQLException;
   
   /**
    * @author Jay_Soul
    */
   public class UserServiceImpl implements UserService{
       /**
        * 业务层都会调用dao层，所以我们要引入Dao层
        */
       private UserDao userDao;
       public UserServiceImpl(){
           userDao = new UserDaoImpl();
       }
       @Override
       public User login(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.closeResource(connection, null, null);
           }
           return  user;
       }
   }
   

   7. 业务层代码测试

   @Test
   public void test(){
       UserService userService = new UserServiceImpl();
       User user = userService.login("admin", "wedawdasdfa");
       System.out.println(user.getUserPassword());
   
   }
   

   8. 编写Servlet

   package com.jezer.servlet.user;
   
   import com.jezer.pojo.User;
   import com.jezer.service.user.UserService;
   import com.jezer.service.user.UserServiceImpl;
   import com.jezer.util.Constants;
   
   import javax.servlet.ServletException;
   import javax.servlet.http.HttpServlet;
   import javax.servlet.http.HttpServletRequest;
   import javax.servlet.http.HttpServletResponse;
   import java.io.IOException;
   
   /**
    * @author Jay_Soul
    */
   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 (null != user && userPassword.equals(user.getUserPassword())){
               //将用户信息放到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);
       }
   }
   

   9. 注册Servlet

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

   10. 测试访问,保证以上功能可以成功

---

3. 登录功能优化

3.1 注销功能

思路：移除session，返回登录页面

package com.jezer.servlet.user;

import com.jezer.util.Constants;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author Jay_Soul
 */
public class LogoutServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //移除用户的Constant.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);
    }
}

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

---

3.2 登录拦截优化

编写一个过滤器，并注册

package com.jezer.filter;

import com.jezer.pojo.User;
import com.jezer.util.Constants;

import javax.servlet.*;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author Jay_Soul
 */
public class SysFilter implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        HttpServletRequest request = (HttpServletRequest) servletRequest;
        HttpServletResponse response = (HttpServletResponse) servletResponse;
        //过滤器，从Session中获取用户
        User user = (User) request.getSession().getAttribute(Constants.USER_SESSION);
        if (user == null){
            //说明已经被移除或注销或者未登录
            response.sendRedirect("/smbms/error.jsp");
        }else {
            filterChain.doFilter(servletRequest, servletResponse);
        }
    }

    @Override
    public void destroy() {

    }
}

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

---

4. 密码修改

1. 导入前端素材
2. 写项目，建议从底层向上写
3. UserDao接口

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

4. UserDao接口实现类

/**
     * 修改用户密码
*/
@Override
public int updatePwd(Connection connection, int id, String password) throws SQLException {
    PreparedStatement preparedStatement = null;
    int execute = 0;
    if (connection != null) {
        String sql = "update smbms_user set userPassword = ? where id = ? ";
        Object params[] = {password, id};
        execute = BaseDao.execute(connection, preparedStatement, sql, params);
        BaseDao.closeResource(null, preparedStatement, null);
    }
    return execute;
}

5. UserService层

/**
     * 根据用户id修改密码
*/
public boolean updatePwd(int id, String password);

6. UserService实现类

/**
  * 修改用户密码
*/
@Override
public boolean updatePwd(int id, String password) {
    Connection connection = null;
    boolean isOK = false;

    //修改密码
    try {
        connection = BaseDao.getConnection();
        if (userDao.updatePwd(connection, id, password) > 0){
            isOK = true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        BaseDao.closeResource(connection,null,null);
    }
    return isOK;
}

7. servlet记得实现复用，要提取出方法！

package com.jezer.servlet.user;

import com.jezer.pojo.User;
import com.jezer.service.user.UserService;
import com.jezer.service.user.UserServiceImpl;
import com.jezer.util.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;

public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //修改密码
        String method = req.getParameter("method");
        if ("method".equals(method) && null != method){
            this.updatePwd(req, resp);
        }

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }

    /**
     * 修改用户密码
     */
    public void updatePwd(HttpServletRequest req, HttpServletResponse resp){
        Object object = req.getSession().getAttribute(Constants.USER_SESSION);
        String newpassword = req.getParameter("newpassword");
        boolean isOK = false;
        if (null != object && !StringUtils.isNullOrEmpty(newpassword)){
            UserService userService = new UserServiceImpl();
            User user = (User) object;
            isOK = userService.updatePwd(user.getId(), newpassword);
            if (isOK){
                req.setAttribute(Constants.SYS_MESSAGE, "修改密码成功,请退出并使用新密码重新登录！");
                req.getSession().removeAttribute(Constants.USER_SESSION);
            }else{
                req.setAttribute(Constants.SYS_MESSAGE, "修改密码失败！");
            }
        }else{
            req.setAttribute(Constants.SYS_MESSAGE, "修改密码失败！");
        }
        try {
            req.getRequestDispatcher("pwdmodify.jsp").forward(req, resp);
        } catch (ServletException | IOException e) {
            e.printStackTrace();
        }
    }
}

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

8. 测试

---

5. 优化密码修改使用Ajax

1. 导入阿里的包

<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.68</version>
</dependency>

2. 后台代码修改

package com.jezer.servlet.user;

import com.alibaba.fastjson.JSONArray;
import com.jezer.pojo.User;
import com.jezer.service.user.UserService;
import com.jezer.service.user.UserServiceImpl;
import com.jezer.util.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;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Map;

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) && null != method){
            this.updatePwd(req, resp);
        }else if ("pwdmodify".equals(method) && null != method){
            this.pwdModify(req, resp);
        }

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }

    /**
     * 修改用户密码
     */
    public void updatePwd(HttpServletRequest req, HttpServletResponse resp){
        Object object = req.getSession().getAttribute(Constants.USER_SESSION);
        String newpassword = req.getParameter("newpassword");
        boolean isOK = false;
        if (null != object && !StringUtils.isNullOrEmpty(newpassword)){
            UserService userService = new UserServiceImpl();
            User user = (User) object;
            isOK = userService.updatePwd(user.getId(), newpassword);
            if (isOK){
                req.setAttribute(Constants.SYS_MESSAGE, "修改密码成功,请退出并使用新密码重新登录！");
                req.getSession().removeAttribute(Constants.USER_SESSION);
            }else{
                req.setAttribute(Constants.SYS_MESSAGE, "修改密码失败！");
            }
        }else{
            req.setAttribute(Constants.SYS_MESSAGE, "修改密码失败！");
        }
        try {
            req.getRequestDispatcher("pwdmodify.jsp").forward(req, resp);
        } catch (ServletException | IOException e) {
            e.printStackTrace();
        }
    }
//    验证旧密码
    public void pwdModify(HttpServletRequest req, HttpServletResponse resp){
        Object o = req.getSession().getAttribute(Constants.USER_SESSION);
        String oldpassword = req.getParameter("oldpassword");
        //万能的Map：结果集
        Map<String, String> resultMap = new HashMap<String, String>();
        if (null == o){
            //Session失效
            resultMap.put("result", "sessionerror");
        }else if (StringUtils.isNullOrEmpty("oldpassword")){
            resultMap.put("result", "error");
        }else{
            User user = (User) o;
            String userPassword = user.getUserPassword();
            if (oldpassword.equals(userPassword)){
                resultMap.put("result", "true");
            }else{
                resultMap.put("result", "false");
            }
        }

        try {
            resp.setContentType("application/json");
            PrintWriter out = resp.getWriter();
            //JSONArray 阿里巴巴的JSON工具类，转换格式
            out.write(JSONArray.toJSONString(resultMap));
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

3. 测试

---

6. 用户管理实现

1. 导入分页的工具类-PageSupport

2. 用户列表页面导入userlist.jsp

6.1 获取用户数量

1. UserDao

//根据用户名或者角色查询用户总数
public int getUserCount(Connection connection,String username ,int userRole)throws SQLException, Exception;

2. UserDaoImpl

/**
   * 查询用户总数
*/
@Override
public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = 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<>();//存放参数
        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;
        }
        //把List转换为数组
        Object[] params = list.toArray();
        System.out.println("UserDaoImpl->getUserCount:" + sql.toString());
        resultSet = BaseDao.executeQuery(connection,preparedStatement, resultSet, sql.toString(),  params);
        if (resultSet.next()){
            count = resultSet.getInt("count");//从结果集中获取数量
        }
        BaseDao.closeResource(null, preparedStatement, resultSet);
    }
    return count;
}

3. UserService

//查询记录数
	public int getUserCount(String username, int userRole);

4. UserServiceImpl

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

5. 测试

@Test
public void test(){
    UserServiceImpl userService = new UserServiceImpl();
    int userCount = userService.getUserCount(null, 0);
    System.out.println(userCount);
}

---

6.2 获取用户列表

1. UserDao

//通过条件查询-userList
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;

2. UserDaoImpl

/**
        查询用户信息
     */
@Override
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    ArrayList<User> userList = new ArrayList<>();
    if (null != connection){
        StringBuffer sql = new StringBuffer();
        sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
        ArrayList<Object> list = new ArrayList<>();
        if (!StringUtils.isNullOrEmpty(userName)){
            sql.append(" and u.userName like ?");
            list.add("%"+userName+"%");
        }
        if(userRole > 0){
            sql.append(" and u.userRole = ?");
            list.add(userRole);
        }
        sql.append(" order by creationDate DESC limit ?,?");
        currentPageNo = (currentPageNo-1)*pageSize;
        list.add(currentPageNo);
        list.add(pageSize);

        Object[] params = list.toArray();
        System.out.println("sql ----> " + sql.toString());
        resultSet = BaseDao.executeQuery(connection, preparedStatement, resultSet, sql.toString(), params);
        while(resultSet.next()){
            User _user = new User();
            _user.setId(resultSet.getInt("id"));
            _user.setUserCode(resultSet.getString("userCode"));
            _user.setUserName(resultSet.getString("userName"));
            _user.setGender(resultSet.getInt("gender"));
            _user.setBirthday(resultSet.getDate("birthday"));
            _user.setPhone(resultSet.getString("phone"));
            _user.setUserRole(resultSet.getInt("userRole"));
            _user.setUserRoleName(resultSet.getString("userRoleName"));
            userList.add(_user);
        }
        BaseDao.closeResource(null, preparedStatement, resultSet);
    }
    return userList;
}

3. UserService

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

4. UserServiceImpl

/**
   根据条件查询用户列表
*/
@Override
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 e) {
        e.printStackTrace();
    }finally {
        BaseDao.closeResource(connection, null, null);
    }
    return userList;
}

5. 测试

@Test
public void test(){
    UserServiceImpl userService = new UserServiceImpl();
    List<User> userList = userService.getUserList("系统管理员", 1, 1, 5);
    System.out.println("处理了" + userList.size() + "条！");
}

---

6.3 获取角色信息

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

1. RoleDao

//获取角色列表
public List<Role> getRoleList(Connection connection)throws Exception;

2. RoleDaoImpl

package com.jezer.dao.role;

import com.jezer.dao.BaseDao;
import com.jezer.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 preparedStatement = null;
        ResultSet resultSet = null;
        List<Role> roleList = new ArrayList<Role>();
        if (connection != null){
            String sql = "select * from smbms_role";
            Object[] params= {};
            resultSet = BaseDao.executeQuery(connection, preparedStatement, 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, preparedStatement, resultSet);
        }
        return roleList;
    }
}

3. RoleService

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

4. RoleServiceImpl

package com.jezer.service.role;

import com.jezer.dao.BaseDao;
import com.jezer.dao.role.RoleDao;
import com.jezer.dao.role.RoleDaoImpl;

import com.jezer.pojo.Role;
import org.junit.Test;

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

public class RoleServiceImpl implements RoleService {
    /**
     * 业务层都会调用dao层，所以我们要引入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 test(){
        RoleService roleService = new RoleServiceImpl();
        List<Role> roleList = roleService.getRoleList();
        System.out.println(roleList.size());
    }
}

---

6.4 用户显示的Servlet

1. 获取用户前端的数据（查询）
2. 判断请求是否需要执行，看参数的值判断
3. 为了实现分页，需要计算出当前页面和总页面，页面大小…
4. 用户列表展示
5. 返回前端

·/** 用户管理查询  */
public void query(HttpServletRequest req, HttpServletResponse resp){
    //查询用户列表
    //从前端获取数据
    String queryname = req.getParameter("queryname");
    String queryUserRole = req.getParameter("queryUserRole");
    String pageIndex= req.getParameter("pageIndex");
    //获取用户列表
    UserServiceImpl userService = new UserServiceImpl();
    RoleServiceImpl roleService = new RoleServiceImpl();
    int pageSize = 5;
    List<User> userList = null;
    List<Role> roleList = null;
    /* 将String类型转化为int，同时进行错误判断 */
    int currentPageNo = 1;
    int queryUserRole_int = 0;
    if (queryname == null){
        queryname = "";
    }
    if (queryUserRole != null  && !"".equals(queryUserRole)){
        queryUserRole_int = Integer.parseInt(queryUserRole);
    }
    if (pageIndex != null){
        currentPageNo = Integer.parseInt(pageIndex);
    }
    //获取用户总数
    int totalCount = userService.getUserCount(queryname, queryUserRole_int);
    //总页数支持
    PageSupport pageSupport = new PageSupport();
    pageSupport.setCurrentPageNo(currentPageNo);
    pageSupport.setPageSize(pageSize);
    pageSupport.setTotalPageCount(totalCount);
    int totalPageCount = pageSupport.getTotalPageCount();
    //控制首页和尾页
    if (currentPageNo < 1){
        currentPageNo = 1;
    }else if (currentPageNo > totalPageCount){
        currentPageNo = totalPageCount;
    }
    //获取用户列表显示
    userList = userService.getUserList(queryname, queryUserRole_int, currentPageNo, pageSize);
    req.setAttribute("userList", userList);

    roleList = roleService.getRoleList();
    req.setAttribute("roleList", roleList);
    req.setAttribute("totalCount", totalCount);
    req.setAttribute("currentPageNo", currentPageNo);
    req.setAttribute("totalPageCount", totalPageCount);
    req.setAttribute("queryUserName", queryname);
    req.setAttribute("queryUserRole",queryUserRole_int);
    //返回前端
    try {
        req.getRequestDispatcher("userlist.jsp").forward(req,resp);
    } catch (ServletException | IOException e) {
        e.printStackTrace();
    }
}

---

7.用户管理代码补齐

SMBMS项目下载

记得修改smbms\WEB-INF\classes下的db.properties文件内容，该项目只完成到用户管理，可直接在Tomcat中跑

SMBMS项目百度网盘下载提取码rex3