样卷：?

开发环境：tomcat9

一、功能要求

举例：请编写一个程序，完成对员工的管理，实现员工列表显示、员工添加功能。

二、具体功能要求及推荐实现步骤

建立一个Web工程，工程名：employ+学号（如：employ20190101101）

2.1.创建数据库employ，数据表：

员工信息表（employee）、部门信息表（department），设置主键自增，并添加不少于3条测试数据，表结构如下：

表名	employee员工信息表
列名	数据类型（精度范围）	空/非空	约束条件	注释
id	int	非空	PK	员工id
name	varchar(30)	非空		员工姓名
phone	varchar(30)	非空		手机号
department_id	Int	非空		部门id

表名	department部门信息表
列名	数据类型（精度范围）	空/非空	约束条件	注释
id	int	非空	PK	部门id
name	varchar(30)	非空		产品名字

2.2.创建实体类Employee、Department

根据业务提供需要的构造方法和setter/getter方法。

/**
 * 部门实体类
 * @author 林辛
 * 2021年12月13日
 */
public class Department {
	
	private Integer id;
	private String name;//部门名称
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	
}

/**
 * 员工实体类
 * @author 林辛
 * 2021年12月13日
 */
public class Employee {
	
	private Integer id;
	private String name;//员工姓名
	private String phone;//员工手机号码
	private Integer departmentId;//员工所属部门Id
	private String departmentName;//员工所属部门名称
//补充getter setter方法}

2.3创建C3p0Util数据连接工具类（考试会提供）

这时可以把相关jar包拷贝到lib目录，如下：直接拷贝传智书城的jar包

src下配置c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///employ</property>
		<property name="user">root</property>
		<property name="password">root</property>
	</default-config> 
</c3p0-config>

?再编写获取数据源的静态公共方法。

/**
 * 数据源工具
 */
public class DataSourceUtils {
	private static DataSource dataSource = new ComboPooledDataSource();
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

	public static DataSource getDataSource() {
		return dataSource;
	}
	/**
	 * 当DBUtils需要手动控制事务时，调用该方法获得一个连接
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		Connection con = tl.get();
		if (con == null) {
			con = dataSource.getConnection();
			tl.set(con);
		}
		return con;
	}
	/**
	 * 开启事务
	 * @throws SQLException
	 */
	public static void startTransaction() throws SQLException {
		Connection con = getConnection();
		if (con != null)
			con.setAutoCommit(false);
	}
	/**
	 * 从ThreadLocal中释放并且关闭Connection,并结束事务
	 * @throws SQLException
	 */
	public static void releaseAndCloseConnection() throws SQLException {
		Connection con = getConnection();
		if (con != null) {
			con.commit();
			tl.remove();
			con.close();
		}
	}
	/**
	 * 事务回滚
	 * @throws SQLException 
	 */
	public static void rollback() throws SQLException {
		Connection con = getConnection();
		if (con != null) {
			con.rollback();
		}
	}
}

2.4.创建EmployeeDAO类

编写添加员工方法 , 获取员工列表方法

public class EmployeeDAO {
	// 添加员工方法
	public void addEmployee(Employee employee) throws SQLException {
		String sql = "insert into employee(name,phone,department_id) values(?,?,?)";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		int row = runner.update(sql, employee.getName(), employee.getPhone(), employee.getDepartmentId());
		if (row == 0) {
			throw new RuntimeException();
		}
	}

	// 获取员工列表
	public List<Employee> findAllEmployees() throws SQLException {
		String sql = "select e.id as id ,e.name as name,e.phone as phone,"
				+ "e.department_id as departmentId,d.name as departmentName "
				+ "from employee e left outer join department d "
				+ "on e.department_id = d.id "
				+ "order by e.id";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		//开启下划线->驼峰转换所用,使用commons-dbutils-1.7.jar时，可以转换
		//BeanProcessor bean = new GenerousBeanProcessor();
		//RowProcessor processor = new BasicRowProcessor(bean);
		//return runner.query(sql, new BeanListHandler<Employee>(Employee.class,processor));
		return runner.query(sql, new BeanListHandler<Employee>(Employee.class));
	}
}

2.5.创建DepartmentDAO

编写获取部门列表的方法。

public class DepartmentDAO {
	
	// 获取所有部门
	public List<Department> findAllDepartments() throws SQLException {
		String sql = "select * from department";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new BeanListHandler<Department>(Department.class));
	}
}

2.6.开发并配置相关Servlet（3个）

实现员工列表查询

/**
 * 处理"/findAllEmployees"请求
 * 通过EmployeeDAO查询出所有员工，并设置请求对象的属性，
 * 最后请求转发给employee_list.jsp
 */
public class FindAllEmployeesServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setCharacterEncoding("utf-8");
		EmployeeDAO dao = new EmployeeDAO();
		try {
			List<Employee> allEmployees = dao.findAllEmployees();
			request.setAttribute("allEmployees", allEmployees);
			request.getRequestDispatcher("/employee_list.jsp").forward(request, response);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

检测web.xml是否配置好了servlet

配置信息如下：注意你们的<servlet-class>的配置跟我的不一样

<servlet>
    <description></description>
    <display-name>FindAllEmployeesServlet</display-name>
    <servlet-name>FindAllEmployeesServlet</servlet-name>
    <servlet-class>cn.itcast.employ20190101101.controller.FindAllEmployeesServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>FindAllEmployeesServlet</servlet-name>
    <url-pattern>/findAllEmployees</url-pattern>
  </servlet-mapping>

实现跳转至添加页面

/**
 * 处理"/toEmployeeAddJSP"请求
 * 先查询出所有的部门信息，然后设置request对象的属性，
 * 最后请求转发给employee_add.jsp
 */
public class ToEmployeeAddJSPServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//查询出所有的部门
		DepartmentDAO dao = new DepartmentDAO();
		try {
			List<Department> allDepartments = dao.findAllDepartments();
			request.setAttribute("allDepartments", allDepartments);
			request.getRequestDispatcher("/employee_add.jsp").forward(request, response);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

<servlet>
    <description></description>
    <display-name>ToEmployeeAddJSPServlet</display-name>
    <servlet-name>ToEmployeeAddJSPServlet</servlet-name>
    <servlet-class>cn.itcast.employ20190101101.controller.ToEmployeeAddJSPServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>ToEmployeeAddJSPServlet</servlet-name>
    <url-pattern>/toEmployeeAddJSP</url-pattern>
  </servlet-mapping>

实现员工添加并跳转至列表界面

/**
 * 处理“/addEmployee”请求
 * 从request对象中获取新员工信息，
 * 通过BeanUtils工具把这些信息封装到员工对象，
 * 然后通过EmployeeDAO添加新员工到数据库，
 * 最后重定向到员工列表页面
 */
public class AddEmployeeServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		Employee employee = new Employee();
		try {
			BeanUtils.populate(employee, request.getParameterMap());
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		EmployeeDAO dao = new EmployeeDAO();
		try {
			dao.addEmployee(employee);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//request.getRequestDispatcher("/index").forward(request, response);
		response.sendRedirect(request.getContextPath() + "/findAllEmployees");
	}

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

}

<servlet>
    <description></description>
    <display-name>AddEmployeeServlet</display-name>
    <servlet-name>AddEmployeeServlet</servlet-name>
    <servlet-class>cn.itcast.employ20190101101.controller.AddEmployeeServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>AddEmployeeServlet</servlet-name>
    <url-pattern>/addEmployee</url-pattern>
  </servlet-mapping>

2.7.开发员工列表页面employee_list.jsp

会使用到标签库，所以下面第三行引入标签库代码不要忘了

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>员工管理</title>
</head>
<body>
	<a href="${pageContext.request.contextPath}/toEmployeeAddJSP">添加员工</a>
	<table width="100%" border="0" cellspacing="0">
		<tr>
			<td bgcolor="#A3E6DF">id</td>
			<td bgcolor="#A3D7E6">姓名</td>
			<td bgcolor="#A3CCE6">电话</td>
			<td bgcolor="#A3CCE6">部门名称</td>

		</tr>
		<c:forEach items="${allEmployees}" var="employee">
			<tr>
				<td class="tableopentd02">${employee.id}</td>
				<td class="tableopentd02">${employee.name }</td>
				<td class="tableopentd02">${employee.phone}</td>
				<td class="tableopentd02">${employee.departmentName}</td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

2.8.员工添加页面（employee_add.jsp）

会使用到标签库，所以下面第三行引入标签库代码不要忘了

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加员工</title>
</head>
<body>
<h3>添加员工</h3>
	<form action="${pageContext.request.contextPath}/addEmployee"
		method="post">
		<table width="70%" >
			<tr>
				<td >姓名：
				<input type="text" name="name" /></td>
				
			</tr>
			<tr>
				<td >手机号：
				<input type="text" name="phone" /></td>
				
			</tr>
			<tr>
				<td >所属部门：
				  <c:forEach items="${allDepartments}" var="department">
			        <input type="radio" name="departmentId" value="${department.id}" >${department.name}
		          </c:forEach>
				</td>
				
			</tr>
			
			<tr>
				<td ><input type="submit" name="添加"/></td>
			</tr>
		</table>
	</form>
</body>
</html>