【项目实践】SMBMS（Javaweb版）（四）用户管理

用户管理模块

用户管理模块需要包含的功能需要有添加用户、删除用户、查询用户、修改用户；显示时会进行表单显示，有分页功能，页码计算功能。

前期准备

共同工具类

翻页使用的对象工具类，计算页数方式如下：

每次显示的页数 = 总件数 % 单页显示的件数 == 0 ? 总件数 / 单页显示的件数 :(总件数 / 单页显示的件数)+1;

package com.common.utils;public class PageSupport {private int pageIndex = 1; // 当前页码private int pageSize; // 总页数private int totalCount;  // 总记录数private int totalPageCount; // 每页显示的记录数public int getPageIndex() {return pageIndex;}public void setPageIndex(int pageIndex) {if (pageIndex > 0) {this.pageIndex = pageIndex;}}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {if (pageSize > 0) {this.pageSize = pageSize;}}public int getTotalCount() {return totalCount;}public void setTotalCount(int totalCount) {if (totalCount > 0) {this.totalCount = totalCount;setByPageNo(totalCount);}}public int getTotalPageCount() {return totalPageCount;}public void setTotalPageCount(int totalPageCount) {this.totalPageCount = totalPageCount;}/*** 根据总记录数设置总页数* 此方法用于计算分页时的总页数，基于当前的每页记录数（pageSize）** @param totalCount 总记录数，即需要分页处理的数据总量*/private void setByPageNo(int totalCount) {// 计算总页数：如果总记录数除以每页记录数的余数为0，则总页数为总记录数除以每页记录数；// 否则，总页数为总记录数除以每页记录数加1this.totalPageCount = totalCount % pageSize == 0 ? totalCount / pageSize :totalCount / pageSize + 1;}}

导入前端代码

导入前端页面和相关js，跳转页面的时候及翻页的时候使用。

获取用户数量

需要为查询数据做好基础，涉嫌到数据统计。

UserDao

通过用户名称或角色查询获取用户数量，设计的功能模块有关下拉选择查询总数。

1. UserDao

    /*** 获取用户数量（根据用户名和角色筛选）** @param connection    数据库连接对象* @param queryUserName 查询的用户名* @param queryUserRole 查询的角色* @return 用户数量* @throws Exception 异常处理*/int getUserCount(Connection connection, String queryUserName, Integer queryUserRole) throws Exception;

2. UserDaoImpl

    /*** 获取用户数量（根据用户名和角色筛选）** @param connection    数据库连接对象* @param queryUserName 查询的用户名* @param queryUserRole 查询的角色* @return 用户数量* @throws Exception 异常处理*/@Overridepublic int getUserCount(Connection connection, String queryUserName, Integer queryUserRole) throws Exception {// 初始化用户数量为0int userCount = 0;// 预编译SQL语句对象PreparedStatement preparedStatement = null;// 结果集对象ResultSet resultSet = null;// 存储查询参数的列表ArrayList<Object> list = new ArrayList<>();// 检查数据库连接是否不为空if (connection != null) {// 构建基础SQL查询语句String sql = "select count(1) as userCount from smbms_user u, smbms_role r where u" +".userRole = r.id ";// 如果查询用户名不为空，则添加用户名模糊查询条件if (queryUserName != null && !queryUserName.isEmpty()) {sql += "and u.userName like ? ";list.add("%" + queryUserName + "%");}// 如果查询角色不为空且大于0，则添加角色精确查询条件if (queryUserRole != null && queryUserRole > 0) {sql += "and u.userRole = ? ";list.add(queryUserRole);}// 准备SQL语句执行preparedStatement = connection.prepareStatement(sql);// 将查询参数列表转换为数组形式Object[] params = list.toArray();// 执行查询并获取结果集resultSet = BaseDao.executeQuery(connection, sql, preparedStatement, params, resultSet);// 如果结果集不为空，则从结果集中获取用户数量if (resultSet != null) {userCount = resultSet.getInt("userCount");} else {// 如果结果集为空，输出提示信息System.out.println("用户查询不到");}// 关闭数据库资源BaseDao.closeResource(connection, preparedStatement, resultSet);}// 返回用户数量return userCount;}

UserService

调用Dao层的sql处理，如果出现问题及时抛出异常，之后在进行关闭连接。

1. UserService

public int getUserCount(String queryUserName, Integer queryUserRole);

2. UserServiceImpl

    /*** 获取用户数量* 根据用户名和用户角色查询用户数量** @param queryUserName 查询的用户名，如果为null，则不根据用户名进行筛选* @param queryUserRole 查询的用户角色，如果为null，则不根据用户角色进行筛选* @return 返回查询到的用户数量*/@Overridepublic int getUserCount(String queryUserName, Integer queryUserRole) {// 定义数据库连接对象Connection conn = null;// 初始化用户数量为0int userCouts = 0;try {// 获取数据库连接conn = BaseDao.connection();// 调用UserDao的getUserCount方法查询用户数量userCouts = userDao.getUserCount(conn, queryUserName, queryUserRole);} catch (Exception e) {// 打印异常信息e.printStackTrace();} finally {// 关闭数据库资源BaseDao.closeResource(conn, null, null);}// 返回查询到的用户数量return userCouts;}

获取用户列表

根据查询条件查询需要的数据，根据翻页页数计算需要显示的数据是那些条；

UserDao

使用order by和 limit 关键字 应用；

1. userDao

List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception;

2. userDaoImpl

    /*** 分页查询用户集合* @param connection 数据库连接* @param userName 用户名字* @param userRole 角色id* @param currentPageNo 当前页码* @param pageSize 每页数量* @return 用户对象集合* @throws Exception 抛出SQL异常*/@Overridepublic List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception {List<User> users = new ArrayList<>();if (connection == null) {return users;}PreparedStatement preparedStatement = null;ResultSet rs = null;StringBuilder sql = new StringBuilder();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<>();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 ?, ?");// 准备SQL语句执行preparedStatement = connection.prepareStatement(sql.toString());currentPageNo = (currentPageNo - 1) * pageSize;list.add(currentPageNo);list.add(pageSize);Object[] params = list.toArray();rs = BaseDao.executeQuery(connection, sql.toString(),preparedStatement,  params, rs);while (rs.next()) {User user = getUserByResult(rs);user.setUserRoleName(rs.getString("userRoleName"));users.add(user);}BaseDao.closeResource(connection,preparedStatement, rs);return users;}

UserService

1. UserService

    List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo,int pageSize);

2. UserServiceImpl

    /*** @param queryUserName* @param queryUserRole* @param currentPageNo* @param pageSize* @return*/@Overridepublic List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo,int pageSize) {Connection conn = null;List<User> userList = null;try {conn = BaseDao.connection();userList = userDao.getUserList(conn, queryUserName, queryUserRole, currentPageNo,pageSize);} catch (Exception e) {e.printStackTrace();} finally {BaseDao.closeResource(conn, null, null);}return userList;}

获取用户角色 列表

查询数据库中的角色

RoleDao

1. RoleDao

package com.dashangms.dao.role;import com.dashangms.dao.BaseDao;
import com.dashangms.pojo.Role;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;public interface RoleDao {List<Role> getRoleList(Connection connection) throws SQLException;
}

2. RoleDaoImpl

package com.dashangms.dao.role;import com.dashangms.dao.BaseDao;
import com.dashangms.pojo.Role;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;public class RoleDaoImpl implements RoleDao {/*** 获取角色列表** @param connection 数据库连接对象，用于执行SQL查询* @return 返回一个Role对象列表，包含从数据库查询到的所有角色信息* @throws SQLException 如果在执行数据库操作时发生错误*/@Overridepublic List<Role> getRoleList(Connection connection) throws SQLException {// 创建一个ArrayList用于存储Role对象ArrayList<Role> roleList = new ArrayList<Role>();// 定义ResultSet对象用于存储查询结果ResultSet resultSet = null;// 检查传入的数据库连接对象是否不为空if (connection != null) {// 定义SQL查询语句，用于从smbms_role表中查询所有数据String sql = "select * from smbms_role";// 定义一个空的参数数组，用于执行带有参数的SQL语句（此处无参数）Object[] params = {};// 执行SQL查询，并将结果存储在resultSet中resultSet = BaseDao.executeQuery(connection, sql, null, params, resultSet);// 遍历查询结果，将每条记录封装成Role对象while (resultSet.next()) {Role role = new Role();// 从结果集中获取id、roleCode、roleName等字段，并设置到Role对象中role.setId(resultSet.getInt("id"));role.setRoleCode(resultSet.getString("roleCode"));role.setRoleName(resultSet.getString("roleName"));role.setCreateBy(resultSet.getInt("createBy"));role.setCreateDate(resultSet.getTimestamp("createDate"));role.setModifyBy(resultSet.getInt("modifyBy"));role.setModifyDate(resultSet.getTimestamp("modifyDate"));// 将封装好的Role对象添加到列表中roleList.add(role);}// 关闭数据库资源，包括连接、声明和结果集BaseDao.closeResource(connection, null, resultSet);}// 返回角色列表return roleList;}}

RoleService

1. RoleService

package com.dashangms.service.role;import com.dashangms.pojo.Role;import java.util.List;public interface RoleService {List<Role> getRoleList();
}

2. RoleServiceImpl

package com.dashangms.service.role;import com.dashangms.dao.BaseDao;
import com.dashangms.dao.role.RoleDaoImpl;
import com.dashangms.pojo.Role;
import com.dashangms.dao.role.RoleDao;import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;public class RoleServiceImpl implements RoleService{private RoleDao roleDao;public RoleServiceImpl() {roleDao = new RoleDaoImpl();}/***/@Overridepublic List<Role> getRoleList() {Connection connection = null;List<Role> roleList = null;try {connection = BaseDao.connection();roleList = roleDao.getRoleList(connection);}catch (Exception e){e.printStackTrace();}finally {BaseDao.closeConnection(connection);}return roleList;}
}

编写查询用户的Servlet

编写查询的servlet，在画面初次加载或者查询的时候调用该方法，查询数据，显示分页，避免非空场景。

doGet方法

if ("query".equals(method)) {query(req, resp);
}

query方法

    /*** 处理用户查询请求的方法* 该方法根据用户输入的查询条件（用户名和用户角色）以及分页信息（当前页码），* 查询并返回用户列表同时将结果存储在HttpServletRequest对象中，* 以便在JSP页面中显示** @param req 用于获取请求参数和设置属性的HttpServletRequest对象* @param resp 用于响应的HttpServletResponse对象*/private void query(HttpServletRequest req, HttpServletResponse resp) {// 获取查询条件：用户名和用户角色String queryUserName = req.getParameter("queryUserName");String queryUserRoleTemp = req.getParameter("queryUserRole");String pageIndex = req.getParameter("pageIndex");int queryUserRole = 0; // 默认用户角色// 创建UserService实例UserService userService = new UserServiceImpl();List<User> userList;// 默认分页设置int currentPageNo = 1;int pageSize = 5;// 处理查询条件，确保空值安全queryUserName = queryUserName == null ? "" : queryUserName;queryUserRole = queryUserRoleTemp.isEmpty() ? queryUserRole :Integer.parseInt(queryUserRoleTemp);currentPageNo = pageIndex == null ? currentPageNo : Integer.parseInt(pageIndex);// 获取用户总数，用于分页int totalCount = userService.getUserCount(queryUserName, queryUserRole);// 创建并配置PageSupport对象PageSupport pageSupport = new PageSupport();pageSupport.setPageIndex(currentPageNo);pageSupport.setPageSize(pageSize);pageSupport.setTotalCount(totalCount);// 计算总页数int totalPageCount = pageSupport.getTotalPageCount();// 校验当前页码if (currentPageNo < 1) {currentPageNo = 1;} else {if (currentPageNo > totalPageCount) {currentPageNo = totalPageCount;}}// 根据查询条件和分页信息获取用户列表userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);// 将用户列表和其他信息存储在请求对象中req.setAttribute("userList", userList);// 创建RoleService实例并获取角色列表RoleService 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);// 转发到userlist.jsp页面显示结果try {req.getRequestDispatcher("/userlist.jsp").forward(req, resp);} catch (ServletException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}

添加用户子模块

添加用户，查找用户是否存在；需要考虑到添加失败后事务回滚

导入jsp请求按钮

UserDao逻辑

1. UserDao

 //用户管理模块中的 子模块—— 添加用户public int addUser(Connection conn,User user)throws SQLException;

2. UserDaoImpl

 //用户管理模块中的 子模块—— 添加用户public  int addUser(Connection conn,User user)throws SQLException{{PreparedStatement preparedStatement;int updateRows = 0;if(connection != null){String sql = "insert into smbms_user (userCode,userName,userPassword,gender,birthday,phone,address,userRole,createdBy,creationDate)values(?,?,?,?,?,?,?,?,?,?)";Object[] params ={user.getUserRole(),user.getUserName(),user.getUserPassword(),user.getGender(),user.getBirthday(),user.getPhone(),user.getAddress(),user.getUserRole(),user.getCreatedBy(),user.getCreateDate()};preparedStatement = connection.prepareStatement(sql);//执行sql 返回执行结果(成功的语句数量)updateRows= BaseDao.executeUpdate(connection,sql,preparedStatement,params);//释放资源BaseDao.closeResource(null,preparedStatement,null);}return updateRows;}

UserService逻辑

1. UserService

//用户管理模块中的 子模块—— 添加用户boolean  addUser(User user);

2. UserServiceImpl

    /*** @param user* @return*/@Overridepublic boolean addUser(User user) throws SQLException {Connection conn = null;boolean flag = false;try {//获取数据库连接conn = BaseDao.connection();//开启JDBC事务管理conn.setAutoCommit(false);//Service层调用dao层的方法添加用户int updateRows = userDao.add(conn, user);conn.commit();if(updateRows > 0){flag = true;}} catch (Exception e) {e.printStackTrace();if (conn != null) {conn.rollback();}}finally {//释放连接BaseDao.closeResource(conn,null,null);}return flag;}

删除用户子模块

导入jsp请求按钮

UserDao逻辑

1. UserDao

    int deleteUserById(Connection connection, Integer delId) throws Exception;

2. UserDaoImpl

    /*** 根据用户id删除用户** @param connection 数据库连接对象* @param delId      删除的用户id* @return 影响行数* @throws Exception 异常处理*/@Overridepublic int deleteUserById(Connection connection, Integer delId) throws Exception {PreparedStatement preparedStatement = null;int updateRows = 0;if(connection != null){String sql = "delete from smbms_user where id = ?";preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, delId);updateRows = preparedStatement.executeUpdate();}BaseDao.closeResource(connection, preparedStatement, null);return updateRows;}

UserService逻辑

1. UserService

int deleteUser(Integer delId);

2. UserServiceImpl

    /*** @param delId* @return*/@Overridepublic int deleteUser(Integer delId) {Connection conn = null;int delectedRows = 0;try {conn = BaseDao.connection();delectedRows = userDao.deleteUserById(conn, delId);} catch (Exception e) {e.printStackTrace();} finally {BaseDao.closeResource(conn, null, null);}return delectedRows;}

修改用户子模块

导入jsp请求按钮，检查

UserDao逻辑

1. UserDao

    int modify(Connection connection, User user) throws Exception;

2. UserDaoImpl

    /*** 修改用户信息** @param connection 数据库连接对象* @param user       用户对象* @return 影响行数* @throws Exception 异常处理*/@Overridepublic int modify(Connection connection, User user) throws Exception {PreparedStatement preparedStatement = null;int updateRows = 0;if(connection != null){String sql = "update smbms_user " +" set userName = ?, " +" gender = ?, " +" birthday = ?, " +" phone = ?, " +" address = ?, " +" userRole = ?, " +" modifyBy = ?, " +" modifyDate = ? " +"where " +" id = ?";Object[] params = {user.getUserName(),user.getGender(),user.getBirthday(),user.getPhone(),user.getAddress(),user.getUserRole(),user.getModifyBy(),user.getModifyDate(),user.getId()};preparedStatement = connection.prepareStatement(sql);updateRows = BaseDao.executeUpdate(connection, sql, preparedStatement, params);BaseDao.closeResource(connection, preparedStatement, null);}return updateRows;}

UserService逻辑

1. UserService

int modifyUser(User user);

2. UserServiceImp

    /*** @param user* @return*/@Overridepublic int modifyUser(User user) {Connection conn = null;int updateRows = 0;try {conn = BaseDao.connection();updateRows = userDao.modify(conn, user);conn.commit();} catch (Exception e) {e.printStackTrace();} finally {BaseDao.closeResource(conn, null, null);}return updateRows;}

查询用户子模块

导入jsp请求按钮，检查

思路：

• 跟前面查询数据走同一个Dao的数据查询逻辑，在service/servlet层对数据进行区分？
• 通过画面hidden项传递？
• 通过页面id（主键查询）传递？

Servlet

public class UserServlet extends HttpServlet {@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {String method = req.getParameter("method");if ("savePassword".equals(method)) {savePassword(req, resp);} else if ("query".equals(method)) {query(req, resp);} else if ("modify".equals(method)) {modify(req, resp);} else if ("delete".equals(method)) {delete(req, resp);} else if ("add".equals(method)) {try {add(req, resp);} catch (SQLException e) {throw new RuntimeException(e);}} else if ("viewUser".equals(method)) {viewUser(req, resp);}}/*** 该方法用于处理用户信息查看请求* 它从HTTP请求中提取用户ID，调用服务层方法获取用户信息，* 并将用户信息发送到前端页面进行展示** @param req  HTTP请求对象，用于获取请求参数和设置属性* @param resp HTTP响应对象，用于跳转到其他页面* @throws ServletException 如果Servlet操作失败* @throws IOException      如果输入输出操作失败*/private void viewUser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {//从前端获取 要查询用户 的idString id = req.getParameter("id");int userId = 0;try {//将获取到的字符串id转换为整型，以便后续使用userId = Integer.parseInt(id);} catch (Exception e) {//异常处理：打印错误信息，如果转换失败e.printStackTrace();}//调用 根据id查询用户信息的方法UserServiceImpl userService = new UserServiceImpl();User user = userService.getUserById(userId);//将此user发送到展示前端 的页面进行展示req.setAttribute("user", user);//跳转到前端 的展示页面req.getRequestDispatcher("userview.jsp").forward(req, resp);}
}

UserService逻辑 ：getUserById

1. UserService

    User getUserById(int userId);

2. UserServiceImpl

    /*** @param userId* @return*/@Overridepublic User getUserById(int userId) {Connection conn = null;User user = null;try {conn = BaseDao.connection();user = userDao.getUserById(conn, userId);} catch (Exception e) {e.printStackTrace();} finally {BaseDao.closeResource(conn, null, null);}return user;}

UserDao

1. UserDao

/*** 根据用户id获取用户信息** @param connection 数据库连接对象* @param id 用户id* @return 用户对象* @throws Exception 异常处理*/
User getUserById(Connection connection, int id) throws Exception;

2. UserDaoImpl ：主键查询

    /*** 根据用户id获取用户信息** @param connection 数据库连接对象* @param id         用户id 主键* @return 用户对象* @throws Exception 异常处理*/@Overridepublic User getUserById(Connection connection, int id) throws Exception {PreparedStatement  preparedStatement = null;ResultSet rs = null;List<User> users =  new ArrayList<>();User user = new User();if(connection != null){String sql = "select * from smbms_user where id = ?";preparedStatement = connection.prepareStatement(sql);//          Object[] parmes = new Object[1];
//          parmes[0] = Integer.parseInt(id);preparedStatement.setInt(1, id);
//          rs = BaseDao.executeQuery(connection, sql, preparedStatement, parmes, rs);rs = preparedStatement.executeQuery();//          while (rs.next()){user = getUserByResult(rs);
//              users.add(user);
//          }BaseDao.closeResource(connection, preparedStatement, rs);}return user;}