SMBMS

项目流程图如下：

数据库

数据库相应的代码：
smbms_address

CREATE TABLE `smbms_address` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `contact` VARCHAR(15) DEFAULT NULL COMMENT '联系人姓名',
  `addressDesc` VARCHAR(50) DEFAULT NULL COMMENT '收货地址',
  `postCode` VARCHAR(15) DEFAULT NULL COMMENT '邮编',
  `tel` INT(20) DEFAULT NULL COMMENT '联系人电话',
  `createdBy` VARCHAR(20) DEFAULT NULL COMMENT '创建者',
  `creationDate` DATETIME DEFAULT NULL COMMENT '创建时间',
  `modifyBy` BIGINT(20) DEFAULT NULL COMMENT '修改者',
  `modifyDate` DATETIME DEFAULT NULL COMMENT '修改时间',
  `userId` BIGINT(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

smbms_bill

CREATE TABLE `smbms_bill` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `billCode` VARCHAR(30) DEFAULT NULL COMMENT '账单编码',
  `productName` VARCHAR(20) DEFAULT NULL COMMENT '商品名称',
  `productDesc` VARCHAR(50) DEFAULT NULL COMMENT '商品描述',
  `productUnit` VARCHAR(60) DEFAULT NULL COMMENT '商品数量',
  `productCount` DECIMAL(20,2) DEFAULT NULL COMMENT '总金额',
  `totalPrice` DECIMAL(20,2) DEFAULT NULL COMMENT '是否支付',
  `isPayment` INT(10) DEFAULT NULL COMMENT '供应商ID',
  `createdBy` BIGINT(20) DEFAULT NULL COMMENT '创建者',
  `creationDate` DATETIME DEFAULT NULL COMMENT '创建时间',
  `modifyBy` BIGINT(20) DEFAULT NULL COMMENT '更新者',
  `modifyDate` DATETIME DEFAULT NULL COMMENT '更新时间',
  `providerId` BIGINT(20) NOT NULL COMMENT '供应商ID',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

smbms_provider

CREATE TABLE `smbms_provider` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `proCode` VARCHAR(15) DEFAULT NULL COMMENT '供应商编码',
  `proName` VARCHAR(15) DEFAULT NULL COMMENT '供应商名称',
  `proDesc` VARCHAR(50) DEFAULT NULL COMMENT '供应商描述',
  `proContact` VARCHAR(15) DEFAULT NULL COMMENT '供应商联系人',
  `proPhone` VARCHAR(20) DEFAULT NULL COMMENT '供应商电话',
  `proAddress` VARCHAR(30) DEFAULT NULL COMMENT '供应商地址',
  `proFax` VARCHAR(20) DEFAULT NULL COMMENT '供应商传真',
  `createdBy` BIGINT(20) DEFAULT NULL COMMENT '创建者',
  `creationDate` DATETIME DEFAULT NULL COMMENT '创建时间',
  `modifyDate` DATETIME DEFAULT NULL COMMENT '更新时间',
  `modifyBy` BIGINT(20) DEFAULT NULL COMMENT '更新者',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

smbms_role

CREATE TABLE `smbms_user` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `userCode` VARCHAR(15) DEFAULT NULL COMMENT '用户编码',
  `userName` VARCHAR(15) DEFAULT NULL COMMENT '用户名字',
  `userPassword` VARCHAR(20) DEFAULT NULL COMMENT '用户密码',
  `gender` INT(10) DEFAULT NULL COMMENT '性别',
  `birthday` DATE DEFAULT NULL COMMENT '出生日期',
  `phone` VARCHAR(20) DEFAULT NULL COMMENT '电话',
  `address` VARCHAR(30) DEFAULT NULL COMMENT '地址',
  `userRole` BIGINT(20) DEFAULT NULL COMMENT '用户角色',
  `createdBy` BIGINT(20) DEFAULT NULL COMMENT '创建者',
  `creationDate` DATETIME DEFAULT NULL COMMENT '创建时间',
  `modifyBy` BIGINT(20) DEFAULT NULL COMMENT '更新者',
  `modifyDate` DATETIME DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

1、项目搭建（准备工作）

1.1、搭建一个Maven web项目

1. 2、配置Tomcat

1.3、确定项目是否可以跑起来

1.4、导入项目中会遇到的jar包： jsp、servlet、mysql、jstl、stand

<dependencies>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.11</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.20</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>4.0.1</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>javax.servlet.jsp</groupId>
        <artifactId>javax.servlet.jsp-api</artifactId>
        <version>2.3.3</version>
    </dependency>
    <dependency>
        <groupId>javax.servlet.jsp.jstl</groupId>
        <artifactId>jstl-api</artifactId>
        <version>1.2</version>
    </dependency>
    <dependency>
        <groupId>taglibs</groupId>
        <artifactId>standard</artifactId>
        <version>1.1.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
</dependencies>

1.5、创建项目结构

1.6、编写实体类：

ORM映射：表-类映射

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;//更新时间
private String providerName;//供应商名称

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;//更新时间

Role

private Integer id;   //id
private String roleCode; //角色编码
private String roleName; //角色名称
private Integer createdBy; //创建者
private Date creationDate; //创建时间
private Integer modifyBy; //更新者
private Date modifyDate;//更新时间

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;   //更新时间

private Integer age;//年龄
private String userRoleName;    //用户角色名称

public Integer getAge(){
    return (new Date()).getYear()-birthday.getYear();
}

1.7、编写基础公共类

1、数据库配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf8&useSSL=flase
username=root
password=root

2、编写操作数据库公共类

package com.yt.dao;



import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * ClassName:BaseDao
 * Package:com.yt.dao
 * Description:
 *
 * @date:2021/7/12 17:47
 * @author:yt
 */

public class BaseDao {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    //静态代码块，类加载的时候就初始化了
    static{
        Properties properties = new Properties();
        //通过类加载器读取对应的资源
        InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
    }

    //获取数据库的连接
    public static Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    //编写查询公共类
    public static ResultSet execute(Connection connection, String sql, Object[] params, ResultSet resultSet, PreparedStatement preparedStatement) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);

        for (int i = 0; i < params.length; i++) {
            //setObjext，占位符从1开始，但是我们的数组是从0开始！
            preparedStatement.setObject(i+1,params[i]);
        }
        resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    //编写增删改公共方法
    public static int execute(Connection connection,String sql,Object[] params,PreparedStatement preparedStatement) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);

        for (int i = 0; i < params.length; i++) {
            //setObjext，占位符从1开始，但是我们的数组是从0开始！
            preparedStatement.setObject(i+1,params[i]);
        }
        int updateRows= preparedStatement.executeUpdate();
        return updateRows;
    }

    //释放资源
    public static boolean closeResource(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        boolean flag = true;
        if (resultSet!=null){
            try {
                resultSet.close();
                //GC回收
                resultSet = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }

        if (preparedStatement!=null){
            try {
                preparedStatement.close();
                //GC回收
                resultSet = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }

        if (connection!=null){
            try {
                connection.close();
                //GC回收
                resultSet = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                flag = false;
            }
        }
        return flag;
    }
}

1.8、导入静态资源

2、登入功能实现

在这里插入图片描述

2.1、登入

（1）编写前端代码

（2）将前端页面设置为首页

在web-inf中的web.xml加入以下代码即可实现设置首页功能

<!--    设置欢迎页面 -->
<welcome-file-list>
    <welcome-file>Login.jsp</welcome-file>
</welcome-file-list>

（3）编写dao层用户登录的接口UserDao

在dao中编写一个user包，在包中创建一个UserDao接口。

public interface UserDao {

    /**
     * 得到要登录的用户
     * @param connection
     * @param userCode
     * @return
     * @throws SQLException
     */
    public User getLoginUser(Connection connection,String userCode) throws SQLException;
}

（4）编写UserDao接口的实现类UserDaoImpl

public class UserDaoImpl implements UserDao{
    @Override
    public User getLoginUser(Connection connection, String userCode) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;

        if (connection!=null){
            String sql = "select * from smbms_user where userCode=?";
            Object[] params = {userCode};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            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.closeResource(null,pstm,rs);
        }
        return user;
    }
}

（5）编写业务层接口

在service下建立user包，建立UserService接口

public interface UserService {
    /**
     * 用户登入
     * @param userCode
     * @param password
     * @return
     */
    public User login(String userCode, String password);
}

（6）编写业务层接口的实现类

在service的user包中建立UserServiceImpl类

public class UserServiceImpl implements UserService {
    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.getConnection();
            //通过业务层调用对应的具体的数据库操作
            user = userDao.getLoginUser(connection, userCode,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return user;
    }
}

（7）编写Servlet类

在Servlet包中创建user包，在user包中建立LoginServlet类

package com.yt.servlet.user;

import com.yt.pojo.User;
import com.yt.service.user.UserService;
import com.yt.service.user.UserServiceImpl;
import com.yt.util.Constants;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

import static com.yt.util.Constants.USER_SESSION;

/**
 * ClassName:LoginServlet
 * Package:com.yt.servlet.user
 * Description:
 *
 * @date:2021/7/12 19:22
 * @author:yt
 */
@WebServlet("/login.do")
public class LoginServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("LoginServlet...start....");
        //获取用户名和密码
        String userCode = req.getParameter("userCode");
        String userPassword = req.getParameter("userPassword");

        //和数据库中的密码进行对比，调用业务层
        UserService userService = new UserServiceImpl();
        // 这里已经把登入的人给查出来了
        User user = userService.login(userCode,userPassword);
        if(user!=null){
            //查到此人，可以登入
            //将用户的信息放到session中;
            req.getSession().setAttribute(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);
    }
}

（8）导入frame.jsp、foot.jsp、head.jsp

在这里插入图片描述
测试访问，确保以上能够成功

2.2、注销

在Servlet包的user包中建立LogoutServlett类

@WebServlet("/jsp/logout.do")
public class LogoutServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //移除用户的Constants.USER_SESSION
        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);
    }
}

2.3、登录拦截

在filter中建立SysFilter类

@WebFilter(filterName = "SysFilter",urlPatterns = "/jsp/*")
public class SysFilter implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain filterChain) throws IOException, ServletException {
        HttpServletRequest request = (HttpServletRequest) req;
        HttpServletResponse response = (HttpServletResponse) resp;

        //过滤器，从Session中获取用户
        User user = (User) request.getSession().getAttribute(Constants.USER_SESSION);

        if (user == null){
            //已经被移除或注销了，或者未登录
            response.sendRedirect("../error.jsp");
        }else {
            filterChain.doFilter(req, resp);
        }
    }

    @Override
    public void destroy() {

    }
}

3、密码修改的实现

在这里插入图片描述

3.1、编写dao层用户修改密码的接口UserDao

UserDao接口写如下代码

 /**
     * 修改当前用户的密码
     * @param connection
     * @param id
     * @param password
     * @return
     * @throws SQLException
     */
    public int updatePws(Connection connection, int id, String password) throws SQLException;

3.2、编写UserDao 接口实现类UserDaoImpl

    @Override
    public int updatePws(Connection connection, int id, String password) throws SQLException {
        PreparedStatement pstm = null;
        int execute = 0;

        if(connection!=null){
            String sql = "update smbms_user set userPassword = ? where id = ?";
            Object params[] = {password,id};
            execute =  BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
        }
        return execute;
    }

3.3、编写业务层接口

在service的user包中的UserService接口中添加如下代码

    /**
     * 根据用户ID修改密码
     * @param id
     * @param pwd
     * @return
     */
    public boolean updatePws(int id, String pwd);

3.4、编写业务层接口实现类

在service的user包中的UserServiceImpl类中添加以下代码

 @Override
    public boolean updatePws(int id, String pwd) {
        Connection connection = null;
        boolean flag = false;
        //修改密码
        try {
            connection = BaseDao.getConnection();
            if(userDao.updatePws(connection,id,pwd)>0){
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return flag;
    }

3.5、编写Servlet类

在Servlet包中的user包中建立UserServlet类

@WebServlet("/jsp/user.do")
public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //从Session中拿ID;
        Object o = req.getSession().getAttribute(Constants.USER_SESSION);
        String newpassword = req.getParameter("newpassword");

        boolean flag = false;
        System.out.println(o!=null);
        // 这是jdbc里面判断不为空且长度不为0的方法
        System.out.println(StringUtils.isNullOrEmpty(newpassword));

        if(o!=null && newpassword!=null){
            UserService userService = new UserServiceImpl();
            flag = userService.updatePws(((User)o).getId(),newpassword);
            if(flag){
                req.setAttribute("message","修改密码成功，请退出，使用新的密码登入");
                //密码修改成功，溢出当前Session
                req.getSession().removeAttribute(Constants.USER_SESSION);
            }else {
                req.setAttribute("message","密码修改失败");
            }
        }else {
            req.setAttribute("message","新密码有问题");
        }
        req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp);
    }

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

记得实现复用，需要提取方法

优化密码修改使用Ajax：
1、阿里巴巴的fastjson

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.61</version>
</dependency>

package com.yt.servlet.user;

import com.alibaba.fastjson.JSONArray;
import com.mysql.jdbc.StringUtils;
import com.yt.pojo.User;
import com.yt.service.user.UserService;
import com.yt.service.user.UserServiceImpl;
import com.yt.util.Constants;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Map;

/**
 * ClassName:UserServlet
 * Package:com.yt.servlet.user
 * Description:
 *
 * @date:2021/7/12 23:31
 * @author:yt
 */
@WebServlet("/jsp/user.do")
public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method = req.getParameter("method");
        if(method.equals("savepwd")&&method!=null){
            this.updatePwd(req,resp);
        }else if (method.equals("pwdmodify")){
            this.pwdModify(req,resp);
        }
    }


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


    /**
     * 修改新密码
     * @param req
     * @param resp
     */
    public void updatePwd(HttpServletRequest req, HttpServletResponse resp){
        //从Session中拿ID;
        Object o = req.getSession().getAttribute(Constants.USER_SESSION);
        String newpassword = req.getParameter("newpassword");
        boolean flag = false;
        // 这是jdbc里面判断不为空且长度不为0的方法
        if(o!=null && !StringUtils.isNullOrEmpty(newpassword)){
            UserService userService = new UserServiceImpl();
            flag = userService.updatePws(((User)o).getId(),newpassword);
            if(flag){
                req.setAttribute("message","修改密码成功，请退出，使用新的密码登入");
                //密码修改成功，溢出当前Session
                req.getSession().removeAttribute(Constants.USER_SESSION);
            }else {
                req.setAttribute("message","密码修改失败");
            }
        }else {
            req.setAttribute("message","新密码有问题");
        }
        try {
            req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp);
        } catch (ServletException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 验证旧密码session中有用户的密码
     * @param req
     * @param resp
     */
    private void pwdModify(HttpServletRequest req, HttpServletResponse resp) {
        //从Session中拿ID
        Object o = req.getSession().getAttribute(Constants.USER_SESSION);
        String oldpassword = req.getParameter("oldpassword");
        //万能Map:结果集
        Map<String, String> resultMap = new HashMap<String, String>();

        if(o==null){
            //session失效了，session过期了
            resultMap.put("result","sessionerror");
        }else if(StringUtils.isNullOrEmpty(oldpassword)){
            //输入的密码为空
            resultMap.put("result","error");
        }else {
            //Session中用户的密码
            String userPassword = ((User)o).getUserPassword();
            if (oldpassword.equals(userPassword)){
                resultMap.put("result","true");
            }else {
                resultMap.put("result","false");
            }
        }
        try {
            resp.setContentType("application/json");
            PrintWriter writer = resp.getWriter();
            /*JSONArray  阿里巴巴的JSON工具类，转换格式
             *
             * resultMap = ["result","sessionerror", "result", "error"]
             * Json格式 = {key:value}*/
            writer.write(JSONArray.toJSONString(resultMap));
            writer.flush();
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

4、用户管理实现

在这里插入图片描述

1、导入分页的工具类
2、用户列表页面导入

4.1、获取用户数量

（1）编写查询总人数的接口UserDao

UserDao接口写如下代码

/**
 * 查询用户总数
 * @param connection
 * @param username
 * @param userRole
 * @return
 * @throws SQLException
 */
public int getUserCount(Connection connection,String username,int userRole) throws SQLException;

（2）UserDao接口实现类UserDaoImpl

向实现类中添加以下代码

@Override
public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
    PreparedStatement pstm = null;
    ResultSet rs = 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> list = new ArrayList<Object>();
        if(!StringUtils.isNullOrEmpty(username)){
            sql.append(" and u.userName like ?");
            // index:0
            list.add("%"+username+"%");
        }
        if(userRole>0){
            sql.append(" and u.userRole = ?");
            // index:1
            list.add(userRole);
        }

        //怎么把List转换为数组
        Object[] params = list.toArray();

        System.out.println("UserDaoImpl->getUserCount: " + sql.toString());

        rs = BaseDao.execute(connection,pstm,rs,sql.toString(),params);

        if(rs.next()){
            // 从结果集中获得最终的数量
            count = rs.getInt("count");
        }
        BaseDao.closeResource(null,pstm,rs);
    }
    return count;
}

（3）编写业务层接口

在service的user包中的UserService接口中添加如下代码

/**
 * 查询记录数
 * @param username
 * @param userRole
 * @return
 */
public int getUserCount(String username,int userRole);

(4）编写业务层接口实现类

在service的user包中的UserServiceImpl类中添加以下代码

@Override
public int getUserCount(String username, int userRole) {
    Connection connection = null;
    int count = 0;

    try {
        connection = BaseDao.getConnection();
        count = userDao.getUserCount(connection,username,userRole);
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        BaseDao.closeResource(connection,null,null);
    }
    return count;
}

4.2、获取用户列表

（1）编写接口UserDao

UserDao接口写如下代码

/**
 * 通过条件查询 - userList
 * @param connection
 * @param userName
 * @param userRole
 * @param currentPageNo
 * @param pageSize
 * @return
 * @throws Exception
 */
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;

（2）UserDao接口实现类UserDaoImpl

向实现类中添加以下代码

@Override
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception {
    PreparedStatement pstm = null;
    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,pstm,rs,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.closeResource(null,pstm,rs);
    }
    return userList;
}

（3）编写业务层接口

在service的user包中的UserService接口中添加如下代码

/**
 * 根据条件查询用户列表
 * @param queryUserName
 * @param queryUserRole
 * @param currentPageNo
 * @param pageSize
 * @return
 */
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);

(4）编写业务层接口实现类

在service的user包中的UserServiceImpl类中添加以下代码

@Override
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
    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.getConnection();
        userList = userDao.getUserList(connection, queryUserName, queryUserRole, currentPageNo, pageSize);
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        BaseDao.closeResource(connection,null,null);
    }
    return userList;
}

测试

@Test
public void test(){
    UserServiceImpl userService = new UserServiceImpl();
    List<User> i = userService.getUserList(null, 2, 1, 3);
    System.out.println(i.toString());
}

4.3、获取角色操作

为了我们职责统一，可以把角色的操作单独放在一个包中，和POJO类对应

（1）编写接口RoleDao

/**
 * 获取角色列表
 * @param connection
 * @return
 * @throws SQLException
 */
public List<Role> getRoleList(Connection connection)throws SQLException;

（2）RoleDao接口实现类RoleDaoImpl

向实现类中添加以下代码

public class RoleDaoImpl implements RoleDao{
    @Override
    public List<Role> getRoleList(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet re = null;
        List<Role> roleList = new ArrayList<Role>();

        if(connection != null){
            String sql = "select * from smbms_role";
            Object[] params = {};
            re = BaseDao.execute(connection,pstm,re,sql,params);

            while (re.next()){
                Role _role = new Role();
                _role.setId(re.getInt("id"));
                _role.setRoleCode(re.getString("roleCode"));
                _role.setRoleName(re.getString("roleName"));
                roleList.add(_role);
            }
            BaseDao.closeResource(null,pstm,re);
        }
        return roleList;
    }
}

（3）编写业务层接口

在service建立role包，然后建立RoleService接口，添加如下代码

    /**
     * 获取角色列表
     * @return
     */
    public List<Role> getRoleList();

（4）编写业务层接口实现类

在service的role包中的RoleServiceImpl类中添加以下代码

public class RoleServiceImpl implements RoleService {
    private RoleDao roleDao;
    public RoleServiceImpl(){
        roleDao = new RoleDaoImpl();
    }
    @Override
    public List<Role> getRoleList() {
        Connection connection = null;
        List<Role> roleList = null;

        try {
            connection = BaseDao.getConnection();
            roleList = roleDao.getRoleList(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return roleList;
    }
}

测试

@Test
public void test(){
    RoleServiceImpl roleService = new RoleServiceImpl();
    List<Role> roleList = roleService.getRoleList();
    for (Role role : roleList) {
        System.out.println(role.getRoleName());
    }
}

4.4、编写用户显示的Servlet类

1、获取用户前端的数据（查询）
2、判断请求是否需要执行，看参数的值判断
3、为了实现分页，需要计算出当前页面和总页面，页面大小。。。
4、用户列表展示
5、返回前端

对Servlet包中的user包中UserServlet类进行如下添加

private void query(HttpServletRequest req, HttpServletResponse resp) {
    //查询用户列表

    //从前端获取数据
    String queryUsername = req.getParameter("queryname");
    String temp = req.getParameter("queryUserRole");
    String pageIndex = req.getParameter("pageIndex");
    int queryUserRole = 0;

    //获取用户列表
    UserServiceImpl userService = new UserServiceImpl();
    List<User> userList = null;

    //第一次走这个请求，一定是第一页，页面大小固定的
    // 可以把这些到配置文件中，方便后期修改;


    int currentPageNo = 1;
    if (queryUsername == null) {
        queryUsername = "";
    }
    if (temp != null && !temp.equals("")) {
        // 给查询赋值！0,1,2,3
        queryUserRole = Integer.parseInt(temp);
    }
    if (pageIndex != null) {
        currentPageNo = Integer.parseInt(pageIndex);
    }

    //获取用户的总数（分页： 上一页，下一页的情况—）
    int totalCount = userService.getUserCount(queryUsername, queryUserRole);
    //总页数支持
    PageSupport pageSupport = new PageSupport();
    pageSupport.setCurrentPageNo(currentPageNo);
    pageSupport.setPageSize(pageSize);
    pageSupport.setTotalCount(totalCount);

    int totalPageCount = ((int) (totalCount / pageSize)) + 1;

    //控制首页和尾页
    //如果页面要小于1了，就显示第一页的东西
    if (currentPageNo < 1) {
        currentPageNo = 1;
    } else if (currentPageNo > totalPageCount) {
        // 当前页面大于最后一页；
        currentPageNo = totalPageCount;
    }

    //获取用户列表展示
    userList = userService.getUserList(queryUsername, queryUserRole, currentPageNo, pageSize);
    req.setAttribute("userList", userList);

    RoleServiceImpl roleService = new RoleServiceImpl();
    List<Role> roleList = roleService.getRoleList();
    req.setAttribute("roleList", roleList);
    req.setAttribute("totalCount", totalCount);
    req.setAttribute("currentPageNo", currentPageNo);
    req.setAttribute("totalPageCount", totalPageCount);
    req.setAttribute("queryUsername", queryUsername);
    req.setAttribute("queryUserRole", queryUserRole);

    //返回前端
    try {
        req.getRequestDispatcher("userlist.jsp").forward(req, resp);
    } catch (ServletException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

posted @ 2021-07-14 17:41 拒绝平庸* 阅读(157) 评论(0) 编辑收藏举报

刷新页面返回顶部

拒绝平庸

SMBMS

SMBMS

1、项目搭建（准备工作）

1.1、 搭建一个Maven web项目

1. 2、配置Tomcat

1.3、 确定项目是否可以跑起来

1.4、 导入项目中会遇到的jar包： jsp、servlet、mysql、jstl、stand

1.5、 创建项目结构

1.6、 编写实体类：

1.7、 编写基础公共类

1.8、导入静态资源

2、登入功能实现

2.1、登入

（1）编写前端代码

（2）将前端页面设置为首页

（3）编写dao层用户登录的接口UserDao

（4）编写UserDao接口的实现类UserDaoImpl

（5）编写业务层接口

（6）编写业务层接口的实现类

（7）编写Servlet类

（8）导入frame.jsp、foot.jsp、head.jsp

2.2、注销

2.3、登录拦截

3、密码修改的实现

3.1、编写dao层用户修改密码的接口UserDao

3.2、编写UserDao 接口实现类UserDaoImpl

3.3、编写业务层接口

3.4、编写业务层接口实现类

3.5、编写Servlet类

4、用户管理实现

4.1、获取用户数量

4.2、获取用户列表

4.3、获取角色操作

4.4、编写用户显示的Servlet类

公告

1.1、搭建一个Maven web项目

1.3、确定项目是否可以跑起来

1.4、导入项目中会遇到的jar包： jsp、servlet、mysql、jstl、stand

1.5、创建项目结构

1.6、编写实体类：

1.7、编写基础公共类