Pagination is not Working with custom method in SpringBoot-CodePudding

When I was fetching the data only it was working properly, but now I want to apply pagination its not working don't know why? I have tried running query in mysql and there it is working but here not working. Any help can be appreciated, thanks.

My User Class:

@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
}

Address Class:

@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
}

My repository:

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)
}

CodePudding user response：

Native count queries should be declared for pagination at the query method when native SQL is used, according to Spring documentation.

Consider declaring query method as shown below, which doesn't require writing JPQL or native SQL.

public Page<User> findAllByAddress_City(Pageable pageable, String city);

CodePudding user response：

Try JPQL query for pagination like this:

@Query(“select * from User u join u.address a where a.city= :city ”)
public Page<User> findUsersFromCity(Pageable pageable,@Param("city")String city)

CodePudding user response：

try this

@Query(value=“select * from User u join u.address a where a.city= :city ”)
public Page<User> findUsersFromCity(@param("city")String city,Pageable pageable)
}