SpringBoot自定义方法分页失效问题求助

此前仅查询数据时功能正常，但添加分页后功能失效，原因不明。已在MySQL中测试该查询语句可正常运行，但在SpringBoot项目中无法生效，恳请协助解决。

@Entity
@Table(name = "user")
public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "email")
    private String email;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "address_id", referencedColumnName = "id")
    private Address address;

    // ... getters and setters
}

@Entity
@Table(name = "address")
public class Address {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    
    @Column(name = "city")
    private String city;

    @Column(name = "pincode")
    private String pincode;

    @OneToOne(mappedBy = "address")
    private User user;

    //... getters and setters
}

public interface UserRepository extends JpaRepository<User, Long>{

@Query(value="select * from user inner join address on address.id=user.id where city=?2",nativeQuery=true)
public List<User> findUsersFromCity(Pageable pageable,String city)
}

1. 关联条件错误 #

你的SQL关联逻辑写错了：address.id=user.id不符合实体类的关联关系，User是通过address_id字段关联Address的，正确关联条件应为address.id = user.address_id。此前不分页时可能因数据巧合返回结果，但分页时JPA的查询处理逻辑会暴露这个错误。

2. 分页方法返回值错误 #

使用Pageable做分页时，方法应返回Page<User>而非List<User>，这样Spring Data JPA才能正确处理分页参数，返回包含总条数、总页数的完整分页信息。

3. 修正后的原生SQL版本 #

public interface UserRepository extends JpaRepository<User, Long>{

    @Query(value="select u.* from user u inner join address a on a.id = u.address_id where a.city = ?1", nativeQuery = true)
    Page<User> findUsersFromCity(String city, Pageable pageable);
}

4. 更推荐的JPQL自动生成方式 #

无需手写SQL，利用Spring Data JPA的方法命名规则自动生成查询，避免硬编码表字段，更易维护：

public interface UserRepository extends JpaRepository<User, Long>{

    Page<User> findByAddress_City(String city, Pageable pageable);
}

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