For example, to let users of the [Bookshop](/develop/dev-guide-bookshop-schema-design.md) application view the latest published books in a paginated manner, you can use the `LIMIT 0, 10` statement, which returns the first page of the result list, with a maximum of 10 records per page. To get the second page, you can change the statement to `LIMIT 10, 10`.
```sql
SELECT *
FROM books
ORDER BY published_at DESC
LIMIT 0, 10;
```
In application development, the backend program receives the `page_number` parameter (which means the number of the page being requested) and the `page_size` parameter (which controls how many records per page) from the frontend instead of the `offset` parameter. Therefore, some conversions needed to be done before querying.
```java
public List getLatestBooksPage(Long pageNumber, Long pageSize) throws SQLException {
pageNumber = pageNumber < 1L ? 1L : pageNumber;
pageSize = pageSize < 10L ? 10L : pageSize;
Long offset = (pageNumber - 1) * pageSize;
Long limit = pageSize;
List books = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
PreparedStatement stmt = conn.prepareStatement("""
SELECT id, title, published_at
FROM books
ORDER BY published_at DESC
LIMIT ?, ?;
""");
stmt.setLong(1, offset);
stmt.setLong(2, limit);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Book book = new Book();
book.setId(rs.getLong("id"));
book.setTitle(rs.getString("title"));
book.setPublishedAt(rs.getDate("published_at"));
books.add(book);
}
}
return books;
}
```
## Paging batches for single-field primary key tables
Usually, you can write a pagination SQL statement using a primary key or unique index to sort results and the `offset` keyword in the `LIMIT` clause to split pages by a specified row count. Then the pages are wrapped into independent transactions to achieve flexible paging updates. However, the disadvantage is also obvious. As the primary key or unique index needs to be sorted, a larger offset consumes more computing resources, especially in the case of a large volume of data.
The following introduces a more efficient paging batching method:
First, sort the data by primary key and call the window function `row_number()` to generate a row number for each row. Then, call the aggregation function to group row numbers by the specified page size and calculate the minimum and maximum values of each page.
```sql
SELECT
floor((t.row_num - 1) / 1000) + 1 AS page_num,
min(t.id) AS start_key,
max(t.id) AS end_key,
count(*) AS page_size
FROM (
SELECT id, row_number() OVER (ORDER BY id) AS row_num
FROM books
) t
GROUP BY page_num
ORDER BY page_num;
```
The result is as follows:
```
+----------+------------+------------+-----------+
| page_num | start_key | end_key | page_size |
+----------+------------+------------+-----------+
| 1 | 268996 | 213168525 | 1000 |
| 2 | 213210359 | 430012226 | 1000 |
| 3 | 430137681 | 647846033 | 1000 |
| 4 | 647998334 | 848878952 | 1000 |
| 5 | 848899254 | 1040978080 | 1000 |
...
| 20 | 4077418867 | 4294004213 | 1000 |
+----------+------------+------------+-----------+
20 rows in set (0.01 sec)
```
Next, use the `WHERE id BETWEEN start_key AND end_key` statement to query the data of each slice. To update data more efficiently, you can use the above slice information when modifying the data.
To delete the basic information of all books on page 1, replace the `start_key` and `end_key` with values of page 1 in the above result:
```sql
DELETE FROM books
WHERE
id BETWEEN 268996 AND 213168525
ORDER BY id;
```
In Java, define a `PageMeta` class to store page meta information.
```java
public class PageMeta
{
private Long pageNum;
private K startKey;
private K endKey;
private Long pageSize;
// Skip the getters and setters.
}
```
Define a `getPageMetaList()` method to get the page meta information list, and then define a `deleteBooksByPageMeta()` method to delete data in batches according to the page meta information.
```java
public class BookDAO {
public List> getPageMetaList() throws SQLException {
List> pageMetaList = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("""
SELECT
floor((t.row_num - 1) / 1000) + 1 AS page_num,
min(t.id) AS start_key,
max(t.id) AS end_key,
count(*) AS page_size
FROM (
SELECT id, row_number() OVER (ORDER BY id) AS row_num
FROM books
) t
GROUP BY page_num
ORDER BY page_num;
""");
while (rs.next()) {
PageMeta pageMeta = new PageMeta<>();
pageMeta.setPageNum(rs.getLong("page_num"));
pageMeta.setStartKey(rs.getLong("start_key"));
pageMeta.setEndKey(rs.getLong("end_key"));
pageMeta.setPageSize(rs.getLong("page_size"));
pageMetaList.add(pageMeta);
}
}
return pageMetaList;
}
public void deleteBooksByPageMeta(PageMeta pageMeta) throws SQLException {
try (Connection conn = ds.getConnection()) {
PreparedStatement stmt = conn.prepareStatement("DELETE FROM books WHERE id >= ? AND id <= ?");
stmt.setLong(1, pageMeta.getStartKey());
stmt.setLong(2, pageMeta.getEndKey());
stmt.executeUpdate();
}
}
}
```
The following statement is to delete the data on page 1:
```java
List> pageMetaList = bookDAO.getPageMetaList();
if (pageMetaList.size() > 0) {
bookDAO.deleteBooksByPageMeta(pageMetaList.get(0));
}
```
The following statement is to delete all book data in batches by paging:
```java
List> pageMetaList = bookDAO.getPageMetaList();
pageMetaList.forEach((pageMeta) -> {
try {
bookDAO.deleteBooksByPageMeta(pageMeta);
} catch (SQLException e) {
e.printStackTrace();
}
});
```