Mastering Web Pagination: Database, Backend, and Frontend Techniques

1. Database Pagination

MySQL uses the LIMIT clause to restrict rows. The syntax is LIMIT [offset,] row_count. When only row_count is provided, it is equivalent to LIMIT 0, row_count. The offset starts at 0.

2. Server/Backend Pagination

Backend pagination essentially translates to database pagination. The article compares two popular Java components: PageHelper (a MyBatis plugin) and Spring Data JDBC.

PageHelper

/**
 * Calculate start and end row numbers
 * @see com.github.pagehelper.Page#calculateStartAndEndRow
 */
private void calculateStartAndEndRow() {
    // pageNum starts from 1; if pageNum < 1, ignore calculation.
    this.startRow = this.pageNum > 0 ? (this.pageNum - 1) * this.pageSize : 0;
    this.endRow = this.startRow + this.pageSize * (this.pageNum > 0 ? 1 : 0);
}

/**
 * Calculate total pages
 * Also sets total record count.
 */
public void setTotal(long total) {
    if (pageSize > 0) {
        pages = (int) (total / pageSize + ((total % pageSize == 0) ? 0 : 1));
    } else {
        pages = 0;
    }
}

Spring Data JDBC

Key classes: org.springframework.data.domain.Pageable and org.springframework.data.web.PageableDefault .

/**
 * Offset calculation; page index starts from 0.
 * @see org.springframework.data.domain.AbstractPageRequest#getOffset
 */
public long getOffset() {
    return (long) this.page * (long) this.size;
}

/**
 * Total pages calculation using Math.ceil.
 * @see org.springframework.data.domain.Page#getTotalPages()
 */
@Override
public int getTotalPages() {
    return getSize() == 0 ? 1 : (int) Math.ceil((double) total / (double) getSize());
}

/** Apply pagination to a SelectBuilder */
private SelectBuilder.SelectOrdered applyPagination(Pageable pageable, SelectBuilder.SelectOrdered select) {
    SelectBuilder.SelectLimitOffset limitable = (SelectBuilder.SelectLimitOffset) select;
    SelectBuilder.SelectLimitOffset limitResult = limitable.limitOffset(pageable.getPageSize(), pageable.getOffset());
    return (SelectBuilder.SelectOrdered) limitResult;
}

3. Frontend Pagination

Two approaches are shown: server‑side rendering with Thymeleaf and client‑side pagination with Element UI.

Thymeleaf template

<nav>
  <ul class="pagination" th:with="total = ${users.totalPages}">
    <li th:if="${users.hasPrevious()}">
      <a th:href="@{/users(page=${users.previousPageable().pageNumber},size=${users.size})}" aria-label="Previous">
        <span aria-hidden="true">«</span>
      </a>
    </li>
    <li th:each="page : ${#numbers.sequence(0, total - 1)}">
      <a th:href="@{/users(page=${page},size=${users.size})}" th:text="${page + 1}">1</a>
    </li>
    <li th:if="${users.hasNext()}">
      <a th:href="@{/users(page=${users.nextPageable().pageNumber},size=${users.size})}" aria-label="Next">
        <span aria-hidden="true">»</span>
      </a>
    </li>
  </ul>
</nav>

Element UI pagination component

// from node_modules/element-ui/packages/pagination/src/pagination.js
computed: {
  internalPageCount() {
    if (typeof this.total === 'number') {
      // page count uses Math.ceil
      return Math.max(1, Math.ceil(this.total / this.internalPageSize));
    } else if (typeof this.pageCount === 'number') {
      return Math.max(1, this.pageCount);
    }
    return null;
  }
},
/**
 * Calculate the current page; page index starts from 1.
 */
getValidCurrentPage(value) {
  value = parseInt(value, 10);
  const havePageCount = typeof this.internalPageCount === 'number';
  let resetValue;
  if (!havePageCount) {
    if (isNaN(value) || value < 1) resetValue = 1;
  } else {
    if (value < 1) {
      resetValue = 1;
    } else if (value > this.internalPageCount) {
      // out of bounds, clamp to pageCount
      resetValue = this.internalPageCount;
    }
  }
  if (resetValue === undefined && isNaN(value)) {
    resetValue = 1;
  } else if (resetValue === 0) {
    resetValue = 1;
  }
  return resetValue === undefined ? value : resetValue;
}

In summary, pagination strategies are tightly coupled with the underlying database syntax, and various frameworks or plugins adapt these fundamentals to provide consistent paging experiences across the stack.