1数据库准备
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
salary DOUBLE NOT NULL,
age INT NOT NULL
)CHARSET=utf8;
-- 向数据库中添加100条添加记录
2 数据库配置文件db.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/emp
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=5000
3 PageBean类
package com.qf.emp.entity;
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;
}
}
4 创建DAO
package com.qf.emp.dao;
import com.qf.emp.entity.Emp;
import com.qf.emp.entity.Page;
import java.util.List;
public interface EmpDao {
public List<Emp> selectAll();
public int delete(int id);
public int update(Emp emp);
public Emp select(int id);
//分页查询所有
public List<Emp> selectAll(Page page);
//查询数据总行数
public long selectCount();
}
5 EmpDaoImpl实现类
package com.qf.emp.dao.impl;
import com.qf.emp.dao.EmpDao;
import com.qf.emp.entity.Emp;
import com.qf.emp.entity.Page;
import com.qf.emp.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.List;
public class EmpDaoImpl implements EmpDao {
private QueryRunner queryRunner = new QueryRunner();
//...省略其他方法
@Override
public List<Emp> selectAll(Page page) {
try {
List<Emp> emps = queryRunner.query(DbUtils.getConnection(),"select * from emp limit ?,?",new BeanListHandler<Emp>(Emp.class),page.getStartRows(),page.getPageSize());
return emps;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public long selectCount() {
try {
return queryRunner.query(DbUtils.getConnection(),"select count(*) from emp;",new ScalarHandler<>());
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
}
6 创建Service
package com.qf.emp.service;
import com.qf.emp.entity.Emp;
import com.qf.emp.entity.Page;
import java.util.List;
public interface EmpService {
public List<Emp> showAllEmp();
public int removeEmp(int id);
public int modify(Emp emp);
public Emp showEmp(int id);
public List<Emp> showAllEmp(Page page);
}
7 Service实现类
package com.qf.emp.service.impl;
import com.qf.emp.dao.EmpDao;
import com.qf.emp.dao.impl.EmpDaoImpl;
import com.qf.emp.entity.Emp;
import com.qf.emp.entity.Page;
import com.qf.emp.service.EmpService;
import com.qf.emp.utils.DbUtils;
import java.util.ArrayList;
import java.util.List;
public class EmpServiceImpl implements EmpService {
private EmpDao empDao = new EmpDaoImpl();
//...省略其他方法
@Override
public List<Emp> showAllEmp(Page page) {
List<Emp> emps = null;
try {
DbUtils.begin();
//获取总行数
long count = empDao.selectCount();
page.setTotalCounts((int)count);
//根据controller传递的page对象查询对应数据
emps = empDao.selectAll(page);
DbUtils.commit();
} catch (Exception e) {
DbUtils.rollback();
e.printStackTrace();
}
return emps;
}
}
8ShowAllEmpController实现
package com.qf.emp.controller;
import com.qf.emp.entity.Emp;
import com.qf.emp.entity.Page;
import com.qf.emp.service.EmpService;
import com.qf.emp.service.impl.EmpServiceImpl;
import sun.security.util.AuthResources_it;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet(name = "ShowAllEmpController",value = "/manager/safe/showAllEmpController")
public class ShowAllEmpController extends HttpServlet {
protected void doPost(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.showAllEmp(page);
request.setAttribute("emps",emps);
request.setAttribute("page",page);
request.getRequestDispatcher("/manager/safe/showAllEmp.jsp").forward(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
9 showAllEmp.jsp
<%@ page import="com.qf.emp.entity.Emp" %>
<%@ page import="java.util.*" %>
<%@ 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 border="1">
<tr>
<td>编号</td>
<td>姓名</td>
<td>工资</td>
<td>年龄</td>
<td colspan="2">操作</td>
</tr>
<c:forEach var="emp" items="${emps}">
<tr>
<td>${emp.id}</td>
<td>${emp.name}</td>
<td>${emp.salary}</td>
<td>${emp.age}</td>
<td><a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/removeEmpController?id=${emp.id}'></c:url>">删除</a></td>
<td><a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/showEmpController?id=${emp.id}'></c:url>">修改</a></td>
</tr>
</c:forEach>
<tr>
<td colspan="6">
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/showAllEmpController?pageIndex=1' /> ">首页</a>
<c:if test="${page.pageIndex > 1}">
<a href="<c:url context='${pageContext.request.contextPath}' value="/manager/safe/showAllEmpController?pageIndex=${page.pageIndex - 1}" />">上一页</a>
</c:if>
<c:if test="${page.pageIndex == 1}">
<a>上一页</a>
</c:if>
<c:if test="${page.pageIndex < page.totalPages}">
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/showAllEmpController?pageIndex=${page.pageIndex + 1}'/>">下一页</a>
</c:if>
<c:if test="${page.pageIndex == page.totalPages}">
<a>下一页</a>
</c:if>
<a href="<c:url context='${pageContext.request.contextPath}' value='/manager/safe/showAllEmpController?pageIndex=${page.totalPages}' /> ">尾页</a>
</td>
</tr>
</table>
</body>
</html>