一、介绍
在掌握了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 {