超市管理系统总结篇一

项目流程图：

提示：由于时间关系，系统只写了用户这一条线，其他角色基本类似

项目架构设计

1.DAO包下Basedao，封装了数据库底层连接，查询，修改操作，并向外提供调用接口

package com.xia.dao;

import com.mysql.jdbc.Driver;

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 username;
    private static String password;

    static{
        InputStream resourceAsStream = Basedao.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver=properties.getProperty("driver");
        url=properties.getProperty("url");
        username=properties.getProperty("username");
        password=properties.getProperty("password");

    }
    //连接数据库
    public static Connection getConnect(){
        Connection con=null;
        try {
            Class.forName(driver);
             con = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return  con;
    }
    //编写查询公共类
    public static ResultSet execute(Connection connection,String sql,Object[] param) throws SQLException {
        //预编译sql
          PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < param.length; i++) {
            preparedStatement.setObject(i+1,param[i]);
        }
       ResultSet resultSet = preparedStatement.executeQuery();
        return  resultSet;
    }
    //编写增删改
    public static int update(Connection connection,String sql,Object[] param) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < param.length; i++) {
            preparedStatement.setObject(i+1,param[i]);
        }
       int resultSet = preparedStatement.executeUpdate();
        return  resultSet;
    }
    //释放资源
    public static boolean closeResources(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){
        boolean flag = true;
        try {
            if(resultSet!=null){
                resultSet.close();
                //垃圾回收
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            flag=false;
        }
        try {
            if(preparedStatement!=null){
                preparedStatement.close();
                //垃圾回收
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            flag=false;
        }
        try {
            if(connection!=null){
                connection.close();
                //垃圾回收
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            flag=false;
        }
        return  flag;
    }

}

2.DAO包下的UserDao接口,UserDaoImpl实现类，用来实现编写具体sql语句操作，向外提供接口

package com.xia.dao.user;

import com.xia.pojo.Role;
import com.xia.pojo.User;

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

public interface UserDao {
    //得到要登录的用户
    public User getloginUser(Connection connection,String userCode);
    //修改密码
    public  int updatePwd(Connection connection,int id,String pwd);
    //查询用户总数
    public  int getUserCount(Connection connection,String username,int userRole);
   //获取用户列表
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;
   //添加用户
    public  int add(Connection connection,User user)throws Exception;
}

​

package com.xia.dao.user;

import com.mysql.cj.util.StringUtils;
import com.xia.dao.Basedao;
import com.xia.pojo.Role;
import com.xia.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{
    @Override
    public User getloginUser(Connection connection, String userCode) {
        PreparedStatement pstm=null;
        User user=null;
        ResultSet resultSet=null;
        if (connection!=null) {
            String sql = "select * from smbms_user where userCode=?";
            Object[] param = {userCode};
            try {
                ResultSet rs = Basedao.execute(connection,sql,param);
               if (rs.next()) {
                    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(rs.getTimestamp("creationDate"));
                    user.setModifyBy(rs.getInt("modifyBy"));
                    user.setModifyDate(rs.getTimestamp("modifyDate"));
                }
                Basedao.closeResources(connection,null,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return  user;
    }

    @Override
    public int updatePwd(Connection connection, int id, String pwd) {
        String sql = "update smbms_user set userPassword=? where id=? ";
        PreparedStatement pstm = null;
        int rs=0;
        if (connection != null) {
            Object[] param = {pwd,id};
            try {
                rs = Basedao.update(connection, sql, param);
                Basedao.closeResources(connection, null, null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return rs;
    }

    @Override
    public int getUserCount(Connection connection, String username, int userRole) {
        ResultSet resultSet=null;
        int count=0;
        if (connection != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) as count from smbms_user u,smbms_role r where  u.userRole=r.id");
            ArrayList<Object> objects = new ArrayList<>();  //存在参数
            if(!StringUtils.isNullOrEmpty(username)){
               sql.append(" and u.userName like ?");
               objects.add("%"+username+"%");  //index=0
           }
           if(userRole>0){
               sql.append(" and u.userRole = ?");
               objects.add(userRole);
           }
            Object[] objects1=objects.toArray();
            System.out.println("UserdaoImpl->getUserCount:"+sql.toString());

            try {
                resultSet= Basedao.execute(connection, sql.toString(), objects1);
                if(resultSet.next()){
                count=resultSet.getInt("count"); //获取总数

                }
                Basedao.closeResources(null,null ,resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return count;
    }

    @Override
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)
            throws Exception {
        // TODO Auto-generated method stub
        ResultSet rs = null;
        List<User> userList = new ArrayList<User>();
        if(connection != null){
            StringBuffer sql = new StringBuffer();
            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<Object>();
            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%"+userName+"%");
            }
            if(userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }
            //
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object[] params = list.toArray();
            System.out.println("sql ----> " + sql.toString());
            rs = Basedao.execute(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.setGender(rs.getInt("gender"));
                _user.setBirthday(rs.getDate("birthday"));
                _user.setPhone(rs.getString("phone"));
                _user.setUserRole(rs.getInt("userRole"));
                _user.setUserRoleName(rs.getString("userRoleName"));
                userList.add(_user);
            }
            Basedao.closeResources(null, null, rs);
        }
        return userList;
    }

    @Override
    public int add(Connection connection, User user) {
        int updateRows = 0;
        if(null != connection){
            String sql = "insert into smbms_user (userCode,userName,userPassword," +
                    "userRole,gender,birthday,phone,address,creationDate,createdBy) " +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {user.getUserCode(),user.getUserName(),user.getUserPassword(),
                    user.getUserRole(),user.getGender(),user.getBirthday(),
                    user.getPhone(),user.getAddress(),user.getCreationDate(),user.getCreatedBy()};
            try {
                updateRows = Basedao.update(connection, sql, params);
                Basedao.closeResources(null, null,null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return updateRows;
    }


}

3,Service包下的UserService，UserServiceImpL属于业务层代码，承上启下，是servlet和数据库调用的桥梁，专注于业务

package com.xia.service.user;

import com.xia.pojo.User;

import java.util.List;

public interface UserService {
    //用户登录
    public User login(String userCode,String password);
    //修改密码
    public int modifypwd(int id,String password);
    //获取用户总数
    public  int getCount(String username,int userRole);
    //获取用户列表
    public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
   //增加用户
    public boolean add(User user)throws Exception;
}

package com.xia.service.user;

import com.xia.dao.Basedao;
import com.xia.dao.user.UserDao;
import com.xia.dao.user.UserDaoImpl;
import com.xia.pojo.User;
import org.junit.jupiter.api.Test;

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

public class UserServiceImpL implements UserService{
    //业务层会调用dao层，引入dao
    private UserDao userDao;

    public UserServiceImpL() {
        userDao =new UserDaoImpl();
    }


    @Override
    public User login(String userCode, String password) {
        Connection connection=null;
        User user=null;
        try {
            connection= Basedao.getConnect();
            user=userDao.getloginUser(connection,userCode);
            if (!(user.getUserPassword().equals(password)))
            {
                user=null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Basedao.closeResources(connection,null,null);
        }
        return  user;
    }
    @Override
    public int modifypwd(int id, String password) {
        Connection connection=null;
        int user=0;
        try {
            connection= Basedao.getConnect();
            user=userDao.updatePwd(connection,id,password);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Basedao.closeResources(connection,null,null);
        }
        return  user;
    }

    @Override
    public int getCount(String username, int userRole) {
        Connection connection=null;
        int user=0;
        try {
            connection= Basedao.getConnect();
            user=userDao.getUserCount(connection,username,userRole);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Basedao.closeResources(connection,null,null);
        }
        return  user;
    }

    @Override
    public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
        // TODO Auto-generated method stub
        Connection connection = null;
        List<User> userList = null;
        System.out.println("queryUserName ---- > " + queryUserName);
        System.out.println("queryUserRole ---- > " + queryUserRole);
        System.out.println("currentPageNo ---- > " + currentPageNo);
        System.out.println("pageSize ---- > " + pageSize);
        try {
            connection = Basedao.getConnect();
            userList = userDao.getUserList(connection, queryUserName,queryUserRole,currentPageNo,pageSize);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            Basedao.closeResources(connection, null, null);
        }
        return userList;
    }


    //增删改都属于事务，ACID,要考虑失败回滚
    @Override
    public boolean add(User user) {
        boolean flag = false;
        Connection connection = null;
        try {
            connection = Basedao.getConnect();
            connection.setAutoCommit(false);//开启JDBC事务管理
            int updateRows = userDao.add(connection,user);
            connection.commit();
            if(updateRows > 0){
                flag = true;
                System.out.println("add success!");
            }else{
                System.out.println("add failed!");
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            try {
                System.out.println("rollback==================");
                connection.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }finally{
            //在service层进行connection连接的关闭
            Basedao.closeResources(connection, null, null);
        }
        return flag;
    }


    @Test
    public  void test(){
        UserServiceImpL userServiceImpL = new UserServiceImpL();
        System.out.println("总数"+userServiceImpL.getCount("", 0));
    }
}

4.控制层借助Servlet来实现，对视图层的不同请求做出响应，实现页面的跳转，前端要展示数据的传递等

UserServlet 用户登陆

package com.xia.servlet;

import com.xia.pojo.User;
import com.xia.service.user.UserServiceImpL;
import com.xia.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 UserServlet  extends HttpServlet {
    //servlet控制层:调用业务层service

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

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("login");
        //获取用户名和密码
        String username = req.getParameter("userCode");
        String password=req.getParameter("userPassword");
        //验证，调用业务层
        UserServiceImpL userServiceImpL = new UserServiceImpL();
        User login = userServiceImpL.login(username, password);
        if(login!=null){  //验证通过，可以登录
            req.getSession().setAttribute(Constants.USER_SESSION,login);
            resp.sendRedirect(req.getContextPath()+"/jsp/frame.jsp");
        }
        else{  //查无此人，提示错误
            req.setAttribute("error","用户名或密码不正确");
            req.getRequestDispatcher("login.jsp").forward(req,resp);
        }

    }
}

Loginout 用户注销

package com.xia.servlet;

import com.xia.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 Loginout extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //移除用户的session
        req.getSession().removeAttribute(Constants.USER_SESSION);
        resp.sendRedirect(req.getContextPath()+"/login.jsp");
    }

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