Tips:本项目使用Maven搭建,项目实现的分页效果,使用的是最原始的方式,未使用分页插件!
数据库环境搭建
创建数据库:
create database emp_sys;
创建数据表:
员工表
create table employee(
id int primary key auto_increment,
name varchar(20) not null ,
salary double not null ,
age int not null
) charset=utf8;
管理员表
create table manager(
username varchar(20) not null ,
password varchar(20) not null
) charset=utf8;
注:数据表构建完成后自行加入适量数据
准备工作完成,接下来就开始项目的实现。
首先在pom.xml文件中导入本项目所需要的所有依赖,未使用Maven构建项目可下载相应的jar文件导入WEB-INF目录下的lib文件中。
<!--jstl支持-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!--jsp编译环境-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jsp-api</artifactId>
<version>2.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.6</version>
</dependency>
<!--德鲁伊连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<!--mysql依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<!--验证码所需的依赖-->
<dependency>
<groupId>com.github.penggle</groupId>
<artifactId>kaptcha</artifactId>
<version>2.3.2</version>
<!--排除一个servlet-api因为上面已经导过这个api了,排除是为了防止出现不同版本-->
<exclusions>
<exclusion>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>compile</scope>
</dependency>
数据库配置文件:database.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/emp_sys?serverTimezone=UTC
username=root
password=root
initialSize=10
maxActive=20
minIdle=5
maxWait=3000
创建一个用于操作数据库的工具类
public class DbUtils {
private static DruidDataSource ds;//数据库连接池
private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();//控制事务
static {
Properties properties = new Properties();
InputStream inputStream = DbUtils.class.getResourceAsStream("/database.properties");
try {
properties.load(inputStream);
ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection connection = THREAD_LOCAL.get();
try {
if (connection == null) {
connection = ds.getConnection();
THREAD_LOCAL.set(connection);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
public static void begin() {
Connection connection = null;
try {
connection = getConnection();
connection.setAutoCommit(false);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void commit() {
Connection connection = null;
try {
connection = getConnection();
connection.commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll(connection, null, null);
}
}
public static void rollback() {
Connection connection = null;
try {
connection = getConnection();
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll(connection, null, null);
}
}
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (connection != null) {
connection.close();
}
if (statement != null) {
connection.close();
}
if (resultSet != null) {
connection.close();
THREAD_LOCAL.remove();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
创建员工、管理员、以及分页中所要用到的实体类
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 +
'}';
}
}
public class Manager {
private String username;
private String password;
public Manager() {
}
public Manager(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 "Manager{" +
"username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
public class Page {
private Integer pageIndex;//页码
private Integer pageSize;//页大小,每页显示多少行数据
private Integer totalCounts;//数据的总行数
private Integer totalPages;//总页数
private Integer startRows;//起始行
public Page(Integer pageIndex) {
this(pageIndex, 5);
}
public Page(Integer pageIndex, Integer pageSize) {
this.pageIndex = pageIndex;
this.pageSize = pageSize;
this.setStartRows((pageIndex - 1) * pageSize);//算出起始行
}
public Integer getPageIndex() {
return pageIndex;
}
public void setPageIndex(Integer pageIndex) {
this.pageIndex = pageIndex;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getTotalCounts() {
return totalCounts;
}
public void setTotalCounts(Integer totalCounts) {
this.totalCounts = totalCounts;
this.setTotalPages(totalCounts % pageSize == 0 ? totalCounts / pageSize : totalCounts / pageSize + 1);//计算出当前数据一共能显示几页
}
public Integer getTotalPages() {
return totalPages;
}
public void setTotalPages(Integer totalPages) {
this.totalPages = totalPages;
}
public Integer getStartRows() {
return startRows;
}
public void setStartRows(Integer startRows) {
this.startRows = startRows;
}
}
编写Dao
public interface EmpDao {
List<Emp> queryAllEmp();//查询所有员工
int deleteEmp(int id);//根据id删除员工
int updateEmp(Emp emp);//更新员工数据
Emp queryOneEmp(int id);//根据id查询某一个员工
List<Emp> queryPartEmp(Page page);//分页查询操作
long querytTotalCounts();//查询总页数
}
public interface ManagerDao {
Manager query(String username);//根据姓名查询管理员信息
}
创建Dao实现类
public class EmpDaoImpl implements EmpDao {
private QueryRunner queryRunner = new QueryRunner();
@Override
public List<Emp> queryAllEmp() {
try {
//第一个参数:要一个Connection,第二个参数:写查询语句,第三个参数:要一个封装的结果,第四个参数:将查询条件传入
List<Emp> empList = queryRunner.query(DbUtils.getConnection(), "select * from employee", new BeanListHandler<>(Emp.class));
return empList;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
@Override
public int deleteEmp(int id) {
try {
//返回参数值为受影响行数
int update = queryRunner.update(DbUtils.getConnection(), "delete from employee where id=?", id);
return update;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return 0;
}
@Override
public int updateEmp(Emp emp) {
try {
//返回参数值为受影响行数
int update = queryRunner.update(DbUtils.getConnection(), "update employee set" +
" name=?,salary=?,age=? where id=?",
emp.getName(), emp.getSalary(), emp.getAge(), emp.getId());
return update;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return 0;
}
@Override
public Emp queryOneEmp(int id) {
try {
Emp emp = queryRunner.query(DbUtils.getConnection(), "select * from employee where id=?", new BeanHandler<>(Emp.class), id);
return emp;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
@Override
public List<Emp> queryPartEmp(Page page) {
try {
List<Emp> empList = queryRunner.query(DbUtils.getConnection(), "select * from employee limit ?,?", new BeanListHandler<>(Emp.class), page.getStartRows(), page.getPageSize());
return empList;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
@Override
public long querytTotalCounts() {
try {
//返回值为总行数
long counts = (long) queryRunner.query(DbUtils.getConnection(), "select count(*) from employee", new ScalarHandler<>());
return counts;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return 0;
}
}
public class ManagerDaoImpl implements ManagerDao {
private QueryRunner queryRunner = new QueryRunner();
@Override
public Manager query(String username) {
try {
Manager manager = queryRunner.query(DbUtils.getConnection(), "select * from manager where username=?", new BeanHandler<>(Manager.class), username);
return manager;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
}
编写Service
public interface EmpService {
List<Emp> showAllEmp();
void deleteEmp(int id);
void updateEmp(Emp emp);
Emp showOneEmp(int id);
List<Emp> showParEmp(Page page);
}
public interface ManagerService {
//验证管理员账户
Manager login(String username, String password);
}
创建Service实现类
public class EmpServiceImpl implements EmpService {
EmpDao empDao = new EmpDaoImpl();
@Override
public List<Emp> showAllEmp() {
try {
DbUtils.begin();
List<Emp> emps = empDao.queryAllEmp();
if (emps != null) {
DbUtils.commit();
return emps;
}
} catch (Exception e) {
DbUtils.rollback();
e.printStackTrace();
}
return null;
}
@Override
public void deleteEmp(int id) {
try {
DbUtils.begin();
int deleteEmp = empDao.deleteEmp(id);
if (deleteEmp != 0) {
DbUtils.commit();
}
} catch (Exception e) {
DbUtils.rollback();
e.printStackTrace();
}
}
@Override
public void updateEmp(Emp emp) {
try {
DbUtils.begin();
int updateEmp = empDao.updateEmp(emp);
if (updateEmp != 0) {
DbUtils.commit();
}
} catch (Exception e) {
DbUtils.rollback();
e.printStackTrace();
}
}
@Override
public Emp showOneEmp(int id) {
try {
DbUtils.begin();
Emp emp = empDao.queryOneEmp(id);
if (emp != null) {
DbUtils.commit();
return emp;
}
} catch (Exception e) {
DbUtils.rollback();
e.printStackTrace();
}
return null;
}
@Override
public List<Emp> showParEmp(Page page) {
try {
DbUtils.begin();
long counts = empDao.querytTotalCounts();
page.setTotalCounts((int) counts);
List<Emp> emps = empDao.queryPartEmp(page);
if (emps != null) {
DbUtils.commit();
return emps;
}
} catch (Exception e) {
DbUtils.rollback();
e.printStackTrace();
}
return null;
}
}
public class ManagerServiceImpl implements ManagerService {
private ManagerDao managerDao = new ManagerDaoImpl();
@Override
public Manager login(String username, String password) {
try {
DbUtils.begin();
Manager manager = managerDao.query(username);
if (manager != null) {
if (manager.getPassword().equals(password)) {
DbUtils.commit();
return manager;
}
}
} catch (Exception e) {
DbUtils.rollback();
e.printStackTrace();
}
return null;
}
}
创建Servlet
用于登录的Servlet
@WebServlet(name = "ManagerLoginServlet", value = "/manager/managerLogin")
public class ManagerLoginServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String username = request.getParameter("username");
String password = request.getParameter("password");
String inputVCode = request.getParameter("inputVCode");//获取用户输入的验证码
//校验验证码
String captcha = (String) request.getSession().getAttribute("captcha");//从session中拿取图片显示的验证码
if (!inputVCode.isEmpty() && inputVCode.equalsIgnoreCase(captcha)) {//用户输入的验证码不为空且核对一致则通过
Manager manager = new ManagerServiceImpl().login(username, password);//判断用户名与密码是否正确
if (manager != null) {
//登录成功
request.getSession().setAttribute("manager", manager);//将manager对象存入session
//跳转到查询所有的Servlet
response.sendRedirect(request.getContextPath() + "/manager/safe/showAllEmpServlet");
} else {
response.getWriter().write("<script language='javascript'>alert('用户名或密码错误');window.location.href='" + request.getContextPath() + "/login.jsp';</script>");
}
} else {
response.getWriter().write("<script language='javascript'>alert('验证码错误!');window.location.href='" + request.getContextPath() + "/login.jsp';</script>");
}
}
}
用于查询用户的Servlet
@WebServlet(name = "ShowAllEmpServlet", value = "/manager/safe/showAllEmpServlet")
public class ShowAllEmpServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String pageIndex = request.getParameter("pageIndex");//获取当前分页是第几页
if (pageIndex == null) {
//第一次访问设置为第一页
pageIndex = "1";
}
Page page = new Page(Integer.valueOf(pageIndex));
//权限验证,避免用户直接操作,交给过滤器
EmpService empService = new EmpServiceImpl();
List<Emp> emps = empService.showParEmp(page);
request.setAttribute("emps", emps);//将员工信息存入request作用域
request.setAttribute("page", page);//将分页实体存入request作用域,方便在jsp中获取我们需要的一些分页数据
request.getRequestDispatcher("/showEmp.jsp").forward(request, response);
}
}
@WebServlet(name = "QueryOneEmpServlet",value = "/manager/safe/queryOneEmpServlet")
public class QueryOneEmpServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int id = Integer.parseInt(request.getParameter("id"));//获取当前操作的员工id
EmpServiceImpl empService = new EmpServiceImpl();
Emp emp = empService.showOneEmp(id);
request.setAttribute("emp",emp);//将员工信息存入request作用域
request.getRequestDispatcher("/updateEmp.jsp").forward(request,response);
}
}
用于进行删除操作的Servlet
@WebServlet(name = "DeleteEmpServlet", value = "/manager/safe/deleteEmpServlet")
public class DeleteEmpServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int id = Integer.parseInt(request.getParameter("id"));//获取要删除的员工id
EmpService empService = new EmpServiceImpl();
empService.deleteEmp(id);
response.sendRedirect(request.getContextPath() + "/manager/safe/showAllEmpServlet");
}
}
用于进行更新员工信息的Servlet
@WebServlet(name = "UpdateEmpServlet", value = "/manager/safe/updateEmpServlet")
public class UpdateEmpServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int id = Integer.parseInt(request.getParameter("id"));//获取要更新信息的用户id
String name = request.getParameter("name");//获取要更改的姓名
double salary = Double.parseDouble(request.getParameter("salary"));//获取要更改的工资
int age = Integer.parseInt(request.getParameter("age"));//获取要更改的年龄
new EmpServiceImpl().updateEmp(new Emp(id,name,salary,age));
response.sendRedirect(request.getContextPath() + "/manager/safe/showAllEmpServlet");
}
}
接下来写两个用于进行权限验证以及编码处理的过滤器
//对manager路径下的所有资源进行编码处理
@WebFilter("/manager/*")
public class EncodingFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
}
/**
* 进行编码处理
*/
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
servletRequest.setCharacterEncoding("utf-8");
servletResponse.setContentType("text/html;charset=utf-8");
filterChain.doFilter(servletRequest, servletResponse);
}
@Override
public void destroy() {
}
}
//过滤safe路径下的所有资源
@WebFilter("/manager/safe/*")
public class CheckFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
}
/**
* 完成权限验证操作
*/
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
HttpServletRequest request = (HttpServletRequest) servletRequest;
HttpServletResponse response = (HttpServletResponse) servletResponse;
Manager manager = (Manager) request.getSession().getAttribute("manager");//查看当前是否为管理员操作
if (manager != null) {
//权限通过,允许访问
filterChain.doFilter(request, response);
} else {
response.sendRedirect(request.getContextPath() + "/login.jsp");
}
}
@Override
public void destroy() {
}
}
项目中要用到的jsp页面如下:
登录页面:login.jsp
<%@ page import="java.util.Date" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>登录页面</title>
</head>
<body>
<form action="${pageContext.request.contextPath}/manager/managerLogin" method="post">
username:<input type="text" name="username"><br>
password:<input type="password" name="password"><br>
VCode:<input type="text" name="inputVCode"><br>
<img id="img" src="${pageContext.request.contextPath}/captcha" onclick="refresh()"><br>
<input type="submit" value="登录">
</form>
<script>
function refresh() {
var img = document.getElementById("img");
//new Date().getTime():目的是为了让每次请求的地址都不一样,这样可防止浏览器的缓存
img.src = "${pageContext.request.contextPath}/captcha?" + new Date().getTime();
}
</script>
</body>
</html>
用于展示员工信息的页面:showEmp.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>员工信息</title>
</head>
<body>
<table width="500px" align="center" border="1px">
<thead>
<tr>
<th>编号</th>
<th>姓名</th>
<th>工资</th>
<th>年龄</th>
<th colspan="2">操作</th>
</tr>
</thead>
<tbody align="center">
<c:forEach items="${requestScope.emps}" var="emp">
<tr>
<td>${emp.id}</td>
<td>${emp.name}</td>
<td>${emp.salary}</td>
<td>${emp.age}</td>
<td>
<%--动态获取url--%>
<%--<c:url context="${pageContext.request.contextPath}" value="/manager/safe/deleteEmpServlet?id=${emp.id}"></c:url>--%>
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/deleteEmpServlet?id=${emp.id}'></c:url>">删除</a>
</td>
<td>
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/queryOneEmpServlet?id=${emp.id}'></c:url>">修改</a>
</td>
</tr>
</c:forEach>
</tbody>
<tr>
<td colspan="6" align="center">
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/showAllEmpServlet?pageIndex=1'></c:url>">首页</a>
<c:if test="${requestScope.page.pageIndex > 1}">
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/showAllEmpServlet?pageIndex=${requestScope.page.pageIndex-1}'></c:url>">上一页</a>
</c:if>
<c:if test="${requestScope.page.pageIndex == 1}">
<a>上一页</a>
</c:if>
<c:if test="${requestScope.page.pageIndex < requestScope.page.totalPages}">
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/showAllEmpServlet?pageIndex=${requestScope.page.pageIndex+1}'></c:url>">下一页</a>
</c:if>
<c:if test="${requestScope.page.pageIndex == requestScope.page.totalPages}">
<a>下一页</a>
</c:if>
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/showAllEmpServlet?pageIndex=${requestScope.page.totalPages}'></c:url>">尾页</a>
</td>
</tr>
</table>
</body>
</html>
用于修改员工信息的页面:showEmp.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>修改员工信息</title>
</head>
<body>
<form action="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/updateEmpServlet'></c:url>" method="post">
<input type="text" name="id" value="${requestScope.emp.id}" hidden>
name:<input type="text" name="name" value="${requestScope.emp.name}"><br>
salary:<input type="text" name="salary" value="${requestScope.emp.salary}"><br>
age:<input type="text" name="age" value="${requestScope.emp.age}"><br>
<input type="submit" value="修改">
</form>
</body>
</html>
web.xml文件:由于Servlet我使用的是@WebServlet注解,所以在web.xml文件中我没有再配置Servlet
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
id="WebApp_ID" version="3.0">
<display-name>Archetype Created Web Application</display-name>
<welcome-file-list>
<welcome-file>login.jsp</welcome-file>
</welcome-file-list>
<!--验证码servlet-->
<servlet>
<servlet-name>cap</servlet-name>
<servlet-class>com.google.code.kaptcha.servlet.KaptchaServlet</servlet-class>
<!--验证码图片是否需要边框-->
<init-param>
<param-name>kaptcha.border</param-name>
<param-value>no</param-value><!--不需要-->
</init-param>
<!--验证码的字符有几位-->
<init-param>
<param-name>kaptcha.textproducer.char.length</param-name>
<param-value>4</param-value><!--四位-->
</init-param>
<!--验证码可能出现的字符有哪些-->
<init-param>
<param-name>kaptcha.textproducer.char.string</param-name>
<param-value>abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789</param-value><!--就罗列的这些-->
</init-param>
<!--验证码图片的底色-->
<init-param>
<param-name>kaptcha.background.clear.to</param-name>
<param-value>211,229,237</param-value>
</init-param>
<!--把验证码的码值存放在session中
等价于:session.setAttribute("captcha","验证码") -->
<init-param>
<param-name>kaptcha.session.key</param-name>
<param-value>captcha</param-value><!--相当于key-->
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>cap</servlet-name>
<url-pattern>/captcha</url-pattern>
</servlet-mapping>
</web-app>