当前位置：首页 > article >正文

基于Spring Boot和MyBatis的后端主键分页查询接口示例

article 2025/2/13 13:42:34

基于Spring Boot和MyBatis的后端主键分页查询接口示例

- 1. 项目结构
- 2. 代码实现
- - （1）实体类 `User.java`
  - （2）Mapper接口 `UserMapper.java`
  - （3）Mapper XML文件 `UserMapper.xml`
  - （4）Service层 `UserService.java`
  - （5）Controller层 `UserController.java`
  - （6）Spring Boot启动类 `DemoApplication.java`
  - （7）配置文件 `application.yml`
- 3. 接口使用示例
- - 请求示例
  - 响应示例
- 4. 总结

下面是一个基于Spring Boot和MyBatis的后端分页查询接口示例，使用基于主键的分页查询优化方法。假设我们有一个用户表user，其中包含自增主键id和其他字段（如name、email等）。

1. 项目结构

src
├── main
│   ├── java
│   │   └── com
│   │       └── example
│   │           ├── controller
│   │           │   └── UserController.java
│   │           ├── entity
│   │           │   └── User.java
│   │           ├── mapper
│   │           │   └── UserMapper.java
│   │           ├── service
│   │           │   └── UserService.java
│   │           └── DemoApplication.java
│   └── resources
│       ├── application.yml
│       └── mapper
│           └── UserMapper.xml
└── test
    └── java

2. 代码实现

（1）实体类 `User.java`

package com.example.entity;

public class User {
    private Long id;
    private String name;
    private String email;

    // Getters and Setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

（2）Mapper接口 `UserMapper.java`

package com.example.mapper;

import com.example.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface UserMapper {
    // 基于主键的分页查询
    List<User> findUsersAfterId(@Param("lastId") Long lastId, @Param("size") int size);
}

（3）Mapper XML文件 `UserMapper.xml`

在resources/mapper目录下创建UserMapper.xml文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mapper.UserMapper">
    <select id="findUsersAfterId" resultType="com.example.entity.User">
        SELECT id, name, email
        FROM user
        WHERE id > #{lastId}
        ORDER BY id
        LIMIT #{size}
    </select>
</mapper>

（4）Service层 `UserService.java`

package com.example.service;

import com.example.entity.User;
import com.example.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    // 基于主键的分页查询
    public List<User> getUsersAfterId(Long lastId, int size) {
        return userMapper.findUsersAfterId(lastId, size);
    }
}

（5）Controller层 `UserController.java`

package com.example.controller;

import com.example.entity.User;
import com.example.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    // 分页查询接口
    @GetMapping("/users")
    public List<User> getUsers(@RequestParam(value = "lastId", defaultValue = "0") Long lastId,
                               @RequestParam(value = "size", defaultValue = "10") int size) {
        return userService.getUsersAfterId(lastId, size);
    }
}

（6）Spring Boot启动类 `DemoApplication.java`

package com.example;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.mapper") // 扫描Mapper接口
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

（7）配置文件 `application.yml`

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/your_database?useSSL=false&serverTimezone=UTC
    username: your_username
    password: your_password
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  mapper-locations: classpath:mapper/*.xml

3. 接口使用示例

请求示例

第一页：
```
GET /users?lastId=0&size=10
```
第二页（假设第一页的最后一条记录的id为10）：
```
GET /users?lastId=10&size=10
```

响应示例

[
    {
        "id": 1,
        "name": "Alice",
        "email": "alice@example.com"
    },
    {
        "id": 2,
        "name": "Bob",
        "email": "bob@example.com"
    },
    ...
]