前后端分页详解

发布于:2025-05-27 ⋅ 阅读:(57) ⋅ 点赞:(0)

一、后端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覆盖索引)。


网站公告

今日签到

点亮在社区的每一天
去签到