[FIXED] Pagination is not Working with custom method in SpringBoot

Issue

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

Solution

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)

Answered By - Sandeep Kumar Nat
Answer Checked By - Marie Seifert (JavaFixing Admin)