04-JavaWeb-使用Servlet+JSP+JDBC实现CRUD

一、介绍

在掌握了JavaAPI ，HTML，Servlet，JSP、JDBC等知识后，咱们利用这些知识点，打通从前端页面到后端Java，再到数据库的各个环节，通过实现一个员工的CRUD来巩固一下相关知识。
项目架构如下：

实现功能如下：

• 员工列表 和 员工删除
  

• 员工新增
  

• 员工修改
  

二、项目创建

创建JavaWeb项目，并在lib中导入相关jar包依赖，这里页面样式我用到了bootstrap。

三、员工表和实体类

3.1 员工表

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '员工编号，主键',
  `name` varchar(50) DEFAULT NULL COMMENT '员工姓名',
  `age` int(11) DEFAULT NULL COMMENT '员工年龄',
  `sex` int(1) DEFAULT NULL COMMENT '性别：1表示男，0表示女',
  `phone` varchar(50) DEFAULT NULL COMMENT '员工电话',
  `address` varchar(50) DEFAULT NULL COMMENT '住址',
  `createdate` datetime DEFAULT NULL COMMENT '新建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `employee` VALUES (1, '张三', 30, 1, '13455556666', '成都市锦江区xx路1号', '2022-07-29 14:45:04');
INSERT INTO `employee` VALUES (2, '李四', 31, 1, '13566667777', '北京市丰台区xx路2号', '2022-07-29 14:45:07');
INSERT INTO `employee` VALUES (3, '王花', 18, 0, '13588889999', 'xx市xx区xx路3号', '2022-07-29 14:45:09');

3.2 员工实体类

public class Employee {
   
    private Long id;
    private String name;
    private Integer age;
    private Integer sex;
    private String phone;
    private String address;
    private Date createdate;

    public Long getId() {
   
        return id;
    }
    public void setId(Long id) {
   
        this.id = id;
    }
    public String getName() {
   
        return name;
    }
    public void setName(String name) {
   
        this.name = name;
    }
    public Integer getAge() {
   
        return age;
    }
    public void setAge(Integer age) {
   
        this.age = age;
    }
    public Integer getSex() {
   
        return sex;
    }
    public void setSex(Integer sex) {
   
        this.sex = sex;
    }
    public String getPhone() {
   
        return phone;
    }
    public void setPhone(String phone) {
   
        this.phone = phone;
    }
    public String getAddress() {
   
        return address;
    }
    public void setAddress(String address) {
   
        this.address = address;
    }
    public Date getCreatedate() {
   
        return createdate;
    }
    public void setCreatedate(Date createdate) {
   
        this.createdate = createdate;
    }
}

3.3 JDBC工具类

public class JDBCUtils {
   
    static {
   
        try {
   
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
   
            e.printStackTrace();
        }
    }
    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
   
        try {
   
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/库名?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai",
                    "用户名",
                    "密码");
            return conn;
        } catch (SQLException e) {
   
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 释放资源
     * @param conn 连接对接
     * @param ps   预编译语句对象
     * @param rs   结果集
     */
    public static void close(Connection conn,PreparedStatement ps,ResultSet rs){
   
        if(conn!=null){
   
            try {
   
                conn.close();
            } catch (SQLException e) {
   
                e.printStackTrace();
            }
        }
        if(ps!=null){
   
            try {
   
                ps.close();
            } catch (SQLException e) {
   
                e.printStackTrace();
            }
        }
        if(rs!=null){
   
            try {
   
                rs.close();
            } catch (SQLException e) {
   
                e.printStackTrace();
            }
        }

    }
}

四、员工列表实现

4.1 持久层

接口：cn.emp.dao.IEmployeeDao

public interface IEmployeeDao{
   
    /**
     * 查询所有员工数据
     * @return 员工列表
     */
    List<Employee> selectAll();
}

实现类：cn.emp.dao.impl.EmployeeDaoImpl

public class EmployeeDaoImpl implements IEmployeeDao {
   
    @Override
    public List<Employee> selectAll() {
   
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
   
            conn = JDBCUtils.getConnection();
            //创建预编译对象并编写sql
            ps = conn.prepareStatement("select * from employee");
            //执行查询
            rs = ps.executeQuery();
            //遍历结果集
            List<Employee> employees = new ArrayList<>();
            while (rs.next()){
   
                Employee employee = new Employee();
                employee.setId(rs.getLong("id"));
                employee.setName(rs.getString("name"));
                employee.setAge(rs.getInt("age"));
                employee.setSex(rs.getInt("sex"));
                employee.setPhone(rs.getString("phone"));
                employee.setAddress(rs.getString("address"));
                employee.setCreatedate(rs.getTimestamp("createdate"));
                employees.add(employee);
            }
            return employees;
        } catch (SQLException e) {
   
            e.printStackTrace();
        } finally {
   
            JDBCUtils.close(conn,ps,rs);
        }
        return null;
    }
}

4.2 业务层

接口：cn.emp.service.IEmployeeService

public interface IEmployeeService {
   
    /**
     * 查询所有员工数据
     * @return 员工列表
     */
    List<Employee> selectAll();
}

实现类：cn.emp.service.impl.EmployeeServiceImpl

public class EmployeeServiceImpl implements IEmployeeService {
   

    private IEmployeeDao employeeDao = new EmployeeDaoImpl();

    @Override
    public List<Employee> selectAll() {
   
        return employeeDao.selectAll();
    }
}

4.3 控制器实现

cn.emp.controller.EmployeeController

@WebServlet("/emp/*")
public class EmployeeController extends HttpServlet {
   

    private IEmployeeService employeeService = new EmployeeServiceImpl();

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