Why does a query contained keyword IN return always an empty list?-CodePudding

There is an entity:

    @Entity  
    class User {  
        @Id  
        private Long id;  
        ...  
        @NotEmpty  
        @CollectionElement  
        private List<String> IPs;  
        // setters and getters  
    }

The table USER in database:

    ID | ...  
    -----  
    1  | ...  
    =====

The table USER_IPS in database:

    USER_ID | IPS  
    -----  
    1       | '127.0.0.1'  
    =====

The repository is:

    @Repository  
    public interface UserRepository extends JpaRepository<User, Long> {  
        @Query("SELECT u FROM User u "  
                  "WHERE :request IN u.IPs")  
        List<User> getUsers(@Param("requestIP") String iP);  
    }

When the repository method is called with "127.0.0.1" as an argument, it returns always an empty list while it had to return a User with id = 1 according to the data restored in the database.
What is the problem?

CodePudding user response：

you can use IN ELEMENTS or MEMBER OF

@Repository  
public interface UserRepository extends JpaRepository<User, Long> {  
    @Query("SELECT u FROM User u "  
              "WHERE :request IN ELEMENTS(u.IPs)")  
    List<User> getUsers(@Param("requestIP") String iP);  
}

or:

@Repository  
public interface UserRepository extends JpaRepository<User, Long> {  
    @Query("SELECT u FROM User u "  
              "WHERE :request MEMBER OF u.IPs")  
    List<User> getUsers(@Param("requestIP") String iP);  
}