SMBMS 超市订单管理系统
一、项目架构
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jPexhHy2-1617433609007)(smbms.assets/image-20210403150356103.png)]
1、数据库结构
-
user
private Integer id; //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; //更新时间
-
role
private Integer id; //id private String roleCode; //角色编码 private String roleName; //角色名称 private Integer createdBy; //创建者 private Date creationDate; //创建时间 private Integer modifyBy; //更新者 private Date modifyDate;//更新时间
-
bill
private Integer id; //id private String billCode; //账单编码 private String productName; //商品名称 private String productDesc; //商品描述 private String productUnit; //商品单位 private BigDecimal productCount; //商品数量 private BigDecimal totalPrice; //总金额 private Integer isPayment; //是否支付 private Integer providerId; //供应商ID private Integer createdBy; //创建者 private Date creationDate; //创建时间 private Integer modifyBy; //更新者 private Date modifyDate;//更新时间
-
provider
private Integer id; //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 Integer modifyBy; //更新者 private Date modifyDate;//更新时间
-
address
2、常用工具类
Java 开发中,需要将一些易变的配置参数放置再 XML 配置文件或者 properties 配置文件中。然而 XML 配置文件需要通过 DOM 或 SAX 方式解析,而读取 properties 配置文件就比较容易。
介绍几种读取方式:
1、基于ClassLoder读取配置文件
注意:该方式只能读取类路径下的配置文件,有局限但是如果配置文件在类路径下比较方便。
1 Properties properties = new Properties();
2 // 使用ClassLoader加载properties配置文件生成对应的输入流
3 InputStream in = PropertiesMain.class.getClassLoader().getResourceAsStream("config/config.properties");
4 // 使用properties对象加载输入流
5 properties.load(in);
6 //获取key对应的value值
7 properties.getProperty(String key);
2、基于 InputStream 读取配置文件
注意:该方式的优点在于可以读取任意路径下的配置文件
1 Properties properties = new Properties();
2 // 使用InPutStream流读取properties文件
3 BufferedReader bufferedReader = new BufferedReader(new FileReader("E:/config.properties"));
4 properties.load(bufferedReader);
5 // 获取key对应的value值
6 properties.getProperty(String key);
3、通过 java.util.ResourceBundle 类来读取,这种方式比使用 Properties 要方便一些
1>通过 ResourceBundle.getBundle() 静态方法来获取(ResourceBundle是一个抽象类),这种方式来获取properties属性文件不需要加.properties后缀名,只需要文件名即可
1 properties.getProperty(String key);
2 //config为属性文件名,放在包com.test.config下,如果是放在src下,直接用config即可
3 ResourceBundle resource = ResourceBundle.getBundle("com/test/config/config");
4 String key = resource.getString("keyWord");
2>从 InputStream 中读取,获取 InputStream 的方法和上面一样,不再赘述
1 ResourceBundle resource = new PropertyResourceBundle(inStream);
注意:在使用中遇到的最大的问题可能是配置文件的路径问题,如果配置文件入在当前类所在的包下,那么需要使用包名限定,如:config.properties入在com.test.config包下,则要使用com/test/config/config.properties(通过Properties来获取)或com/test/config/config(通过ResourceBundle来获取);属性文件在src根目录下,则直接使用config.properties或config即可。
操作数据库的基类BaseDao
/**
* Author:ckvsok
* Date:2021/4/2
**/
package com.ckvsok.dao;
import java.sql.*;
import java.util.ResourceBundle;
/**
* 操作数据库的基类
*/
public class BaseDao {
//静态代码块,在类加载的时候执行
static {
init();
}
private static String driver;
private static String url;
private static String username;
private static String password;
//初始化连接参数,从配置文件里获得
public static void init() {
ResourceBundle resource = ResourceBundle.getBundle("db");
driver = resource.getString("driver");
url = resource.getString("url");
username = resource.getString("username");
password = resource.getString("password");
}
/**
* 获取数据库连接
*
* @return
*/
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
/**
* 查询操作
*
* @param connection
* @param ps
* @param rs
* @param sql
* @param params
* @return
* @throws SQLException
*/
public static ResultSet execute(Connection connection, PreparedStatement ps, ResultSet rs,
String sql, Object[] params) throws SQLException {
ps = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
rs = ps.executeQuery();
return rs;
}
/**
* 更新操作 增,删 ,改
*
* @param connection
* @param ps
* @param sql
* @param params
* @return
* @throws SQLException
*/
public static int execute(Connection connection, PreparedStatement ps,
String sql, Object[] params) throws SQLException {
int updateRows = 0;
ps = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
updateRows = ps.executeUpdate();
return updateRows;
}
public static boolean closeResource(Connection connection, PreparedStatement ps, ResultSet rs) {
boolean flag = true;
if (rs != null) {
try {
rs.close();
rs = null; //GC回收
} catch (SQLException e) {
e.printStackTrace();
flag = false;
}
}
if (ps != null) {
try {
ps.close();
ps = null; //GC回收
} catch (SQLException e) {
e.printStackTrace();
flag = false;
}
}
if (connection != null) {
try {
connection.close();
connection = null; //GC回收
} catch (SQLException e) {
e.printStackTrace();
flag = false;
}
}
return flag;
}
}
二、登录注销
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2x7sXyWk-1617433609010)(smbms.assets/image-20210402112127009.png)]
1、登录
顺序:DAO - Service - controller
DAO
public int updatePwd(Connection connection, int id, String pwd) throws Exception {
PreparedStatement ps =null;
int updateRows =0;
if (connection!=null){
String sql = "update smbms_user set userPassword =? where id=?";
Object[] params = {pwd,id};
updateRows = BaseDao.execute(connection, ps, sql, params);
}
return updateRows;
}
Service
public User login(String userCode, String userPassword) {
Connection connection = null;
User user = null;
try {
connection = JDBCUtil.getConnection();
if (connection != null) {
user = userDao.getLoginUser(connection, userCode);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection);
}
if (null != user) {
if (!user.getUserPassword().equals(userPassword)) {
return null;
}
}
return user;
}
controller
public class UserLoginController extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String userCode = req.getParameter("userCode");
String userPassword = req.getParameter("userPassword");
User user = null;
if (userCode != null && userPassword != null) {
UserService userService = new UserServiceImpl();
user = userService.login(userCode, userPassword);
}
if (null != user) {
//放入session
req.getSession().setAttribute(SessionConstant.USER_SESSION, user);
//页面跳转(frame.jsp)重定向 地址是 资源相对服务器地址
resp.sendRedirect("jsp/frame.jsp");
} else {
//页面跳转(login.jsp)带出提示信息--请求转发 地址 资源相对服务器地址
req.setAttribute("error", "用户名或密码不正确");
req.getRequestDispatcher("login.jsp").forward(req, resp);
}
}
}
2、注销
3、修改密码
三、用户管理
1、查询任务
-
UserDao
用sql语句,由于这里SQL语句需要拼接,所以使用可变字符串StringBuilder或者StringBuffer。
StringBuilder与StringBuffer区别:
StringBuilder和StringBuffer的使用方式一模一样,两者只需要修改一下类名就可以无缝切换。StringBuilder应用于单线程环境,而StringBuffer应用于多线程环境。
由于Service层存在事务,处理事务需要connection , 所以connection需要在Service层创建。由于Connection是接收过来的数据,需要判断是否为空。
-
UserDaoImpl 查表返回数据
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception { ArrayList<User> users = new ArrayList<>(); StringBuffer sql = new StringBuffer(); PreparedStatement ps = null; ResultSet rs = null; ArrayList<Object> list = new ArrayList<>(); User user = null; if (connection != null) { //select u.id,u.userName,u.gender,u.birthday,u.phone,r.roleName,u.modifyBy from smbms_user u join smbms_role r where u.userRole=r.id order by id asc limit 0,5 sql.append("select u.id,u.userName,u.gender,u.birthday,u.phone,r.roleName,u.userCode from smbms_user u join smbms_role r where u.userRole=r.id"); if (!StringUtils.isNullOrEmpty(userName)) { sql.append(" and userName like ?"); list.add("%"+userName+"%"); } if (userRole > 0) { sql.append(" and userRole = ?"); list.add(userRole); } if (currentPageNo > 0 && pageSize > 0) { sql.append(" order by id asc limit ?,?"); list.add((currentPageNo - 1) * pageSize); list.add(pageSize); } Object[] params = list.toArray(); String s = sql.toString(); rs = BaseDao.execute(connection, ps, rs, s, params); while (rs.next()) { user = new User(); user.setId(rs.getInt("id")); user.setUserCode(rs.getString("userCode")); user.setUserName(rs.getString("userName")); user.setGender(rs.getInt("gender")); user.setBirthday(rs.getDate("birthday")); user.setPhone(rs.getString("phone")); user.setUserRoleName(rs.getString("roleName")); users.add(user); } } BaseDao.closeResource(null, ps, rs); return users; }
-
UserService 、UserServiceImpl 业务逻辑
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) { List<User> users = new ArrayList<>(); Connection connection = null; connection = BaseDao.getConnection(); try { users = userDao.getUserList(connection, queryUserName, queryUserRole, currentPageNo, pageSize); } catch (Exception e) { e.printStackTrace(); }finally { BaseDao.closeResource(connection,null,null); } return users; }
-
UserController 接受前端数据,给前端返回数据
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String method = req.getParameter("method"); if ("query".equals(method)) { /* 当前服务器地址:/smbms "/"用法: ,如果这里不加斜杠, "userlist.jsp",在当前路径下找,例如当前路径是/smbms/jsp/xxx ,那么处理后的路径是/smbms/jsp/+"userlist.jsp" 如果加斜杠,则路径是"/jsp/userlist.jsp", 找的相对服务器的路径 /smbms + "/jsp/userlist.jsp" * */ this.query(req, resp); } else if ("savepwd".equals(method)) { this.savepwd(req, resp); } else if ("pwdmodify".equals(method)) { this.pwdmodify(req, resp); }else if ("pwdmodify".equals(method)) { this.pwdmodify(req, resp); } } private void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { //查询用户列表 //从前端获取数据 , 处理数据 String queryUserName = req.getParameter("queryname"); if (queryUserName == null) { queryUserName = ""; } String queryUserRoleTemp = req.getParameter("queryUserRole"); int queryUserRole = 0; if (queryUserRoleTemp != null && queryUserRoleTemp != "") { queryUserRole = Integer.valueOf(queryUserRoleTemp); } //第一次走页面一定是第一页 Integer pageIndex = 1; String pageIndexTemp = req.getParameter("pageIndex"); if (pageIndexTemp != null && pageIndexTemp != "") { pageIndex = Integer.valueOf(pageIndexTemp); } //页码定义 int pageSize = SessionConstant.PAGE_SIZE; //当前页码 int currentPageNo = pageIndex; //User总数 int totalCount = 0; //页码总数 int totalPageCount = 0; UserService userService = new UserServiceImpl(); totalCount = userService.getUserCount(queryUserName, queryUserRole); totalPageCount = totalCount / SessionConstant.PAGE_SIZE; if (totalCount % SessionConstant.PAGE_SIZE != 0) { totalPageCount++; } List<User> userList = null; userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize); List<Role> roleList = null; RoleService roleService = new RoleServiceImpl(); roleList = roleService.getRoleList(); //返回前端的数据 req.setAttribute("userList", userList); req.setAttribute("roleList", roleList); req.setAttribute("queryUserName", queryUserName); req.setAttribute("queryUserRole", queryUserRole); req.setAttribute("totalPageCount", totalPageCount); req.setAttribute("totalCount", totalCount); req.setAttribute("currentPageNo", currentPageNo); req.getRequestDispatcher("/jsp/userlist.jsp").forward(req, resp); }
2、分页实现
分页,数据库实现通过limit 0,5
表格总行数 totolCount
当前页码pageNum 大于1的数
分页大小pagesize 大于1的数 ,每页行数
总页码totalPage 大于1的数
limit (pageNum-1)*pagesize ,pagesize
3、增删改
一切的增删改都需要处理事务
四、订单管理
UserRole", queryUserRole);
req.setAttribute(“totalPageCount”, totalPageCount);
req.setAttribute(“totalCount”, totalCount);
req.setAttribute(“currentPageNo”, currentPageNo);
req.getRequestDispatcher("/jsp/userlist.jsp").forward(req, resp);
}
### 2、分页实现
分页,数据库实现通过limit 0,5
表格总行数 totolCount
当前页码pageNum 大于1的数
分页大小pagesize 大于1的数 ,每页行数
总页码totalPage 大于1的数
limit (pageNum-1)*pagesize ,pagesize
### 3、增删改
***一切的增删改都需要处理事务***
四、订单管理
五、供应商管理