2-37.1 EmpProject综合案例

EmpProject综合案例

案例阶段一：环境搭建

1.创建数据库

create databas emp;

2.需要两张表

emp员工信息表和empmanager管理员表

create table emp(
    id int PRIMARY KEY  AUTO_INCREMENT,
    name varchar(20) not null,
    salary double not null,
    age int not null
);
create table  empmanager(
    username varchar(20) not null,
    password varchar(20) not null
);

3.插入一些测试数据

insert into  emp(name, salary, age)value('tom',2000,16);
insert into  emp(name, salary, age)value('jerry',2500,14);
insert into  emp(name, salary, age)value('dog',1900,18);

insert into  empmanager(username, password) value ('admin','123456')

4.创建Web项目分层目录

com.qf.emp.controller 调用业务逻辑Servlet

com.qf.emp.jsp 打印显示页面Servlet

com.qf.emp.dao 数据访问层

com.qf.emp.dao.impl 数据访问层实现类

com.qf.emp.entity 实体类

com.qf.emp.filter 过滤器类

com.qf.emp.service 业务逻辑层

com.qf.emp.service.impl 业务逻辑层实现类

com.qf.emp.utils 工具类

database.properties 数据库连接池配置文件

在WEB-INF目录下创建lib包，导入要使用jar

commons-dbutils-1.7.jar

druid-1.1.5.jar

mysql-connector-java-5.1.25-bin.jar

ValidateCode.jar

编写database.properties数据库资源文件

#jdbc连接驱动配置
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/emp
username=root
password=123456
#连接池配置 【连接池初始容量，最大连接数量， 最小空闲连接， 最大等待时长】
initialSize=10
maxActive=20
minIdle=5
maxWait=3000

案例阶段二：代码编写

1.编写DBUtils工具类

ps：utils包下

package com.qf.emp.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 做一个数据库连接工具类
 */
public class DbUtils {
    private DbUtils(){}
    //数据库连接池对象
    private  static DruidDataSource ds;
    //事务的控制 ThreadLocal
     private  static  final  ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();

     //使用静态代码块对文件进行进行加载
    static {
        //1.创建properties文件
         Properties properties = new Properties();
         //创建字节流对象读取数据【建议通过反射】
         InputStream resourceAsStream = DbUtils.class.getResourceAsStream("/database.properties");
         //加载文件
         try {
             properties.load(resourceAsStream);
             //获取连接池对象
             ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
         } catch (IOException e) {
             e.printStackTrace();
         } catch (Exception e) {
             e.printStackTrace();
         }

     }

    /**
     * 获取Connection对象
     */
    public static Connection getConnection(){
        //1.通过ThreadLocal来获取Connection对象
        Connection connection = THREAD_LOCAL.get();
        try {
            if(connection == null){
                //通过连接池获获取Connection对象
                connection = ds.getConnection();
                //存储到ThradLocal中
                THREAD_LOCAL.set(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  connection;
    }
    /**
     * 开启事务
     *
     */
    public static void begin(){
        Connection connection = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 提交事务
     */
    public static  void commit(){
        Connection connection = null;
        try {
            connection = getConnection();
           connection.commit();//提交
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(connection,null,null);
        }
    }
    /**
     * 回滚
      */ 
    public static void rollback(){
        Connection connection = null;
        try {
            connection = getConnection();
            connection.rollback();//回滚
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(connection,null,null);
        }
    }
    /**
     * 统一资源释放
     */
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
           try{
               if(resultSet != null){
                   resultSet.close();
               }
               if(statement != null){
                   statement.close();
               }
               if(connection != null){
                   connection.close();
                   THREAD_LOCAL.remove();
               }
               
           }catch (Exception e){
               e.printStackTrace();
           }
    }

}

2.实现具体功能

2.1实现管理员登录功能

2.1.1在entity包下创建实体类EmpManager类

package com.qf.emp.entity;

public class EmpManager {
    //因为要做实体映射[ORM],所以类名和属性名尽量和表名和列名一致
    private String username;
    private String password;
    public EmpManager() {
    }
    public EmpManager(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "EmpManager{" +
                "username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

2.2编写dao数据层

2.2.1编写EmpManagerDao接口,此接口完成登录查询

package com.qf.emp.dao;

import com.qf.emp.entity.EmpManager;

//管理员登录接口
public interface EmpManagerDao {
    //查询用户名
    public EmpManager select(String name);
}

2.2.2编写接口实现类EmpManagerDaoImpl

package com.qf.emp.dao.impl;

import com.qf.emp.dao.EmpManagerDao;
import com.qf.emp.entity.EmpManager;
import com.qf.emp.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.SQLException;

public class EmpManagerDaoImpl implements EmpManagerDao {
    //通过DBUtil类中提供的QueryRunner对象来进行查询
    private QueryRunner queryRunner = new QueryRunner();
    @Override
    public EmpManager select(String name) {
        try {
            EmpManager empManager = queryRunner.query
                    (DbUtils.getConnection(),
                            "select * from empmanager where username = ?",
                            new BeanHandler<EmpManager>(EmpManager.class), name);
            return empManager;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

2.3service业务层实现

2.3.1 创建EmpManagerService接口提供登录方法

package com.qf.emp.service;

import com.qf.emp.entity.EmpManager;

public interface EmpManagerService {
    //登录方法
     public EmpManager login(String username,String password);
}

2.3.2实现EmpManagerService接口EmpManagerServiceImpl

package com.qf.emp.service.impl;

import com.qf.emp.dao.EmpManagerDao;
import com.qf.emp.dao.impl.EmpManagerDaoImpl;
import com.qf.emp.entity.EmpManager;
import com.qf.emp.service.EmpManagerService;
import com.qf.emp.utils.DbUtils;

import java.util.Objects;

public class EmpManagerServiceImpl implements EmpManagerService {
      //获取访问数据层的对象
    private EmpManagerDao empManagerDao = new EmpManagerDaoImpl();
    @Override
    public EmpManager login(String username, String password) {
        //定义一个变量用来存储查找到的EmpManager对象
        EmpManager em = null;

        try {
            DbUtils.begin();
            //通过数据访问层对象获取EmpManager对象
            EmpManager empManager = empManagerDao.select(username);
            //判断对象是否存在并判断密码是否正确
            if(Objects.nonNull(empManager)){
                if(empManager.getPassword().equals(password)){
                    em = empManager;
                }
            }
            DbUtils.commit();
        } catch (Exception e) {
           //回滚
            DbUtils.rollback();
            e.printStackTrace();
        }
        return  em;
    }
}

2.4对登录Servlet进行处理

2.4.1在Controller包下,创建EmpManagerLoginController

package com.qf.emp.controller;

import com.qf.emp.entity.EmpManager;
import com.qf.emp.service.EmpManagerService;
import com.qf.emp.service.impl.EmpManagerServiceImpl;

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 javax.servlet.http.HttpSession;
import java.io.IOException;
//这里这样写路径的目的是为了以后区分操作,即管理员都写manager路径
@WebServlet(name = "EmpManagerLoginController",value="/manager/EmpManagerLoginController")
public class EmpManagerLoginController extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          //1.收参数
        String  username = request.getParameter("username");
        String password = request.getParameter("password");
        String inputVCode = request.getParameter("inputVCode");
        //2.校验验证码的值
        String codes = (String)request.getSession().getAttribute("codes");
        if(!inputVCode.isEmpty() && inputVCode.equalsIgnoreCase(codes)){
            //此时就开始处理业务逻辑
            EmpManagerService empManagerService = new EmpManagerServiceImpl();
            EmpManager empManager = empManagerService.login(username, password);
            if(empManager != null){
                //登录成功,存当前对象到Session作用域中,以便后续使用
                HttpSession session = request.getSession();
                session.setAttribute("empManager",empManager);
                 //跳转到查询所有的信息的界面
                
            }else{
                //登录失败重定向到登录界面
                response.sendRedirect(request.getContextPath()+"/login.html");
            }
            
        }else{
            //验证码错误重定向到登录界面
            response.sendRedirect(request.getContextPath()+"/login.html"); 
        }
    }
}

2.4.2在Controller包下,创建创建验证码的Servlet

package com.qf.emp.controller;

import cn.dsna.util.images.ValidateCode;

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 javax.servlet.http.HttpSession;
import java.io.IOException;

@WebServlet(name = "CreateCodeServlet",value="/createCode")
public class CreateCodeServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            //生成验证码
        ValidateCode vc = new ValidateCode(200,30,4,10);
        //获取生成验证码的值
        String code = vc.getCode();
        //存储到Session让EmpManagerLoginController使用
        HttpSession session = request.getSession();
        session.setAttribute("codes",code);
        //2.响应客户端
        vc.write(response.getOutputStream());
    }
}

2.4.3在Controller包下,创建查询所有结果的Servlet

package com.qf.emp.controller;

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;

@WebServlet(name = "ShowAllEmpController",value="/manager/safe/showAllEmpController")
public class ShowAllEmpController extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            //先做一个提示,后续补全
        System.out.println("登录成功,查询所有!");
    }
}

回头在EmpManagerLoginController类中补全跳转

 //跳转到查询所有的信息的界面
response.sendRedirect(request.getContextPath()+"/manager/safe/showAllEmpController");

2.5.完成登录界面进行验证

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>登录界面</title>
</head>
<body>
<div>
    <form action="/empproject/manager/EmpManagerLoginController" method="post">
        用户名:<input type="text" name="username"/><br/>
        密码:<input type="password" name="password"/><br/>
        验证码:<input type="text" name="inputVcode"/><img src="//empproject/createCode/createCode"><br/>
        <input type="submit" value = "登录">
    </form>
</div>

</body>
</html>

3.1查询所有员工功能

3.1.1在entity包下创建emp实体类进行使用

package com.qf.emp.entity;
//emp实体类
public class Emp {
    private int id;
    private String name;
    private double salary;
    private int age;
    public Emp() {

    }
    public Emp(int id, String name, double salary, int age) {
        this.id = id;
        this.name = name;
        this.salary = salary;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String  toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", age=" + age +
                '}';
    }
}

3.2在Dao包下创建EmpDao 进行查询方法编写

package com.qf.emp.dao;

import com.qf.emp.entity.Emp;

import java.util.List;

public interface EmpDao {
    public List<Emp> selectAll();
}

3.3在编写EmpDao的实现类EmpDaoImpl

package com.qf.emp.dao.impl;

import com.qf.emp.dao.EmpDao;
import com.qf.emp.entity.Emp;

import com.qf.emp.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class EmpDaoImpl implements EmpDao {
    private QueryRunner queryRunner = new QueryRunner();
    @Override
    public List<Emp> selectAll() {
        try {
            List<Emp> emps = queryRunner.query(DbUtils.getConnection(), "select * from emp", new BeanListHandler<Emp>(Emp.class));
            return  emps;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

3.3编写业务层逻辑处理查询emp业务,创建EmpService

package com.qf.emp.service;

import com.qf.emp.entity.Emp;

import java.util.List;

public interface EmpService {
      public List<Emp> showAllEmp();
}

3.4编写EmpService的实现EmpServiceImpl

package com.qf.emp.service.impl;

import com.qf.emp.dao.EmpDao;
import com.qf.emp.dao.impl.EmpDaoImpl;
import com.qf.emp.entity.Emp;
import com.qf.emp.service.EmpService;
import com.qf.emp.utils.DbUtils;
import jdk.nashorn.internal.ir.CallNode;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class EmpServiceImpl implements EmpService {
     private EmpDao empDao = new EmpDaoImpl();
    @Override
    public List<Emp> showAllEmp() {
        List<Emp> emps = new ArrayList<>();
        try {
            DbUtils.begin();
            List<Emp> temps = empDao.selectAll();
            if (Objects.nonNull(temps)) {
                emps = temps;
            }
            DbUtils.commit();
        } catch (Exception e) {
            DbUtils.rollback();
            e.printStackTrace();
        }
        return  emps;

    }
}

3.5修改Controller包下ShowAllEmpController逻辑

package com.qf.emp.controller;

import com.qf.emp.entity.Emp;
import com.qf.emp.service.EmpService;
import com.qf.emp.service.impl.EmpServiceImpl;

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.util.List;

@WebServlet(name = "ShowAllEmpController",value="/manager/safe/showAllEmpController")
public class ShowAllEmpController extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        EmpService empService = new EmpServiceImpl();
        List<Emp> list = empService.showAllEmp();
        request.setAttribute("emps",list);
        //通过请求转换,将数据转发另外一个Servlet进行处理[页面Servlet]
        request.getRequestDispatcher("/manager/safe/showAllEmpJSP").forward(request,response);
    }
}

ps:只有登录之后才对后台进行访问,所以我们需要对当前访问进行权限限制

3.6在Filter包创建Filter类进行权限限制

package com.qf.emp.filter;

import com.qf.emp.entity.EmpManager;

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.Objects;

@WebFilter(value="/manager/safe/*")
public class CheckFilter implements Filter {
    public void destroy() {
    }

    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws ServletException, IOException {
        HttpServletRequest request = (HttpServletRequest) req;
        HttpServletResponse response = (HttpServletResponse)resp;
        //通过Session获取存储的登录对象
        HttpSession session = request.getSession();
        EmpManager empManager = (EmpManager)session.getAttribute("empManager");
        if(Objects.nonNull(empManager)){//登录过
            //就传递
            chain.doFilter(req, resp);
            
        }else{
            response.sendRedirect(request.getContextPath()+"/login.html");
        }

        
    }

    public void init(FilterConfig config) throws ServletException {

    }

}

在当前报下提供编码统一处理

package com.qf.emp.filter;

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

@WebFilter(value="/manager/*")
public class EncodingFilter implements Filter {
    public void destroy() {
    }

    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");
        chain.doFilter(req, resp);
    }

    public void init(FilterConfig config) throws ServletException {

    }

}

3.7在JSP包下创建显示查询所有结果页面

ps:JSP就是页面版本Servlet

package com.qf.emp.jsp;

import com.qf.emp.entity.Emp;

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.List;

@WebServlet(name = "ShowAllEmpJSP",value ="/manager/safe/showAllEmpJSP")
public class ShowAllEmpJSP extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            //1.全新页面写法
        //1.获取集合数据进行展示
        List<Emp> emps = (List<Emp>)request.getAttribute("emps");
        PrintWriter out = response.getWriter();
        out.println("<html>");
        out.println("   <head>");
        out.println("       <meta charset='UTF-8'>");
        out.println("           <title>查询所有员工信息页面</title>");
        out.println("   </head>");
        out.println("   <body>");
        out.println("       <table border='1'>");
        out.println("        <tr>");
        out.println("           <td>编号</td>");
        out.println("           <td>姓名</td>");
        out.println("           <td>工资</td>");
        out.println("           <td>年龄</td>");
        out.println("           <td colspan='2'>操作</td>");
        out.println("         </tr>");
        for(Emp emp:emps) {
            out.println("               <tr>");
            out.println("               <td>"+emp.getId()+"</td>");
            out.println("               <td>"+emp.getName()+"</td>");
            out.println("               <td>"+emp.getSalary()+"</td>");
            out.println("               <td>"+emp.getAge()+"</td>");
            out.println("               <td>删除</td>");
            out.println("               <td>修改</td>");
            out.println("               </tr>");
        }
        out.println("          </table>");
        out.println("          </body>");
        out.println("          </html>");



    }
}

作业:完成删除和修改操作