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"?>


  4.0.0

  com.happy
  SMBMS
  1.0-SNAPSHOT
  war

  SMBMS Maven Webapp
  
  http://www.example.com

  
    UTF-8
    1.7
    1.7
  

  
    
      junit
      junit
      4.11
      test
    
    
      javax.servlet.jsp
      jsp-api
      2.1
    
    
      javax.servlet
      servlet-api
      2.5
    
    
      javax.servlet
      jstl
      1.2
    
    
      taglibs
      standard
      1.1.2

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

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 设置欢迎页

一般为登陆界面


    login.jsp

6 设置session超时



    30

四、登陆注销模块实现

4.1 登陆

1 编写前端页面

login.jsp

2 设置首页


    
        /login.jsp

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


    LoginServlet
    com.happy.servlet.LoginServlet



    LoginServlet
    /login.do

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



    LogoutServlet
    com.happy.servlet.LogoutServlet


    LogoutServlet
    /jsp/logout.do

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 注册过滤器



    SysFilter
    com.happy.filter.SysFilter


    SysFilter
    /jsp/*

五、密码修改模块实现

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注册

  
    
        UserServlet
        com.happy.servlet.UserServlet
    
    
        UserServlet
        /jsp/user.do

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依赖



    com.alibaba
    fastjson
    2.0.1

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 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"%>
        

            

                你现在所在的位置是:
                用户管理页面
            

            

           		

					
					 用户名：
					 
					 
					 用户角色：
					 
					 
					 
					 
					 添加用户
				

            

            
            
					

                

                    用户编码
                    用户名称
                    性别
                    年龄
                    电话
                    用户角色
                    操作
                

                   

						
						${user.userCode }
						
						
						${user.userName }
						
						
							
								男
								女
							
						
						
						${user.age}
						
						
						${user.phone}
						
						
							${user.userRoleName}
						
						
						查看
						修改
						删除
						
					

				
			

			
		  	
	          	
	          	
	          	
          	
        

    






    

        
提示

        

            
你确定要删除该用户吗？

            确定
            取消
        

    




<%@include file="/jsp/common/foot.jsp" %>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/userlist.js"></script>
2 分页条rollpage.jsp导入

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 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 getUserList(Connection connection, String username, int roleId,int currentPageNo,int pageSize) throws SQLException;




2 UserDaoImpl


  //    根据用户名和角色查询用户清单
    @Override
    public List getUserList(Connection connection, String username, int roleId,int currentPageNo,int pageSize) throws SQLException {
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List 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 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 getUserList(String username, int roleId,int currentPageNo,int pageSize);



4 UserServiceImpl


 @Override
    public List getUserList(String username, int roleId,int currentPageNo,int pageSize) {
        Connection connection = BaseDao.getConnection();
        List 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 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 getRoleList(Connection connection) throws Exception {
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		List roleList = new ArrayList();

		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 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 getRoleList() {
		Connection connection = null;
		List 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 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 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();
        }
    }


						  
					  
						
							


						

							
							
			Web基础							
						



						

						
						


						

						
					
					

					
					

						
相关

						

				
						
			

																
								

										
51-Web基础-CSS

									
									

								 

        

								

							
							

																
								

										
JavaWeb基础【HTTP】

									
									

								 

        

								

							
							

																
								

										
web基础4-servlet入门3(web的MVC架构)

									
									

								 

        

								

							
							

																
								

										
【分布式技术专题】「LVS负载均衡」全面透析Web基础架构负载均衡LVS机制的原理分析指

									
									

								 

        

								

							
							
            
            
            
                                    
           
  			
						

					

					
				

				

														
					
					

					
									

					
									   
标签

										

										
    
										

									

										
										

									

					

					
				


			
		
		
		
			


						





	

	
一品网 冀ICP备14022925号-6