Java分页查询&条件查询
1 分页查询
1.1 分页核心
设计一个用于封装当前页所有分页相关的数据的对象，叫分页对象PageBean
/**
* 分页对象。用于封装当前页的分页相关的所有数据
* @author h
*
*/
public class PageBean {
private List<Employee> data;//当前页的数据
private Integer firstPage;//首页
private Integer prePage;//上一页
private Integer nextPage;//下一页
private Integer totalPage;//末页、总页数
private Integer currentPage;//当前页
private Integer totalCount;//总记录数
private Integer pageSize;//每页显示的记录数
}
1.2 分页的实现步骤
1）编写分页对象和实体对象
/**
* 分页对象。用于封装当前页的分页相关的所有数据
* @author h
*
*/
public class PageBean {
private List<Employee> data;//当前页的数据
private Integer firstPage;//首页
private Integer prePage;//上一页
private Integer nextPage;//下一页
private Integer totalPage;//末页、总页数
private Integer currentPage;//当前页
private Integer totalCount;//总记录数
private Integer pageSize;//每页显示的记录数
public List<Employee> getData() {
return data;
}
public void setData(List<Employee> data) {
this.data = data;
}
public Integer getFirstPage() {
return 1;
}
public void setFirstPage(Integer firstPage) {
this.firstPage = firstPage;
}
/**
* 计算上一页
* @return
*/
public Integer getPrePage() {
return this.getCurrentPage()==this.getFirstPage() ? 1 : this.getCurrentPage()-1;
}
public void setPrePage(Integer prePage) {
this.prePage = prePage;
}
/**
* 计算下一页
* @return
*/
public Integer getNextPage() {
return this.getCurrentPage()==this.getTotalPage()?
this.getTotalPage()
: this.getCurrentPage()+1;
}
public void setNextPage(Integer nextPage) {
this.nextPage = nextPage;
}
public Integer getTotalPage() {
return this.getTotalCount()%this.getPageSize()==0 ?
this.getTotalCount()/this.getPageSize()
:this.getTotalCount()/this.getPageSize()+1;
}
public void setTotalPage(Integer totalPage) {
this.totalPage = totalPage;
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public Integer getTotalCount() {
return totalCount;
}
public void setTotalCount(Integer totalCount) {
this.totalCount = totalCount;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
}
以员工查询信息为例子
/**
* 员工对象
* @author h
*
*/
public class Employee {
private int id;
private String name;
private String gender;
private int age;
private String title;
private String phone;
private String email;
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 String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Employee(int id, String name, String gender, int age, String title,
String phone, String email) {
super();
this.id = id;
this.name = name;
this.gender = gender;
this.age = age;
this.title = title;
this.phone = phone;
this.email = email;
}
public Employee() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Employee [age=" + age + ", email=" + email + ", gender="
+ gender + ", id=" + id + ", name=" + name + ", phone=" + phone
+ ", title=" + title + "]";
}
}
2）编写DAO层代码（查询总记录数和查询当前页数据）
/**
* 员工的DAO类
* @author h
*
*/
public class EmpDao {
/**
* 提供一个查询当前页员工的方法
*/
public List<Employee> queryCurrentData(Integer currentPage,Integer pageSize){
try {
//1.创建QueryRunner对象
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
//2.执行查询sql操作
//计算查询的起始行
int startNo = (currentPage-1)*pageSize;
List<Employee> list = (List<Employee>)qr.query("SELECT * FROM employee LIMIT ?,?",
new BeanListHandler(Employee.class),
new Object[]{startNo,pageSize});
return list;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 提供查询总记录数的方法
* @param args
*/
public Integer queryTotalCount(){
try {
//1.创建QueryRunner
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
//2.执行sql查询
Long count = (Long)qr.query("SELECT COUNT(*) FROM employee", new ScalarHandler(1));
return count.intValue();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
3）编写Service层代码（封装PageBean对象）
/**
* 员工的业务类
* @author h
*
*/
public class EmpService {
/**
* 提供用于封装PageBean对象方法（处理业务逻辑）
*/
public PageBean queryPageBean(Integer currentPage,Integer pageSize){
//封装PageBean分页对象数据
PageBean pageBean = new PageBean();
//设置当前页
pageBean.setCurrentPage(currentPage);
//设置每页显示的记录数
pageBean.setPageSize(pageSize);
EmpDao empDao = new EmpDao();
/**
* 从数据库中查询出总记录数
*/
int totalCount = empDao.queryTotalCount();
//设置总记录数
pageBean.setTotalCount(totalCount);
//设置当前页的数据
/**
* 从数据库中查询出当前页的员工数据
*/
List<Employee> list = empDao.queryCurrentData(pageBean.getCurrentPage(), pageBean.getPageSize());
pageBean.setData(list);
return pageBean;
}
}
4）编写Servlet代码（接收用户输入）
public class PageServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
/***********一、获取用户输入**************/
//设置当前页(获取用户的输入)
String currentPageStr = request.getParameter("currentPage");
//如果用户没有输入，就是默认第1页
if(currentPageStr==null || currentPageStr.equals("")){
currentPageStr = "1";
}
//设置每页显示的记录数(获取用户的输入)
String pageSizeStr = request.getParameter("pageSize");
if(pageSizeStr==null || pageSizeStr.equals("")){
pageSizeStr = "5";
}
/***************二、调用业务方法，获取PageBean对象***********************/
EmpService empService = new EmpService();
PageBean pageBean = empService.queryPageBean(Integer.parseInt(currentPageStr), Integer.parseInt(pageSizeStr));
/****************三、得到业务数据，跳转视图*********************/
//把PageBean数据发送到jsp页面中显示
request.setAttribute("pageBean", pageBean);
//转发
request.getRequestDispatcher("/list.jsp").forward(request, response);
}
}
5）编写jsp页面代码（显示分页效果）
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>分页显示效果</title>
</head>
<body>
<table border="1" align="center" width="700px">
<tr>
<th>编号</th>
<th>姓名</th>
<th>性别</th>
<th>年龄</th>
<th>职位</th>
<th>电话</th>
<th>邮箱</th>
</tr>
<c:forEach items="${requestScope.pageBean.data}" var="emp">
<tr>
<td>${emp.id }</td>
<td>${emp.name }</td>
<td>${emp.gender }</td>
<td>${emp.age}</td>
<td>${emp.title }</td>
<td>${emp.phone }</td>
<td>${emp.email }</td>
</tr>
</c:forEach>
<tr>
<td colspan="7" align="center">
<%--
1）如果当前页是首页，则不显示“首页”和“上一页”的连接
2）如果当前页是末页，则不显示“末页”和“下一页”的连接
--%>
<c:choose>
<c:when test="${pageBean.currentPage==pageBean.firstPage}">
首页
上一页
</c:when>
<c:otherwise>
<a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.firstPage }&pageSize=${pageBean.pageSize}">首页</a>
<a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.prePage }&pageSize=${pageBean.pageSize}">上一页</a>
</c:otherwise>
</c:choose>
<c:choose>
<c:when test="${pageBean.currentPage==pageBean.totalPage}">
下一页
末页
</c:when>
<c:otherwise>
<a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.nextPage}&pageSize=${pageBean.pageSize}">下一页</a>
<a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.totalPage }&pageSize=${pageBean.pageSize}">末页</a>
</c:otherwise>
</c:choose>
当前第${pageBean.currentPage }页/共${pageBean.totalPage }页，
共${pageBean.totalCount }条 每页显示 <input type="text" name="pageSize" id="pageSize" size="2" value="${pageBean.pageSize }" onblur="changePageSize()"/> 条
</td>
</tr>
</table>
<script type="text/javascript">
//改变每页显示记录数的方法
function changePageSize(){
//获取用户输入的记录数
var pageSize = document.getElementById("pageSize").value;
//判断是否输入的数值
var reg = /^[1-9][0-9]?$/;
if(!reg.test(pageSize)){
alert("请输入数组类型！");
return;
}
//把记录数发送到后台
var url = "${pageContext.request.contextPath}/PageServlet?pageSize="+pageSize;
window.location.href=url;
}
</script>
</body>
</html>
2 条件查询
2.1 条件查询的核心
根据用户的查询条件组装sql语句：
//组装sql
StringBuffer sql = new StringBuffer("select * from department where 1=1 ");
if(query!=null){
//部门名称不为空时
if(query.getDeptName()!=null && !query.getDeptName().equals("")){
sql.append(" and deptName like '%"+query.getDeptName()+"%'");
}
//部门负责人不为空时
if(query.getPrincipal()!=null && !query.getPrincipal().equals("")){
sql.append(" and principal like '%"+query.getPrincipal()+"%'");
}
//部门职能不为空时
if(query.getFunctional()!=null && !query.getFunctional().equals("")){
sql.append(" and functional like '%"+query.getFunctional()+"%'");
}
}
