本文主要是介绍狂神 原生JavaWeb 三层架构 JDBC (超市订单管理系统smbms)---【优化版】,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
- 本项目中供应商、订单模块没有实现,实现步骤与用户管理类似!
- 在JDBC、结果集、分页工具及部分功能的实现逻辑等方面做了优化
- 实测可以正常运行!
- 后面有源码、文档、资源下载链接!
- 如遇问题请联系!
1、项目分析
1.1、功能分析
1.2、数据库
2、环境搭建
1)搭建 maven web 项目
更新web.xml为新版
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"version="4.0"metadata-complete="true"></web-app>
2)配置Tomcat
3)导jar包
<dependencies><!-- junit--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version><scope>test</scope></dependency><!-- servlet--><dependency><groupId>javax.servlet</groupId><artifactId>servlet-api</artifactId><version>2.5</version></dependency><!-- jsp--><dependency><groupId>javax.servlet.jsp</groupId><artifactId>jsp-api</artifactId><version>2.2</version></dependency><!-- JSTL表达式的依赖 --><dependency><groupId>javax.servlet.jsp.jstl</groupId><artifactId>jstl-api</artifactId><version>1.2</version></dependency><!-- standard标签库 --><dependency><groupId>taglibs</groupId><artifactId>standard</artifactId><version>1.1.2</version></dependency><!-- mysql驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.25</version></dependency>
</dependencies>
4)构建包结构
5)测试环境
3、实体、基础类
1)编写实体类
public class User {private Integer id; //idprivate 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 LocalDateTime creationDate; //创建时间private Integer modifyBy; //更新者private LocalDateTime modifyDate; //更新时间@SuppressWarnings("unused")private Integer age;//年龄private String userRoleName; //用户角色名称
}
出生日期为:Date型
时间为:LocalDateTime型
2)编写基础公共类
(1)db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useSSL=true&useUnicode=true&characterEncoding=utf8
username=root
ps=123456
(2)数据库操作基础类
BaseDao.java
- 定义变量及读取资源文件
package com.tuwer.dao;import com.sun.rowset.CachedRowSetImpl;import javax.sql.RowSet;
import java.io.*;
import java.sql.*;
import java.util.Properties;/*** @author 土味儿* Date 2021/12/15* @version 1.0* 数据库公共类* 静态方法*/
public class BaseDao {private static String driver;private static String url;private static String username;private static String ps;// 静态代码块:读取资源文件(类加载时执行一次)static {loadResources3();}/*** 方式一* FileInputStream 读取资源文件*/private static void loadResources1() {// ---得到资源文件流---Properties properties = new Properties();FileInputStream fis = null;try {String configFile = ".\\target\\classes\\db.properties";fis = new FileInputStream(configFile);properties.load(fis);driver = properties.getProperty("driver");url = properties.getProperty("url");username = properties.getProperty("username");ps = properties.getProperty("ps");} catch (IOException e) {e.printStackTrace();} finally {try {fis.close();} catch (IOException e) {e.printStackTrace();}}}/*** 方式二* FileReader 读取资源文件*/private static void loadResources2() {// ---得到资源文件流---Properties properties = new Properties();FileReader reader = null;try {String configFile = ".\\target\\classes\\db.properties";reader = new FileReader(configFile);properties.load(reader);driver = properties.getProperty("driver");url = properties.getProperty("url");username = properties.getProperty("username");ps = properties.getProperty("ps");} catch (IOException e) {e.printStackTrace();} finally {try {reader.close();} catch (IOException e) {e.printStackTrace();}}}/*** 方式三* 类加载器读取资源文件* BaseDao.class.getClassLoader().getResourceAsStream(configFile);*/private static void loadResources3() {// ---得到资源文件流---Properties properties = new Properties();InputStream is = null;try {// 方式三String configFile = "db.properties";is = BaseDao.class.getClassLoader().getResourceAsStream(configFile);properties.load(is);driver = properties.getProperty("driver");url = properties.getProperty("url");username = properties.getProperty("username");ps = properties.getProperty("ps");} catch (IOException e) {e.printStackTrace();} finally {try {is.close();} catch (IOException e) {e.printStackTrace();}}}
}
- 获取数据库连接
/*** 获取数据库连接** @return*/public static Connection getConnection() {Connection connection = null;try {Class.forName(driver);connection = DriverManager.getConnection(url, username, ps);} catch (Exception e) {e.printStackTrace();}return connection;}
- 查询方法
1、查询结束后关闭 PreparedStatement 和 ResultSet
2、Connection 放在业务层关闭,方便事务操作
3、返回 RowSet 行集,不是 ResultSet 结查集。ResultSet 和 PreparedStatement 有关联关系,关闭 PreparedStatement 后,在下次执行 ResultSet 时,也会关闭;RowSet 没有关联关系;结果转为 RowSet 后就可以及时关闭 ResultSet 和 PreparedStatement;通过 CachedRowSetImpl 类可以把 ResultSet 转为 RowSet
/*** 查询* 查询结束后关闭PreparedStatement和ResultSet* Connection放在业务层关闭,方便事务操作** @param connection 连接* @param sql 查询语句(可以有占位符)* @param params 参数数组|List* @return* @throws SQLException*/public static RowSet executeQuery(Connection connection,String sql,List params) throws SQLException {PreparedStatement ps = connection.prepareStatement(sql);if (params != null) {// 为占位符赋值for (int i = 0; i < params.size(); i++) {ps.setObject(i + 1, params.get(i));}}// 查询ResultSet rs = ps.executeQuery();// 把ResultSet结果集转为RowSet行集// 联表查询时,行集不支持别名,行集中的列名仍然为表中的列名,不是别名// 用 rs.getMetaData().getColumnName(列位置) 查列名,列位置从1开始CachedRowSetImpl crs = new CachedRowSetImpl();crs.populate(rs);if(params!=null){params.clear();}// 关闭PreparedStatement和ResultSet// Connection放在业务层关闭,方便事务操作close(null, ps, rs);return crs;}
- 更新方法
/*** 更新** @param connection 连接* @param sql 查询语句(可以有占位符)* @param params 参数数组|List* @return* @throws SQLException*/public static int executeUpdate(Connection connection,String sql,List params) throws SQLException {PreparedStatement ps = connection.prepareStatement(sql);if (params != null) {// 为占位符赋值for (int i = 0; i < params.size(); i++) {ps.setObject(i + 1, params.get(i));}}// 更新int i = ps.executeUpdate();if(params!=null){params.clear();}// 关闭PreparedStatementclose(null, ps, null);return i;}
- 关闭
/*** 关闭/释放资源* @param connection* @param ps* @param rs* @return*/public static boolean close(Connection connection,PreparedStatement ps,ResultSet rs) {boolean flag = true;if (rs != null) {try {rs.close();// 方便GC回收rs = null;System.out.println("ResultSet关闭");} catch (SQLException e) {e.printStackTrace();flag = false;}}if (ps != null) {try {ps.close();ps = null;System.out.println("PreparedStatement关闭");} catch (SQLException e) {e.printStackTrace();flag = false;}}if (connection != null) {try {connection.close();connection = null;System.out.println("Connection关闭");} catch (SQLException e) {e.printStackTrace();flag = false;}}return flag;}
(3)字符乱码过滤器
public class CharacterFilter implements Filter {@Overridepublic void init(FilterConfig filterConfig) throws ServletException {}@Overridepublic void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {request.setCharacterEncoding("utf8");response.setCharacterEncoding("utf8");chain.doFilter(request,response);}@Overridepublic void destroy() {}
}
- web.xml配置
<filter><filter-name>characterFilter</filter-name><filter-class>com.tuwer.filter.CharacterFilter</filter-class>
</filter>
<filter-mapping><filter-name>characterFilter</filter-name><url-pattern>/*</url-pattern>
</filter-mapping>
(4)导入静态资源
(5)分页工具类
- PageUtil.java
package com.tuwer.util;import java.util.regex.Pattern;import static java.util.regex.Pattern.*;/*** @author 土味儿* Date 2021/12/19* @version 1.0* 分页工具*/
public class PageUtil {/*** 得到总页数** @param rows 总数据量* @param pageSize 每页显示数量* @return 总页数*/public static int getPages(int rows, int pageSize) {if (rows < 1) {return 0;}if (pageSize < 1) {pageSize = 1;}if (pageSize > rows) {pageSize = rows;}return (int) Math.ceil(((double) rows) / pageSize);}/*** 得到页面数据量** @param pageSizeStr 页面数据量字符串* @param max 最大页面数据量* @return 返回Int值,介于 1 和 最大值 之间*/public static int getPageSize(String pageSizeStr, int max) {return strToInt_123(pageSizeStr, max, 1);}/*** 得到当前页码** @param currentPageStr 当前页码字符串* @param max 最大页码* @return 返回Int值,介于 1 和 最大值 之间*/public static int getCurrentPage(String currentPageStr, int max) {return strToInt_123(currentPageStr, max, 1);}/*** 得到当前页的数据起始索引** @param currentPage 当前页码* @param pageSize 当前数据量* @return*/public static int getStartIndex(int currentPage, int pageSize) {if (currentPage < 1) {currentPage = 1;}if (pageSize < 1) {pageSize = 1;}return (currentPage - 1) * pageSize + 1;}/*** 字符串转正整数** @param str 目标字符串* @param max 最大值* @param def 默认值* @return 返回Int值,介于 默认值 和 最大值 之间*/private static int strToInt_123(String str, int max, int def) {// 判断max和def大小if (max < 1) {max = 1;}if (def < 1) {def = 1;}if (def > max) {def = max;}// 把默认值赋于返回值int num = def;if (str == null || str.length() < 1) {return num;}if (str.length() > 8) {str = str.substring(0, 8);}// 检测字符串是否是正整数组成Pattern pattern = compile("^[1-9][0-9]*$");boolean isNumber = pattern.matcher(str).matches();// 不是正整数组成if (!isNumber) {return num;}// 是正整数组成int strNum = Integer.parseInt(str);if (strNum > max) {num = max;} else if (strNum < def) {//num = def;} else {num = strNum;}return num;}/*** 字符串转非负整数** @param str 目标字符串* @return 返回Int值,大于等于0*/public static int strToInt_0123(String str) {if (str == null || str.length() < 1) {return 0;}if (str.length() > 8) {str = str.substring(0, 8);}// 检测字符串是否是正整数组成Pattern pattern = compile("^[1-9][0-9]*$");boolean isNumber = pattern.matcher(str).matches();// 不是正整数组成if (!isNumber) {return 0;}// 是正整数组成:解析并返回return Integer.parseInt(str);}
}
4、登录注销改密码
1)登录
(1)设置login.jsp为欢迎页面
<welcome-file-list><welcome-file>login.jsp</welcome-file>
</welcome-file-list>
(2)Dao层
操作数据库
不需要做关闭操作
- UserDao.java 接口
package com.tuwer.dao.user;import com.tuwer.pojo.User;import java.sql.Connection;
import java.sql.SQLException;/*** @author 土味儿* Date 2021/12/17* @version 1.0*/
public interface UserDao {/*** 根据用户编码获取用户* @param connection 连接* @param userCode 用户代码* @return* @throws SQLException*/User getLoginUser(Connection connection,String userCode);
}
- UserDaoImpl.java 实现类
package com.tuwer.dao.user;import com.tuwer.dao.BaseDao;
import com.tuwer.pojo.User;import javax.sql.RowSet;
import java.sql.Connection;
import java.sql.SQLException;/*** @author 土味儿* Date 2021/12/17* @version 1.0*/
public class UserDaoImpl implements UserDao{@Overridepublic User getLoginUser(Connection connection,String userCode){if(connection == null){return null;}String sql = "select * from smbms_user where userCode = ?";List<Object> params = new ArrayList<>();params.add(userCode); User user = null;try {RowSet rs = BaseDao.executeQuery(connection,sql, params);if(rs.next()){user = new User();user.setId(rs.getInt("id"));user.setUserCode(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"));}} catch (SQLException e) {e.printStackTrace();}finally {return user;}}
}
(3)Service业务层
只负责业务逻辑
- UserService.java 接口
public interface UserService {/*** 用户登录* @param userCode 用户编码* @param userPassword 用户密码* @return*/User login(String userCode, String userPassword);
}
- UserServiceImpl.java 实现类
public class UserServiceImpl implements UserService{private UserDao userDao;public UserServiceImpl() {this.userDao = new UserDaoImpl();}@Overridepublic User login(String userCode, String userPassword) {Connection connection = BaseDao.getConnection();User user = userDao.getLoginUser(connection, userCode);String ps = user.getUserPassword();if (!userPassword.equals(ps)) {user = null;}// 关闭连接BaseDao.close(connection, null, null);return user;}
}
(4)Servlet控制层
1、接收客户端的请求参数
2、调用业务层方法
3、重定向或请求转发
- LoginServlet.java
public class LoginServlet extends HttpServlet {@Overrideprotected 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.login(userCode, userPassword);// 登录成功if(user != null){// 保存用户信息到sessionreq.getSession().setAttribute(Constants.USER_SESSION,user);// 跳转到后台主页resp.sendRedirect("jsp/frame.jsp");}// 登录失败else{// 请求转发到login.jsp,并携带错误信息req.setAttribute("error","用户名或密码错误!");req.getRequestDispatcher("login.jsp").forward(req,resp);}}@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {doGet(req, resp);}
}
- Constants.java 常量类
项目中需要的常量集中在一起,方便管理
public class Constants {public final static String USER_SESSION = "userSession";
}
- web.xml 中配置 Servlet
<servlet><servlet-name>loginServlet</servlet-name><servlet-class>com.tuwer.servlet.user.LoginServlet</servlet-class></servlet><servlet-mapping><servlet-name>loginServlet</servlet-name><url-pattern>/login.do</url-pattern></servlet-mapping>
- 展示
2)注销
移除Session,返回登录页
- Servlet
req.getContextPath()
项目根目录
public class LogoutServlet extends HttpServlet {@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {req.getSession().removeAttribute(Constants.USER_SESSION);resp.sendRedirect(req.getContextPath() + "/login.jsp");}@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {doGet(req, resp);}
}
- web.xml 中配置 Servlet
<!--注销--><servlet><servlet-name>logoutServlet</servlet-name><servlet-class>com.tuwer.servlet.user.LogoutServlet</servlet-class></servlet><servlet-mapping><servlet-name>logoutServlet</servlet-name><url-pattern>/jsp/logout.do</url-pattern></servlet-mapping>
3)权限拦截
未登录用户不允计访问后台
- 过滤器
public class LoginFilter implements Filter {@Overridepublic void init(FilterConfig filterConfig) throws ServletException {}@Overridepublic void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException {HttpServletRequest request = (HttpServletRequest) req;HttpServletResponse response = (HttpServletResponse) resp;Object user = request.getSession().getAttribute(Constants.USER_SESSION);// 未登录if (user == null) {response.sendRedirect(request.getContextPath() + "/error.jsp");}// 执行下一链chain.doFilter(request, response);}@Overridepublic void destroy() {}
}
4)修改密码
(1)Dao层
public interface UserDao {/*** 修改密码* @param connection 连接* @param id 用户id* @param newPassword 新密码* @return 成功修改的记录数*/int modifyPwd(Connection connection, int id, String newPassword);
}
public class UserDaoImpl implements UserDao{ @Overridepublic int modifyPwd(Connection connection, int id, String newPassword){if(connection == null){return 0;}String sql = "update smbms_user set userPassword = ? where id = ?";//Object[] params = {newPassword,id};List<Object> params = new ArrayList<>();params.add(newPassword);params.add(id);int i = 0;try {i = BaseDao.executeUpdate(connection, sql, params);} catch (SQLException e) {e.printStackTrace();} finally {return i;}}
}
(2)Service层
public interface UserService {/*** 修改密码* @param id 当前用户id* @param newPassword 新密码* @return*/boolean modifyPwd(int id, String newPassword);
}
public class UserServiceImpl implements UserService {@Overridepublic boolean modifyPwd(int id, String newPassword) {Connection connection = BaseDao.getConnection();int i = userDao.modifyPwd(connection, id, newPassword);// 关闭连接BaseDao.close(connection, null, null);if (i > 0) {return true;}return false;}
}
(3)Servlet层
类的复用
public class UserServlet extends HttpServlet {@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String method = req.getParameter("method");if(StringUtils.isNullOrEmpty(method)){return;}// 修改密码if (Objects.equals("modifypwd", method)) {modifyPwd(req, resp);return;}// 检验旧密码if (Objects.equals("checkpwd", method)) {checkPwd(req, resp);return;}}@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {doGet(req, resp);}/*** 修改密码* @param req* @param resp* @throws ServletException* @throws IOException*/private void modifyPwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// ---从Session中获取用户---User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);// Session失效if(user==null){req.setAttribute(Constants.MESSAGE,"请重新登录!");req.getRequestDispatcher("/login.jsp").forward(req,resp);return;}// ---得到用户输入的旧密码---String oldpassword = req.getParameter("oldpassword");if(StringUtils.isNullOrEmpty(oldpassword) || !Objects.equals(user.getUserPassword(),oldpassword)){// 旧密码不合法:空 | null | 新旧不一致// 请求转发至修改密码页面,返回req.setAttribute(Constants.MESSAGE,"旧密码错误!");req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req,resp);return;}// ---得到用户输入的新密码---String newpassword = req.getParameter("newpassword");// 修改密码UserServiceImpl userService = new UserServiceImpl();boolean b = userService.modifyPwd(user.getId(), newpassword);// 修改成功:移除Session中的User,重定向至登录页,给出提示信息,退出if(b){req.getSession().removeAttribute(Constants.USER_SESSION);req.setAttribute(Constants.MESSAGE,"密码修改成功!请重新登录!");req.getRequestDispatcher("/login.jsp").forward(req,resp);return;}// 修改失败:请求转发至修改密码页面,给出提示信息req.setAttribute(Constants.MESSAGE,"密码修改失败!");req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req,resp);}
}
- web.xml 中配置 Servlet
<servlet><servlet-name>userServlet</servlet-name><servlet-class>com.tuwer.servlet.user.UserServlet</servlet-class></servlet><servlet-mapping><servlet-name>userServlet</servlet-name><url-pattern>/jsp/user.do</url-pattern></servlet-mapping>
(4)优化:Ajax检验旧密码
- js片段
$.ajax({type:"GET",url:path+"/jsp/user.do",data:{method:"checkpwd",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);}});
- Servlet
private void checkPwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// 结果集Map<String, String> resultMap = new HashMap<>(1);// ---从Session中获取用户---User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);// Session失效if (user == null) {resultMap.put("result", "sessionerror");} else {// ---得到用户输入的旧密码---String oldpassword = req.getParameter("oldpassword");if (StringUtils.isNullOrEmpty(oldpassword)) {// 旧密码不合法:空 | nullresultMap.put("result", "error");} else if (Objects.equals(user.getUserPassword(), oldpassword)) {// 密码新旧一致resultMap.put("result", "true");} else {resultMap.put("result", "false");}}// ---返回json数据---resp.setContentType("application/json");PrintWriter writer = resp.getWriter();// 自定义JSON {"result":"true"}//writer.print("{\"result\":\"" + resultMap.get("result") + "\"}");// 调用阿里巴巴JSONwriter.print(JSONArray.toJSONString(resultMap));writer.close();}
<!--阿里巴巴JSON依赖--><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.78</version></dependency>
- web.xml中不用再注册(复用)
5、用户管理
1)查看用户列表
分页查询用户信息
- 需要查用户信息 / 需要统计用户总数 / 需要查角色表
- 支持名称模糊查询
- 支持按角色查询
(1)Dao层
- 接口方法
UserDao.java
/*** 得到用户总数* @param connection 连接* @param userName 用户名(支持模糊查询)* @param userRole 用户角色* @return*/int getUserCount(Connection connection,String userName,int userRole);/*** 得到用户列表** @param connection 连接* @param userName 用户名(支持模糊查询)* @param userRole 用户角色* @param startIndex 当前页数据开始索引:从1开始,1.2.3...* @param pageSize 页面数量* @return*/List<User> getUserList(Connection connection,String userName,int userRole,int startIndex,int pageSize);
角色接口
public interface RoleDao {/*** 得到角色列表** @param connection* @return*/List<Role> getRoleList(Connection connection);
}
- 实现类
UserDaoImpl.java
@Overridepublic int getUserCount(Connection connection, String userName, int userRole) {if (connection == null) {return 0;}//String sql = "select count(1) from smbms_user u,smbms_role r where u.userRole = ? and u.userName like ? and u.userRole = r.id";// 只要数量,不需要查角色表StringBuffer sql = new StringBuffer();sql.append("select count(1) as count from smbms_user u");// 参数List<Object> params = new ArrayList<>();// ---拼接sql及参数列表---// 判断是否有用户名boolean noUserName = StringUtils.isNullOrEmpty(userName);// 判断是否有用色boolean noUserRole = userRole < 1;// 没有角色if (noUserRole) {// 有用户名if (!noUserName) {sql.append(" where u.userName like ?");params.add("%" + userName + "%");}}// 有角色else {sql.append(" where u.userRole = ?");params.add(userRole);// 有用户名if (!noUserName) {sql.append(" and u.userName like ?");params.add("%" + userName + "%");}}int count = 0;try {RowSet rowSet = BaseDao.executeQuery(connection, sql.toString(), params);if (rowSet.next()) {count = rowSet.getInt("count");}} catch (SQLException e) {e.printStackTrace();}return count;}@Overridepublic List<User> getUserList(Connection connection,String userName,int userRole,int startIndex,int pageSize) {if (connection == null) {return null;}StringBuffer sql = new StringBuffer();sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where");// 参数List<Object> params = new ArrayList<>();// ---拼接sql及参数列表---// 判断是否有用户名boolean noUserName = StringUtils.isNullOrEmpty(userName);// 判断是否有用色boolean noUserRole = userRole < 1;// 没有角色if (noUserRole) {// 有用户名if (!noUserName) {sql.append(" u.userName like ? and");params.add("%" + userName + "%");}sql.append(" u.userRole = r.id");}// 有角色else {sql.append(" u.userRole = ? and");params.add(userRole);// 有用户名if (!noUserName) {sql.append(" u.userName like ? and");params.add("%" + userName + "%");}sql.append(" u.userRole = r.id");}sql.append(" order by creationDate desc limit ?,?");params.add(startIndex - 1);params.add(pageSize);List<User> userList = new ArrayList<>();try {RowSet rs = BaseDao.executeQuery(connection, sql.toString(), params);while (rs.next()) {User user = new User();user.setId(rs.getInt("id"));user.setUserCode(rs.getString("userCode"));user.setUserName(rs.getString("userName"));user.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((LocalDateTime) rs.getObject("creationDate"));user.setModifyBy(rs.getInt("modifyBy"));user.setModifyDate((LocalDateTime) rs.getObject("modifyDate"));user.setUserRoleName(rs.getString("roleName"));//user.setUserRoleName(rs.getString(rs.getMetaData().getColumnName(14)));userList.add(user);}} catch (SQLException e) {e.printStackTrace();} finally {return userList;}}
RoleDaoImpl.java
public class RoleDaoImpl implements RoleDao{@Overridepublic List<Role> getRoleList(Connection connection) {if(connection == null){return null;}String sql = "select * from smbms_role order by creationDate desc";List<Role> roleList = new ArrayList<>();try {RowSet rs = BaseDao.executeQuery(connection, sql, null);while (rs.next()){Role r = new Role();r.setId(rs.getInt("id"));r.setRoleCode(rs.getString("roleCode"));r.setRoleName(rs.getString("roleName"));r.setCreatedBy(rs.getInt("createdBy"));r.setCreatedBy(rs.getInt("createdBy"));r.setCreationDate((LocalDateTime) rs.getObject("creationDate"));r.setModifyBy(rs.getInt("modifyBy"));r.setModifyDate((LocalDateTime) rs.getObject("modifyDate"));roleList.add(r);}} catch (SQLException e) {e.printStackTrace();}return roleList;}
}
(2)Service层
- 接口方法
UserService.java
/*** 查询用户数量* @param userName 用户名* @param userRole 用户角色* @return*/int getUserCount(String userName,int userRole);/*** 得到用户列表* @param userName 用户名* @param userRole 用户角色* @param startIndex 起始位置:从1开始,1.2.3...* @param pageSize 数据量* @return*/List<User> getUserList(String userName,int userRole,int startIndex,int pageSize);
RoleService.java
public interface RoleService {/*** 得到角色列表** @return*/List<Role> getRoleList();
}
- 实现类
UserServiceImpl.java
@Overridepublic int getUserCount(String userName, int userRole) {Connection connection = BaseDao.getConnection();int count = userDao.getUserCount(connection, userName, userRole);// 关闭连接BaseDao.close(connection, null, null);return count;}@Overridepublic List<User> getUserList(String userName, int userRole, int startIndex, int pageSize) {Connection connection = BaseDao.getConnection();List<User> userList = userDao.getUserList(connection, userName, userRole, startIndex, pageSize);// 关闭连接BaseDao.close(connection, null, null);return userList;}
RoleServiceImpl.java
public class RoleServiceImpl implements RoleService {RoleDao roleDao;public RoleServiceImpl() {this.roleDao = new RoleDaoImpl();}@Overridepublic List<Role> getRoleList() {Connection connection = BaseDao.getConnection();List<Role> roleList = roleDao.getRoleList(connection);// 关闭连接BaseDao.close(connection, null, null);return roleList;}
}
(3)Servlet层
UserServlet.java
@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {String method = req.getParameter("method");if (StringUtils.isNullOrEmpty(method)) {return;}// 查询用户列表if (Objects.equals("query", method)) {query(req, resp);return;} }/*** 查询用户列表** @param req* @param resp* @throws ServletException* @throws IOException*/private void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// ---从Session中获取用户---User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);// Session失效if (user == null) {req.setAttribute(Constants.MESSAGE, "请重新登录!");req.getRequestDispatcher("/login.jsp").forward(req, resp);return;}// ---准备参数---// 要查询用户名String queryName = req.getParameter("queryname");// 要查询角色 int userRoleId = PageUtil.strToInt_0123(req.getParameter("queryUserRole"));// 总记录数UserServiceImpl userService = new UserServiceImpl();int rows = userService.getUserCount(queryName, userRoleId);// 总页数int pages = PageUtil.getPages(rows, Constants.PAGE_SIZE);// 当前页int currentPage = PageUtil.getCurrentPage(req.getParameter("pageIndex"), pages);// 查询起始位置int startIndex = PageUtil.getStartIndex(currentPage, Constants.PAGE_SIZE);// ---获取角色列表---RoleServiceImpl roleService = new RoleServiceImpl();List<Role> roleList = roleService.getRoleList();// 把角色列表存入Session中,方便后面使用req.getSession().setAttribute(Constants.ROLE_SESSION, roleList);// ---获取用户列表---List<User> userList = userService.getUserList(queryName, userRoleId, startIndex, Constants.PAGE_SIZE);// 转发请求req.setAttribute("roleList", roleList);req.setAttribute("userList", userList);req.setAttribute("totalCount", rows);req.setAttribute("currentPageNo", currentPage);req.setAttribute("totalPageCount", pages);req.setAttribute("queryUserName", queryName);req.setAttribute("queryUserRole", userRoleId);req.setAttribute("startIndex", startIndex);req.getRequestDispatcher("/jsp/userlist.jsp").forward(req, resp);}
2)添加用户
(1)Dao层
/*** 验证用户账号是否已存在* @param connection 连接* @param userCode 用户账号* @return*/boolean userCodeIsExist(Connection connection,String userCode);/*** 添加用户* @param connection 连接* @param user 用户* @return*/int addUser(Connection connection,User user);
@Overridepublic boolean userCodeIsExist(Connection connection, String userCode) {if (connection == null) {return false;}String sql = "select count(1) as count from smbms_user where userCode = ?";// 参数List<Object> params = new ArrayList<>();params.add(userCode);boolean isExist = false;try {RowSet rowSet = BaseDao.executeQuery(connection, sql, params);if (rowSet.next()) {if (rowSet.getInt("count") > 0) {isExist = true;}}} catch (SQLException e) {e.printStackTrace();}return isExist;}@Overridepublic int addUser(Connection connection, User user) {if (connection == null) {return 0;}String sql = "insert into smbms_user (userCode,userName,userPassword," +"userRole,gender,birthday,phone,address,creationDate,createdBy) " +"values(?,?,?,?,?,?,?,?,?,?)";// 参数List<Object> params = new ArrayList<>();params.add(user.getUserCode());params.add(user.getUserName());params.add(user.getUserPassword());params.add(user.getUserRole());params.add(user.getGender());params.add(user.getBirthday());params.add(user.getPhone());params.add(user.getAddress());params.add(user.getCreationDate());params.add(user.getCreatedBy());int i = 0;try {i = BaseDao.executeUpdate(connection, sql, params);} catch (SQLException e) {e.printStackTrace();}return i;}
(2)Service层
/*** 验证用户账号是否已存在* @param userCode 用户账号* @return*/boolean userCodeIsExist(String userCode);/*** 添加用户* @param user* @return*/boolean addUser(User user);
@Overridepublic boolean userCodeIsExist(String userCode) {Connection connection = BaseDao.getConnection();boolean isExist = userDao.userCodeIsExist(connection,userCode);// ---关闭---BaseDao.close(connection, null, null);return isExist;} @Overridepublic boolean addUser(User user) {boolean flag = false;Connection connection = BaseDao.getConnection();try {// ---开启事务---connection.setAutoCommit(false);// ---添加---int i = userDao.addUser(connection, user);// ---正常返回,提交事务---connection.commit();if (i > 0) {flag = true;}} catch (SQLException e) {e.printStackTrace();// ---异常---// ---事务回滚---try {connection.rollback();} catch (SQLException throwables) {throwables.printStackTrace();}} finally {// ---关闭---BaseDao.close(connection, null, null);}// ---返回---return flag;}
(3)Servlet层
@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {String method = req.getParameter("method");if (StringUtils.isNullOrEmpty(method)) {return;}// 添加用户if (Objects.equals("add", method)) {addUser(req, resp);return;}// 验证userCode是否已存在if (Objects.equals("ucexist", method)) {userCodeIsExist(req, resp);return;}}/*** 检验用户账号是否已存在** @param req* @param resp* @throws ServletException* @throws IOException*/private void userCodeIsExist(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// 结果集Map<String, String> resultMap = new HashMap<>(1);// ---得到用户账号---String userCode = req.getParameter("userCode");if (StringUtils.isNullOrEmpty(userCode)) {// 用户账号不合法:空 | nullresultMap.put("userCode", "error");} else if (new UserServiceImpl().userCodeIsExist(userCode)) {// 已存在resultMap.put("userCode", "exist");} else {// 不存在resultMap.put("userCode", "notexist");}// ---返回json数据---resp.setContentType("application/json");PrintWriter writer = resp.getWriter();// 自定义JSON {"result":"true"}//writer.print("{\"result\":\"" + resultMap.get("result") + "\"}");// 调用阿里巴巴JSONwriter.print(JSONArray.toJSONString(resultMap));writer.close();}/*** 添加用户** @param req* @param resp* @throws ServletException* @throws IOException*/private void addUser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// ---从Session中获取用户---User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);// Session失效if (user == null) {req.setAttribute(Constants.MESSAGE, "请重新登录!");req.getRequestDispatcher("/login.jsp").forward(req, resp);return;}// ---接收参数---String userCode = req.getParameter("userCode").trim();String userName = req.getParameter("userName").trim();String userPassword = req.getParameter("userPassword");String gender = req.getParameter("gender").trim();String birthday = req.getParameter("birthday").trim();String phone = req.getParameter("phone").trim();String address = req.getParameter("address").trim();String userRole = req.getParameter("userRole").trim();// ---生成User---User newUser = new User();newUser.setUserCode(userCode);newUser.setUserName(userName);newUser.setUserPassword(userPassword);newUser.setGender(Integer.parseInt(gender));try {newUser.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));} catch (ParseException e) {e.printStackTrace();}newUser.setPhone(phone);newUser.setAddress(address);newUser.setUserRole(Integer.parseInt(userRole));// 创建时间:当前时间newUser.setCreationDate(LocalDateTime.now());// 创建者:当前登录用户IdnewUser.setCreatedBy(user.getId());// ---添加---UserServiceImpl userService = new UserServiceImpl();// 添加成功:重定向至用户列表页if (userService.addUser(newUser)) {resp.sendRedirect(req.getContextPath() + "/jsp/user.do?method=query");return;}// 添加失败:转发至添加用户页req.getRequestDispatcher("/jsp/useradd.jsp").forward(req, resp);}
3)查看特定用户
(1)Dao层
/*** 根据用户ID获取用户* @param userId* @return*/User getUserById(Connection connection,int userId);
@Overridepublic User getUserById(Connection connection, int userId) {if (connection == null) {return null;}String sql = "select u.*,r.roleName from smbms_user u,smbms_role r where u.id = ? and u.userRole = r.id";// 参数List<Object> params = new ArrayList<>();params.add(userId);User user = null;try {RowSet rs = BaseDao.executeQuery(connection, sql, params);if (rs.next()) {user = new User();user.setId(userId);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((LocalDateTime) rs.getObject("creationDate"));user.setModifyBy(rs.getInt("modifyBy"));user.setModifyDate((LocalDateTime) rs.getObject("modifyDate"));user.setUserRoleName(rs.getString(rs.getMetaData().getColumnName(14)));}} catch (SQLException e) {e.printStackTrace();}return user;}
(2)Service层
/*** 根据Id获取用户* @param userId* @return*/User getUserById(int userId);
@Overridepublic User getUserById(int userId) {Connection connection = BaseDao.getConnection();User user = userDao.getUserById(connection, userId);// ---关闭---BaseDao.close(connection, null, null);return user;}
(3)Servlet层
@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {String method = req.getParameter("method");if (StringUtils.isNullOrEmpty(method)) {return;}// 查看用户if (Objects.equals("view", method)) {viewUser(req, resp);return;}}/*** 查看特定用户** @param req* @param resp* @throws ServletException* @throws IOException*/private void viewUser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// ---从Session中获取用户---User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);// Session失效if (user == null) {req.setAttribute(Constants.MESSAGE, "请重新登录!");req.getRequestDispatcher("/login.jsp").forward(req, resp);return;}// ---准备参数---// 要查询用户IdString userId = req.getParameter("uid");if (StringUtils.isNullOrEmpty(userId)) {// 用户Id不合法:空 | nullreturn;} else {User u = new UserServiceImpl().getUserById(Integer.parseInt(userId));// 转发请求req.setAttribute("user", u);req.getRequestDispatcher("/jsp/userview.jsp").forward(req, resp);}}
4)修改用户
(1)Dao层
/*** 修改用户* @param connection 连接* @param user 用户* @return*/boolean modifyUser(Connection connection,User user);
@Overridepublic boolean modifyUser(Connection connection, User user) {if (connection == null) {return false;}String sql = "update smbms_user set userName = ?,gender = ?,birthday = ?,phone = ?,address = ?,userRole = ?,modifyBy = ?,modifyDate = ? where id = ?";// 参数List<Object> params = new ArrayList<>();params.add(user.getUserName());params.add(user.getGender());params.add(user.getBirthday());params.add(user.getPhone());params.add(user.getAddress());params.add(user.getUserRole());params.add(user.getModifyBy());params.add(user.getModifyDate());params.add(user.getId());boolean success = false;try {int i = BaseDao.executeUpdate(connection, sql, params);if (i > 0) {success = true;}} catch (SQLException e) {e.printStackTrace();}return success;}
(2)Service层
/*** 修改用户* @param user* @return*/boolean modifyUser(User user);
@Overridepublic boolean modifyUser(User user) {boolean flag = false;Connection connection = BaseDao.getConnection();try {// ---开启事务---connection.setAutoCommit(false);// ---修改---flag = userDao.modifyUser(connection, user);// ---正常返回,提交事务---connection.commit();} catch (SQLException e) {e.printStackTrace();// ---异常---// ---事务回滚---try {connection.rollback();} catch (SQLException throwables) {throwables.printStackTrace();}} finally {// ---关闭---BaseDao.close(connection, null, null);}// ---返回---return flag;}
(3)Servlet层
@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {String method = req.getParameter("method");if (StringUtils.isNullOrEmpty(method)) {return;}// 进入修改用户页面if (Objects.equals("modify", method)) {goModifyUser(req, resp);return;}// 修改用户if (Objects.equals("modifyexe", method)) {modifyUser(req, resp);return;}}/*** 进入修改用户页面** @param req* @param resp* @throws ServletException* @throws IOException*/private void goModifyUser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// ---从Session中获取用户---User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);// Session失效if (user == null) {req.setAttribute(Constants.MESSAGE, "请重新登录!");req.getRequestDispatcher("/login.jsp").forward(req, resp);return;}// ---准备参数---// 用户IdString userId = req.getParameter("uid");if (StringUtils.isNullOrEmpty(userId)) {// 用户Id不合法:空 | nullreturn;} else {User u = new UserServiceImpl().getUserById(Integer.parseInt(userId));// 转发请求req.setAttribute("user", u);req.getRequestDispatcher("/jsp/usermodify.jsp").forward(req, resp);}}/*** 修改用户** @param req* @param resp* @throws ServletException* @throws IOException*/private void modifyUser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// ---从Session中获取用户---User user = (User) req.getSession().getAttribute(Constants.USER_SESSION);// Session失效if (user == null) {req.setAttribute(Constants.MESSAGE, "请重新登录!");req.getRequestDispatcher("/login.jsp").forward(req, resp);return;}// ---准备参数---// 用户IdString userId = req.getParameter("uid");String userName = req.getParameter("userName");String gender = req.getParameter("gender");String birthday = req.getParameter("birthday");String phone = req.getParameter("phone");String address = req.getParameter("address");String userRole = req.getParameter("userRole");if (StringUtils.isNullOrEmpty(userId)) {// 用户Id不合法:空 | nullreturn;} else {// ---生成User---UserServiceImpl userService = new UserServiceImpl();User newUser = userService.getUserById(Integer.parseInt(userId));newUser.setUserName(userName);newUser.setGender(Integer.parseInt(gender));try {newUser.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));} catch (ParseException e) {e.printStackTrace();}newUser.setPhone(phone);newUser.setAddress(address);newUser.setUserRole(Integer.parseInt(userRole));// 修改时间:当前时间newUser.setCreationDate(LocalDateTime.now());// 创建者:当前登录用户IdnewUser.setCreatedBy(user.getId());if (userService.modifyUser(newUser)) {// 修改成功:返回用户信息页面resp.sendRedirect(req.getContextPath() + "/jsp/user.do?method=view&uid=" + userId);} else {// 修改失败:返回修改页面// 转发请求req.setAttribute("user", newUser);req.getRequestDispatcher("/jsp/usermodify.jsp").forward(req, resp);}}}
5)删除用户
(1)Dao层
/*** 删除用户* @param connection* @param userId* @return*/boolean deleteUser(Connection connection,int userId);
@Overridepublic boolean deleteUser(Connection connection, int userId) {if (connection == null) {return false;}String sql = "delete from smbms_user where id = ?";// 参数List<Object> params = new ArrayList<>();params.add(userId);boolean success = false;try {if (BaseDao.executeUpdate(connection, sql, params) > 0) {success = true;}} catch (SQLException e) {e.printStackTrace();}return success;}
(2)Service层
/*** 删除用户* @param userId 用户Id* @return*/boolean deleteUser(int userId);
@Overridepublic boolean deleteUser(int userId) {Connection connection = BaseDao.getConnection();boolean success = userDao.deleteUser(connection, userId);// ---关闭---BaseDao.close(connection, null, null);return success;}
(3)Servlet层
@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {String method = req.getParameter("method");if (StringUtils.isNullOrEmpty(method)) {return;}// 删除用户if (Objects.equals("deluser", method)) {deleteUser(req, resp);return;}}/*** 删除用户** @param req* @param resp* @throws ServletException* @throws IOException*/private void deleteUser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// 结果集Map<String, String> resultMap = new HashMap<>(1);// ---得到用户id---String userId = req.getParameter("uid");if (StringUtils.isNullOrEmpty(userId)) {// 用户Id不合法:空 | nullresultMap.put("result", "false");} else if (new UserServiceImpl().deleteUser(Integer.parseInt(userId))) {// 已存在resultMap.put("result", "true");} else {// 不存在resultMap.put("result", "notexist");}// ---返回json数据---resp.setContentType("application/json");PrintWriter writer = resp.getWriter();// 自定义JSON {"result":"true"}//writer.print("{\"result\":\"" + resultMap.get("result") + "\"}");// 调用阿里巴巴JSONwriter.print(JSONArray.toJSONString(resultMap));writer.close();}
6、供应商管理
略,类似用户管理
7、订单管理
略,类似用户管理
》》》源码+文档+资源下载
这篇关于狂神 原生JavaWeb 三层架构 JDBC (超市订单管理系统smbms)---【优化版】的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!