SMBMS

1.项目搭建准备工作

1.1.搭建一个maven web项目

1.2.配置Tomcat

1.3.导包

<dependencies> <!-- https://mvnrepository.com/artifact/javax.servlet.jsp/javax.servlet.jsp-api --> <dependency> <groupId>javax.servlet.jsp</groupId> <artifactId>javax.servlet.jsp-api</artifactId> <version>2.3.3</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>4.0.1</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.21</version> </dependency> <!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-api --> <dependency> <groupId>org.junit.jupiter</groupId> <artifactId>junit-jupiter-api</artifactId> <version>5.7.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.14</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/javax.servlet.jsp.jstl/jstl-api --> <dependency> <groupId>javax.servlet.jsp.jstl</groupId> <artifactId>jstl-api</artifactId> <version>1.2</version> </dependency> <!-- https://mvnrepository.com/artifact/taglibs/standard --> <dependency> <groupId>taglibs</groupId> <artifactId>standard</artifactId> <version>1.1.2</version> </dependency> </dependencies>

1.4.创建项目包结构

1.5.编写实体类

Address.java package com.mashiro.pojo; import lombok.Data; import java.util.Date; @Data public class Address { private Integer id; private String cootact; private String addressDesc; private String postCode; private String tel; private Integer createBy; private Date creationDate; private Integer modifyBy; private Date modifyDate; private Integer userId; } Bill.java package com.mashiro.pojo; import lombok.Data; import java.math.BigDecimal; import java.util.Date; @Data public class Bill { private Integer id; private String billCode; private String productName; private String productDesc; private String productUnit; private String productCount; private BigDecimal totalPrice; private Integer isPayment; private Integer createdBy; private Date creationDate; private Integer modifyBy; private Date modifyDate; private Integer providerId; private String providerName; } Provider.java package com.mashiro.pojo; import lombok.Data; import java.util.Date; @Data public class Provider { private Integer id; private String proCode; private String proName; private String proDesc; private String proContact; private String proPhone; private String proAddress; private String proFax; private Integer createdBy; private Date creationDate; private Date modifyDate; private Integer modifyBy; } Role.java package com.mashiro.pojo; import lombok.Data; import java.util.Date; @Data public class Role { private Integer id; private String roleCode; private String roleName; private Integer createdBy; private Date creationDate; private Integer modifyBy; private Date modifyDate; } User.java package com.mashiro.pojo; import lombok.Data; import java.util.Date; @Data public class User { private Integer id; private String userCode; private String userName; private String userPassword; private Integer gender; private Date birthday; private String phone; private String address; private Integer userRole; private Integer createdBy; private Date creationDate; private Integer modifyBy; private Date modifyDate; private Integer age; private String userRoleName; public Integer getAge(){ return (new Date()).getYear()-birthday.getYear(); } }

1.6.编写基础公共类

dp.properties driver=com.mysql.cj.jdbc.Driver url=url=jdbc:mysql://localhost:3306/smbms?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false name=root password=123456 BaseDao.java package com.mashiro.dao; 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 name; private static String password; static { InputStream resourceAsStream = BaseDao.class.getClassLoader().getResourceAsStream("dp.properties"); Properties properties = new Properties(); try { properties.load(resourceAsStream); } catch (IOException e) { e.printStackTrace(); } driver = properties.getProperty("driver"); url = properties.getProperty("url"); name = properties.getProperty("name"); password = properties.getProperty("password"); } /** * 获取sql连接 * @return */ public static Connection getConnection() { Connection connection = null; try { Class.forName(driver); connection = DriverManager.getConnection(url, name, password); } catch (Exception e) { e.printStackTrace(); } return connection; } /** * 查询公共类 * @param connection * @param sql sql语句 * @param params Object参数 * @param statement * @return 返回查询到的结果集 * @throws SQLException */ public static ResultSet execute(Connection connection,String sql,Object[] params,PreparedStatement statement,ResultSet resultSet) throws SQLException { statement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { statement.setObject(i+1,params[i]); } resultSet = statement.executeQuery(); return resultSet; } /** * 增删改公共类 * @param connection sql连接 * @param sql sql语句 * @param params Object参数 * @param statement * @return 执行成功语句数 * @throws SQLException */ public static int execute(Connection connection,String sql,Object[] params,PreparedStatement statement) throws SQLException { statement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { statement.setObject(i+1,params[i]); } int i = statement.executeUpdate(); return i; } /** * 关闭释放资源 * @param resultSet * @param statement * @param connection * @return */ public static boolean close(ResultSet resultSet,PreparedStatement statement,Connection connection){ boolean flag = true; if (resultSet != null){ try { resultSet.close(); // resultSet关闭成功后， resultSet置为null GC回收 resultSet = null; } catch (SQLException e) { flag = false; e.printStackTrace(); } } if (statement != null){ try { statement.close(); statement = null; } catch (SQLException e) { flag = false; e.printStackTrace(); } } if (connection != null){ try { connection.close(); // resultSet关闭成功后， resultSet置为null GC回收 connection = null; } catch (SQLException e) { flag = false; e.printStackTrace(); } } return flag; } }

1.7.编写中文字符集编码过滤器

CharacterEncodingFilter.java package com.mashiro.filter; import javax.servlet.*; import java.io.IOException; public class CharacterEncodingFilter implements Filter { public void init(FilterConfig filterConfig) throws ServletException { } 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 destroy() { } } web.xml <filter> <filter-name>CharacterEncodingFilter</filter-name> <filter-class>com.mashiro.filter.CharacterEncodingFilter</filter-class> </filter> <filter-mapping> <filter-name>CharacterEncodingFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping>

1.8.导入静态资源

2.实现登录功能

2.1.编写前端页面 && 设置为欢迎页

Login.jsp <%@ 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>用户名：</label> <input type="text" class="input-text" id="userCode" name="userCode" placeholder="请输入用户名" required/> </div> <div class="inputbox"> <label>密码：</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> web.xml <welcome-file-list> <welcome-file>login.jsp</welcome-file> </welcome-file-list>

2.2.编写用户登录的Dao接口

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

2.3.编写Dao接口的实现类

package com.mashiro.dao.user; import com.mashiro.dao.BaseDao; import com.mashiro.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 getUser(Connection connection, String userCode) throws SQLException { PreparedStatement preparedStatement = null; ResultSet resultSet = null; User user = null; String sql = "select * from `smbms_user` where userCode = ?"; Object[] params = {userCode}; resultSet = BaseDao.execute(connection, sql, params, preparedStatement, resultSet); while (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("createBy")); user.setCreationDate(resultSet.getDate("creationDate")); user.setModifyBy(resultSet.getInt("modifyBy")); user.setModifyDate(resultSet.getDate("modifyDate")); } BaseDao.close(resultSet,preparedStatement,null); return user; } }

2.4.编写业务层接口

public interface UserLogin { public User Login(String userCode,String password) throws SQLException; }

2.5.编写业务层接口实现类

package com.mashiro.service; import com.mashiro.dao.BaseDao; import com.mashiro.dao.user.UserDao; import com.mashiro.dao.user.UserDaoImpl; import com.mashiro.pojo.User; import org.junit.jupiter.api.Test; import java.sql.Connection; import java.sql.SQLException; public class UserLoginImpl implements UserLogin { private UserDao userDao = null; public UserLoginImpl(){ userDao = new UserDaoImpl(); } public User Login(String userCode, String password) { Connection connection = null; User user = null; try { connection = BaseDao.getConnection(); user = userDao.getUser(connection, userCode); } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.close(null,null,connection); } return user; } }

2.6.编写控制层代码

package com.mashiro.servlet.user; import com.mashiro.pojo.User; import com.mashiro.service.user.UserService; import com.mashiro.service.user.UserServiceImpl; import com.mashiro.util.Constants; import lombok.SneakyThrows; 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 { @SneakyThrows @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { System.out.println("进入Servlet"); // 注意跟前端参数名对应 String userCode = req.getParameter("userCode"); String userPassword = req.getParameter("userPassword"); UserService userService = new UserServiceImpl(); User user = userService.login(userCode, userPassword); if (user != null){ 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); } }

2.7.注册Servlet

<servlet> <servlet-name>LoginServlet</servlet-name> <servlet-class>com.mashiro.servlet.user.LoginServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>LoginServlet</servlet-name> <!-- 注意跟前端给 action="${pageContext.request.contextPath }/login.do" 的一致 --> <url-pattern>/login.do</url-pattern> </servlet-mapping>

2.8.运行错误处理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NWW9ubIL-1603266788435)(C:\Users\25716\AppData\Roaming\Typora\typora-user-images\image-20201013133614835.png)]

解决方法： 在Tomcat下lib目录中导入mysql-connection-java的包

3.登录功能完善

3.1.注销登录功能

package com.mashiro.servlet.user; import com.mashiro.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; public class LogoutServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { 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); } }

3.2.注册Servlet

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

3.3.权限拦截功能

package com.mashiro.filter; import com.mashiro.pojo.User; import com.mashiro.util.Constants; import javax.servlet.*; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; public class SysFilter implements Filter { public void init(FilterConfig filterConfig) throws ServletException { } 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){ resp.sendRedirect(req.getContextPath()+"/error.jsp"); } else { filterChain.doFilter(req,resp); } } public void destroy() { } }

3.4.注册拦截器

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

3.5.在service中添加密码判断，修改后代码如下

package com.mashiro.service.user; import com.mashiro.dao.BaseDao; import com.mashiro.dao.user.UserDao; import com.mashiro.dao.user.UserDaoImpl; import com.mashiro.pojo.User; import java.sql.Connection; import java.sql.SQLException; public class UserServiceImpl implements UserService { private UserDao userDao; public UserServiceImpl(){ userDao = new UserDaoImpl(); } public User login(String userCode, String password) { Connection connection = null; User user = null; try { connection = BaseDao.getConnection(); user = userDao.getUser(connection, userCode); } catch (Exception e) { e.printStackTrace(); } finally { BaseDao.close(null,null,connection); } // 更新的代码如下 if (user != null && user.getUserPassword().equals(password)){ return user; } else { return null; } } }

4.用户密码修改

4.1.UserDao层增加密码修改接口

http://localhost:8080/smbms/login.jsp

4.2. Dao层实现类

// 修改用户密码 public int updatePassword(Connection connection, int id, String password) throws SQLException { System.out.println("进入了updatePassword"); String sql = "update `smbms_user` set `userPassword` = ? where `id` = ?"; PreparedStatement prepareStatement = null; int execute = 0; Object[] params= {password,id}; if (connection != null){ execute = BaseDao.execute(connection, prepareStatement, sql, params); } BaseDao.close(null,prepareStatement,null); return execute; }

4.3.业务层接口

// 修改用户密码 public boolean updatePassword(int id,String password);

4.4.业务层实现类

// 通过id来修改新密码 public boolean updatePassword(int id,String password){ System.out.println("进入了UserServiceImpl updatePassword"); Connection connection = null; boolean flag = false; try { connection = BaseDao.getConnection(); if(userDao.updatePassword(connection, id, password) > 0){ flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.close(null,null,connection); } return flag; }

4.5.Servlet层

package com.mashiro.servlet.user; import com.mashiro.pojo.User; import com.mashiro.service.user.UserService; import com.mashiro.service.user.UserServiceImpl; import com.mashiro.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 UpdatePasswordServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String oldpassword = req.getParameter("oldpassword"); String newpassword = req.getParameter("newpassword"); Object o = req.getSession().getAttribute(Constants.USER_SESSION); boolean flag = false; if ( o != null && !StringUtils.isNullOrEmpty(newpassword)) { UserService userService = new UserServiceImpl(); flag = userService.updatePassword(((User)o).getId(),newpassword); if (flag){ req.setAttribute("message","修改密码成功，请退出后 用新密码登录。"); req.getSession().removeAttribute(Constants.USER_SESSION); } else { req.setAttribute("message","修改密码失败。"); } } else { req.setAttribute("message","新密码不合法"); } req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req,resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doGet(req, resp); } }

4.6.注册servlet

<servlet> <servlet-name>UpdatePasswordServlet</servlet-name> <servlet-class>com.mashiro.servlet.user.UpdatePasswordServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>UpdatePasswordServlet</servlet-name> <url-pattern>/jsp/user.do</url-pattern> </servlet-mapping>

4.7.代码优化，提高代码复用

package com.mashiro.servlet.user; import com.mashiro.pojo.User; import com.mashiro.service.user.UserService; import com.mashiro.service.user.UserServiceImpl; import com.mashiro.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 { // <input type="hidden" name="method" value="savepwd"> String method = req.getParameter("method"); if (method != null && method.equals("savapwd")){ this.updatePassword(req,resp); } } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doGet(req, resp); } public void updatePassword(HttpServletRequest req, HttpServletResponse resp) { String oldpassword = req.getParameter("oldpassword"); String newpassword = req.getParameter("newpassword"); Object o = req.getSession().getAttribute(Constants.USER_SESSION); boolean flag = false; if ( o != null && !StringUtils.isNullOrEmpty(newpassword)) { UserService userService = new UserServiceImpl(); flag = userService.updatePassword(((User)o).getId(),newpassword); if (flag){ req.setAttribute("message","修改密码成功，请退出后 用新密码登录。"); req.getSession().removeAttribute(Constants.USER_SESSION); } else { req.setAttribute("message","修改密码失败。"); } } else { req.setAttribute("message","新密码不合法"); } req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req,resp); } }

5.密码修改优化

5.1.ajax局部动态更新

oldpassword.on("blur",function(){ $.ajax({ type:"GET", url:path+"/jsp/user.do", data:{method:"pwdmodify",oldpassword:oldpassword.val()}, dataType:"json", success:function(data){ if(data.result == "true"){//旧密码正确 validateTip(oldpassword.next(),{"color":"green"},imgYes,true); }else if(data.result == "false"){//旧密码输入不正确 validateTip(oldpassword.next(),{"color":"red"},imgNo + " 原密码输入不正确",false); }else if(data.result == "sessionerror"){//当前用户session过期，请重新登录 validateTip(oldpassword.next(),{"color":"red"},imgNo + " 当前用户session过期，请重新登录",false); }else if(data.result == "error"){//旧密码输入为空 validateTip(oldpassword.next(),{"color":"red"},imgNo + " 请输入旧密码",false); } }, error:function(data){ //请求出错 validateTip(oldpassword.next(),{"color":"red"},imgNo + " 请求错误",false); } });

5.2.UserServlet代码优化

@Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) { String method = req.getParameter("method"); System.out.println(method); if (method != null && method.equals("savepwd")){ this.updatePassword(req,resp); } else if (method != null && method.equals("pwdmodify")) { this.verOldpassword(req,resp); } }

5.3.verOldpassword 密码验证方法编写

// 验证旧密码 public void verOldpassword(HttpServletRequest req, HttpServletResponse resp){ Object o = req.getSession().getAttribute(Constants.USER_SESSION); String oldpassword = req.getParameter("oldpassword"); Map<String, String> map = new HashMap<String, String>(); if (o == null){ // Session失效或过期 System.out.println("Session失效或过期"); map.put("result","sessionerror"); } else if (StringUtils.isNullOrEmpty(oldpassword)){ // 旧密码为空 System.out.println("旧密码为空"); map.put("result","error"); } else { String userPassword = ((User) o).getUserPassword(); if (oldpassword.equals(userPassword)) { System.out.println("旧密码验证成功"); map.put("result", "true"); } else { System.out.println("旧密码不正确"); map.put("result", "false"); } } try { // 设置类型为JSON类型 resp.setContentType("application/json"); PrintWriter writer = resp.getWriter(); // JSONArray.toJSONString(map) 将map类型改为JSONString类型 writer.write(JSONArray.toJSONString(map)); writer.flush(); writer.close(); } catch (IOException e) { e.printStackTrace(); } }

5.4.注意事项

导入fastjson的包（alibaba） <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.74</version> </dependency> Tomcat中也要导入fastjson的包，否则会报500错误

6.用户管理功能实现

6.1.获取用户数量

6.1.1.UserDao

// 查询用户数量 public int getUserCount(Connection connection,String userName,int userRole) throws SQLException;

6.1.2.UserDaoImpl

// 获取用户数量 public int getUserCount(Connection connection, String userName, int userRole) throws SQLException { PreparedStatement prepareStatement = null; ResultSet resultSet = null; ArrayList<Object> list = new ArrayList<Object>(); StringBuffer sb = new StringBuffer(); int count = 0; if (connection != null){ sb.append("select COUNT(1) as count from `smbms_user` `u`, `smbms_role` `r` where `u`.userRole = `r`.id "); if ( !StringUtils.isNullOrEmpty(userName) ){ sb.append(" and `u`.userName like ?"); list.add("'%"+userName+"%'"); } if (userRole > 0){ sb.append(" and `r`.id = ?"); list.add(userRole); } String sql = sb.toString(); Object[] objects = list.toArray(); System.out.println("UserDaoImpl-->getUserCount()-->sql:\n"+sql); resultSet = BaseDao.execute(connection, prepareStatement,resultSet , sql, objects); if (resultSet.next()){ count = resultSet.getInt("count"); } BaseDao.close(resultSet,prepareStatement,null); } return count; }

6.1.3.UserService

// 查询用户数量 public int getUserCount(String name,int userRole);

6.1.4.UserServiceImpl

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

6.2.获取用户信息列表

6.2.1.UserDao

// 查询用户信息+分页 public List<User> getUserLimit(Connection connection, String userName, int userRole, int startIndex, int pageSize) throws SQLException;

6.2.2.UserDaoImpl

// 查询用户信息+分页 public List<User> getUserLimit(Connection connection, String userName, int userRole, int startIndex, int pageSize) throws SQLException { PreparedStatement preparedStatement = null; ResultSet resultSet = null; List<User> userList = new ArrayList<User>(); ArrayList<Object> list = new ArrayList<Object>(); if (connection != null){ StringBuffer sb = new StringBuffer(); sb.append("select * from smbms_user, smbms_role where smbms_user.userRole = smbms_role.id"); if ( !StringUtils.isNullOrEmpty(userName) ){ sb.append(" and userName like ?"); list.add(userName); } if (userRole > 0){ sb.append(" and userRole = ?"); list.add(userRole); } // 实现id升序排序分页 sb.append(" order by smbms_user.id ASC limit ?,?"); list.add(startIndex); list.add(pageSize); // 执行sql 获取resultSet System.out.println(sb.toString()); resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sb.toString(), list.toArray()); // 取出resultSet中的数据 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("roleName")); userList.add(_user); } } return userList; }

6.2.3.UserService

// 查询用户信息+分页 public List<User> getUserLimit(String userName, int userRole, int startIndex, int pageSize);

6.2.4.UserServiceImpl

// 查询用户数量 public int getUserCount(String name, int userRole) { Connection connection = null; int userCount = 0; try { connection = BaseDao.getConnection(); userCount = userDao.getUserCount(connection, name, userRole); } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.close(null,null,connection); } return userCount; }

6.3.获取角色信息列表

6.3.1.UserDao

// 查询角色信息 public List<Role> getRoleList(Connection connection) throws SQLException;

6.3.2.UserDaoImpl

// 查询角色信息 public List<Role> getRoleList(Connection connection) throws SQLException { PreparedStatement preparedStatement = null; ResultSet resultSet = null; List<Role> list = new ArrayList<Role>(); if (connection != null) { String sql = "select * from `smbms_role`"; Object[] params = {}; resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params); while (resultSet.next()){ Role _role = new Role(); _role.setId(resultSet.getInt("id")); _role.setRoleName(resultSet.getString("roleName")); _role.setRoleCode(resultSet.getString("roleCode")); list.add(_role); } BaseDao.close(resultSet,preparedStatement,null); } return list; }

6.3.3.UserService

// 查询角色信息 public List<Role> getRoleList();

6.3.4.UserServiceImpl

// 查询角色信息 public List<Role> getRoleList() { Connection connection = null; List<Role> list = null; try { connection = BaseDao.getConnection(); list = userDao.getRoleList(connection); } catch (SQLException e) { e.printStackTrace(); } finally { BaseDao.close(null,null,connection); } return list; }

6.4.UserServler

// 查询用户列表 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; // 从后端获取数据 UserService userService = new UserServiceImpl(); RoleService roleService = new RoleServiceImpl(); // 设置配置 int pageSize = 5; int currentPageNo = 1; // 验证前端数据 if (queryUserName == null){ queryUserName = ""; } // 前端获取跳转角色不为空，则跳转到该角色查询 if (temp != null && !temp.equals("")){ queryUserRole = Integer.parseInt(temp); } if (pageIndex != null) { currentPageNo = Integer.parseInt(pageIndex); } // 获取用户总数 int totalCount = userService.getUserCount(queryUserName, queryUserRole); // 调用页面工具类 PageSupport 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; } // 获取用户列表 List<User> userList = userService.getUserLimit(queryUserName, queryUserRole, (currentPageNo-1)*pageSize, pageSize); req.setAttribute("userList",userList); // 获取角色列表 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(); } }