How to use Java to implement paging query function

Paging query

Paging query displays the huge data in the database in segments. Each page displays a user-defined number of rows to improve the user experience. The most important thing is that if it is read from the server disk at one time Output all the data to the memory, there is a risk of memory overflow

True and false paging

False paging: The principle is to read all the data into the memory, turn the page to read the data from the memory, Advantages: Simple implementation, high performance Disadvantages: If the data is large, it is easy to cause memory overflow
True paging: Query data from the database (i.e. disk) every time the page is turned, Advantages: It is not easy to cause memory overflow Disadvantages: Complex implementation, relatively low performance Some

Paging effects

General paging functions include: Home page Previous page Next page Last page How many pages are there currently How many pages in total How many rows in total How many pages does the data jump to? How many pages per page We need to query this data and encapsulate it into an object, which embodies the idea of ​​encapsulation and saves a lot of complex code

Parameters that need to be passed for paging

Parameters that need to be passed in by the user:
currentPage: current page, which page to jump to, the first time we visit, we create an object, the default value is 1
pageSize: every How many rows of data are displayed on the page? For the first time, we also give a default value, such as 10.

Data to be displayed in each page

1. Product information of the current page
2. What page is the homepage?
3. What page is the previous page?
4. What page is the next page?
5. How many pages are there in total? The value of the last page is the same.
6. How many pieces (rows) of data are there in total?
7. What page is the current page?
8. How many pieces of information are displayed on each page?

The source of the data that needs to be displayed in paging

Sourced from user upload: current page, how many pieces of data are displayed on each page
Sourced from database query: total number of data, product information to be displayed on each page
Sourced from based on Calculation of the above known information: total number of pages, previous page, next page

Write the sql statement to query from the database

The first sql query in the database How many pieces of data are there? There can be no spaces after COUNT

SELECT COUNT(*) FROM 表名

The second sql queries the page based on the parameters passed in, and the result set of how many pieces of data on one page

# 第一个 ?：从哪一个索引的数据开始查询(默认从 0 开始)
# 第二个 ?：查询多少条数据
SELECT * FROM 表名 LIMIT ?, ?

Next analyze the page Two of the two SQLs? Value source:
Assume that there are 21 pieces of data in the product table, and each page is divided into 5 pieces of data:
Query the first page of data: SELECT * FROM product LIMIT 0, 5
Query the data on the second page: SELECT * FROM product LIMIT 5, 5
Query the data on the third page: SELECT * FROM product LIMIT 10, 5
Query the data on the fourth page: SELECT * FROM product LIMIT 15, 5
By looking for the rules, we found: the first? value comes from (currentPage - 1) * pageSize; the second? value comes from
pageSize, that is, they all come from the paging parameters passed by the user.

Total number of pages, previous page and next page

// 优先计算总页数
int totalPage = rows % pageSize == 0 ? rows / pageSize : rows / pageSize + 1;
//上一页等于当前页-1,但不能超过1页界限
int prevPage = currentPage - 1 >= 1 ? currentPage - 1 : 1;
//下一页等于当前页+1,但不能超过总页数界限
int nextPage = currentPage + 1 <= totalPage ? currentPage + 1 : totalPage;

Paging query implementation

Access process:

Encapsulate the data that needs to be displayed

If the data is not encapsulated, each data needs to be stored in the scope, and the data is too scattered. It is inconvenient for unified management

/**
* 封装结果数据（某一页的数据）
*/
@Getter
public class PageResult<T> {
    // 两个用户的输入
    private int currentPage; // 当前页码
    private int pageSize; // 每页显示的条数
    // 两条 SQL 语句执行的结果
    private int totalCount; // 总条数
    private List<T> data; // 当前页结果集数据
    // 三个程序计算的数据
    private int prevPage; // 上一页页码
    private int nextPage; // 下一页页码
    private int totalPage; // 总页数/末页页码
    // 分页数据通过下面构造期封装好
    public PageResult(int currentPage, int pageSize, int totalCount, List<T>
    data) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.totalCount = totalCount;
        this.data = data;
        // 计算三个数据
        this.totalPage = totalCount % pageSize == 0 ? totalCount / pageSize :
        totalCount / pageSize + 1;
        this.prevPage = currentPage - 1 >= 1 ? currentPage - 1 : 1;
        this.nextPage = currentPage + 1 <= this.totalPage ? currentPage + 1 :
        this.totalPage;
    }
}

Persistence layer DAO

The operation method provided by Mybatis can only pass in one parameter to execute the SQL task, and we are now querying the data of a certain page , we need to know the two parameters of which page it is and how many pieces of data per page, so we need to encapsulate these two parameters in an object
Write a class (named query object class) to encapsulate these query data

@Setter
@Getter
/**
* 封装分页查询需要的两个请求传入的分页参数
*/
public class QueryObject {
    private int currentPage = 1; // 当前页码，要跳转到哪一页的页码（需要给默认值）
    private int pageSize = 3; // 每页显示条数（需要给默认值）
}

Then write the persistence layer DAO interface and implementation class

//DAO接口提供两个根据查询对象的查询方法
int queryForCount();
List<Product> queryForList(QueryObject qo);
//DAO实现类
@Override
//查询数据库总数据条数
public int queryForCount() {
    SqlSession session = MyBatisUtil.getSession();
    int totalCount =
    session.selectOne("cn.xxx.mapper.ProductMapper.queryForCount");
    session.close();
    return totalCount;
}
@Override
//查询某一页的结果集
public List<Product> queryForList(QueryObject qo) {
    SqlSession session = MyBatisUtil.getSession();
    List<Product> products =
    session.selectList("cn.xxx.mapper.ProductMapper.queryForList",qo);
    session.close();
    return products;
}

Modify productMapper.xml

<select id="queryForCount" resultType="int">
SELECT COUNT(*) FROM product
</select>
<select id="queryForList" resultType="cn.xxx.domain.Product">
SELECT * FROM product LIMIT #{start}, #{pageSize}
</select>

Modify QueryObject.java

Add the getStart method to this class, Returns the row size of each page that needs to be displayed from the database based on the current page

@Setter
@Getter
/**
* 封装分页查询需要的两个请求传入的分页参数
*/
public class QueryObject {
    private int currentPage = 1; // 当前页码，要跳转到哪一页的页码（需要给默认值）
    private int pageSize = 3; // 每页显示条数（需要给默认值）
    // 用于 Limit 子句第一个 ? 取值
    public int getStart(){
        return (currentPage - 1) * pageSize;
    }
}

Business layer ProductService

Calls the persistence layer DAO to complete the data query, and Multiple data are encapsulated into one object

//IProductService接口
public interface IProductService {
    /**
    * 完成查询某一页的业务逻辑功能
    */
    PageResult<Product> query(QueryObject qo);
}
//Service实现类
public class ProductServiceImpl implements IProductService {
    private IProductDAO productDAO = new ProductDAOImpl();
    @Override
    public PageResult<Product> query(QueryObject qo) {
        // 调用 DAO 查询数据数量
        int totalCount = productDAO.queryForCount();
        // 为了性能加入判断，若查询的数据数量为 0，说明没有数据，返回返回空集合，即集合中没有
        元素
        if(totalCount == 0){
            return new PageResult(qo.getCurrentPage(), qo.getPageSize(),
            totalCount, Collections.emptyList());
        }
        // 执行到这里代表有数据，查询当前页的结果数据
        List<Product> products = productDAO.queryForList(qo);
        return new PageResult(qo.getCurrentPage(), qo.getPageSize(), totalCount,
        products);
    }
}

Implementation of front-end paging function

1. The business layer components must be completed first to ensure that the background test passes.
2. Follow the MVC idea.
3. The browser sends the paging request parameters (which page to go to/how many pieces of data per page), receives these parameters in the Servlet, and encapsulates them
4. To the QueryObject object, call the paging query method in the Service ( query).
5. Share the obtained paging query result object (PageResult) in the request scope, jump to JSP, and display it.
6. Modify the JSP page and write out the paging bar information (the information in the paging bar comes from the PageResult object).

Modify ProductServlet.java and display jsp

1. Get the page request parameters, determine it is a query operation, call the query method, and get the paging parameters
2. The parameters are encapsulated into QueryObject
3. Call the business layer method to query a certain page of data
4. Store the query results in the scope
5. Forward to the display page jsp
6. Take the results out of the scope in jsp and respond to the browser

//创建业务层对象
private IProductService productService = new ProductServiceImpl();

protected void list(HttpServletRequest req, HttpServletResponse resp) throws
ServletException, IOException {
    QueryObject qo = new QueryObject();
    // 获取请求参数 currentPage，并转型封装
    String currentPage = req.getParameter("currentPage");
    if(StringUtil.hasLength(currentPage)) {
        qo.setCurrentPage(Integer.valueOf(currentPage));
    }
    // 获取请求参数 pageSize，并转型封装
    String pageSize = req.getParameter("pageSize");
    if(StringUtil.hasLength(pageSize)) {
        qo.setPageSize(Integer.valueOf(pageSize));
    }
    // 调用业务层方法来处理请求查询某一页数据
    PageResult<Product> pageResult = productService.query(qo);
    // 把数据共享给 list.jsp
    req.setAttribute("pageResult", pageResult);
    // 控制跳转到 list.jsp 页面
    req.getRequestDispatcher("/WEB-INF/views/product/list.jsp").forward(req,
    resp);
}

Modify the jsp file and use JSTL EL to obtain the data in the scope

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
    <head>
        <title>产品列表</title>
        <script type="text/javascript">
            window.onload = function () {
                var trClzs = document.getElementsByClassName("trClassName");
                for(var i = 0; i < trClzs.length; i++){
                    trClzs[i].onmouseover = function () {
                        console.log(1);
                        this.style.backgroundColor = "gray";
                    }
                    trClzs[i].onmouseout = function () {
                        console.log(2);
                        this.style.backgroundColor = "";
                    }
                }
            }
            // 分页 JS
            function changePageSize() {
                document.forms[0].submit();
            }
        </script>
    </head>
    <body>
        <a href="/replaceImg.jsp" rel="external nofollow" ><img src="/static/imghw/default1.png"  data-src="${USER_IN_SESSION.headImg}"  class="lazy"   title="更换头
        像"/></a><br/>
        <a href="/product?cmd=input" rel="external nofollow" >添加</a>
        <form action="/product">
            <table border="1" cellspacing="0" cellpadding="0" width="80%">
                <tr>
                    <th>编号</th>
                    <th>货品名</th>
                    <th>分类编号</th>
                    <th>零售价</th>
                    <th>供应商</th>
                    <th>品牌</th>
                    <th>折扣</th>
                    <th>进货价</th>
                    <th>操作</th>
                </tr>
                <c:forEach var="product" items="${pageResult.data}" varStatus="status">
                    <tr class="trClassName">
                        <td>${status.count}</td>
                        <td>${product.productName}</td>
                        <td>${product.dir_id}</td>
                        <td>${product.salePrice}</td>
                        <td>${product.supplier}</td>
                        <td>${product.brand}</td>
                        <td>${product.cutoff}</td>
                        <td>${product.costPrice}</td>
                        <td>
                            <a href="/product?cmd=delete&id=${product.id}" rel="external nofollow" >删除</a>
                            <a href="/product?cmd=input&id=${product.id}" rel="external nofollow" >修改</a>
                        </td>
                    </tr>
                </c:forEach>
                <tr align="center">
                    <td colspan="9">
                        <a href="/product?currentPage=1" rel="external nofollow" >首页</a>
                        <a href="/product?currentPage=${pageResult.prevPage}" rel="external nofollow" >上一页</a>
                        <a href="/product?currentPage=${pageResult.nextPage}" rel="external nofollow" >下一页</a>
                        <a href="/product?currentPage=${pageResult.totalPage}" rel="external nofollow" >尾页</a>
                        当前第 ${pageResult.currentPage} / ${pageResult.totalPage} 页
                        一共 ${pageResult.totalCount} 条数据
                        跳转到<input type="number" onchange="changePageSize()"
                        name="currentPage" value="${pageResult.currentPage}" 页
                        每页显示
                        <select name="pageSize" onchange="changePageSize()">
                            <option value="3" ${pageResult.pageSize == 3 ? &#39;selected&#39; : &#39;&#39;}> 3 </option>
                            <option value="5" ${pageResult.pageSize == 5 ? &#39;selected&#39; : &#39;&#39;}> 5 </option>
                            <option value="8" ${pageResult.pageSize == 8 ? &#39;selected&#39; : &#39;&#39;}> 8 </option>
                        </select>条数据
                    </td>
                </tr>
            </table>
        </form>
    </body>
</html>

FAQ

If the page turning operation is successful and you can't turn after a few pages, you can only turn tomcat by restarting. Problem: The SqlSession object is not closed in DAO

The above is the detailed content of How to use Java to implement paging query function. For more information, please follow other related articles on the PHP Chinese website!