web基础7-订单管理系统项目实战

web基础7-订单管理系统项目实战

一、项目功能

二、项目数据库设计

创建数据库

CREATE DATABASE `smbms`CHARACTER SET utf8 COLLATE utf8_general_ci; 
SHOW DATABASES; 
USE `smbms`; 

三、项目搭建准备工作

3.1 搭建一个Maven web项目

3.2 配置tomcat

3.3 测试项目是否能够跑起来

3.4 导入项目中会遇到的jar包

jsp，servlet，mysql驱动，jstl，standard

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.happy</groupId>
  <artifactId>SMBMS</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>SMBMS Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>jsp-api</artifactId>
      <version>2.1</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>servlet-api</artifactId>
      <version>2.5</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>
    <dependency>
      <groupId>taglibs</groupId>
      <artifactId>standard</artifactId>
      <version>1.1.2</version>
    </dependency>
  </dependencies>


</project>

3.5 创建项目包结构

3.6 编写实体类

3.7 ORM映射：表-类映射

3.8 编写基础公共类

1 数据库配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf-8
username=root
password=1987518g

2 编写数据库公共类BaseDao

package com.happy.dao;

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

//操作数据库的公共类
public class BaseDao {
    public static String driver=null;
    public static String url=null;
    public static String username=null;
    public static String password=null;

//静态代码块=>类加载的时候就初始化
    static{
//       通过类加载器，读取获取properties文件资源
        InputStream resourceAsStream = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(resourceAsStream);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();

        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败！");
            e.printStackTrace();
        }
    }


    public static Connection getConnection(){
        Connection connection=null;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            System.out.println("获取连接失败！");
            e.printStackTrace();
        }
        return connection;
    }


    public static boolean release(Connection connection, Statement statement, ResultSet resultSet){
        Boolean flag=true;
        if(connection!=null){
            try {
                connection.close();
                connection=null;
            } catch (SQLException e) {
                System.out.println("关闭资源失败");
                flag=false;
                e.printStackTrace();
            }
        }


        if(statement!=null){
            try {
                statement.close();
                statement=null;
            } catch (SQLException e) {
                System.out.println("关闭资源失败");
                flag=false;
                e.printStackTrace();
            }
        }

        if(resultSet!=null){
            try {
                resultSet.close();
                resultSet=null;
            } catch (SQLException e) {
                System.out.println("关闭资源失败");
                flag=false;
                e.printStackTrace();
            }
        }
        return  flag;
    }

    public static ResultSet executeQuery(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet,Object[] objects){
        try {
            for (int i=0;i<objects.length;i++) {
//                setObject的占位符从1开始
                preparedStatement.setObject(i+1,objects[i]);
            }
            resultSet = preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }


    public static int executeUpdate(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet,Object[] objects) throws SQLException {
        int result=0;
        for (int i=0;i<objects.length;i++) {
                preparedStatement.setObject(i+1,objects[i]);
        }
            result= preparedStatement.executeUpdate();
        return result;
    }

}

3 编写字符编码过滤器

package com.happy.filter;

import javax.servlet.*;
import java.io.IOException;

public class CharcterEncodingFilter implements Filter {
    public void init(FilterConfig config) throws ServletException {
    }

    public void destroy() {
    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        chain.doFilter(request, response);
    }
}

4 导入静态资源

5 设置欢迎页

一般为登陆界面

<welcome-file-list>
    <welcome-file>login.jsp</welcome-file>
</welcome-file-list>

6 设置session超时

<!--  默认session过期时间,在真实业务需要-->
<session-config>
    <session-timeout>30</session-timeout>
</session-config>

四、登陆注销模块实现

4.1 登陆

1 编写前端页面

login.jsp

2 设置首页

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

3 编写dao层登陆用户登陆的接口

package com.happy.dao.user;

import com.happy.pojo.User;

import java.sql.Connection;

public interface UserDao {
    //等到要登陆的用户
    public User getLoginUser(Connection connection, String userCode);
}

4 编写dao层接口的实现类

package com.happy.dao.user;

import com.happy.dao.BaseDao;
import com.happy.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoImpl implements UserDao {
    @Override
    public User getLoginUser(Connection connection, String userCode) {
        String sql = "select * from user where usercode=?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Object[] params = {userCode,};
        User user = new User();

        if (connection != null) {
            try {
                resultSet = BaseDao.executeQuery(connection, preparedStatement, resultSet, sql, params);
                if (resultSet.next()) {
                    user = new User();
                    user.setId(resultSet.getInt("id"));
                    user.setUserCode(resultSet.getString("userCode"));
                    user.setUserName(resultSet.getString("userName"));
                    user.setUserPassword(resultSet.getString("userPassword"));
                    user.setGender(resultSet.getInt("gender"));
                    user.setBirthday(resultSet.getDate("birthday"));
                    user.setPhone(resultSet.getString("phone"));
                    user.setAddress(resultSet.getString("address"));
                    user.setUserRole(resultSet.getInt("userRole"));
                    user.setCreateBy(resultSet.getInt("createdBy"));
                    user.setCreateDate(resultSet.getDate("createDate"));
                    user.setModifyBy(resultSet.getInt("modifyBy"));
                    user.setModifyDate(resultSet.getDate("modifyDate"));
                }


            } catch (SQLException e) {
                System.out.println("查询失败！");
                e.printStackTrace();
            } finally {
                //到业务层去关connection，因为有事务可能
                BaseDao.release(null, preparedStatement, resultSet);
            }
        }
        return user;
    }
}

5 编写service层的用户登陆接口UserDao

package com.happy.service.user;

import com.happy.pojo.User;

public interface UserService {
    public User login(String userCode,String password);
}

6 编写service层接口的实现类UserDaoImpl

package com.happy.service.user;

import com.happy.dao.BaseDao;
import com.happy.dao.user.UserDao;
import com.happy.dao.user.UserDaoImpl;
import com.happy.pojo.User;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;

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;
        connection = BaseDao.getConnection();
        User loginUser = null;
//        通过业务层调用数据库dao层
        try {
            loginUser = userDao.getLoginUser(connection, userCode);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.release(connection, null, null);
        }
        return loginUser;
    }

    @Test
    public void testLogin(){
        User happy = login("sunlei", "123456");
        if(happy!=null){
            System.out.println(happy.getUserName()+"|"+happy.getAge());

        }else{
            System.out.println("no user!");
        }
    }
}

7 编写控制层servlet

package com.happy.servlet;

import com.happy.pojo.User;
import com.happy.service.user.UserService;
import com.happy.service.user.UserServiceImpl;
import com.happy.utils.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 LoginServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//        servlet控制层调用service代码
        System.out.println("进入LoginServlet处理...");
        String userCode = req.getParameter("userCode");
        String userPassword = req.getParameter("userPassword");

//        和数据库中的密码进行对比
        UserService userService = new UserServiceImpl();
        User user = userService.login(userCode, userPassword);
        if(user!=null){
//            查有此人
            req.getSession().setAttribute(Constants.USER_SESSION,user);
//            登陆成功后，跳转到主页
            resp.sendRedirect(req.getContextPath()+"/jsp/frame.jsp");
        }else {
//            查无此人
            req.setAttribute("error","用户名或者密码错误");
            req.getRequestDispatcher("/login.jsp").forward(req,resp);
//            resp.sendRedirect(req.getContextPath()+"/login.jsp");
        }

    }

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

8 注册servlet

<servlet>
    <servlet-name>LoginServlet</servlet-name>
    <servlet-class>com.happy.servlet.LoginServlet</servlet-class>
</servlet>

<servlet-mapping>
    <servlet-name>LoginServlet</servlet-name>
    <url-pattern>/login.do</url-pattern>
</servlet-mapping>

4.2 注销

注销功能：

1. 移除session
2. 返回登陆页面

1 编写logout控制层servlet

package com.happy.servlet;

import com.happy.utils.Constants;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;

public class LogoutServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//        第一步注销session
        HttpSession session = request.getSession();
//        注销session两种方式
//        方式一：invalidate
//        session.invalidate();
//         方式二：从session精确移除相应属性
        session.removeAttribute(Constants.USER_SESSION);

//        第二步，注销后，重定向到登陆页面
        response.sendRedirect(request.getContextPath()+"/login.jsp");
    }

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

2 注册serlvet

<!--退出注销session的servlet-->
<servlet>
    <servlet-name>LogoutServlet</servlet-name>
    <servlet-class>com.happy.servlet.LogoutServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>LogoutServlet</servlet-name>
    <url-pattern>/jsp/logout.do</url-pattern>
</servlet-mapping>

4.3 登陆拦截优化

1 编写过滤器Filter

package com.happy.filter;

import com.happy.service.user.UserService;
import com.happy.utils.Constants;

import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;

public class SysFilter implements Filter {
    public void init(FilterConfig config) throws ServletException {
    }

    public void destroy() {
    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws ServletException, IOException {
//        第1步：强转对象，拿session
        HttpServletRequest req=(HttpServletRequest)request;
        HttpServletResponse resp=(HttpServletResponse)response;
        HttpSession session = req.getSession();
        Object USER_SESSION = session.getAttribute(Constants.USER_SESSION);
//        第2步：如果USER_SESSION不能等于null,则放行
        if(USER_SESSION!=null){
            chain.doFilter(request, response);
        }else{
//            方式一：
//            如果等于空，则重定向login页面,并且最好存一个提示到error，用于login页面展示
            session.setAttribute("error","您还没有登陆，请先登陆");
            resp.sendRedirect(req.getContextPath()+"/login.jsp");
//            方式二：
//            或者做一个error.jsp专门负责重定向处理此类未登录的情况
//            resp.sendRedirect(req.getContextPath()+"/error.jsp");
        }
    }
}

2 注册过滤器

<!--    拦截器2：处理登陆拦截，对未登录的用户实行重定向到登陆页面-->
<filter>
    <filter-name>SysFilter</filter-name>
    <filter-class>com.happy.filter.SysFilter</filter-class>
</filter>
<filter-mapping>
    <filter-name>SysFilter</filter-name>
    <url-pattern>/jsp/*</url-pattern>
</filter-mapping>

五、密码修改模块实现

5.1 密码修改

1 编写jsp页面

导入前端素材

2 增加Dao层接口UserDao相关方法

    public int updatePwd(Connection connection, String pwd, String id) throws SQLException;

3 增加Dao层实现类UserDaoImpl相关方法

 public int updatePwd(Connection connection, String pwd, String id) throws SQLException {
//        Statement在dao层声明和生产，在用完即关闭。而connection放到service层做这些，是因为业务层可能要控制事务
        PreparedStatement preparedStatement = null;
        String sql="update smbms_user set userPassword=? where id=?";
        Object[] prams={pwd,id};
        int result=0;
        if(connection!=null) {
            preparedStatement = connection.prepareStatement(sql);
            result = BaseDao.executeUpdate(connection, preparedStatement, null, null, prams);
        }
        BaseDao.release(null, preparedStatement, null);
        return result;
    }

4 增加service层接口UserService相关方法

//根据用户ID修改密码
//    不需要传connection，connection在这层创建和关闭
    public boolean updatePwd(int id, String pwd);

5 增加service层实现类UserServiceImpl相关方法

@Override
    public boolean updatePwd(int id, String pwd) {
        int result = 0;
        Connection connection = null;
        Boolean flag = false;
        try {
            connection = BaseDao.getConnection();
            result = userDao.updatePwd(connection, id, pwd);
            if (result > 0) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.release(connection, null, null);
        }
        return flag;
    }

6 编写控制层servlet

编写修改密码控制层servlet

public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //        第1步，从session获取id,因为在login的时候把查出来的user信息全部放到session了
        boolean flag = false;
        String pwd = req.getParameter("newpassword");
        HttpSession session = req.getSession();
        Object userObj = session.getAttribute(Constants.USER_SESSION);
        if (userObj != null && !StringUtils.isNullOrEmpty(pwd)) {
            User user = (User) userObj;
            Integer id = user.getId();
//        第2步，从前端传来参数获取password
            UserService userService = new UserServiceImpl();
            flag = userService.updatePwd(id, pwd);
            if (flag) {
                session.setAttribute("message", "用户密码修改成功！请退出，使用新密码登陆。");
                //        修改完密码，有可能会要求重新登陆
//                密码修改成功，移除session，重新登陆
                session.removeAttribute(Constants.USER_SESSION);
            } else {
                session.setAttribute("message", "用户密码修改失败！请退出，使用新密码登陆。");
            }
        }else{
            session.setAttribute("message","新密码输入有问题");
        }
        req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req,resp);
    }

7 servlet注册

  <!--修改密码的servlet-->
    <servlet>
        <servlet-name>UserServlet</servlet-name>
        <servlet-class>com.happy.servlet.UserServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>UserServlet</servlet-name>
        <url-pattern>/jsp/user.do</url-pattern>
    </servlet-mapping>

8 优化：实现servlet复用

需要提取方法updatePwd，再在doGet里调用

public void updatePwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //        第1步，从session获取id,因为在login的时候把查出来的user信息全部放到session了
        boolean flag = false;
        String pwd = req.getParameter("newpassword");
        HttpSession session = req.getSession();
        Object userObj = session.getAttribute(Constants.USER_SESSION);
        if (userObj != null && !StringUtils.isNullOrEmpty(pwd)) {
            User user = (User) userObj;
            Integer id = user.getId();
//        第2步，从前端传来参数获取password
            UserService userService = new UserServiceImpl();
            flag = userService.updatePwd(id, pwd);
            if (flag) {
                session.setAttribute("message", "用户密码修改成功！请退出，使用新密码登陆。");
                //        修改完密码，有可能会要求重新登陆
//                密码修改成功，移除session，重新登陆
                session.removeAttribute(Constants.USER_SESSION);
            } else {
                session.setAttribute("message", "用户密码修改失败！请退出，使用新密码登陆。");
            }
        }else{
            session.setAttribute("message","新密码输入有问题");
        }
        req.getRequestDispatcher("/jsp/pwdmodify.jsp").forward(req,resp);
    }

  protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method=req.getParameter("method");
        if(method!=null&&method.equals("savepwd")){
            this.updatePwd(req,resp);
        }
    }

5.2 优化密码修改：ajax实时后台验证旧密码

实现在页面没有刷新的情况下，实现和后天交互。

1 导入alibaba fastjson依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>2.0.1</version>
</dependency>

2 编写JavaScript实现ajax

var oldpassword = null;
var newpassword = null;
var rnewpassword = null;
var saveBtn = null;

$(function(){
	oldpassword = $("#oldpassword");
	newpassword = $("#newpassword");
	rnewpassword = $("#rnewpassword");
	saveBtn = $("#save");
	
	oldpassword.next().html("*");
	newpassword.next().html("*");
	rnewpassword.next().html("*");
	
	oldpassword.on("blur",function(){
		$.ajax({
			type:"GET",
			url:path+"/jsp/user.do",
			data:{method:"pwdmodify",oldpassword:oldpassword.val()},
			dataType:"json",
			success:function(data){
				if(data.result == "true"){//旧密码正确
					validateTip(oldpassword.next(),{"color":"green"},imgYes,true);
				}else if(data.result == "false"){//旧密码输入不正确
					validateTip(oldpassword.next(),{"color":"red"},imgNo + " 原密码输入不正确",false);
				}else if(data.result == "sessionerror"){//当前用户session过期，请重新登录
					validateTip(oldpassword.next(),{"color":"red"},imgNo + " 当前用户session过期，请重新登录",false);
				}else if(data.result == "error"){//旧密码输入为空
					validateTip(oldpassword.next(),{"color":"red"},imgNo + " 请输入旧密码",false);
				}
			},
			error:function(data){
				//请求出错
				validateTip(oldpassword.next(),{"color":"red"},imgNo + " 请求错误",false);
			}
		});
		
		
	}).on("focus",function(){
		validateTip(oldpassword.next(),{"color":"#666666"},"* 请输入原密码",false);
	});
	
	newpassword.on("focus",function(){
		validateTip(newpassword.next(),{"color":"#666666"},"* 密码长度必须是大于6小于20",false);
	}).on("blur",function(){
		if(newpassword.val() != null && newpassword.val().length > 5
				&& newpassword.val().length < 20 ){
			validateTip(newpassword.next(),{"color":"green"},imgYes,true);
		}else{
			validateTip(newpassword.next(),{"color":"red"},imgNo + " 密码输入不符合规范，请重新输入",false);
		}
	});
	
	
	rnewpassword.on("focus",function(){
		validateTip(rnewpassword.next(),{"color":"#666666"},"* 请输入与上面一致的密码",false);
	}).on("blur",function(){
		if(rnewpassword.val() != null && rnewpassword.val().length > 5
				&& rnewpassword.val().length < 20 && newpassword.val() == rnewpassword.val()){
			validateTip(rnewpassword.next(),{"color":"green"},imgYes,true);
		}else{
			validateTip(rnewpassword.next(),{"color":"red"},imgNo + " 两次密码输入不一致，请重新输入",false);
		}
	});
	
	
	saveBtn.on("click",function(){
		oldpassword.blur();
		newpassword.blur();
		rnewpassword.blur();
		if(oldpassword.attr("validateStatus") == "true"
			&&newpassword.attr("validateStatus") == "true"
			&& rnewpassword.attr("validateStatus") == "true"){
			if(confirm("确定要修改密码？")){
				$("#userForm").submit();
			}
		}
		
	});
});

3 修改后端控制层servlet

 //验证旧密码是否正确
    private void pwdmodify(HttpServletRequest req, HttpServletResponse resp) {
        HttpSession session = req.getSession();
        Object obj = session.getAttribute(Constants.USER_SESSION);
        String oldpassword = req.getParameter("oldpassword");
//        用万能的map封装数据，一切的东西都可以存到map里，不用写对象
//        万能map：结果集
        Map<String, String> resultMap = new HashMap<>();

        //如果obj为空，证明session被删除或者超时了
        if (obj != null) {
            resultMap.put("result", "sessionerror");
        } else if (StringUtils.isNullOrEmpty(oldpassword)) {
//            输入密码为空
            resultMap.put("result", "error");
        } else if (obj != null && !StringUtils.isNullOrEmpty(oldpassword)) {
            User user = (User) obj;
            if (oldpassword.equals(user.getUserPassword())) {
                resultMap.put("result", "true");
            } else {
//                密码输入不正确，和session里不一致
                resultMap.put("result", "false");
            }
        }
//        设置返回为json值
        PrintWriter out = null;
        try {
            String jsonString = null;
            resp.setContentType("application/json");
            out = resp.getWriter();
//            使用阿里巴巴的JSON工具类，转化格式
//            Map:{result=error, result2=success}
//            jsonString:{"result":"error","result2":"success"}
            jsonString = JSONArray.toJSONString(resultMap);
            out.write(jsonString);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }

4 前端浏览器验证

六、用户管理模块实现

6.1 总处理流程示意

一个页面的数据可以从多个数据库查询，甚至多个机器上查mysql，oracle，radius

6.2 准备工作

6.2.1 编写分页工具类

4个属性

• currentPageNo 当前页面
• totalCount 总记录数量
• pageSize 页面数量
• totalPageCount 总页数

package com.happy.utils;

public class PageSupport {
	//当前页码-来自于用户输入
	private int currentPageNo = 1;
	
	//总数量（表）
	private int totalCount = 0;
	
	//页面容量
	private int pageSize = 0;
	
	//总页数-totalCount/pageSize（+1）
	private int totalPageCount = 1;

	public int getCurrentPageNo() {
		return currentPageNo;
	}

	public void setCurrentPageNo(int currentPageNo) {
		if(currentPageNo > 0){
			this.currentPageNo = currentPageNo;
		}
	}

	public int getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(int totalCount) {
		if(totalCount > 0){
			this.totalCount = totalCount;
			//设置总页数
			this.setTotalPageCountByRs();
		}
	}
	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		if(pageSize > 0){
			this.pageSize = pageSize;
		}
	}

	public int getTotalPageCount() {
		return totalPageCount;
	}

	public void setTotalPageCount(int totalPageCount) {
		this.totalPageCount = totalPageCount;
	}
	
	public void setTotalPageCountByRs(){
		if(this.totalCount % this.pageSize == 0){
			this.totalPageCount = this.totalCount / this.pageSize;
		}else if(this.totalCount % this.pageSize > 0){
			this.totalPageCount = this.totalCount / this.pageSize + 1;
		}else{
			this.totalPageCount = 0;
		}
	}
	
}

补充知识点：OOP封装的三大特性

• 继承
• 多台
• 封装
  • 属性私有get、set
  • 在set中限定一些不安全的情况

6.2.2 Jsp页面导入

1 用户列表页面userlist.jsp导入

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@include file="/jsp/common/head.jsp"%>
        <div class="right">
            <div class="location">
                <strong>你现在所在的位置是:</strong>
                <span>用户管理页面</span>
            </div>
            <div class="search">
           		<form method="get" action="${pageContext.request.contextPath }/jsp/user.do">
					<input name="method" value="query" class="input-text" type="hidden">
					 <span>用户名：</span>
					 <input name="queryname" class="input-text"	type="text" value="${queryUserName }">
					 
					 <span>用户角色：</span>
					 <select name="queryUserRole">
						<c:if test="${roleList != null }">
						   <option value="0">--请选择--</option>
						   <c:forEach var="role" items="${roleList}">
						   		<option <c:if test="${role.id == queryUserRole }">selected="selected"</c:if>
						   		value="${role.id}">${role.roleName}</option>
						   </c:forEach>
						</c:if>
	        		</select>
					 
					 <input type="hidden" name="pageIndex" value="1"/>
					 <input	value="查 询" type="submit" id="searchbutton">
					 <a href="${pageContext.request.contextPath}/jsp/useradd.jsp" >添加用户</a>
				</form>
            </div>
            <!--用户-->
            <table class="providerTable" cellpadding="0" cellspacing="0">
                <tr class="firstTr">
                    <th width="10%">用户编码</th>
                    <th width="20%">用户名称</th>
                    <th width="10%">性别</th>
                    <th width="10%">年龄</th>
                    <th width="10%">电话</th>
                    <th width="10%">用户角色</th>
                    <th width="30%">操作</th>
                </tr>
                   <c:forEach var="user" items="${userList }" varStatus="status">
					<tr>
						<td>
						<span>${user.userCode }</span>
						</td>
						<td>
						<span>${user.userName }</span>
						</td>
						<td>
							<span>
								<c:if test="${user.gender==1}">男</c:if>
								<c:if test="${user.gender==2}">女</c:if>
							</span>
						</td>
						<td>
						<span>${user.age}</span>
						</td>
						<td>
						<span>${user.phone}</span>
						</td>
						<td>
							<span>${user.userRoleName}</span>
						</td>
						<td>
						<span><a class="viewUser" href="javascript:;" userid=${user.id } username=${user.userName }><img src="${pageContext.request.contextPath }/images/read.png" alt="查看" title="查看"/></a></span>
						<span><a class="modifyUser" href="javascript:;" userid=${user.id } username=${user.userName }><img src="${pageContext.request.contextPath }/images/xiugai.png" alt="修改" title="修改"/></a></span>
						<span><a class="deleteUser" href="javascript:;" userid=${user.id } username=${user.userName }><img src="${pageContext.request.contextPath }/images/schu.png" alt="删除" title="删除"/></a></span>
						</td>
					</tr>
				</c:forEach>
			</table>
			<input type="hidden" id="totalPageCount" value="${totalPageCount}"/>
		  	<c:import url="rollpage.jsp">
	          	<c:param name="totalCount" value="${totalCount}"/>
	          	<c:param name="currentPageNo" value="${currentPageNo}"/>
	          	<c:param name="totalPageCount" value="${totalPageCount}"/>
          	</c:import>
        </div>
    </section>

<!--点击删除按钮后弹出的页面-->
<div class="zhezhao"></div>
<div class="remove" id="removeUse">
    <div class="removerChid">
        <h2>提示</h2>
        <div class="removeMain">
            <p>你确定要删除该用户吗？</p>
            <a href="#" id="yes">确定</a>
            <a href="#" id="no">取消</a>
        </div>
    </div>
</div>

<%@include file="/jsp/common/foot.jsp" %>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/userlist.js"></script>

2 分页条rollpage.jsp导入

<c:import url="rollpage.jsp">
    <c:param name="totalCount" value="${totalCount}"/>
    <c:param name="currentPageNo" value="${currentPageNo}"/>
    <c:param name="totalPageCount" value="${totalPageCount}"/>
</c:import>

6.3 获取用户数量（分页）

1 UserDao

//查询用户总数,where条件为筛选条件，即删选条件为name和角色，这里参数也有这些。
    public int getUserCount(Connection connection,String username, int roleId) throws SQLException;

2 UserDaoImpl

//    根据用户名和角色查询数量
    @Override
    public int getUserCount(Connection connection, String username, int roleId) throws SQLException {
        Integer count=0;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
//        1. 编写动态sql
//        删选条件都要走动态sql
//        String sql="SELECT COUNT(1) AS COUNT FROM smbms_user u LEFT JOIN smbms_role r ON u.`userRole`=r.`id` WHERE username LIKE ? AND r.id =? ";
//         必须写动态sql，因为无法知道筛选条件有几个
//        基本sql,默认执行
        String sql="SELECT COUNT(1) AS COUNT FROM smbms_user u LEFT JOIN smbms_role r ON u.`userRole`=r.`id` where 1=1" ;
        StringBuilder sb=new StringBuilder(sql);

        //2.编写参数
        List<Object> paramsList = new ArrayList<>();

//        如果筛选条件username不为空，则追加sql
        if(!StringUtils.isNullOrEmpty(username)){
            sb.append(" and u.username LIKE ?");
            paramsList.add("%"+username+"%");
        }
        if(roleId>0){
            sb.append(" and u.userRole=? ");
            paramsList.add(roleId);
        }
        sql=sb.toString();
        System.out.println("【UserDaoImpl】最终查询动态sql--->"+sql);

        Object[] params = paramsList.toArray();
//        Object[] params2={username,roleId};
        System.out.println("【UserDaoImpl】最终动态sql参数数组--->"+ Arrays.toString(params));

        if (connection!=null){
            preparedStatement = connection.prepareStatement(sql);
//            不用传connection后面用不上了，不用传sql到basedao，因为prepareStatement已经封装了
            resultSet = BaseDao.executeQuery(null, preparedStatement, resultSet, null, params);
        }
        if(resultSet.next()){
            count = resultSet.getInt("count");
            System.out.println("【UserDaoImpl】查询count数量："+count);
        }

        BaseDao.release(null,preparedStatement,resultSet);
        return count;
    }

3 UserService

    //根据筛选条件如name和roleid查询总数据量 ，为了做分页
//    不需要传connection，connection在这层创建和关闭
    public int getUserCount(String name, int roleId);

4 UserServiceImpl

 @Override
    public int getUserCount(String username, int roleId) {
        Connection connection=null;
        int userCount=0;
        try {
            connection=BaseDao.getConnection();
            userCount = userDao.getUserCount(connection, username, roleId);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.release(connection,null,null);
        }
        return  userCount;
    }

6.4 获取用户列表

1 UserDao

//    通过条件查询-userlist
    public List<User> getUserList(Connection connection, String username, int roleId,int currentPageNo,int pageSize) throws SQLException;

2 UserDaoImpl

  //    根据用户名和角色查询用户清单
    @Override
    public List<User> getUserList(Connection connection, String username, int roleId,int currentPageNo,int pageSize) throws SQLException {
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List<User> userList=new ArrayList<>();
        int startNo;
//        1.编写动态sql
//        基本sql
        String sql="select * from smbms_user u left join smbms_role r on u.userrole=r.id where 1=1";
//        String sql="select r.*,u.userrole from smbms_user u left join smbms_role r on u.userrole=r.id where 1=1";

        StringBuilder sb=new StringBuilder(sql);
        ArrayList<Object> paramsList = new ArrayList<>();

        if(!StringUtils.isNullOrEmpty(username)){
            sb.append(" and u.username like ?");
            paramsList.add("%"+username+"%");
        }
        if(roleId>0){
            sb.append(" and u.userrole=?");
            paramsList.add(roleId);
        }

//        在数据库中使用limit分页
//        sb.append(" order by u.creationDate desc limit ?,?");
        sb.append(" order by u.id asc limit ?,?");
        startNo=(currentPageNo-1)*pageSize;
        paramsList.add(startNo);
        paramsList.add(pageSize);


        //        准备好sql语句
        sql=sb.toString();
        System.out.println("【UserDaoImpl】最终查询动态sql--->"+sql);
        Object[] params = paramsList.toArray();
        System.out.println("【UserDaoImpl】最终动态sql参数数组--->"+ Arrays.toString(params));

        if(connection!=null){
            preparedStatement = connection.prepareStatement(sql);
            resultSet = BaseDao.executeQuery(null, preparedStatement, resultSet, null, params);
        }

        while(resultSet.next()){
            User user = new User();
            user.setId(resultSet.getInt("u.id"));
            user.setUserCode(resultSet.getString("userCode"));
            user.setUserName(resultSet.getString("userName"));
            user.setUserPassword(resultSet.getString("userPassword"));
            user.setGender(resultSet.getInt("gender"));
            user.setBirthday(resultSet.getDate("birthday"));
            user.setPhone(resultSet.getString("phone"));
            user.setAddress(resultSet.getString("address"));
            user.setUserRole(resultSet.getInt("userRole"));
            user.setCreateBy(resultSet.getInt("createdBy"));
            user.setCreateDate(resultSet.getDate("creationDate"));
            user.setModifyBy(resultSet.getInt("modifyBy"));
            user.setModifyDate(resultSet.getDate("modifyDate"));
            user.setUserRoleName(resultSet.getString("r.rolename"));
            userList.add(user);
        }

3 UserService

    //根据筛选条件如name和roleid查询userList
//    不需要传connection，connection在这层创建和关闭
    public List<User> getUserList(String username, int roleId,int currentPageNo,int pageSize);

4 UserServiceImpl

 @Override
    public List<User> getUserList(String username, int roleId,int currentPageNo,int pageSize) {
        Connection connection = BaseDao.getConnection();
        List<User> userList=null;
        try {
            userList = userDao.getUserList(connection, username, roleId,currentPageNo,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.release(connection, null, null);
        }

        return userList;
    }

6.5 获取角色列表

为了职责统一清楚，可以把角色操作单独放在一个包中，和pojo类对应（每个表对应一个pojo类）

1.RoleDao

package com.happy.dao.role;

import com.happy.pojo.Role;

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

public interface RoleDao {
	
	public List<Role> getRoleList(Connection connection)throws Exception;

}

2 RoleDaoImpl

package com.happy.dao.role;

import com.happy.dao.BaseDao;
import com.happy.pojo.Role;

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

public class RoleDaoImpl implements RoleDao{

	@Override
	public List<Role> getRoleList(Connection connection) throws Exception {
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		List<Role> roleList = new ArrayList<Role>();

		if(connection != null){
			String sql = "select * from smbms_role";
//			准备好数据库操作
			preparedStatement = connection.prepareStatement(sql);
			Object[] params = {};
			rs = BaseDao.executeQuery(null, preparedStatement, rs, null, params);
			while(rs.next()){
				Role _role = new Role();
				_role.setId(rs.getInt("id"));
				_role.setRoleCode(rs.getString("roleCode"));
				_role.setRoleName(rs.getString("roleName"));
				roleList.add(_role);
			}
			BaseDao.release(null, preparedStatement, rs);
		}
		
		return roleList;
	}

}

3 RoleService

package com.happy.service.role;

import com.happy.pojo.Role;

import java.util.List;

public interface RoleService {
	
	public List<Role> getRoleList();
	
}

4. RoleServiceImpl

package com.happy.service.role;

import com.happy.dao.BaseDao;
import com.happy.dao.role.RoleDao;
import com.happy.dao.role.RoleDaoImpl;
import com.happy.pojo.Role;

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

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 (Exception e) {
			e.printStackTrace();
		}finally{
			BaseDao.release(connection, null, null);
		}
		return roleList;
	}
	
}

6.6 Servlet增加方法（复用serlvet）

用serlvet组成前面3个子模块

1 获取用户前端的数据（删选条件等）

2 判断请求参数是否需要执行，查看参数的值

3 为了实现分页，需要计算当前页面、总页面、页面大小等

4 用户列表展示

5 展示页面

可以复用之前修改密码的servlet，只需要新增方法即可

和页面查询条件的两个参数对应上

@Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method = req.getParameter("method");
        if (method != null && method.equals("savepwd")) {
            this.updatePwd(req, resp);
        } else if (method != null && method.equals("pwdmodify")) {
            this.pwdmodify(req, resp);
        } else if (method != null && method.equals("query")) {
            this.query(req, resp);
        }
    }

    private void query(HttpServletRequest req, HttpServletResponse resp) {
//        1.查询用户列表
//        从前端获取数据，刚加载页面是空的
        String queryname = req.getParameter("queryname");
        String queryUserRoleString = req.getParameter("queryUserRole");
        String pageIndex = req.getParameter("pageIndex");
//        默认查询条件角色为0
        int queryUserRole=0;
//        如果queryname为null或者空字符串，统一为空字符串
        if(StringUtils.isNullOrEmpty(queryname)){
            queryname="";
        }
        if(!StringUtils.isNullOrEmpty(queryUserRoleString)){
            queryUserRole=Integer.parseInt(queryUserRoleString);
        }
//        第一次走这个servlet，一定是第一页，且页面大小固定
         int pageSize=5; //建议把这个写在配置文件中，方便后期修改
         int currentPageNo=1;
         if(pageIndex!=null){
             currentPageNo=Integer.parseInt(pageIndex);
         }

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

//        获取用户的总数（分页：存在上一页后下一页的情况）
        int userCount = userService.getUserCount(queryname, queryUserRole);
//        总页数支持
        PageSupport pageSupport = new PageSupport();
//        设置当前页码
        pageSupport.setCurrentPageNo(currentPageNo);
        pageSupport.setPageSize(pageSize);
        pageSupport.setTotalCount(userCount);
        int totalPageCount = pageSupport.getTotalPageCount();
//        控制首页和尾页
        if(totalPageCount<1){
            currentPageNo=1;
//            当前页面大于最后一页
        }else if(currentPageNo>totalPageCount){
            currentPageNo=totalPageCount;
        }


        userList = userService.getUserList(queryname, queryUserRole, currentPageNo, pageSize);
//        将userlist存到request域，并在前端通过c：foreach遍历
        req.setAttribute("userList",userList);

        RoleService roleService = new RoleServiceImpl();
        List<Role> roleList = roleService.getRoleList();
        req.setAttribute("roleList",roleList);

//        设置分页数据
        req.setAttribute("totalCount",userCount);
        req.setAttribute("currentPageNo",currentPageNo);
        req.setAttribute("totalPageCount",totalPageCount);

//        设置筛选条件栏值
        req.setAttribute("queryUserName",queryname);
        req.setAttribute("queryUserRole",queryUserRole);

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