JavaEE实现MySQL的简单分页查询

为什么要分页查询

如果前台需要展示数据时,由于数据量庞大,一次性展示,这时页面将会出现一大片数据,而且还不一定加载完成,效率很差,此时分页查询就可以很好的解决这一问题,将庞大的数据按照一定数目显示出,还可以通过点击下一页或者上一页展示其它数据,效率更高。

以下使用mysql实现简单的分页查询

MySQL中limit x,y可以实现分页查询
limit可以接收一个或者两个的整形数据
第一个参数x:是从哪一条数据开始(0开始)
第二个参数y:是指查询多少条
limit 0,5就是指从第一条开始查询5条记录
limit 5,5就是指从第六条开始查询5条记录
limit 5就是指从第一条开始查询5条记录
x是由servlet接收到的页数(page)在业务层进行计算起始页(startPage)

实现效果

看代码

1. 创建一个封装分页查询的通用类
   这里应该定义一个startPage(起始页)变量,由于在编写的时候没有想到,所以我放在业务层定义起始页。

package com.tjg.blogs.pagebook.utils;

import java.util.ArrayList;
import java.util.List;
/**
 1. 通用分页查询
 2. @author tjg
 3.  */
public class PageBean<T> {
	private Integer page;//页数
	private Integer firstPage;//默认第一页
	private Integer count;//总条数
	private Integer totalPage;//总页数
	private Integer limit;//查询条数
	private List<T> list = new ArrayList();//存放数据
	public Integer getPage() {
		return page;
	}
	public void setPage(Integer page) {
		this.page = page;
	}
	public Integer getFirstPage() {
		return firstPage;
	}
	public void setFirstPage(Integer firstPage) {
		this.firstPage = firstPage;
	}
	public Integer getCount() {
		return count;
	}
	public void setCount(Integer count) {
		this.count = count;
	}
	public Integer getTotalPage() {
		return totalPage;
	}
	public void setTotalPage(Integer totalPage) {
		this.totalPage = totalPage;
	}
	public Integer getLimit() {
		return limit;
	}
	public void setLimit(Integer limit) {
		this.limit = limit;
	}
	public List<T> getList() {
		return list;
	}
	public void setList(List<T> list) {
		this.list = list;
	}
	public PageBean(Integer page, Integer firstPage, Integer count, Integer totalPage, Integer limit, List<T> list) {
		super();
		this.page = page;
		this.firstPage = firstPage;
		this.count = count;
		this.totalPage = totalPage;
		this.limit = limit;
		this.list = list;
	}
}

2. 创建数据库连接类
   被static关键字修饰的方法或者变量不需要依赖于对象来进行访问只要
   类被加载了，就可以通过类名去进行访问。

package com.tjg.blogs.pagebook.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 1. 数据库连接类
 2. @author 
 3.  */
public class DBConnection {
	private static String driverClass = "com.mysql.cj.jdbc.Driver";
	private static String url = "jdbc:mysql://127.0.0.1:3306/book?serverTimezone=UTC&characterEncoding=UTF-8";
	private static String user = "root";
	private static String password = "123456";
	// 建立连接
	public static Connection getConnection() throws SQLException {
		try {
			Class.forName(driverClass);
			Connection conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	// 关闭连接
	public static void closeConnection(Connection conn) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	// 关闭结果集
	public static void closeResultSet(ResultSet rs) {

		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	// 关闭PreParedStatement
	public static void closePreParedStatement(PreparedStatement pst) {

		try {
			if (pst != null) {
				pst.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}

3. 创建servlet
   @WebServlet("/BookServlet")访问BookServlet

package com.tjg.blogs.pagebook.servlet;

import java.io.IOException;

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 com.tjg.blogs.pagebook.entity.Book;
import com.tjg.blogs.pagebook.service.BookService;
import com.tjg.blogs.pagebook.utils.PageBean;

@WebServlet("/BookServlet")
public class BookServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		//设置字符
		req.setCharacterEncoding("utf-8");
		resp.setContentType("text/html;charset=utf-8");
		resp.setCharacterEncoding("utf-8");
		String page = req.getParameter("page");
		BookService bookService = new BookService();
		PageBean<Book> pageBean = null;
		//判断page是否为空
		if (page != null) {
			try {
				pageBean = bookService.findPageBook(Integer.valueOf(page));
				req.setAttribute("pageBean", pageBean);
			} catch (NumberFormatException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		req.getRequestDispatcher("book.jsp").forward(req, resp);
	}
}

4. 业务层
   int firstPage =1; 指默认的第一页。当页面点击首页时,将page设置为firstPage获取第一页数据。

   int startPage = (page-1)乘limit: 获取起始页。意思就是当servlet接收的page为1时,因为limit x,y中x是以0为起点所以需要将它减1,至于乘limit(这个limit是我定义查询条数的变量,注意区分哦)。

   假如有10条数据,每页查询4条数据
   limit 0,4-----从第一条开始查询4条(此时第三条的下标为3),这时候查询下一页的起始页就是4,
   所以当page为1时,(1-1)*4起始页为0
   当page为2时(2-1)*4起始页为4
   当page为3时(3-1)*4起始页为8

   int totalPage = 0;总页数:总记录数/查询条数
   需要判断是否整除,能整除totalPage = count / limit,不能整除totalPage = count / limit+1;
   也可以使用Math.ceil()向上取整,注意 count 或 limit不能都是整数类型

package com.tjg.blogs.pagebook.service;

import java.util.List;

import com.tjg.blogs.pagebook.dao.BookDao;
import com.tjg.blogs.pagebook.entity.Book;
import com.tjg.blogs.pagebook.utils.PageBean;
/**
 * 业务层
 * @author tjg
 *
 */
public class BookService {
	private BookDao bookDao = null;
	private PageBean<Book> pageBean = null;
	public PageBean<Book> findPageBook(Integer page){
		bookDao = new BookDao();
		int firstPage = 1;//第一页
		int limit = 4;//查询条数
		int startPage = (page-1)*limit;//从哪里开始查询
		int count = 0;//总条数
		int totalPage = 0;//总页数
		count = bookDao.findCount();
		if(count%limit!=0) {
			totalPage = count/limit+1;
		}else {
			totalPage = count/limit;
		}
		List<Book> list = bookDao.findPageBook(startPage, limit);
		
		pageBean = new PageBean<Book>(page, firstPage, count, totalPage, limit, list);
		return pageBean;
	}
}

5. List item
   获取总条数和进行分页查询

package com.tjg.blogs.pagebook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tjg.blogs.pagebook.entity.Book;
import com.tjg.blogs.pagebook.utils.DBConnection;
/**
 1. 数据访问层
 2. @author tjg
 3.  */
public class BookDao {
	private Connection conn = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	//分页查询
	public List<Book> findPageBook(Integer page,Integer limit){
		String sql = "select * from book limit ?,?";
		try {
			conn = DBConnection.getConnection();
			pst = conn.prepareStatement(sql);
			pst.setInt(1, page);
			pst.setInt(2, limit);
			rs = pst.executeQuery();
			List<Book> books = new ArrayList<Book>();
			while(rs.next()) {
				Integer bid = rs.getInt("bid");
				String bname = rs.getString("bname");
				String bcategory = rs.getString("bcategory");
				String bauthor = rs.getString("bauthor");
				Book book = new Book(bid, bname, bcategory, bauthor);
				books.add(book);
			}
			return books;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBConnection.closeResultSet(rs);
			DBConnection.closePreParedStatement(pst);
			DBConnection.closeConnection(conn);
		}
		
		
	}
	//查询总条数
	public int findCount(){
		String sql = "select count(*) from book";
		int count = 0;
		try {
			 conn = DBConnection.getConnection();
			 pst = conn.prepareStatement(sql);
			 rs = pst.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBConnection.closeResultSet(rs);
			DBConnection.closePreParedStatement(pst);
			DBConnection.closeConnection(conn);
		}
		return count;
	}
}

6. 展示数据
   <%@ include file=“page.jsp” %>引入page.jsp文件
   <%@ taglib prefix=“c” uri=“http://java.sun.com/jsp/jstl/core” %>引入jstl标签
   <c:forEach var=“book” items="${pageBean.list}" ></c:forEach>迭代 var 定义一个变量,items 注入到jsp的集合数据放在里面
   ${book.bid}通过变量book获取实体类中的属性

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8" isELIgnored="false"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>书籍</title>
</head>
<body>
<table>
	<tr>
		<td>id</td><td>书名</td><td>类型</td><td>作者</td>
	</tr>
	<c:forEach var="book" items="${pageBean.list}" >
	<tr>
	<td>${book.bid}</td><td>${book.bname }</td><td>${book.bcategory }</td><td>${book.bauthor}</td>
	</tr>
	</c:forEach>
</table>
<%@ include file="page.jsp" %>
</body>
</html>

7. 分页组件

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8" isELIgnored="false"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>分页</title>
</head>
<body>
<span>第${pageBean.page }页|共${pageBean.totalPage }页</span>
<c:if test="${pageBean.page >1}">
<!-- 将page大于1时显示否则不显示 -->
<a href="${pageContext.request.contextPath }/BookServlet?page=${pageBean.firstPage }">首页</a>
<a href="${pageContext.request.contextPath }/BookServlet?page=${pageBean.page-1 }">上一页</a>
</c:if>
<!-- 将page小于总页数时显示否则不显示 -->
<c:if test="${pageBean.page < pageBean.totalPage }">
<a href="${pageContext.request.contextPath }/BookServlet?page=${pageBean.page+1 }">下一页</a>
<a href="${pageContext.request.contextPath }/BookServlet?page=${pageBean.totalPage }">尾页</a>
</c:if>
</body>
</html>

谢谢看到这么后面,第一次写,也许不尽人意(谢谢大家观看)