Spring Data JPA Pagination - Query creation from method names

Question

I have a OneToMany relationship between User and PhoneNumber. Therefore one user can have multiple phone numbers.

In the front end I have to pull only the phone numbers of the logged in user or if the user is an Admin I have to display all users phone numbers. I've managed to implement this part but the problem I have is the following: In the Frontend the user have also a search box by "phone number". So if the user or the admin search for 987 (let's say first 3 digits) any phone number which contains '987' and is associated with his profile should be displayed. If user is an admin I should display all numbers of all users which contains '987'.

For pagination purposes I am using Spring Paging and Sorting Repository. So far I have tried the SQL LIKE wildcard like so.

    public interface UserRepository extends JpaRepository<User, Integer> {
            //this is used for fetching phone numbers by userName, if user is admin I call findAll()
            Page<User> findByUserName(String userName, Pageable pageable);

            //this is used for fetching phone numbers by username by phoneNo LIKE
            Page<User> findByUserNameAndPhoneNumbersPhoneNoLike(String userName,String phoneNo, Pageable pageable);
    }

The problem is that the LIKE doesn't seem to work. No matter what, it returns all phone numbers associated with the user and it will ignore whatever I am passing in, hence "%987%". So if I have 2 phone numbers (987123 and 321432) associated with user X, it will return both instead of only "987123".

Doe's anyone have any idea how I can query this but in return to get the Spring pageable object? I think the query creation from method names is not powerful enough to fit the purpose but I still need the pageable object as all the rest of the implementation in the front end, service layer is based on this. Somebody in work suggested that I should use JPA criteria but I've no idea how to do it so in return I can get the pageable object

User class

@Entity
    @Table(name="USER")
    public class User implements Serializable{

        @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
        private Integer id;

        @NotEmpty
        @Column(name="USER_ID", unique=true, nullable=false)
        private String userName;

        @OneToMany(fetch = FetchType.EAGER)
        @JoinColumn(name="USER_ID")
        private Set<PhoneNumer> phoneNumbers;

        @NotEmpty
        @JsonIgnore
        @ManyToMany(fetch = FetchType.LAZY)
        @JoinTable(name = "USER_USER_PROFILE",
                joinColumns = { @JoinColumn(name = "USER_ID") },
                inverseJoinColumns = { @JoinColumn(name = "USER_PROFILE_ID") })
        private Set<UserProfile> userProfiles = new HashSet<UserProfile>();
        //Getters and Setters

PhoneNumber Class

@Entity
@Table(name = "PHONE_NUMBER")
public class PhoneNumber {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private long id;

    @Column(name = "PHONE_NUMBER")
    private String phoneNo;
    //Getters and Setters

Answer 1

Try containing keyword :

Containing
findByFirstnameContaining
… where x.firstname like ?1 (parameter bound wrapped in %)

Page<User> findByUserNameAndPhoneNumbersPhoneNoContaining(String userName,String phoneNo, Pageable pageable);

Docs : https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repository-query-keywords

Answer 2

First of all, client shouldn't be allowed to filter data. Data filtration logic should be implemented on server side. Therefore default findAll() should be overriden to incorporate this behavior as follows:

@Override
@Query(
        "select p from User u join u.phoneNumbers p where "
        + "u.userId = ?#{@userService.getUserId()} or "
        + "?#{@userService.isAdmin()}"
)
Page findAll(Pageable pageable);

userService would have generic structure as follows- fill in the functions with appropriate logic:

@Service("userService")
@Transactional
public class UserServiceJPA {

    public Integer getUserId(){
        /**
         * This function should return current logged in user's id from db
         */
    }
    
    public boolean isAdmin(){
        /**
         * This function should return true or false based on current logged in user
         */
    }
}

This completes the first part of your question.
For searching by partial phone number define, use the following code.

@Query(
            "select p from PhoneNumber p where "
            + "("
            + "     p.user.userId = ?#{@userService.getUserId()} or "
            + "     ?#{@userService.isAdmin()} "
            + ") "
            + "and "
            + "p.phoneNo like :#{#partialPhone}%"
    )
    @RestResource(path="partialPhone")
    Page findMatchingPhoneNumbers(
            @Param("partialPhone")String partialPhone,
            Pageable pageable);