前后端分页详解

一、后端Java分页实现

1. 使用MyBatis + XML（带分页插件）

依赖包：MyBatis + PageHelper

<!-- pom.xml -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.6</version>
</dependency>

代码实现：

// Controller
@GetMapping("/users")
public PageInfo<User> getUsers(@RequestParam int pageNum, @RequestParam int pageSize) {
    return userService.getUsers(pageNum, pageSize);
}

// Service
public PageInfo<User> getUsers(int pageNum, int pageSize) {
    PageHelper.startPage(pageNum, pageSize);
    List<User> users = userMapper.selectAll();
    return new PageInfo<>(users);
}

// Mapper XML（无需手写分页SQL，插件自动处理）
<select id="selectAll" resultType="User">
    SELECT * FROM users
</select>

2. 纯JDBC手写分页（无框架）

核心逻辑：

public class PaginationService {
    public PageResult<User> getUsers(int pageNum, int pageSize) throws SQLException {
        int offset = (pageNum - 1) * pageSize;
        String sql = "SELECT * FROM users LIMIT ? OFFSET ?";
        String countSql = "SELECT COUNT(*) FROM users";
        
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement ps = conn.prepareStatement(sql);
             PreparedStatement countPs = conn.prepareStatement(countSql)) {
            
            // 查询数据
            ps.setInt(1, pageSize);
            ps.setInt(2, offset);
            ResultSet rs = ps.executeQuery();
            List<User> users = new ArrayList<>();
            while (rs.next()) {
                // 解析User对象...
            }
            
            // 查询总数
            ResultSet countRs = countPs.executeQuery();
            int total = countRs.next() ? countRs.getInt(1) : 0;
            
            return new PageResult<>(users, total);
        }
    }
}

二、前端Vue分页实现

1. 使用Element UI分页组件

安装依赖：

npm install element-ui

组件代码：

<template>
  <div>
    <el-table :data="tableData">
      <!-- 表格列定义 -->
    </el-table>
    <el-pagination
      @current-change="handlePageChange"
      @size-change="handleSizeChange"
      :current-page="currentPage"
      :page-sizes="[10, 20, 50]"
      :page-size="pageSize"
      layout="total, sizes, prev, pager, next"
      :total="total">
    </el-pagination>
  </div>
</template>

<script>
export default {
  data() {
    return {
      tableData: [],
      currentPage: 1,
      pageSize: 10,
      total: 0
    };
  },
  methods: {
    async fetchData() {
      const res = await axios.get('/api/users', {
        params: { pageNum: this.currentPage, pageSize: this.pageSize }
      });
      this.tableData = res.data.list;
      this.total = res.data.total;
    },
    handlePageChange(page) {
      this.currentPage = page;
      this.fetchData();
    },
    handleSizeChange(size) {
      this.pageSize = size;
      this.fetchData();
    }
  },
  mounted() {
    this.fetchData();
  }
};
</script>

2. 手动实现分页控件

自定义分页组件：

<template>
  <div class="pagination">
    <button @click="prevPage" :disabled="currentPage === 1">上一页</button>
    <span v-for="page in pages" :key="page" 
          @click="goToPage(page)"
          :class="{ active: page === currentPage }">
      {{ page }}
    </span>
    <button @click="nextPage" :disabled="currentPage === totalPages">下一页</button>
  </div>
</template>

<script>
export default {
  props: ['currentPage', 'pageSize', 'total'],
  computed: {
    totalPages() {
      return Math.ceil(this.total / this.pageSize);
    },
    pages() {
      const range = [];
      for (let i = 1; i <= this.totalPages; i++) {
        range.push(i);
      }
      return range;
    }
  },
  methods: {
    goToPage(page) {
      this.$emit('page-change', page);
    },
    prevPage() {
      if (this.currentPage > 1) this.goToPage(this.currentPage - 1);
    },
    nextPage() {
      if (this.currentPage < this.totalPages) this.goToPage(this.currentPage + 1);
    }
  }
};
</script>

三、数据库分页SQL示例

1. MySQL：

   SELECT * FROM table LIMIT #{pageSize} OFFSET #{offset};

2. Oracle：

   SELECT * FROM (
     SELECT t.*, ROWNUM rn FROM (
       SELECT * FROM table ORDER BY id
     ) t WHERE ROWNUM <= #{end}
   ) WHERE rn >= #{start}

四、完整流程说明

1. 前后端交互协议：

   • 请求参数：pageNum（当前页）、pageSize（每页条数）

   • 响应格式：{ list: [], total: 100 }

2. 关键公式：

   offset = (pageNum - 1) * pageSize;
   totalPages = ceil(total / pageSize);

五、总结

• 使用组件/包：快速开发，适合标准场景（推荐PageHelper + Element UI）。

• 手动实现：灵活定制，适合特殊需求或学习原理。

• 性能注意：大数据量时建议使用索引优化分页查询（如MySQL覆盖索引）。