一.開發(fā)圖示
1.1圖解
1.2包圖
?
二.代碼實現(xiàn)
需求：實現(xiàn)分頁查詢
2.1JavaBean
package work.itcase.entity;
/**
* javaBean 實體類
* @author Administrator
*
*/
public class Employee {
private int empId; // 員工id
private String empName; // 員工名稱
private int deptid; // 部門id
public int getEmpId() {
return empId;
}
public void setEmpId(int empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public int getDeptid() {
return deptid;
}
public void setDeptid(int deptid) {
this.deptid = deptid;
}
public Employee(int empId,String empName, int deptid) {
super();
this.empId = empId;
this.empName = empName;
this.deptid = deptid;
}
public Employee() {
}
@Override
public String toString() {
return "Employee [empId=" empId ", empName=" empName
", deptid=" deptid "]";
}
}
2.2dao數(shù)據(jù)訪問層接口
package work.itcase.dao;
import work.itcase.entity.Employee;
import work.itcase.utils.PageBean;
/**
* 數(shù)據(jù)訪問層
* @author Administrator
*
*/
public interface EmployeeDao {
/* 分頁查詢數(shù)據(jù) */
public void getAll(PageBean<Employee> pb);
/* 查詢總記錄 */
public int getTotalCount();
}
2.3分頁參數(shù)的封裝
package work.itcase.utils;
import java.util.List;
/**
* 分頁參數(shù)的封裝
*
* @author Administrator
*
* @param <T>
*/
public class PageBean<T> {
private int currentPage = 1; // 當(dāng)前頁，默認(rèn)顯示第一頁
private int pageCount = 4; // 每頁顯示的行數(shù)(查詢返回的行數(shù)), 默認(rèn)每頁顯示4行
private int totalCount; // 總記錄數(shù)
private int totalPage; // 總頁數(shù) = 總記錄數(shù) / 每頁顯示的行數(shù) ( 1)
private List<T> pageData;
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
// 返回總頁數(shù)
public int getTotalpage() {
if (totalCount % pageCount == 0) {
totalPage = totalCount / pageCount;
} else {
totalPage = totalCount / pageCount 1;
}
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public List<T> getPageData() {
return pageData;
}
public void setPageData(List<T> pageData) {
this.pageData = pageData;
}
public int getTotalPage() {
return totalPage;
}
}
2.4dao數(shù)據(jù)訪問層接口實現(xiàn)
package work.itcase.daoimpl;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import work.itcase.dao.EmployeeDao;
import work.itcase.entity.Employee;
import work.itcase.utils.JdbcUtils;
import work.itcase.utils.PageBean;
/**
* 數(shù)據(jù)訪問層實現(xiàn)
*
* @author Administrator
*
*/
public class EmployeeDaoImpl implements EmployeeDao {
@Override
public void getAll(PageBean<Employee> pb) {
// 1. 查詢總記錄； 設(shè)置到pb對象中
int totalCount = this.getTotalCount();
pb.setTotalCount(totalCount);
/*
* 分頁查詢 考慮因素 1.當(dāng)前頁面是首頁點擊 上一頁 報錯 2.當(dāng)前頁面是末頁點擊 下一頁 報錯
*
* 解決 ： 1.如果當(dāng)前頁<= 0; 當(dāng)前頁面設(shè)置為1 2.如果當(dāng)前頁面>最大頁面數(shù)； 將當(dāng)前頁面設(shè)置為最大頁數(shù)
*/
// 判斷
if (pb.getCurrentPage() <= 0) {
pb.setCurrentPage(1); // 把當(dāng)前頁設(shè)置為1
} else if (pb.getCurrentPage() > pb.getTotalPage()) {
// 把當(dāng)前頁設(shè)置為最大頁數(shù)
pb.setCurrentPage(pb.getTotalPage());
}
// 2. 獲取當(dāng)前頁： 計算查詢的起始行、返回的行數(shù)
int currentPage = pb.getCurrentPage();
int index = (currentPage - 1) * pb.getPageCount(); // 查詢的起始行
int count = pb.getPageCount(); // 查詢返回的行數(shù)
try {
// 3.分頁查詢數(shù)據(jù); 把查詢到的數(shù)據(jù)設(shè)置到pb對象中
String sql = "select * from employee limit ?,?";
// 得到QueryRunner對象
QueryRunner qr = JdbcUtils.getQueryRuner();
// 根據(jù)當(dāng)前頁，查詢當(dāng)前頁數(shù)據(jù)(一頁數(shù)據(jù))
List<Employee> pageData = qr
.query(sql, new BeanListHandler<Employee>(Employee.class),
index, count);
// 設(shè)置到對象中
pb.setPageData(pageData);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
@Override
public int getTotalCount() {
String sql = "select count(*) from employee";
try {
// 創(chuàng)建QueryRunner對象
QueryRunner qr = JdbcUtils.getQueryRuner();
// 執(zhí)行查詢， 返回結(jié)果的第一行的第一列
Long count = qr.query(sql, new ScalarHandler<Long>());
return count.intValue();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
2.5業(yè)務(wù)邏輯層接口
package work.itcase.service;
import work.itcase.entity.Employee;
import work.itcase.utils.PageBean;
/**
* 業(yè)務(wù)邏輯層接口設(shè)計
* @author Administrator
*
*/
public interface EmployeeService {
/**
* 分頁查詢數(shù)據(jù)
*/
public void getAll(PageBean<Employee> pb);
}
2.6業(yè)務(wù)邏輯層j接口實現(xiàn)
package work.itcase.serviceimpl;
import work.itcase.dao.EmployeeDao;
import work.itcase.daoimpl.EmployeeDaoImpl;
import work.itcase.entity.Employee;
import work.itcase.service.EmployeeService;
import work.itcase.utils.PageBean;
/**
* 業(yè)務(wù)邏輯層實現(xiàn)
*
* @author Administrator
*
*/
public class EmployeeServiceImpl implements EmployeeService {
// 創(chuàng)建dao實例
private EmployeeDao employ = new EmployeeDaoImpl();
@Override
public void getAll(PageBean<Employee> pb) {
try {
employ.getAll(pb);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
2.7控制器
package work.itcase.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import work.itcase.entity.Employee;
import work.itcase.service.EmployeeService;
import work.itcase.serviceimpl.EmployeeServiceImpl;
import work.itcase.utils.PageBean;
/**
* 4.開發(fā)控制器
* @author Administrator
*
*/
public class IndexServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
// 1.創(chuàng)建Service實例
private EmployeeService service = new EmployeeServiceImpl();
// 跳轉(zhuǎn)資源
private String uri;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 設(shè)置編碼格式
request.setCharacterEncoding("utf-8");
try {
// 1.獲取 當(dāng)前頁面 參數(shù)：(第一次訪問當(dāng)前頁為null)
String currPage = request.getParameter(" currPage");
// 判斷
if (currPage == null || "".equals(currPage.trim())) {
currPage = "1"; // 第一次訪問，設(shè)置當(dāng)前頁為1;
}
// 轉(zhuǎn)換
int currentPage = Integer.parseInt(currPage);
// 2. 創(chuàng)建PageBean對象，設(shè)置當(dāng)前頁參數(shù)； 傳入service方法參數(shù)
PageBean<Employee> pageBean = new PageBean<Employee>();
pageBean.setCurrentPage(currentPage);
// 3. 調(diào)用service
service.getAll(pageBean); // 【pageBean已經(jīng)被dao填充了數(shù)據(jù)】
// 4. 保存pageBean對象，到request域中
request.setAttribute("pageBean", pageBean);
// 5. 跳轉(zhuǎn)
uri = "/WEB-INF/list.jsp";
} catch (NumberFormatException e) {
e.printStackTrace();
// 出現(xiàn)錯誤，跳轉(zhuǎn)到錯誤頁面；給用戶友好提示
uri = "/error/error.jsp";
}
request.getRequestDispatcher(uri).forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}
}
2.8Jdbc工具類
package work.itcase.utils;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* 工具類 1.初始化c3p0連接池 2.創(chuàng)建Dbutils核心工具對象
*
* @author Administrator
*/
public class JdbcUtils {
/*
* 1.初始化池c3p0連接池
*/
private static DataSource dataSource;
static {
dataSource = new ComboPooledDataSource();
}
/*
* 2.創(chuàng)建Dbutils核心工具類對象
*/
public static QueryRunner getQueryRuner() {
/*
* 創(chuàng)建QueryRunner對象，傳入連接池對象
* 在創(chuàng)建QueryRunner對象的時候，如果傳入了數(shù)據(jù)源對象；
* 那么在使用QueryRunner對象方法的時候，就不需要傳入連接對象；
* 會自動從數(shù)據(jù)源中獲取連接(不用關(guān)閉連接)
*/
return new QueryRunner(dataSource);
}
}
2.9c3p0連接池
<c3p0-config>
<default-config>
<!--連接數(shù)據(jù)庫 -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/demo01?characterEncoding=utf8
</property>
<!--數(shù)據(jù)庫驅(qū)動-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!--用戶名。Default: null -->
<property name="user">root</property>
<!--密碼。Default: null -->
<property name="password">0000</property>
<!--初始化時獲取三個連接，取值應(yīng)在minPoolSize與maxPoolSize之間。Default: 3 -->
<property name="initialPoolSize">3</property>
<!--連接池中保留的最大連接數(shù)。Default: 15 -->
<property name="maxPoolSize">6</property>
<!--最大空閑時間,60秒內(nèi)未使用則連接被丟棄。若為0則永不丟棄。Default: 0 -->
<property name="maxIdleTime">1000</property>
</default-config>
<named-config name="oracle_config">
<property name="jdbcUrl">jdbc:mysql://localhost:3306/demo01?characterEncoding=utf8</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">0000</property>
<property name="initialPoolSize">3</property>
<property name="maxPoolSize">6</property>
<property name="maxIdleTime">1000</property>
</named-config>
</c3p0-config>
2.10 jsp頁面
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<!-- 引入jstl核心標(biāo)簽庫 -->
<%@taglib uri="http://java./jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme() "://" request.getServerName() ":" request.getServerPort() path "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>分頁查詢數(shù)據(jù)</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
</head>
<body>
<body>
<table border="1" width="80%" align="center" cellpadding="5" cellspacing="0">
<tr>
<td>序號</td>
<td>員工編號</td>
<td>員工姓名</td>
</tr>
<!-- 迭代數(shù)據(jù) -->
<c:choose>
<c:when test="${not empty requestScope.pageBean.pageData}">
<!-- ${not empty requestScope.pageBean.pageData}
not empty:不為空
requestScope:使用El表達輸出數(shù)據(jù)類型
從四個域獲取:${pageBean}
從指定域獲?。?${pageScope.pageBean}
域范圍： pageScoep / requestScope / sessionScope / applicationScope
-->
<c:forEach var="emp" items="${requestScope.pageBean.pageData}" varStatus="vs">
<tr>
<td>${vs.count }</td>
<td>${emp.empId }</td>
<td>${emp.empName }</td>
</tr>
</c:forEach>
</c:when>
<c:otherwise>
<tr>
<td colspan="3">對不起，沒有你要找的數(shù)據(jù)</td>
</tr>
</c:otherwise>
</c:choose>
<tr>
<td colspan="3" align="center">
當(dāng)前${requestScope.pageBean.currentPage }/${requestScope.pageBean.totalPage }頁
<a href="${pageContext.request.contextPath }/index?currentPage=1">首頁</a>
<a href="${pageContext.request.contextPath }/index?currentPage=${requestScope.pageBean.currentPage-1}">上一頁 </a>
<a href="${pageContext.request.contextPath }/index?currentPage=${requestScope.pageBean.currentPage 1}">下一頁 </a>
<a href="${pageContext.request.contextPath }/index?currentPage=${requestScope.pageBean.totalPage}">末頁</a>
</td>
</tr>
</table>
</body>
</html>
2.11數(shù)據(jù)庫
/*建庫代碼*/
CREATE DATABASE demo01 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
/*建表代碼*/
CREATE TABLE dept( /*主表*/
deptid INT PRIMARY KEY AUTO_INCREMENT,
deptName VARCHAR(16) NOT NULL
)
CREATE TABLE employee(/*從表*/
empId INT PRIMARY KEY AUTO_INCREMENT,
empName VARCHAR(16) NOT NULL,
deptid INT NOT NULL,
CONSTRAINT employee_dept_fk FOREIGN KEY(deptid) REFERENCES dept(deptid) ON UPDATE CASCADE ON DELETE CASCADE
)
INSERT INTO dept(deptName) VALUES('開發(fā)部');
INSERT INTO dept(deptName) VALUES('銷售部');
INSERT INTO dept(deptName) VALUES('后勤部');
INSERT INTO employee(empName,deptid) VALUES('張三',1)
INSERT INTO employee(empName,deptid) VALUES('李四',1)
INSERT INTO employee(empName,deptid) VALUES('王五',2)
INSERT INTO employee(empName,deptid) VALUES('劉六',2)
INSERT INTO employee(empName,deptid) VALUES('唐八',3)
INSERT INTO employee(empName,deptid) VALUES('吳七',3)
分頁查詢
起始行從0開始
分頁：當(dāng)前頁 每頁顯示多少條
分頁查詢當(dāng)前頁的數(shù)據(jù)的sql: SELECT * FROM student LIMIT (當(dāng)前頁-1)*每頁顯示多少條,每頁顯示多少條
第一頁兩條數(shù)據(jù)
SELECT * FROM employee LIMIT 0,2
?
?
?
?
?
?
?
?
?
?
?
?
?
?
來源：https://www./content-1-259401.html
|
