Spring Boot分页返回空数组问题排查求助

我正在尝试使用Spring Boot实现分页功能，但调用接口后返回空数组。系统能识别每页2条数据的配置，但就是没有数据返回，同时日志里出现了count查询，不知道问题出在哪，以下是相关代码和环境信息：

相关代码与环境信息 #

Controller代码 #

package mypackage;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class Controller {

    @Autowired
    CustomerRepository customerRepository;
    
    @GetMapping(value="/get/customers/{page}")
    public List<Customers> getCustomersPaginated(@PathVariable("page") int page)
    {
        Pageable pageableObject=PageRequest.of(page, 2, Sort.by("cnum").ascending());
        Page<Customers> pageObject = customerRepository.findAll(pageableObject);
        return pageObject.getContent();
    }
}

CustomerRepository代码 #

package mypackage;
import org.springframework.data.jpa.repository.JpaRepository;

public interface CustomerRepository extends JpaRepository<Customers, Integer> {

}

Customers实体类代码 #

package mypackage;
import javax.persistence.Entity;
import javax.persistence.Id;

import lombok.Data;
import lombok.RequiredArgsConstructor;

@Entity//(name = "customers")
@Data
@RequiredArgsConstructor
public class Customers {
    
    @Id
    int cnum;
    int rating,snum;
    String cname, city;

}

数据库表结构 #

mysql> desc customers;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| cnum   | int         | NO   | PRI | NULL    |       |
| cname  | varchar(20) | NO   |     | NULL    |       |
| city   | varchar(20) | NO   |     | NULL    |       |
| rating | int         | NO   |     | NULL    |       |
| snum   | int         | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

pom.xml配置 #

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>SpringPagination</groupId>
  <artifactId>SpringPagination</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.0</version>
        <relativePath/> <!-- lookup parent from repository -->
  </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
  <build>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.1</version>
        <configuration>
          <release>17</release>
        </configuration>
      </plugin>
      <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
      <plugin>
        <artifactId>maven-war-plugin</artifactId>
        <version>3.2.3</version>
      </plugin>
    </plugins>
  </build>
</project>

问题排查与解决方案 #

1. 分页页码索引问题 #

Spring Data JPA的PageRequest.of方法第一个参数是从0开始的页码索引，而非从1开始的页码数。如果请求/get/customers/1，实际会查询第二页数据，若数据库中数据不足2条，就会返回空数组。

解决方法：将传入的页码转为0开始的索引：

@GetMapping(value="/get/customers/{page}")
public List<Customers> getCustomersPaginated(@PathVariable("page") int page)
{
    // 转换为0起始的索引
    Pageable pageableObject=PageRequest.of(page - 1, 2, Sort.by("cnum").ascending());
    Page<Customers> pageObject = customerRepository.findAll(pageableObject);
    return pageObject.getContent();
}

2. 实体类与表名映射问题 #

实体类名为Customers，数据库表名为customers（全小写）。若JPA未开启驼峰转下划线的默认规则，会导致表名不匹配，查询不到数据。

解决方法：显式指定实体对应的表名：

@Entity(name = "customers") // 或使用@Table(name = "customers")
@Data
@RequiredArgsConstructor
public class Customers {
    // 字段保持不变
}

3. 数据库连接与数据验证 #

• 检查application.properties/application.yml中的数据库连接配置，确保连接的是目标数据库。
• 直接在数据库执行SELECT * FROM customers LIMIT 2，确认表中存在数据。
• 开启JPA SQL日志，查看实际执行的SQL语句：
  在application.properties中添加：

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

4. Lombok注解问题 #

@RequiredArgsConstructor仅生成包含final字段的构造方法，但JPA实例化实体需要无参构造方法，可能导致实体无法正常实例化。

解决方法：替换为@NoArgsConstructor：

@Entity(name = "customers")
@Data
@NoArgsConstructor
@AllArgsConstructor // 可选，方便创建实例
public class Customers {
    @Id
    int cnum;
    int rating,snum;
    String cname, city;
}

内容的提问来源于stack exchange，提问作者Amit Dev