前言：因为研究生入学任务需要做一个企业人员管理系统，那就做呗。。。
项目及数据文件下载地址：https://download.csdn.net/download/qq_39410381/11546527
最终效果展示如下图：
管理员登录，界面如下：
管理员输入账号和密码，进入主页面，如下图：
管理员根据需求添加数据，（这里以部门为例，点击信息添加中部门添加），如下图：
添加完信息后，管理员可查看所有数据，（这里以部门为例），点击信息展示中的部门展示，如下图：
管理员可根据需求在每条记录右边更改信息，更改状态。
期间遇到的问题以及解决方案记录如下：
报错：HttpServlet cannot be resolved to a type
解决：将tomcat包下的lib文件下的servlet-api.jar复制到WEB-INF下的lib文件夹中，即可解决报错：插入数据到数据库中，中文会变为问号
解决:在数据库连接配置时,url后面加上 ?useUnicode=true&characterEncoding=UTF-8报错：javax.servlet.jsp.JspException cannot be resolved to a type
解决：在解决了第一个问题基础上，再将tomcat包下的lib文件下的jsp-api.jar复制到WEB-INF下的lib文件夹中，即可解决
第一部分（项目设计&数据库设计）：
数据库设计：
管理员表：编号、账号、密码、状态
部门表：编号、名称、部门经理、描述、状态
职称表：编号、名称、基本工资、房补、职务补助、状态
职工表：编号、姓名、性别、出生日期、籍贯、部门、职称、状态
项目设计：非常简单，就是管理员对职工进行增删改查，职工表有部门表和职称表两个外键
第二部分（环境搭建）：
①下载jdk，我下的是java9，之后就是百度配置环境变量
②下载tomcat，这个最简单，能启动就算成功
③下载ecplise，然后配置jdk和tomcat，有点复杂，耐心百度吧
④下载mysql数据库和管理工具，我用的是5.7，管理工具是workbench
最终能通过ecplise启动一个页面，打出hello world，环境就算成功了
第三部分（干活）：
一、创建数据库
数据库名为 citel_4
建表
CREATE TABLE `citel_4`.`admins` (
`id` INT NOT NULL AUTO_INCREMENT,
`account` VARCHAR(45) NULL,
`password` VARCHAR(45) NULL,
`status` INT NULL,
PRIMARY KEY (`id`));CREATE TABLE `citel_4`.`departments` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
`manager` VARCHAR(45) NULL,
`description` VARCHAR(45) NULL,
`status` INT NULL,
PRIMARY KEY (`id`));CREATE TABLE `citel_4`.`titles` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
`base_wage` VARCHAR(45) NULL,
`room_supply` VARCHAR(45) NULL,
`job_supply` VARCHAR(45) NULL,
`status` INT NULL,
PRIMARY KEY (`id`));CREATE TABLE `citel_4`.`staffs` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
`sex` CHAR(1) NULL,
`birth` VARCHAR(45) NULL,
`home` VARCHAR(45) NULL,
`department` INT NULL,
`title` INT NULL,
`status` INT NULL,
PRIMARY KEY (`id`));添加外键
ALTER TABLE `citel_4`.`staffs`
ADD INDEX `fk_department_idx` (`department` ASC);
;
ALTER TABLE `citel_4`.`staffs`
ADD CONSTRAINT `fk_department`
FOREIGN KEY (`department`)
REFERENCES `citel_4`.`departments` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE `citel_4`.`staffs`
ADD INDEX `fk_title_idx` (`title` ASC);
;
ALTER TABLE `citel_4`.`staffs`
ADD CONSTRAINT `fk_title`
FOREIGN KEY (`title`)
REFERENCES `citel_4`.`titles` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;二、搭建项目
打开ecplise，右键->new->Dynamic Web Project，项目名为InformationManageProject
最终我的项目最终结构如下图：
找模板、改模板
由于目标只是对后台几个表的增删改查，所以找了一个后台管理模板。
由于数据库中没有数据，所有先改数据添加的模板，由于三个表添加类似，以部门表为例述说，如图：
前端展示改好，修改web.xml
<servlet>
<servlet-name>DepartmentServlet</servlet-name>
<servlet-class>com.sky.servlets.DepartmentServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DepartmentServlet</servlet-name>
<url-pattern>*.department</url-pattern>
</servlet-mapping>三、实现逻辑（全部以部门表为例）
3.1 添加数据
因为刚创建数据库，什么数据都没有，所以第一步就是添加数据。
修改前端的添加表单
<form action="<%=request.getContextPath() %>/add.department" method="post">
<input type="hidden" name="operation" value="add">
<div class="form-group">
<label>部门名称</label>
<input type="text" name="name" class="form-control" placeholder="请输入部门名称">
</div>
<div class="form-group">
<label>部门经理</label>
<input type="text" name="manager" class="form-control" placeholder=" 请输入该部门经理">
</div>
<div class="form-group">
<label>部门描述</label>
<input type="text" name="description" class="form-control" placeholder="请描述该部门">
</div>
<div class="checkbox">
<label>
<input type="checkbox"> <a href="#">同意遵守用户协议</a>
</label>
</div>
<button type="submit" class="btn btn-primary btn-flat m-b-30 m-t-30">添加</button>
</form>后端 DepartmentServlet
public class DepartmentServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
DepartmentDao departmentDao = new DepartmentDaoImpl();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("utf-8");
String operation = req.getParameter("operation");
if (operation.equals("query")) {
//test
int id = Integer.parseInt(req.getParameter("id"));
Departments dept = departmentDao.selectOneById(id);
System.out.println("data: " + dept);
}else if (operation.equals("add")) {
String name = req.getParameter("name");
String manager = req.getParameter("manager");
String description = req.getParameter("description");
boolean res = departmentDao.addOneDepartment(new Departments(name, manager, description));
if (res) {
//添加成功
req.getRequestDispatcher("/index.jsp").forward(req, resp);
} else {
//添加失败
req.getRequestDispatcher("/error.jsp").forward(req, resp);
}
}
}
}之后是Dao层（DepartmentDao）及其实现（DepartmentDaoImpl）
public interface DepartmentDao {
//按ID查询部门
Departments selectOneById(Integer id);
//添加部门
boolean addOneDepartment(Departments dept);
}public class DepartmentDaoImpl implements DepartmentDao{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Boolean result = false;
Departments dept = null;
@Override
public Departments selectOneById(Integer id) {
try {
conn = DBUtils.getConnection();
ps = conn.prepareStatement("SELECT * FROM citel_4.departments where id = ?;");
ps.setInt(1, id);
rs = ps.executeQuery();
if(rs.next()){
int _id = rs.getInt(1);
String _name = rs.getString(2);
String _manager = rs.getString(3);
String _description = rs.getString(4);
int _status = rs.getInt(5);
dept = new Departments(_id, _name, _manager, _description, _status);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, ps, conn);
}
return dept;
}
@Override
public boolean addOneDepartment(Departments dept) {
try {
conn = DBUtils.getConnection();
ps = conn.prepareStatement("insert into departments values (0, ?, ?, ?, 1);");
ps.setString(1, dept.getName());
ps.setString(2, dept.getManager());
ps.setString(3, dept.getDescription());
result = ps.executeUpdate()>0?true:false;
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, ps, conn);
}
return result;
}
}最终添加部门效果：
3.2 展示数据
终于有了数据，接下来，当然是展示数据啦。
按照过程，第一个是主页面的部门展示的url，后面带了一个参数，表示查询全部
<li><a class="sidebar-sub-toggle"><i class="ti-layout-grid4-alt"></i> 信息展示 <span class="sidebar-collapse-icon ti-angle-down"></span></a>
<ul>
<li><a href="<%=request.getContextPath() %>/queryAll.department?operation=queryAll">部门展示</a></li>
<li><a href="table-basic.html">职位展示</a></li>
<li><a href="table-basic.html">员工展示</a></li>
</ul>
</li>接下来，处理servlet
public class DepartmentServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
DepartmentDao departmentDao = new DepartmentDaoImpl();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
...//省略,前面写过,后面代码一样
}else if (operation.equals("queryAll")) {
List<Departments> depts = departmentDao.selectAllDepartments();
req.setAttribute("departments", depts);
//请求转发跳转
req.getRequestDispatcher("/show-department.jsp").forward(req, resp);
}
}
}然后是Dao层代码逻辑
public interface DepartmentDao {
...
//查询所有部门
List<Departments> selectAllDepartments();
}
public class DepartmentDaoImpl implements DepartmentDao{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Boolean result = false;
Departments dept = null;
List<Departments> list = null;
...
@Override
public List<Departments> selectAllDepartments() {
try {
conn = DBUtils.getConnection();
ps = conn.prepareStatement("SELECT * FROM departments where status=1 or status=2;");
rs = ps.executeQuery();
list = new ArrayList<Departments>();
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
String manager = rs.getString(3);
String description = rs.getString(4);
int status = rs.getInt(5);
Departments dept = new Departments(id, name, manager, description, status);
list.add(dept);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, ps, conn);
}
return list;
}
}好了，到现在为止，数据获取到了，最后就是把它展示出来，所以修改部门展示页面，这里不用前面，使用EL表达式，先导入了jstl的jar包，然后构建路径，最后在页面头部写上 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> 就可以了。展示页面代码如下:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="java.util.List"%>
<%@page import="com.sky.pojo.Departments"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html lang="en">
...
<table class="table table-hover ">
<thead>
<tr>
<th>#</th>
<th>名称</th>
<th>经理</th>
<th>描述</th>
<th>状态</th>
<th>操作</th>
</tr>
</thead>
<tbody>
<c:forEach items="${departments}" var="department">
<tr>
<td scope="row">${department.id}</td>
<td>${department.name}</td>
<td>${department.manager}</td>
<td>${department.description}</td>
<c:if test="${department.status eq '1' }">
<td><span class="badge badge-success">在用</span></td>
<td>停用</td>
</c:if>
<c:if test="${department.status eq '2' }">
<td><span class="badge badge-danger">停用</span></td>
<td>启用</td>
</c:if>
</tr>
</c:forEach>
</tbody>
</table>
...
最终效果图：
3.3 修改数据
要想修改数据，首先出现的问题就是回显已有数据，当然在这之前，得在每个数据加上修改按钮，如下：
这里在每个链接上接上了对应的id，依然使用的是EL表达式
<c:if test="${department.status eq '1' }">
<td><span class="badge badge-success">在用</span></td>
<td>
<a href="${pageContext.request.contextPath}/download.department?operation=download&id=${department.id}">停用</a>/
<a href="${pageContext.request.contextPath}/update.department?operation=showOne&id=${department.id}">修改</a>/
<a href=" ${pageContext.request.contextPath}/delete.department?operation=delete&id=${department.id}">删除</a>
</td>
</c:if>
<c:if test="${department.status eq '2' }">
<td><span class="badge badge-danger">停用</span></td>
<td>
<a href="${pageContext.request.contextPath}/upload.department?operation=upload&id=${department.id}">启用</a>/
<a href="${pageContext.request.contextPath}/showOne.department?operation=showOne&id=${department.id}">修改</a>/
<a href="${pageContext.request.contextPath}/delete.department?operation=delete&id=${department.id}">删除</a>
</td>
</c:if>点击修改后，跳转到servlet，
public class DepartmentServlet extends HttpServlet {
...
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("utf-8");
String operation = req.getParameter("operation");
if (operation.equals("showOne")) {
int id = Integer.parseInt(req.getParameter("id"));
Departments dept = departmentDao.selectOneById(id);
req.setAttribute("department", dept);
req.getRequestDispatcher("/update-department.jsp").forward(req, resp);
}
...
}
}servlet调用Dao方法
public interface DepartmentDao {
//按ID查询部门
Departments selectOneById(Integer id);
...
}public class DepartmentDaoImpl implements DepartmentDao{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Boolean result = false;
Departments dept = null;
List<Departments> list = null;
@Override
public Departments selectOneById(Integer id) {
try {
conn = DBUtils.getConnection();
ps = conn.prepareStatement("SELECT * FROM departments where id = ?;");
ps.setInt(1, id);
rs = ps.executeQuery();
if(rs.next()){
int _id = rs.getInt(1);
String _name = rs.getString(2);
String _manager = rs.getString(3);
String _description = rs.getString(4);
int _status = rs.getInt(5);
dept = new Departments(_id, _name, _manager, _description, _status);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, ps, conn);
}
return dept;
}
...
}最终，回显数据成功，如下图 update-department.jsp
该页面的表单改写如下：
<form action="<%=request.getContextPath() %>/update.department" method="post">
<input type="hidden" name="operation" value="update">
<input type="hidden" name="id" value="${department.id}">
<input type="hidden" name="status" value="${department.status}">
<div class="form-group">
<label>部门名称</label>
<input type="text" name="name" class="form-control" placeholder="请输入部门名称" value="${department.name }">
</div>
<div class="form-group">
<label>部门经理</label>
<input type="text" name="manager" class="form-control" placeholder=" 请输入该部门经理" value="${department.manager }">
</div>
<div class="form-group">
<label>部门描述</label>
<input type="text" name="description" class="form-control" placeholder="请描述该部门" value="${department.description }">
</div>
<button type="submit" class="btn btn-primary btn-flat m-b-30 m-t-30">确认修改</button>
</form>然后传达修改servlet，
public class DepartmentServlet extends HttpServlet {
...
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
...
}else if (operation.equals("update")) { //修改
int id = Integer.parseInt(req.getParameter("id"));
String name = req.getParameter("name");
String manager = req.getParameter("manager");
String description = req.getParameter("description");
int status = Integer.parseInt(req.getParameter("status"));
Departments dept = new Departments(id, name, manager, description, status);
res = departmentDao.updateDepartment(dept);
if (res) {
//修改成功
resp.sendRedirect(req.getContextPath()+"/queryAll.department?operation=queryAll");
} else {
//修改失败
req.getRequestDispatcher("/error.jsp").forward(req, resp);
}
}
}
}servlet调用底层Dao和实现
public interface DepartmentDao {
...
//修改部门信息
boolean updateDepartment(Departments newDeptDepartments);
}public class DepartmentDaoImpl implements DepartmentDao{
...
@Override
public boolean updateDepartment(Departments newDeptDepartments) {
try {
conn = DBUtils.getConnection();
ps = conn.prepareStatement("update citel_4.departments set name=?, manager=?, description=?,status=? where id=?;");
ps.setString(1, newDeptDepartments.getName());
ps.setString(2, newDeptDepartments.getManager());
ps.setString(3, newDeptDepartments.getDescription());
ps.setInt(4, newDeptDepartments.getStatus());
ps.setInt(5, newDeptDepartments.getId());
result = ps.executeUpdate()>0?true:false;
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, ps, conn);
}
return result;
}
}
最后修改成功后，跳转到展示所有的部门，至此修改结束。
3.4 删除数据
最后一步，删除数据相对更简单了，因为是假删除，只是更改状态就可以了，这里和同样是更改状态的“启用”和“停用”一直说了。和前面一样，前端代码如下：
<c:if test="${department.status eq '1' }">
<td><span class="badge badge-success">在用</span></td>
<td>
<a href="${pageContext.request.contextPath}/download.department?operation=download&id=${department.id}">停用</a>/
<a href="${pageContext.request.contextPath}/update.department?operation=showOne&id=${department.id}">修改</a>/
<a href=" ${pageContext.request.contextPath}/delete.department?operation=delete&id=${department.id}">删除</a>
</td>
</c:if>
<c:if test="${department.status eq '2' }">
<td><span class="badge badge-danger">停用</span></td>
<td>
<a href="${pageContext.request.contextPath}/upload.department?operation=upload&id=${department.id}">启用</a>/
<a href="${pageContext.request.contextPath}/showOne.department?operation=showOne&id=${department.id}">修改</a>/
<a href="${pageContext.request.contextPath}/delete.department?operation=delete&id=${department.id}">删除</a>
</td>
</c:if>发送相应的请求到servlet，代码如下：
public class DepartmentServlet extends HttpServlet {
...
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
...
}else if (operation.equals("upload")) { //启用
int id = Integer.parseInt(req.getParameter("id"));
res = departmentDao.updatedepartmentStatus(id, 1);
if (res) {
resp.sendRedirect(req.getContextPath()+"/queryAll.department?operation=queryAll");
} else {
req.getRequestDispatcher("/error.jsp").forward(req, resp);
}
}else if (operation.equals("download")) { //停用
int id = Integer.parseInt(req.getParameter("id"));
res = departmentDao.updatedepartmentStatus(id, 2);
if (res) {
resp.sendRedirect(req.getContextPath()+"/queryAll.department?operation=queryAll");
} else {
req.getRequestDispatcher("/error.jsp").forward(req, resp);
}
}else if (operation.equals("delete")) { //删除
int id = Integer.parseInt(req.getParameter("id"));
res = departmentDao.updatedepartmentStatus(id, 0);
if (res) {
resp.sendRedirect(req.getContextPath()+"/queryAll.department?operation=queryAll");
} else {
req.getRequestDispatcher("/error.jsp").forward(req, resp);
}
}
}
}由上面的代码可以看出，它们公用了一个方法，改变部门的状态，其中：0：删除；1：在用；2：停用
public interface DepartmentDao {
...
//修改部门状态
boolean updatedepartmentStatus(Integer id,Integer status);
}public class DepartmentDaoImpl implements DepartmentDao{
...
@Override
public boolean updatedepartmentStatus(Integer id,Integer status) {
try {
conn = DBUtils.getConnection();
ps = conn.prepareStatement("update departments set status=? where id=?;");
ps.setInt(1, status);
ps.setInt(2, id);
result = ps.executeUpdate()>0?true:false;
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, ps, conn);
}
return result;
}
}至此，关于部门的增删改查操作全部结束，而对于职称表和员工表都是一样的，只是在员工表多了两个外键，前端可使用axios，先去获取所有的部门和职称，然后让用户选择即可。
...
<script src="//cdn.bootcss.com/axios/0.19.0-beta.1/axios.min.js"></script>
<script type="text/javascript">
function getAllDepartments(){
axios.get("InformationManageProject/queryAll.department?operation=queryAllByAjax").then((response)=> {
dataList = response.data;
//console.log(dataList);
var root = document.getElementById('depts');
for(var i=0;i<dataList.length;i++){
var op = document.createElement("option");
op.setAttribute("value", dataList[i].id);
var node=document.createTextNode(dataList[i].name);//创建文本节点
op.appendChild(node);
root.appendChild(op);
}
});
}
getAllDepartments();
</script>
...而后端这边主要是把数据转换为json，这里我使用的方式是借助Gson.jar包实现转换。
else if (operation.equals("queryAllByAjax")) {
resp.setContentType("application/json; charset=utf-8");
req.setCharacterEncoding("UTF-8");
PrintWriter out = resp.getWriter();
List<Departments> depts = departmentDao.selectAllDepartments();
//list转换为json
Gson gson = new Gson();
String str = gson.toJson(depts);
out.print(str);
out.flush();
out.close();
}
}最终效果图如下：
大致的内容就是这么多，还有一些小的细节，太琐碎了，没写出来（比如防止在session放置管理员信息，每个页面判断一下是否按照正常登录后进入的，否则跳回到登录页面）。好了，暑假作业完成。
关于下载资源后如何启动，我在我的windows系统上试了下，大致如下：
1. 数据库：新建一个mysql数据库，数据库名为 citel_4 ，执行database.sql文件
2. 导入项目：打开eclipse，File -> Import -> General -> Existing Projects into Workspace -> Browse -> 选择InformationManageProject -> 选择文件夹 -> Finish
NB. 注意修改数据库连接配置信息：db-config.properties
3.1 绑定Tomcat：Window -> Preference -> Server -> Runtime Environments -> Add -> 选择Apache Tomcat v9.0 -> Apply and Close
3.2 打开Servers窗口：Window -> Show View -> Servers
3.3 在Servers窗口中：右键 -> Add and Remove -> 选择选择InformationManageProject -> Add -> Finish
4. 启动项目：在Servers窗口中选中Tomcat v9.0 -> 点击右边绿色按钮 （之后可在控制台看见启动信息）
5. 在浏览器访问项目：eg: http://localhost:8080/InformationManageProject/
142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
